Google Apps Spreadsheet Scripts Randomly Stopping?

Question

I have written a simple Google Apps Spreadsheet script that takes details in some rows and converts them to a single list; all quite straightforward. The issue is that the script keeps stopping at random points, and the loop never finishes - there is no error message, and the message at the top of the script editor still indicates it is running, when it has clearly stopped.

I initially thought maybe it was being throttled somehow, but the random stopping points (sometimes at source sheet row 6x, sometimes at 7x, sometimes well into the hundreds) and the lack of an error message suggests otherwise.

Can anyone assist me in working out why this is happening? Is it because I am running it from the script editor window? Are there any other reasons (network issues, maybe) that an apps script could stop running, and if so, can it be run in background or similar to prevent this?

Thanks!

function convertTrimsToList(){
  var ss = SpreadsheetApp.openById("[REDACTED]");
  var sheet = ss.getSheets()[0];
  var newss = SpreadsheetApp.create("MMT List");
  var newsheet = newss.getSheets()[0];

x = 1;
  z = 1;
  i = 1;
  q = 1;
  while (sheet.getRange(x,1).getValue() != "")
  {
    sheet.getRange(1,6).setValue("Processing row " + x);
    y = 5;
    while (sheet.getRange(x,y).getValue() != "")
    {
      sheet.getRange(1,7).setValue("Processing row " + y);
      if (x != 1)
      {
         if (y == 5 && sheet.getRange(x,1).getValue() != sheet.getRange((x-1),1).getValue())
        {
          i++
        } 
      }
      newsheet.getRange(z,1).setValue(i);
      newsheet.getRange(z,2).setValue(q);
      newsheet.getRange(z,3).setValue(sheet.getRange(x,y).getValue());
      z++
      y++
    }
    if (x != 1)
      {
        if (sheet.getRange(x,3).getValue() != sheet.getRange((x-1),3).getValue())
        {
          q++
        }
      }
    else
    {
      q++
    }
    x++
  }
}

score 2 · Answer 1 · answered Jul 24 '12 at 16:11

Your code is rather inefficient. Every call made to a service is expensive. This is probably why your code stops abruptly. A more efficient way would be to read all data into a 2D array and then after your two nested loops write back to the spreadsheet in one go.

score 0 · Accepted Answer · edited May 23 '17 at 10:34

0

It turns out that this is the same issue that was happening here: Exceeded maximum execution time in Google Apps Script - not sure why I didn't get an error message, but it seems to be the same problem, so I have refactored my code to rectify it.

edited May 23 '17 at 10:34

Community

1
1

answered Jul 24 '12 at 16:41

Harry

4,660
7
37
65

Google Apps Spreadsheet Scripts Randomly Stopping?

2 Answers2