Google Spreadsheet Script problem - Error: Service Times Out: Apps Script

Question

I've been trying to whip up a quick google script to count rsvps for the invite response spreadsheet for a wedding. The script worked perfectly for a week as new entries were added to the spreadsheet, then suddenly stopped working with the following error message in each cell:

Error: Service Times Out: Apps Script

The script itself is simple. It queries the relevant column (there are multiple events) and then checks to see whether there is some response spefied by the user - "YES", "NO", or a blank, typically.

What does this error mean, and does anyone have any suggestions for fixes?

function sumRSVP(response, rsvpType) {
  var rsvpCol = 7;
  if (rsvpType == "rehearsal") rsvpCol = 8;  
  if (rsvpType == "brunch") rsvpCol = 9;

  var mySum = 0;

  var sh = SpreadsheetApp.getActiveSheet();
  for( i=2; i<177; i++){

    var rsvp = sh.getRange(i, rsvpCol).getValue();
    var nguests = sh.getRange(i, 6).getValue();
    if(nguests != "" && rsvp == response){
      mySum = mySum + parseFloat(nguests);
    }
  }

  return mySum;
}

Can you provide the code that calls this function? Some Timeout errors occur every now and then, the best thing to do sometimes is wait a couple of minutes and try again. — Eduardo

ScampMichael ScampMichael · Accepted Answer · 2012-05-11T20:46:01

Hopefully the wedding went well. This was asked some time ago but has been viewed over 300 times at this post and I believe is important:

Data should not be extracted from a spreadsheet in a loop. The data needed should be extracted in a batch to an array and the array evaluated in the loop.

See docs reference at: https://developers.google.com/apps-script/guide_common_tasks#OptimizeScripts

You can write scripts to take maximum advantage of the built-in caching, by minimizing the number of reads and writes. Alternating read and write commands is slow. To speed up a script, read all data into an array with one command, perform any operations on the data in the array, and write the data out with one command.

function sumRSVP(response, rsvpType) {
  var rsvpCol = 7;
  if (rsvpType == "rehearsal") rsvpCol = 8;  
  if (rsvpType == "brunch") rsvpCol = 9;

  var mySum = 0;

  var sh = SpreadsheetApp.getActiveSheet();
  // start at row 2 - uses columns 6-9
  var data = sh.getRange(2, 6, 177 - 1 , 4).getValues();
  for(var i=0; i<data.length; i++){

    var rsvp = data[i][rsvpCol - 6];
    var nguests = data[i][0];
    if(nguests != "" && rsvp == response){
      mySum = mySum + parseFloat(nguests);
    }
  }

  return mySum;
}

Google Spreadsheet Script problem - Error: Service Times Out: Apps Script

1 Answers