Google Apps Script - exceeded maximum execution time

Question

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

Answer 1

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:

1. 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().
2. 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:

• https://developers.google.com/apps-script/overview
• https://developers.google.com/apps-script/guides/sheets