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?

score 0 · Answer 1 · answered Mar 05 '14 at 05:32

0

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.

answered Mar 05 '14 at 05:32

Amit Agarwal

10,910
1
32
43

It is timing out, but it's not that big, all it does is record all the data in the spreadsheet into a data array, then it uses that array for the rest of the function. And all it has to do is check a couple cells from every row, it shouldn't take NEARLY as long as it does, I feel like something has to be wrong but I can't figure out what. As stated in the question I can watch the Calendar and watch the script slowly make its way from one line to the next. Seriously, I could update manually faster than the script goes. – Mark Kramer Mar 05 '14 at 05:33
It is making a connection to the Calendar service for every row and that could increase the execution time. – Amit Agarwal Mar 05 '14 at 05:35
Have you looked at the Execution Log in the Script editor? Does it show any errors? – Amit Agarwal Mar 05 '14 at 05:43
Hmm, after looking into it, it looks like it might be getting caught up on this line: `event.deleteEventSeries();` any idea why? – Mark Kramer Mar 05 '14 at 06:07
Also, I added a function and a line of code to make it skip the rows without a valid date. – Mark Kramer Mar 05 '14 at 06:07
You probably need to check if that returned event is not null before calling the delete method. – Amit Agarwal Mar 05 '14 at 06:10
Well, actually, I just ran the script with my new line that skips the lines without a date and it works fine. Except that the Event IDs aren't being recorded to the spreadsheet so it'll still create duplicate entries every time I run the script. – Mark Kramer Mar 05 '14 at 06:14
Nevermind, I just didn't see them because I had entered the wrong row. They were supposed to be in 48, not 28. I suppose it's fixed. Thanks! – Mark Kramer Mar 05 '14 at 06:20

Google Apps Script isn't finishing

1 Answers1