Google Apps Script - Code optimization for writing to Google Calendar

Question

I'm currently writing a script that aims to write Google Calendars for 50 people over 6 months, at one event per day. These events can have different start/end times and be whole day events or not. The calendars will all be written on the same Google account, and users will subscribe to them using their personal accounts.

The data for the calendars is originally stored on a Google Spreadsheet. My script reads this spreadsheet in one step using getRange.getValues method, as recommended, but I can't find a way to "batch" the writing except by using a .createEvent method inside a for loop. This loop gets killed about 1/10th of the way through because it is too long to run (about 9000 calendar events for the whole batch, exceeds the 5 min range).

If I comment out the .createEvent line, the script runs completely just fine.

Is there a way to optimize writing to Google Calendar (passing an array to .createEvent or something else...) ?

Answer 1

There's no batch methods for the Calendar API. You have to insert one by one in the "slow" loop.

The solution I use in my scripts is to divide the load into "doable" chunks. You can save on each row if you have processed it or not, or you can save somewhere (e.g. a cell or script property) the row you stopped last. And then, configure your script to stop by itself when it has worked its share (e.g. 3000 events).

I'm not sure if you have seen it already, but there's a new dashboard where you can see that your quotas for the various APIs. It seems that after you solve this time limit quota, you'll run into the calendars. Good news is that this spread the load solution will also help you to deal with it.