Google Apps Script – Fix Maximum Execution Time Exceeded

google sheetsgoogle-appsgoogle-apps-script

I'm currently developing a new spreadsheet for my company to make one of the tasks we do on a weekly basis more easier – the way it works is data is imported into this spreadsheet and then the scripts are ran to generate the reports we use. Below is an example of one of the scripts that I've made for working out one part of the spreadsheet.

The data that is manually imported into the spreadsheet can contain up to 3000 cells of information hence the loop for checking the cells – however whenever this script is ran it will get up to row 1617 and then give me the error exceeded maximum execution time – is there anyway for me to avoid this problem as I have several over scripts that need to be ran afterwards to help generate the reports we use.

There is 6 sheets, each sheet has data entered depending on the value of the cell in the data that has been imported.

function WorkoutTotals() {
// -----------------------------------------------------------------------------
// This function is used for working out the totals of each centre
// -----------------------------------------------------------------------------
var value;
for (var j = 2; j < abignumber; j++) {
    var rawcentres = rawdata.getRange(j, 1);
    var rawcategory = rawdata.getRange(j, 6);
    switch (rawcentres.getValue()) {
    case centres[0]:
        centresheet = ss.getSheetByName(sheets[0]);
        switch (rawcategory.getValue()) {
        case "a1":
            range = centresheet.getRange(cramrow, 1)
            range.setValue(range.getValue() + 1)
            break;
        case "A2":
            range = centresheet.getRange(cramrow, 2)
            range.setValue(range.getValue() + 1)
            break;
        case "a3":
            range = centresheet.getRange(cramrow, 3)
            range.setValue(range.getValue() + 1)
            break;
        case "a4":
            range = centresheet.getRange(cramrow, 4)
            range.setValue(range.getValue() + 1)
            break;
        case "a5":
            range = centresheet.getRange(cramrow, 5)
            range.setValue(range.getValue() + 1)
            break;
        case "a6":
            range = centresheet.getRange(cramrow, 6)
            range.setValue(range.getValue() + 1)
            break;
        case "a7":
            range = centresheet.getRange(cramrow, 7)
            range.setValue(range.getValue() + 1)
            break;
        case "a8":
            range = centresheet.getRange(cramrow, 8)
            range.setValue(range.getValue() + 1)
            break;
        case "a9":
            range = centresheet.getRange(cramrow, 9)
            range.setValue(range.getValue() + 1)
            break;
        case "a10":
            range = centresheet.getRange(cramrow, 10)
            range.setValue(range.getValue() + 1)
            break;
        }
        break;
    case centres[1]:
        centresheet = ss.getSheetByName(sheets[1]);
        switch (rawcategory.getValue()) {
        case "a1":
            range = centresheet.getRange(cramrow, 1)
            range.setValue(range.getValue() + 1)
            break;
        case "A2":
            range = centresheet.getRange(cramrow, 2)
            range.setValue(range.getValue() + 1)
            break;
        case "a3":
            range = centresheet.getRange(cramrow, 3)
            range.setValue(range.getValue() + 1)
            break;
        case "a4":
            range = centresheet.getRange(cramrow, 4)
            range.setValue(range.getValue() + 1)
            break;
        case "a5":
            range = centresheet.getRange(cramrow, 5)
            range.setValue(range.getValue() + 1)
            break;
        case "a6":
            range = centresheet.getRange(cramrow, 6)
            range.setValue(range.getValue() + 1)
            break;
        case "a7":
            range = centresheet.getRange(cramrow, 7)
            range.setValue(range.getValue() + 1)
            break;
        case "a8":
            range = centresheet.getRange(cramrow, 8)
            range.setValue(range.getValue() + 1)
            break;
        case "a9":
            range = centresheet.getRange(cramrow, 9)
            range.setValue(range.getValue() + 1)
            break;
        case "a10":
            range = centresheet.getRange(cramrow, 10)
            range.setValue(range.getValue() + 1)
            break;
        }
        break;
    case centres[2]:
        centresheet = ss.getSheetByName(sheets[2]);
        switch (rawcategory.getValue()) {
        case "a1":
            range = centresheet.getRange(cramrow, 1)
            range.setValue(range.getValue() + 1)
            break;
        case "A2":
            range = centresheet.getRange(cramrow, 2)
            range.setValue(range.getValue() + 1)
            break;
        case "a3":
            range = centresheet.getRange(cramrow, 3)
            range.setValue(range.getValue() + 1)
            break;
        case "a4":
            range = centresheet.getRange(cramrow, 4)
            range.setValue(range.getValue() + 1)
            break;
        case "a5":
            range = centresheet.getRange(cramrow, 5)
            range.setValue(range.getValue() + 1)
            break;
        case "a6":
            range = centresheet.getRange(cramrow, 6)
            range.setValue(range.getValue() + 1)
            break;
        case "a7":
            range = centresheet.getRange(cramrow, 7)
            range.setValue(range.getValue() + 1)
            break;
        case "a8":
            range = centresheet.getRange(cramrow, 8)
            range.setValue(range.getValue() + 1)
            break;
        case "a9":
            range = centresheet.getRange(cramrow, 9)
            range.setValue(range.getValue() + 1)
            break;
        case "a10":
            range = centresheet.getRange(cramrow, 10)
            range.setValue(range.getValue() + 1)
            break;
        }
        break;
    case centres[3]:
        centresheet = ss.getSheetByName(sheets[3]);
        switch (rawcategory.getValue()) {
                    case "a1":
            range = centresheet.getRange(cramrow, 1)
            range.setValue(range.getValue() + 1)
            break;
        case "A2":
            range = centresheet.getRange(cramrow, 2)
            range.setValue(range.getValue() + 1)
            break;
        case "a3":
            range = centresheet.getRange(cramrow, 3)
            range.setValue(range.getValue() + 1)
            break;
        case "a4":
            range = centresheet.getRange(cramrow, 4)
            range.setValue(range.getValue() + 1)
            break;
        case "a5":
            range = centresheet.getRange(cramrow, 5)
            range.setValue(range.getValue() + 1)
            break;
        case "a6":
            range = centresheet.getRange(cramrow, 6)
            range.setValue(range.getValue() + 1)
            break;
        case "a7":
            range = centresheet.getRange(cramrow, 7)
            range.setValue(range.getValue() + 1)
            break;
        case "a8":
            range = centresheet.getRange(cramrow, 8)
            range.setValue(range.getValue() + 1)
            break;
        case "a9":
            range = centresheet.getRange(cramrow, 9)
            range.setValue(range.getValue() + 1)
            break;
        case "a10":
            range = centresheet.getRange(cramrow, 10)
            range.setValue(range.getValue() + 1)
            break;
        }
        break;
    case centres[4]:
        centresheet = ss.getSheetByName(sheets[4]);
        switch (rawcategory.getValue()) {
                    case "a1":
            range = centresheet.getRange(cramrow, 1)
            range.setValue(range.getValue() + 1)
            break;
        case "A2":
            range = centresheet.getRange(cramrow, 2)
            range.setValue(range.getValue() + 1)
            break;
        case "a3":
            range = centresheet.getRange(cramrow, 3)
            range.setValue(range.getValue() + 1)
            break;
        case "a4":
            range = centresheet.getRange(cramrow, 4)
            range.setValue(range.getValue() + 1)
            break;
        case "a5":
            range = centresheet.getRange(cramrow, 5)
            range.setValue(range.getValue() + 1)
            break;
        case "a6":
            range = centresheet.getRange(cramrow, 6)
            range.setValue(range.getValue() + 1)
            break;
        case "a7":
            range = centresheet.getRange(cramrow, 7)
            range.setValue(range.getValue() + 1)
            break;
        case "a8":
            range = centresheet.getRange(cramrow, 8)
            range.setValue(range.getValue() + 1)
            break;
        case "a9":
            range = centresheet.getRange(cramrow, 9)
            range.setValue(range.getValue() + 1)
            break;
        case "a10":
            range = centresheet.getRange(cramrow, 10)
            range.setValue(range.getValue() + 1)
            break;
        }
        break;
    case centres[5]:
        centresheet = ss.getSheetByName(sheets[5]);
        switch (rawcategory.getValue()) {
                    case "a1":
            range = centresheet.getRange(cramrow, 1)
            range.setValue(range.getValue() + 1)
            break;
        case "A2":
            range = centresheet.getRange(cramrow, 2)
            range.setValue(range.getValue() + 1)
            break;
        case "a3":
            range = centresheet.getRange(cramrow, 3)
            range.setValue(range.getValue() + 1)
            break;
        case "a4":
            range = centresheet.getRange(cramrow, 4)
            range.setValue(range.getValue() + 1)
            break;
        case "a5":
            range = centresheet.getRange(cramrow, 5)
            range.setValue(range.getValue() + 1)
            break;
        case "a6":
            range = centresheet.getRange(cramrow, 6)
            range.setValue(range.getValue() + 1)
            break;
        case "a7":
            range = centresheet.getRange(cramrow, 7)
            range.setValue(range.getValue() + 1)
            break;
        case "a8":
            range = centresheet.getRange(cramrow, 8)
            range.setValue(range.getValue() + 1)
            break;
        case "a9":
            range = centresheet.getRange(cramrow, 9)
            range.setValue(range.getValue() + 1)
            break;
        case "a10":
            range = centresheet.getRange(cramrow, 10)
            range.setValue(range.getValue() + 1)
            break;
        }
        break;
        }
    break;
    }
}
}

Best Answer

Key in working with Google Apps Script is to handle things in batches. API calls are time consuming and need to be kept at a bare minimum.

Try applying the following:

You're trying to retrieve appr. 3000 values by using getValue(). Better is to use the getValues() and add that to an array (all data that is !!). This minimizes API calls by 2999 times; var allData = SpreadsheetApp.getActiveSpreadsheet().getDataRange().getValues().
The same is applicable for the setValue(). Here, you can also use setValues() to batch that process and thus minimizing the API calls.

Furthermore, most of the script is basically the same. Why not squeeze that in a standardized piece of code?

References:

Related Solutions

Google-sheets – Pass range in Google Apps script custom function without using A1 notation

To @Jacob's claim of impossibility, I refute it thus... (but thanks for the improved speed)

using:

=sumIfBgColor("#ffffff", A1:A10, COLUMN(A1), ROW(A1))

with the following functions will do what you want.

/**
 * Sums cell values in a range if they have the given background color
 * 
 * @param  {String} color    Hex string of color eg ("#ffffff")
 * @param  {Array.Array} range    Values of the desired range
 * @param  {int} startcol The column of the range
 * @param  {int} startrow The first row of the range
 * 
 * @return {int}          Sum of all cell values matching the condition
 */
function sumIfBgColor(color, range, startcol, startrow){
  // convert from int to ALPHANUMERIC - thanks to 
  // Daniel at http://stackoverflow.com/a/3145054/2828136
  var col_id = String.fromCharCode(64 + startcol);
  var endrow = startrow + range.length - 1
  // build the range string, then get the background colours
  var range_string = col_id + startrow + ":" + col_id + endrow
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var getColors = ss.getRange(range_string).getBackgrounds();

  var x = 0;
  for(var i = 0; i < range.length; i++) {
    for(var j = 0; j < range[0].length; j++) {
      // Sometimes the cell background is eg 'white' rather than '#ffffff'.
      // I don't know why - I think it's a bug.
      // so we remove that inconsistency with colourNameToHex
      // courtesy of Greg at http://stackoverflow.com/a/1573141/2828136
      if(colourNameToHex(getColors[i][j].toString()) == color) {
        x += range[i][j];
      }
    }
  }
  return x;
}

/**
 * Takes a colour string and returns it to a hex string. If a non-matching string is
 * passed, it will return the argument as is - for this situation it means that a
 * hex string can be passed to it and be returned as is. This is not for production.
 * 
 * @param  {string} color    Must be either a colour name or hex string of color eg ("#ffffff")
 * 
 * @return {object|string}          hex string of color eg ("#ffffff") or the argument given.
 */
function colourNameToHex(colour)
{
    var colours = {"aliceblue":"#f0f8ff","antiquewhite":"#faebd7","aqua":"#00ffff","aquamarine":"#7fffd4","azure":"#f0ffff",
    "beige":"#f5f5dc","bisque":"#ffe4c4","black":"#000000","blanchedalmond":"#ffebcd","blue":"#0000ff","blueviolet":"#8a2be2","brown":"#a52a2a","burlywood":"#deb887",
    "cadetblue":"#5f9ea0","chartreuse":"#7fff00","chocolate":"#d2691e","coral":"#ff7f50","cornflowerblue":"#6495ed","cornsilk":"#fff8dc","crimson":"#dc143c","cyan":"#00ffff",
    "darkblue":"#00008b","darkcyan":"#008b8b","darkgoldenrod":"#b8860b","darkgray":"#a9a9a9","darkgreen":"#006400","darkkhaki":"#bdb76b","darkmagenta":"#8b008b","darkolivegreen":"#556b2f",
    "darkorange":"#ff8c00","darkorchid":"#9932cc","darkred":"#8b0000","darksalmon":"#e9967a","darkseagreen":"#8fbc8f","darkslateblue":"#483d8b","darkslategray":"#2f4f4f","darkturquoise":"#00ced1",
    "darkviolet":"#9400d3","deeppink":"#ff1493","deepskyblue":"#00bfff","dimgray":"#696969","dodgerblue":"#1e90ff",
    "firebrick":"#b22222","floralwhite":"#fffaf0","forestgreen":"#228b22","fuchsia":"#ff00ff",
    "gainsboro":"#dcdcdc","ghostwhite":"#f8f8ff","gold":"#ffd700","goldenrod":"#daa520","gray":"#808080","green":"#008000","greenyellow":"#adff2f",
    "honeydew":"#f0fff0","hotpink":"#ff69b4",
    "indianred ":"#cd5c5c","indigo ":"#4b0082","ivory":"#fffff0","khaki":"#f0e68c",
    "lavender":"#e6e6fa","lavenderblush":"#fff0f5","lawngreen":"#7cfc00","lemonchiffon":"#fffacd","lightblue":"#add8e6","lightcoral":"#f08080","lightcyan":"#e0ffff","lightgoldenrodyellow":"#fafad2",
    "lightgrey":"#d3d3d3","lightgreen":"#90ee90","lightpink":"#ffb6c1","lightsalmon":"#ffa07a","lightseagreen":"#20b2aa","lightskyblue":"#87cefa","lightslategray":"#778899","lightsteelblue":"#b0c4de",
    "lightyellow":"#ffffe0","lime":"#00ff00","limegreen":"#32cd32","linen":"#faf0e6",
    "magenta":"#ff00ff","maroon":"#800000","mediumaquamarine":"#66cdaa","mediumblue":"#0000cd","mediumorchid":"#ba55d3","mediumpurple":"#9370d8","mediumseagreen":"#3cb371","mediumslateblue":"#7b68ee",
    "mediumspringgreen":"#00fa9a","mediumturquoise":"#48d1cc","mediumvioletred":"#c71585","midnightblue":"#191970","mintcream":"#f5fffa","mistyrose":"#ffe4e1","moccasin":"#ffe4b5",
    "navajowhite":"#ffdead","navy":"#000080",
    "oldlace":"#fdf5e6","olive":"#808000","olivedrab":"#6b8e23","orange":"#ffa500","orangered":"#ff4500","orchid":"#da70d6",
    "palegoldenrod":"#eee8aa","palegreen":"#98fb98","paleturquoise":"#afeeee","palevioletred":"#d87093","papayawhip":"#ffefd5","peachpuff":"#ffdab9","peru":"#cd853f","pink":"#ffc0cb","plum":"#dda0dd","powderblue":"#b0e0e6","purple":"#800080",
    "red":"#ff0000","rosybrown":"#bc8f8f","royalblue":"#4169e1",
    "saddlebrown":"#8b4513","salmon":"#fa8072","sandybrown":"#f4a460","seagreen":"#2e8b57","seashell":"#fff5ee","sienna":"#a0522d","silver":"#c0c0c0","skyblue":"#87ceeb","slateblue":"#6a5acd","slategray":"#708090","snow":"#fffafa","springgreen":"#00ff7f","steelblue":"#4682b4",
    "tan":"#d2b48c","teal":"#008080","thistle":"#d8bfd8","tomato":"#ff6347","turquoise":"#40e0d0",
    "violet":"#ee82ee",
    "wheat":"#f5deb3","white":"#ffffff","whitesmoke":"#f5f5f5",
    "yellow":"#ffff00","yellowgreen":"#9acd32"};

    if (typeof colours[colour.toLowerCase()] != 'undefined')
        return colours[colour.toLowerCase()];

    return colour;
}

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

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.