Increasing speed of script using cache

Question

I have a script that gets all the values of a spreadsheet and uses those values to create entries in a calendar.

However, it takes too long to run and times out.

It didn't used to take that long because there wasn't enough entries, but now there's enough entries that it cant finish before it times out, so I need to increase the speed.

I believe the reason it runs so slow is because there's a loop that runs through every row of the spreadsheet and at the end of every loop it writes a calendar event. I think it's this that adds to the execution time because it has to reconnect to the calendar over and over again. I think this massively adds to the execution time.

I believe I can reduce this with caching but I have not even the slightest clue how that works.

Here is my 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 = "trhcom7eiadkcn39mg9d0hfceg@group.calendar.google.com";
  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]);
    if (!(isValidDate(date))) continue; // Skip rows without a date
    var title = row[19]+" - "+row[3]+" - "+row[1]+" - "+row[2];
    var id = row[31];
    // Check if event already exists, delete it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.deleteEventSeries();
      row[31] = '';  // 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).addEmailReminder(4320).addEmailReminder(60).addSmsReminder(4320).addSmsReminder(60).getId();
    row[31] = newEvent;  // Update the data array with event ID
  }
  i=0;
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[13]);
    if (!(isValidDate(date))) continue; // Skip rows without a date
    var title = "Expected Pay Date: "+row[19];
    var id = row[32];
    // Check if event already exists, delete it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.deleteEventSeries();
      row[32] = '';  // Remove event ID
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    date.setDate(date.getDate() + 12);
    var newEvent = cal.createAllDayEvent(title, date).addEmailReminder(4320).addEmailReminder(60).addSmsReminder(4320).addSmsReminder(60).getId();
    row[32] = newEvent;  // Update the data array with event ID
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
};

I'm trying to use the information from this page but I don't even know where to begin.

Is the cache stored locally or on the server? How do I access it? What is a key and where do I find it? What url do I use? How will this end up increasing my speed?

I feel like this is simple but I just don't grasp the concept.

Update: After doing some research I'm not sure a cache can help me since it's not getting data that's taking a long time but rather creating it.

Maybe instead I should be trying to figure out a way to simply write all the events to the calendar at once at the end of the loop but I wouldn't know how to do that either.

Answer 1

Your question contains a lot of question but you already answered a couple of them yourself... ;-) it is indeed not a matter of reading sheet data (which you already do the right way using getDataRange().getValues() ) but a problem with the event creation that takes a long time...

Unfortunately there is no way to speed that up, the only thing we can do is proceed by reduced size batch and let the script run automatically every 10 minutes or so until all the events are created.

Nothing really complicated, here is a script that shows the process :

function createEvents() {
  // check if the script runs for the first time or not,
  // if so, create the trigger and PropertiesService.getScriptProperties() the script will use
  // a start index to know were from it has to continue

  if(PropertiesService.getScriptProperties().getKeys().length==0){ 
    PropertiesService.getScriptProperties().setProperties({'startRow':0 });
    ScriptApp.newTrigger('createEvents').timeBased().everyMinutes(10).create();
  }
  // initialize all variables when we start 
  var startRow = Number(PropertiesService.getScriptProperties().getProperty('startRow'));

  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 = "trhcom7eiadkcn39mg9d0hfceg@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  var counter = 0
  for (var i=tstartRow ; i < data.length ; i++) {
    counter++ ;
    if(counter == 30){ break }
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    ...
    ... continue your own code 
   }
   // update the spreadsheet
   // if i== data.length then kill the trigger and eventually send yourself a message 
   // to tell you that the script has finished successfully .
   // killing the current trigger goes like this :
   var trigger = ScriptApp.getProjectTriggers()[0];
   ScriptApp.deleteTrigger(trigger);

Good luck.