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

The major issue here is that the script gets / sets value for each iteration. Instead, you could get the entire range, calculate, and then update the range to reduce the execution time. Here's a similar example from the Developers site.