1

Using this great answer, I've managed to alter it to create a script to export events from a Google Spreadsheet to Google Calendar.

Create Google Calendar Events from Spreadsheet but prevent duplicates

I then got some great advice, and worked out that it wasn't populating the eventID column due to the error I was getting - "Exceeded maximum execution time" - due to the large number of rows (up to 1000).

Create Google Calendar events from a Google Spreadsheet - script is creating duplicates

I've been looking through answers to try and work out a way to get around this, but can't seem to work out an answer! Apologies - I'm quite new to all this.

Can anyone point me in the right direction, as to how I can either force the script to process beyond the 5 minutes, or anything else?

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 2;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "pma5g2rd5cft4lird345j7pke8@group.calendar.google.com";// use default claendar for tests
  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[12]);  // WHC
    var title = row[18];           // WHC Title
    var tstart = setTimeToDate(date,row[15]);// start time
    var tstop = setTimeToDate(date,row[16]);// end time
    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var id = row[17]; //EventID WHC
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');// this is to "force error" if the event does not exist, il will never show for real ;-)
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop); // create a "normal" event
      row[17] = newEvent.getId();  // Update the data array with event ID
      Logger.log('event created');// while debugging
      var event = cal.getEventSeriesById(row[17]);// make it an event Serie
    }
    event.setTitle(title);
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}
Community
  • 1
  • 1
Ingrid
  • 15
  • 2
  • 8

2 Answers2

1

The idea is to count the time the script is taking inside the main function and to interrupt it when we reach the limit.

We have to store the row number where we interrupt the script and continue from there on the next run.

Since we don't want to do that manually (how lazy we are :-) we'll set up a trigger to run it every 5 minutes.

Below is a full script. It will send you an email on each run to tell you the progress... you'll have to remove this line after your test of course (unless you like receiving emails every 5 minutes from yourself !)

You will have to change the calendar ID, the row distribution (I tested it on a sheet with less column than yours) but that will be fairly easy.

function createEventsWithBatch() {
  // 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 and a total counter for processed items
  // else continue the task
  if(PropertiesService.getScriptProperties().getKeys().length==0){ 
    PropertiesService.getScriptProperties().setProperties({'itemsprocessed':0});
    ScriptApp.newTrigger('createEventsWithBatch').timeBased().everyMinutes(5).create();
  }
  // initialize all variables when we start a new task, "notFinished" is the main loop condition
  var itemsProcessed = Number(PropertiesService.getScriptProperties().getProperty('itemsprocessed'));
  var startTime = new Date().getTime();
  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 = "h22nevo15tm0nojb6ul4hu7ft8@group.calendar.google.com";
  var cal = CalendarApp.getCalendarById(calId);
  for (var i = itemsProcessed ; i < data.length ; i++){
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = setTimeToDate(date,row[2]);
    var tstop = setTimeToDate(date,row[3]);
//    Logger.log('date = '+date+'tstart = '+tstart+'  tstop = '+tstop);
    var loc = row[4];
    var desc = row[5];
    var type = row[6];
    var times = row[7]
    var id = row[8]; 
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
      event.setTitle('got you');
    }catch(e){
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
      row[8] = newEvent.getId();  // Update the data array with event ID
//      Logger.log('event created');
      var event = cal.getEventSeriesById(row[8]);
    }
    event.setTitle(title);
    event.setDescription(desc);
    event.setLocation(loc);
    if(type=='PM'){
      var recurrence = CalendarApp.newRecurrence().addMonthlyRule().times(times);
      event.setRecurrence(recurrence, tstart, tstop);
    }else if(type=='PW'){
      var recurrence = CalendarApp.newRecurrence().addWeeklyRule().times(times)
      event.setRecurrence(recurrence, tstart, tstop);
    }
    data[i] = row ; 
    Logger.log(i+'    '+new Date().getTime()-startTime)
    if(new Date().getTime()-startTime > 240000){ // if > 4 minutes
      var processed = i+1;// save usefull variable
      PropertiesService.getScriptProperties().setProperties({'itemsprocessed':processed});
      range.setValues(data);
      MailApp.sendEmail(Session.getEffectiveUser().getEmail(),'progress sheet to cal','item processed : '+processed);
      return;
    }
  }  
  range.setValues(data);// this time we are done !
  killTrigger();// delete the trigger
  PropertiesService.getScriptProperties().deleteAllProperties(); // clean up properties
}

function setTimeToDate(date,time){
  var t = new Date(time);
  var hour = t.getHours();
  var min = t.getMinutes();
  var sec = t.getSeconds();
  var dateMod = new Date(date.setHours(hour,min,sec,0))
  return dateMod;
}

function killTrigger(){
  var trigger = ScriptApp.getProjectTriggers()[0];
  ScriptApp.deleteTrigger(trigger);
}
Serge insas
  • 45,904
  • 7
  • 105
  • 131
  • Thank you Serge! So from what it advised, I'd guess that getting it to interrupt itself every 125 lines would be safe... and excuse my confusion, but where would I use/put this info to fix the issue? – Ingrid Sep 14 '14 at 13:39
  • This code does not count rows, it counts the time taken...that's safer. This is a full code, just use it as it is or copy the relevant parts in yours. – Serge insas Sep 14 '14 at 16:56
  • Many, many thanks! Coming back and looking at it afresh suddenly made it all make perfect sense! You're the bomb. – Ingrid Sep 17 '14 at 14:10
  • Hi again Serge! Do you have any idea why running this script would be removing the formulas I have in the spreadsheet? ie. in rows 12, 13, 18, 19... like this: =if(E4="","",E4+1) =if(B4="","","WHC - "&B4) – Ingrid Sep 19 '14 at 03:05
  • Yes, it is normal, the script uses setValues on the whole sheet, overwriting everything and ignoring formulas... please raise another question about that specific situation and give an accurate description. – Serge insas Sep 19 '14 at 04:50
1

There are various GAS libraries that help you get past the 5 minute execution timeout, using the same idea as @Serge suggested. I personally use the Continuous Batch Library.

Andrew Roberts
  • 2,720
  • 1
  • 14
  • 26