Google Apps Script isn't finishing

Question

I'm currently trying to make a script that goes through my Google Spreadsheet and updates a Google Calendar with information from the Spreadsheet.

I based my code off of an answer found here.

Here is the code:

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "<ID>";
  var cal = CalendarApp.getCalendarById(calId);
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[9]);
    var title = row[19]+" - "+row[3]+" - "+row[1]+" - "+row[2];
    var id = row[28];
    // Check if event already exists, delete it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.deleteEventSeries();
      row[28] = '';  // Remove event ID
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    var newEvent = cal.createAllDayEvent(title, date).getId();
    row[28] = newEvent;  // Update the data array with event ID
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
};

It works for the most part. It goes through the spreadsheet and for the rows that have a date set, it adds a calendar entry with the right title.

However, it takes a long time to do so, I can watch the calendar and watch the script slowly add the events one by one. I thought it would be nearly instant. On top of that, it never actually finishes. I'm not sure if it can't finish or if it times out before it can, but it never does finish, it never reaches the part where it writes the Event IDs to the Spreadsheet, which is a problem, because without the eventIDs, the function will keep creating duplicate entries.

It might be relevant that not every row has a date set so some of the times the loop runs, no event gets created since the date is Null.

Am I missing something is something in my script running really inefficiently? How can I speed it up and make it reach the end?

Amit Agarwal Amit Agarwal · Accepted Answer · 2014-03-05T05:32:35

How big is the spreadsheet? If there are too many rows in the sheet, it is likely that the script is timing out.

I suggest that you create a time-based trigger that processes 'n' rows in a batch and once all the rows in the sheet have been processed, the trigger should delete itself.

Google Apps Script isn't finishing

1 Answers