Google Script - Internal Error after 15 seconds

问题:

So I am writing a script that we give the sum of all the data that has a specific tag in the same row.

Col 1  |  Col 2
-------+---------
grp1   |   2
grp1   |   1 
grp2   |   1
-------+---------

If I was to pass this function grp1 the result would be 3.

When I use this script over 1000 rows, I get an error "Internal Error Executing the custom function" after a short time (like 15 seconds). I thought it might be the timeout but it happens well before 30 seconds. Any ideas?

function collectgrpdata(group, startrow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var lastrow = sheet.getLastRow();
  var currentcell = sheet.getActiveCell();
  var col = currentcell.getColumn();
  var total = 0;

  for(var x = startrow;  x <= lastrow; x++) {
    var v = sheet.getRange(x, col).getValue();
    if(v != "" ) { 
      if (sheet.getRange(x, 2).getValue() == group) {
           total += v;
      }    
    }
  }
  return total
}

回答1:

Your problem is most likely due to the fact that you do so many calls to getRange and getValue.
You are probably hitting your quota limit of calls.

instead of doing that do one big call to get all the data and then work with that:

function collectgrpdata2(group, startrow) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var currentcell = sheet.getActiveCell();
  var col = currentcell.getColumn();
  var range = sheet
      .getRange(startrow, 
                col, 
                sheet.getLastRow() - startrow + 1, 
                sheet.getLastColumn() - col + 1)

  var data = range.getValues();

  return data
      .filter(function(row) {return row[0] === group;})
      .map(function(row) {return row[1];})
      .concat(0)
      .reduce(function(x,y) {return x+y;});
}