Google Apps Script – Fixing Maximum Execution Time Exceeded

google sheetsgoogle-apps-script

My code for an API pull has been timing out a lot lately. Here is my code (note that I have changed some variables for privacy): 

// SHEET DATA
var ss = SpreadsheetApp.getActive();
var data_sheet = ss.getSheetByName('data');
var view_sheet = ss.getSheetByName('views');
var num_rows = view_sheet.getDataRange().getNumRows()

// API URL
var api_url = "url"
var api_url2 = "end of url"

// AUTH
var user = 'email'
var apikey = "apikey"
var token = user + "/token:" + apikey;
var encode = Utilities.base64Encode(token);
var params =
    {
      "method" : "get",
      "headers" :
      {
        "Content-type":"application/xml",
        "Authorization": "Basic " + encode
      }
    };

// calls the zendesk API for each view on the views tab, adds it's view count as a new row on the data sheet
function zdAPIcall() {

  // for date formatting
  var time = new Date();
  var time_utc = new Date(time.getUTCFullYear(), time.getUTCMonth(), time.getUTCDate(),  time.getUTCHours(), time.getUTCMinutes(), time.getUTCSeconds());

  // loops through views on views tab (excluding header row)
  for (i=2; i<=num_rows; i++) {

    // pulls the view ID
    var view_id = view_sheet.getRange(i, 1).getValue();

    // builds the API URL
    var full_api = api_url + view_id + api_url2;

    // finds the last row to add the value to
    var update_row = data_sheet.getLastRow() + 1;

    // hits the zendesk api
    var response = UrlFetchApp.fetch(full_api,
                                     params);

    // pulls the response as json, extracts the view count value
    var json = response.getContentText();
    var data = JSON.parse(json);
    var view_count = data['view_count']['value']

    // adds the data as a new row to the data sheet
    data_sheet.getRange(update_row,1).setValue(time_utc)
    data_sheet.getRange(update_row,2).setValue(view_id)
    data_sheet.getRange(update_row,3).setValue(view_count)
    Logger.log("row added!")

  };
};

Best Answer

It's better not to call setValue in a loop, but instead form a double array of values to be inserted, and put them all at once with setValues. This often helps with keeping resource use under quota.

However, in your case the main issue is not setValue but UrlFetchApp.fetch in a loop. Hitting some API a bunch of times in a loop is an expensive thing to do, both for your script and the API provider. The maximum execution time of Apps Script is 6 minutes, after which the script is terminated with the message you quoted. To avoid this, follow Anton Soradoi's advice:

  • Store the necessary information (i.e. like a loop counter) in a spreadsheet or another permanent store (i.e. ScriptProperties).
  • Have your script terminate every five minutes or so.
  • Set up a time driven trigger to run the script every five minutes(or create a trigger programmatically using the Script service).
  • On each run read the saved data from the permanent store you've used and continue to run the script from where it left off.

Terminating a script means recording the starting time start = Date.now(), and then checking in the loop whether you are running over 5 minutes (start+5*60*1000). If this happens, exit the loop (hence the script) but set a trigger to resume work in a few minutes. Since you already work with a spreadsheet and record the results there, it will not be difficult to find the place where the script should pick up the work after restart.

Related Topic