/ code

Google Apps Script - Scripting is Fun!

Just found out that I am able to script Google Spreadsheets! It is truly amazing that I can actually code my Spreadsheet using JavaScript.

I was having trouble tracking finances and I really wanted something that I can own. Hence I wrote some code! The code below is a Google Apps Script for the document in the link below:

https://docs.google.com/spreadsheets/d/1p8lcgAOWYFwxCdFFZtoaS1Cwv_tdhi3fIp9aRZgpwo8/pubhtml

The code is pretty straight forward. Pains the rows according what type of spending it is. And do some adding and subtracting.

function ColorifyExpenses() {

  allowed_monthly_expenses = [
    'TV',
    'Electricity',
    'Internet',
    'Rent',
    'House Insurance',
    'Car Loan',
    'Phone',
    'Natural Gas'
  ];

  warning_monthly_expenses = [
    'Groceries',
    'Car Gas',
    'Work - Food',
    'Feed Me Ninja Project'
  ];
  
  dangerous_monthly_expenses = [
    'Outing - Food',
    'International Phone'
  ]
  
  mandatory_monthly_expenses = [
    'Immigration',
    'Johns Server',
    'Credit Cart Payment'
  ]
  
  function contains(a, obj) {
    for (var ip = 0; ip < a.length; ip++) {
        if (a[ip] === obj) {
            return true;
        }
    }
    return false;
  }
  
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  
  for (var sheet_num = 0; sheet_num < sheets.length; sheet_num++)
  {
    var month = sheets[sheet_num].getSheetName();
    Logger.log(month)
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName(month);
    var rows = sheet.getRange('f10:j');
    var numRows = rows.getNumRows();
    var values = rows.getValues();
    
    var monthly_payments = 0;
    var mandatory_expenses = 0;
    var warning_expenses = 0;
    var dangerous_expenses = 0;
    
    //green background for allowed expenses
    for (var i = 0; i <= numRows - 1; i++) {
      var n = i + 10;
      if (contains(allowed_monthly_expenses, values[i][1])){
        sheet.getRange('f'+n+':j'+n).setBackground("#BDF4CB");
        monthly_payments = monthly_payments + values[i][2];
      }else if (contains(warning_monthly_expenses, values[i][1])){
        sheet.getRange('f'+n+':j'+n).setBackground("#FFFFB5");
        warning_expenses = warning_expenses + values[i][2];
      }else if (contains(dangerous_monthly_expenses, values[i][1])){
        dangerous_expenses = dangerous_expenses + values[i][2];
        sheet.getRange('f'+n+':j'+n).setBackground("#f0b2b2");
      }else if (contains(mandatory_monthly_expenses, values[i][1])){
        mandatory_expenses = mandatory_expenses + values[i][2];
        sheet.getRange('f'+n+':j'+n).setBackground("#57BCD9");
      }
    }
    sheet.getRange('G3').setValue(monthly_payments);
    sheet.getRange('G4').setValue(mandatory_expenses);
    sheet.getRange('G5').setValue(warning_expenses);
    sheet.getRange('G6').setValue(dangerous_expenses);
  }
}

So using this you will be able to color rows in selected sheets.

Have fun!

John Roach

John Roach

I am John Roach, a devops architect. Find more information here: https://johnroach.io/about/

Read More
Google Apps Script - Scripting is Fun!
Share this