Google Apps Script alternative to SUMIF

Question

I'm fairly new to JavaScript and am looking to progress my learning through making a few easy scripts that will help me automate some processes. My first script is looking to lookup the client's budget from a spreadsheet so I can then calculate whether the campaigns need pausing.

So far I've got this:

function main() {
  var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/sheetname/edit';
  var SHEET_NAME = 'Main';
  var clientName = 'Client';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

    var cell = sheet.getRange("L5");
    cell.setFormula('=SUMIF(C5:C100, "' + clientName + '", D5:D100)');
    Logger.log(clientName + ' budget is £' + cell.getValue());   
}

Where Column C = Client Name and D = Budget.

Would anybody be able to offer insight into perhaps a better way for me to check the clients budget without having to set the formula in the sheet?

Thanks in advance.

Answer 1

How about this modification?

Modification points :

• At first, retrieve values of "C5:D100".
• Calculate SUMIF() using the retrieved values.

Modified script :

function main() {
  var SPREADSHEET_URL = 'https://docs.google.com/spreadsheets/d/sheetname/edit';
  var SHEET_NAME = 'Main';
  var clientName = 'Client';

  var ss = SpreadsheetApp.openByUrl(SPREADSHEET_URL);
  var sheet = ss.getSheetByName(SHEET_NAME);

  var values = sheet.getRange("C5:D100").getValues();
  var res = 0;
  for (var i in values) {
    if (values[i][0] == clientName) {
      res += values[i][1];
    }
  }
  sheet.getRange("L5").setValue(res); // I'm not sure whether this is required.
  Logger.log(clientName + ' budget is £' + res);
}

If I misunderstand your question, I'm sorry.