Runtime limit exceeded when I try to sum the values of a column according to the rows that satisfy the desired filters

Question

Let's say I want to filter the rows of a worksheet that have:

car in Column AF
home in Column E
work in Column B

And sum the values of Column V to know if it is above zero or not:

function fix_value(pg,sum,cell) {
  var ss = SpreadsheetApp.getActive().getSheetByName(pg).getRange(cell);
  if (sum > 0) {
    ss.setValue('on');
  } else {
    ss.setValue('off');
  }
}

function main_event() {
  var vls = SpreadsheetApp.getActive().getSheetByName('Sheet111').getRange('A14:A17').getValues();
  var comb = SpreadsheetApp.openById('XXXXXXXXXXXXXXXXXXXXXXXXXX');
  var rgs = comb.getSheetByName('Historic');
  var rgs_vls = rgs.getRange('A3:AF').getValues();

  var sum = 0;
  for (var i = 0; i < rgs_vls.length; i++) {
    if (
      rgs_vls[i][31] == vls[0][0] && 
      rgs_vls[i][4] == vls[1][0] && 
      rgs_vls[i][1] == vls[2][0]
    ) {
      sum += rgs_vls[i][21];
    }
  }
  fix_value('Sheet111',sum,'A10');
}

But my worksheet is very big and this analysis and this looping need to be run every 5 minutes.

As it takes a long time to finish the process, at certain times of the day the code ends up overflowing the execution time limit.

How to proceed in this case to end the high execution time problem?

Answer 1

This works for me:

function main_event() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet1');
  var vs = sh.getRange('A14:A17').getValues().flat();
  var ass = SpreadsheetApp.getActive();
  var ash = ass.getSheetByName('Sheet0');
  var avs = ash.getRange('A3:AF'+ash.getLastRow()).getValues();
  let sum = 0;
  avs.forEach((r,i) => {
    if(r[31] == vs[0] && r[4] == vs[1] && r[1] == vs[2]) {
      sum += r[21];
    }
  });
  if(sum > 0 ) {
    sh.getRange("A10").setValue('on')
  } else {
    sh.getRange("A10").setValue('off');
  }
}

Answer 2

In this answer, please think of this as my challenge about whether the process cost can be reduced. From currently the page has 288324 rows and 32 columns, how about the following modifications? In this modification, I modified your script by using the following references.

Modified script 1:

In this script, Sheets API is used. So, please enable Sheets API at Advanced Google services.

function main_event2() {
  var spreadsheetId = "###"; // Please set spreadsheet ID of "Historic" sheet.

  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet111");
  var [[a], [b], [c]] = sheet.getRange('A14:A16').getValues();
  var [bb, ee, afaf, vv] = Sheets.Spreadsheets.Values.batchGet(spreadsheetId, { ranges: ["'Historic'!B3:B", "'Historic'!E3:E", "'Historic'!AF3:AF", "'Historic'!V3:V"] }).valueRanges;
  var sum = afaf.values.reduce((res, [e], i) => {
    if (e == a && ee.values[i][0] == b && bb.values[i][0] == c) {
      res += Number(vv.values[i]);
    }
    return res;
  }, 0);
  sheet.getRange("A10").setValue(sum > 0 ? "on" : "off");
}

• When this script is run for the sample sheet with 288324 rows and 32 columns, the process cost was about 20 seconds. But, I cannot know your actual situation. So, please test it using your Spreadsheet.

Modified script 2:

In this script, the values are retrieved using the query language.

function main_event3() {
  var spreadsheetId = "###"; // Please set spreadsheet ID of "Historic" sheet.

  var sheet = SpreadsheetApp.getActive().getSheetByName("Sheet111");
  var [[a], [b], [c]] = sheet.getRange('A14:A16').getValues();
  var query = `SELECT V WHERE B='${c}' AND E='${b}' AND AF='${a}'`;
  var url = `https://docs.google.com/spreadsheets/d/${spreadsheetId}/gviz/tq?sheet=Historic&tqx=out:csv&tq=${encodeURIComponent(query)}`;
  var res = UrlFetchApp.fetch(url, { headers: { authorization: "Bearer " + ScriptApp.getOAuthToken() } });
  var [, ...ar] = Utilities.parseCsv(res.getContentText());
  var sum = ar.reduce((res, e) => res += Number(e), 0);
  sheet.getRange("A10").setValue(sum > 0 ? "on" : "off");
}

• When this script is run for the sample sheet with 288324 rows and 32 columns, the process cost was about 15 seconds. But, I cannot know your actual situation. So, please test it using your Spreadsheet.

References:

• Benchmark: Reading and Writing Spreadsheet using Google Apps Script (Author: me)
• Benchmark: Conditional Branch using Google Apps Script (Author: me)
• Benchmark: Process Costs for Retrieving Values from Arrays for Spreadsheet using Google Apps Script (Author: me)
• Method: spreadsheets.values.batchGet
• How to speed ​up the search data in sheet