Google-sheets – How to solve error “Exceeded maximum execution time” in Google Sheets

google sheetsgoogle-apps-scriptgoogle-sheets-custom-function

I found a script which I really need (link):

function getTotalSum(cell) {
    var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    var sum = 0;
    for (var i = 0; i < sheets.length ; i++ ) {
        var sheet = sheets[i];
        var val = sheet.getRange(cell).getValue();

        if (typeof(val) == 'number') {
            sum += val;   
        }       
    }

    return sum;
}

but it gives an error (Exceeded maximum execution time) generally.

How can I solve this error?

Best Answer

I have tried the following custom function successfully up to 170 sheets:

function getTotalSum(startrow, startcol) {
  var ss = SpreadsheetApp.getActive();
  var start = new Date();   
  var numSheets = ss.getNumSheets(), sum=0;
  for(var k=1; k<numSheets; k++) {
    var data = ss.getSheets()[k].getDataRange().getValues();
    var value = data[startrow-1][startcol-1];
    if(typeof(value) == 'number') {
      sum += value;   
    }       
  }
  return sum;
}

Going beyond the 170, problems ariase: enter image description here

Therefore I created the code below that can be used for at least 750 sheets (but not to be used as a custom function).

Code

// global 
var ss = SpreadsheetApp.getActive();

function onOpen() {
  ss.addMenu("Sum", [{name: "Go !!", functionName: "getSum"}]);
}

function getSum() {
  // get user input
  var cell = Browser.inputBox('Enter A1 notation', 'like B21',
    Browser.Buttons.OK_CANCEL);

  // retrieve cell reference from a sheet and get row and col index
  var aCell = ss.getActiveSheet().getRange(cell.toString());
  var row = aCell.getRow()-1, col = aCell.getColumn()-1;

  // get number of sheets and set sum to zero
  var numSheets = ss.getNumSheets(), sum=0;

  // iterate through sheets, starting from second sheet
  for(var k=1; k<numSheets; k++) {
    // bulk load sheet at once 
    var data = ss.getSheets()[k].getDataRange().getValues();

    // get value from array
    var value = data[row][col];

    // valid value
    if(typeof(value) == 'number') {
      sum += value;   
    }       
  }

  // return value to active cell   
  ss.getActiveCell().setValue(sum);
}

Explained

The custom function isn't really meant to be used this extensively. Oddly, the custom function runs well within the 5 minutes of execution time for the 170 sheets (30s). The usage of .getDataRange().getValues() is slightly advantageous over the .getValue() (appr. 30s for 750 sheets).

Example

I've created an example file for you: 750 sheets
Create a copy and look for the extra pieces of code I added.

Related Topic