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?

CodePudding user response：

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');
  }
}