Google Apps Script - Exception: Service timed out: Spreadsheets

Question

First off, apologies if this is a poorly written post as this is my first time asking a question to SO.

I am working on a project that imports several different gCals and combines them into one sheet, then recreates them in a master combined calendar. This sheet will ultimately also have the ability to update event info in their respective calendars and thus on the combined calendar as well. However, I am running into this error on about 40% of my time based trigger executions for a function that imports both of the calendars, then recreates the events on the combined calendar and lastly logs the event creations by moving data from one sheet to another: "Exception: Service timed out: Spreadsheets".

I am trying to figure out any poorly optimized code (I am sure there is a lot, as I am not a developer and have just been piecing together my knowledge as I go), or if there are any changes I can make to the spreadsheet to speed up the processing, as the sheet moves very sluggish after successful execution, though that I believe is just due to the large amount of vlookups used in the sheet to reference different data sources.

Below is the code for importing the 2 calendars.

// Calendar 1 Import to sheets
function cal1SortSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cal1 Calendar Import");
  spreadsheet.sort(5, true);
};

function cal1_export_gcal_to_gsheet(){

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Backend");
var date1 = sheet.getRange('B6').getValue();
var date2 = sheet.getRange('B7').getValue();
var calendarId = sheet.getRange('B2').getValue();
var cal = CalendarApp.getCalendarById(calendarId);

var events = cal.getEvents(new Date(date1), new Date(date2));

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cal1 Calendar Import");
// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the getRange entry below
var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event","Event ID"]]
var range = sheet2.getRange(1,1,1,15);
range.setValues(header);

console.log('Cal1 start loop')
// Loop through all calendar events found and write them out starting on calulated ROW 2 (i+2)
for (var i=0;i<events.length;i++) {
var row=i+2;
var myformula_placeholder = '';

var details=[[calendarId,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent(),events[i].getId()]];
var range=sheet2.getRange(row,1,1,15);
range.setValues(details).sort(9);
}

var cell=sheet2.getRange(row,7);
cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
cell.setNumberFormat('.00');

//sort values by event start
Cal1SortSheet();
console.log('Cal1 start sort')

}

function cal2SortSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cal1 Calendar Import");
  spreadsheet.sort(5, true);
};

// Calendar 2 Import to sheets
function cal2_export_gcal_to_gsheet(){

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Backend");
var date1 = sheet.getRange('B6').getValue();
var date2 = sheet.getRange('B7').getValue();
var calendarId = sheet.getRange('B3').getValue();
var cal = CalendarApp.getCalendarById(calendarId);

var events = cal.getEvents(new Date(date1), new Date(date2));

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Cal2 Calendar Import");
// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the getRange entry below
var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event","Event ID"]]
var range = sheet2.getRange(1,1,1,15);
range.setValues(header);

console.log('Cal2 start loop')
// Loop through all calendar events found and write them out starting on calculated ROW 2 (i+2)
for (var i=0;i<events.length;i++) {
var row=i+2;
var myformula_placeholder = '';

var details=[[calendarId,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent(),events[i].getId()]];
var range=sheet2.getRange(row,1,1,15);
range.setValues(details).sort(9);
}

var cell=sheet2.getRange(row,7);
cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
cell.setNumberFormat('.00');

//sort values by event start
Cal2SortSheet();
console.log('Cal2 start sort')

}

function importBoth() {
  cal1_export_gcal_to_gsheet()
  cal2_export_gcal_to_gsheet()
}

Then here is the code for importing the combined calendar, to reference which events already exist vs which events are new (this is done within the spreadsheet with vlookups)

// Combined calendar Import to sheets
function combined_export_gcal_to_gsheet(){

var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Backend");
var date1 = sheet.getRange('B6').getValue();
var date2 = sheet.getRange('B7').getValue();
var calendarId = sheet.getRange('B1').getValue();
var cal = CalendarApp.getCalendarById(calendarId);

var events = cal.getEvents(new Date(date1), new Date(date2));

var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Combined Schedule Import");
// Create a header record on the current spreadsheet in cells A1:N1 - Match the number of entries in the "header=" to the last parameter
// of the getRange entry below
var header = [["Calendar Address", "Event Title", "Event Description", "Event Location", "Event Start", "Event End", "Calculated Duration", "Visibility", "Date Created", "Last Updated", "MyStatus", "Created By", "All Day Event", "Recurring Event","Event ID"]]
var range = sheet2.getRange(1,1,1,15);
range.setValues(header);

console.log('combined start loop')    
// Loop through all calendar events found and write them out starting on calculated ROW 2 (i+2)
for (var i=0;i<events.length;i++) {
var row=i+2;
var myformula_placeholder = '';


var details=[[calendarId,events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime(), myformula_placeholder, ('' + events[i].getVisibility()), events[i].getDateCreated(), events[i].getLastUpdated(), events[i].getMyStatus(), events[i].getCreators(), events[i].isAllDayEvent(), events[i].isRecurringEvent(),events[i].getId()]];
var range=sheet2.getRange(row,1,1,15);
range.setValues(details).sort(9);
}
var cell=sheet2.getRange(row,7);
cell.setFormula('=(HOUR(F' +row+ ')+(MINUTE(F' +row+ ')/60))-(HOUR(E' +row+ ')+(MINUTE(E' +row+ ')/60))');
cell.setNumberFormat('.00');

//sort values by event start
CombinedSortSheet();
console.log('combined start sort')  


}

And lastly, here is the code that creates events on the combined calendar by referencing a spreadsheet that pulls in both the calendar imports. After creation, the events are then logged in 2 respective sheets in their respective "archives".

function CombinedScheduleru24() {
  console.log('combined sched u24 start')
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Combined Scheduler u24");
  var calendarId = spreadsheet.getRange('C1').getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var lr = spreadsheet.getLastRow();
  var count = spreadsheet.getRange("B3:F"+lr+"").getValues();
  for (x=0; x<count.length; x++) {
          var shift = count[x];
      var summary = shift[0];
      var startTime = new Date(shift[1]);
      var endTime = new Date(shift[2]);
      var guests = shift[3];
      var description = shift[4];
      var location = shift[5];
     var event = {
         'location': location,
         'description': description,
         'guests':guests +',',
         'sendInvites': 'True',
}
     try {
      eventCal.createEvent(summary, startTime, endTime, event)
} catch(error) {
console.log(error.message, error.stack);
break;
}
console.log('combined sched u24 end')
}
} 

function CombinedSchedulerov24() {
  console.log('combined sched ov24 start')
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Combined Scheduler 24up");
  var calendarId = spreadsheet.getRange('C1').getValue();
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var lr = spreadsheet.getLastRow();
  var count = spreadsheet.getRange("B3:F"+lr+"").getValues();
  
  for (x=0; x<count.length; x++) {
          var shift = count[x];
      var summary = shift[0];
      var startTime = new Date(shift[1]);
      var endTime = new Date(shift[2]);
      var guests = shift[3];
      var description = shift[4];
      var location = shift[5];
     var event = {
         'location': location,
         'description': description,
         'guests':guests +',',
         'sendInvites': 'True',
}
     try {
  eventCal.createAllDayEvent(summary, startTime, endTime, event)
} catch(error) {
console.log(error.message, error.stack);
break;
}
console.log('combined sched ov24 end')
}
} 

function MoveDataCSu24() {
var ss = SpreadsheetApp.getActive();
var activeSheet = ss.getSheetByName("Combined Schedule Logger u 24hrs");
var appendSheet = ss.getSheetByName("Combined Schedule Archive");
var values = activeSheet.getRange("Combined Schedule Logger u 24hrs!A2:" + activeSheet.getLastRow()).getValues();
values = values.filter(e=>e[0]); //gets rid of blank rows. filters based on the first column (index 0).
appendSheet.getRange(appendSheet.getLastRow()+1,1,values.length,values[0].length).setValues(values);
}

function MoveDataCSov24() {
var ss = SpreadsheetApp.getActive();
var activeSheet = ss.getSheetByName("Combined Schedule Logger 24up");
var appendSheet = ss.getSheetByName("Combined Schedule Archive");
var values = activeSheet.getRange("Combined Schedule Logger 24up!A2:" + activeSheet.getLastRow()).getValues();
values = values.filter(e=>e[0]); //gets rid of blank rows. filters based on the first column (index 0).
appendSheet.getRange(appendSheet.getLastRow()+1,1,values.length,values[0].length).setValues(values);
}

function CombinedSchedulerALL() {
  CombinedScheduleru24()
  CombinedSchedulerov24()
  MoveDataCSu24()
  MoveDataCSov24()
}

So the final function that calls all this for the trigger looks like this:

function ImportAndCombinedSchedule() {
  importBoth()
  CombinedSchedulerALL()
  combined_export_gcal_to_gsheet()
}

I have tried splitting the spreadsheet computation up by using other projects and importing ranges to the master sheet, as well as cutting down on any extra cells that I can. However, the issue still persists.

I realize this is probably very poorly executed, so any help would be greatly appreciated!

Answer 1

SUGGESTION

Note: Stack Overflow community members do not offer coding services. Please utilize this answer as a reference guide while working on your actual project.

In my understanding of your post, you are having problems in running your script in a time based trigger execution as most of the time it produces Exception: Service timed out: Spreadsheets error.

Firstly, please note that triggers have more limitations compared to manually running the script from the Apps Script editor. As mentioned in Google's Quotas docs, "...This most commonly occurs for scripts that are triggered, as they have a lower daily limit than scripts executed manually.".

Looking at your script, you are calling Apps Script methods such as .setValues() and getRange() inside your loop, which is slowing down your script. I would highly recommend considering the use of batch methods for this purpose. This existing answer in a post will provide you with more insights about it. Hope this helps.