0

I have used the following Apps Script code, inside a Google Sheet, for several months without problem.

Fired by a time time trigger at 9pm-10pm daily, it fetches each event from same day from my Google Calendar and adds a corresponding row with those details to a Google Sheet.

// Add Google Calendar events to Google Sheets.
// Sheet row additions to fire Zapier ClickTime actions.
// Script originally from https://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/

function caltest3(){
  //http://www.google.com/google-d-s/scripts/class_calendar.html#getEvents
  // The code below will retrieve events between 2 dates for the user's default calendar and
  // display the events the current spreadsheet
  var cal = CalendarApp.getDefaultCalendar();
  var sheet = SpreadsheetApp.getActiveSheet();

  // Today's date, via https://stackoverflow.com/questions/46548281/how-to-reference-todays-date-in-javascript
  var today = new Date();
  var dd = today.getDate();
  var mm = today.getMonth();
  var yyyy = today.getFullYear();


  // Use Google Calendar classes, https://developers.google.com/apps-script/reference/spreadsheet/sheet#appendRow(Object):

  // Get all events between this range
  var events = cal.getEvents(new Date(yyyy, mm, dd, 0, 0, 0), new Date(yyyy, mm, dd, 23, 0, 0));

  // For every event, 
  for (var i=0;i<events.length;i++) {

    // Calculate hour length of event
    var hours = Math.abs(events[i].getEndTime() - events[i].getStartTime()) / 36e5;

    // Combine elements of event
    // var details=[[events[i].getStartTime(),  events[i].getEndTime(), hours, events[i].getTitle(), events[i].getDescription()]];

    // Appends a new row with columns to the bottom of the spreadsheet containing the values in the array
    sheet.appendRow([events[i].getStartTime(),  events[i].getEndTime(), hours, events[i].getTitle(), events[i].getDescription()]);

  }
}

But, overnight, I received this message from Google...

Your script, Calendar Transfer, has recently failed to finish successfully. A summary of the failure(s) is shown below. To configure the triggers for this script, or change your setting for receiving future failure notifications, click here.

The script is used by the document Calendar Listings.

enter image description here Sincerely, Google Apps Script

The rows were last successfully added on Feb 15. There were no events present to add on Feb 16 or 17, so Feb 18 is the first failure.

What's going on here?

Is it anything related to the switch from Classic Calendar to New Calendar?

Or looks like it is having some trouble adding to Sheets?

Line 34 is the final row marked Appends a new row ....

Robert Andrews
  • 1,209
  • 4
  • 23
  • 47

1 Answers1

1

I searched on this site and googled for "service timed out" error. It looks that this error means that Google servers didn't responded fast enough.

NOTE: Google servers response time isn't deterministic. Sometimes they are faster than others and hopefully very rarely the are so slow that the "service timed out" error occurs.

One solution is to use the exponential backoff library from Bruce McPherson or something similar.

What is exponential backoff

This is recommended technique to use for calling services that are rate limited. They will be retried a few times if they are detected as having failed with errors that can be recovered from using a special wait algorithm. This is a much better technique than using Utilities.sleep between each call since it only waits if it needs to and therefore doesn't waste any execution time.

Another approach is to create a script variant to be ran when the time-driven triggered script fails.

References:

Community
  • 1
  • 1
Rubén
  • 34,714
  • 9
  • 70
  • 166