3

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?

  • 1
    A Google apps script can only run for 5 minutes, it will terminate with that error after that. Do you have very many sheets? – Punchlinern Apr 19 '14 at 23:57
  • unfortunatly yes i have many sheets and i have to add every day one more – user66322 Apr 21 '14 at 13:39
  • 1
    Then it might be hard to get around. Here's a simiar question, you might have some use of the two highest voted answers. – Punchlinern Apr 21 '14 at 14:18
  • How many sheets do you have? – Jacob Jan Tuinstra Apr 22 '14 at 9:56
  • now i have more than 150 – user66322 Apr 22 '14 at 14:47
1

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.

  • Jacob Jan Tuinstra thank you very much for your help. especially your first answer solve my problem. – user66322 Apr 30 '14 at 17:03
  • @Jacop when i use first of your answer (custom function) it gives an error 'Type Error: Cannot read property 'NaN' of undefined (row: 7, file: "Kod")'. this line (var value = data[startrow-1][startcol-1];) what is the wrong? – user66322 Oct 19 '14 at 13:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.