Google Calendar events to Google Sheers automatic refresh with onEdit trigger

Question

I am trying to grab time events from my Google Calendar into a Google Spreadsheet.

When a new time-event is created in my Google Calendar this event should be automatically synchronized into my Google Spreadsheet. This should be done automatically by an onEdit event trigger.

At the moment it is only running by refreshing the Google Spreadsheet.

Maybe someone has a better solution for my challenge. Here is my code:

function createSpreadsheetEditTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myCalendar')
      .forSpreadsheet(ss)
      .onEdit()
      .create();
}

function myCalendar(){

  var now=new Date();

 // Startzeit
  var startpoint=new Date(now.getTime()-60*60*24*365*1000);
  // Endzeit
  var endpoint=new Date(now.getTime()+60*60*24*1000*1000);

  var events=CalendarApp.getCalendarById("your-calendar-ID").getEvents(startpoint, endpoint);

  var ss=SpreadsheetApp.getActiveSpreadsheet().getSheetByName("TEST"); 

  ss.clear(); 

  for (var i=0;i<events.length;i++) {

         ss.getRange(i+1,1 ).setValue(events[i].getTitle());
         ss.getRange(i+1,2).setValue(events[i].getDescription());
         ss.getRange(i+1,3).setValue(events[i].getStartTime());
         ss.getRange(i+1,4).setValue(events[i].getEndTime());
   }
 }

Answer 1

enter code here
// There are can be many calendar in one calendar of user like his main calendar, created calendar, holiday, etc.
// This function will clear all previous trigger from each calendar of user and create new trigger for remaining calendar of the user.
function createTriggers() {
  clearAllTriggers();
  let calendars = CalendarApp.getAllCalendars();
  calendars.forEach(cal => {
 
ScriptApp.newTrigger("calendarUpdate").forUserCalendar(cal.id).onEventUpdated().create();
  });
}

/* This trigger will provide us the calendar ID from which the event was fired, then you can perform your CalendarApp and sheet operation. If you want to synchronize new update more efficiently then use Calendar Advance Service, which will provide you with synchronization token that you can use to retrieve only updated and added events in calendar.
*/
function calendarUpdate(e) {
  logSyncedEvents(e.calendarId);
}

function clearAllTriggers() {
  let triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => {
    if (trigger.getEventType() == ScriptApp.EventType.ON_EVENT_UPDATED) ScriptApp.deleteTrigger(trigger);
  });
}

Answer 2

Problem

Execute a function updating a spreadsheet when an event in Google Calendar is created.

Solution

Use the EventUpdated installable trigger that is fired each time an event is modified in Calendar (e.g. created, updated, or deleted - see reference). From there, you can go the easy way (update all data in the spreadsheet with a built-in CalendarApp class) or the hard way (update data that was changed with incremental sync - see official guide).

Part 0 - install trigger

/**
 * Installs Calendar trigger;
 */
function calendarTrigger() {
  var trigger = ScriptApp.newTrigger('callback name here')
  .forUserCalendar('calendar owners email here')
  .onEventUpdated()
  .create();
}

Part 1 - callback (Calendar -> Spreadsheet)

/**
 * Updates spreadsheet;
 * @param {Object} e event object;
 */
function updateSpreadsheet(e) {  
  //access spreadsheet;
  var ss = SpreadsheetApp.openById('target spreadsheet id');
  var sh = ss.getSheetByName('target sheet name');
  var datarng = sh.getDataRange(); //assumed that data is only calendar data;

  //access calendar;
  var calendar = CalendarApp.getCalendarById(e.calendarId);

  //set timeframes;
  var start = new Date();
  var end =new Date();

  //get year before and three after;
  start.setFullYear(start.getFullYear()-1);
  end.setFullYear(end.getFullYear()+3);

  //get events;
  var events = calendar.getEvents(start, end);

  //map events Array to a two-dimensional array of values;
  events = events.map(function(event){
    return [event.getTitle(),event.getDescription(),event.getStartTime(),event.getEndTime()];
  });

  //clear values;
  datarng.clear();

  //setup range;
  var rng = sh.getRange(1,1, events.length, events[0].length);

  //apply changes;
  rng.setValues(events);
}

Notes

1. As per Tanaike's comment - it is important to account for triggers (both simple and installable) to not firing if event is triggered via script or request (see restrictions reference). To enable such feature you will have to introduce polling or bundle with a WebApp that the script will call after creating an event (see below for a bundling sample).
2. Your solution is better suited for backwards flow: edit in spreadsheet -> edit in Calendar (if you modify it to perform ops on Calendar instead of updating the spreadsheet, ofc).
3. Make use of Date built-in object's methods like getFullYear() (see reference for other methods) to make your code more flexible and easier to understand. Btw, I would store "ms in a day" data as a constant (86400000).
4. Never use getRange(), getValue(), setValue() and similar methods in a loop (and in general call them as little as possible) - they are I/O methods and thus are slow (you can see for yourself by trying to write >200 rows in a loop). Get ranges/values needed at the start, perform modifications and write them in bulk (e.g. with setValues() method).

Reference

1. EventUpdated event reference;
2. Calendar incremental synchronization guide;
3. Date built-in object reference;
4. setValues() method reference;
5. Using batch operations in Google Apps Script;
6. Installable and simple triggers restrictions;

---

WebApp bundling

Part 0 - prerequisites

If you want to create / update / remove calendar events via script executions, you can bundle the target script with a simple WebApp. You'll need to make sure that:

1. The WebApp is deployed with access set as anyone, even anonymous (it is strongly recommended to introduce some form of request authentication);
2. WebApp code has one function named doPost accepting event object (conventionally named e, but it's up to you) as a single argument.

Part 1 - build a WebApp

This build assumes that all modifications are made in the WebApp, but you can, for example, return callback name to run on successfull request and handle updates in the calling script. As only the calendarId property of the event object is used in the callback above, we can pass to it a custom object with only this property set:

/**
 * Callback for POST requests (always called "doPost");
 * @param {Object} e event object;
 * @return {Object} TextOutput;
 */
function doPost(e) {
  //access request params;
  var body = JSON.parse(e.postData.contents);

  //access calendar id;
  var calendarId = body.calendar;

  if(calendarId) {
    updateSpreadsheet({calendarId:calendarId}); //callback;
    return ContentService.createTextOutput('Success');
  }else {
    return ContentService.createTextOutput('Invalid request');
  }
}

Part 2 - sample calling script

This build assumes that calling script and the WebApp are the same script project (thus its Url can be accessed via ScriptApp.getService().getUrl(), otherwise paste the one provided to you during WebApp deployment). Being familiar with UrlFetchApp (see reference) is required for the build.

/**
 * Creates event;
 */
function createEvent() {
  var calendar = CalendarApp.getCalendarById('your calendar id here');

  //modify whatever you need to (this build creates a simple event);
  calendar.createEvent('TEST AUTO', new Date(), new Date()); 

  //construct request parameters;
  var params = {
    method: 'post',
    contentType: 'application/json',
    muteHttpExceptions: true,
    payload: JSON.stringify({
      calendar: calendar.getId()
    })
  };

  //send request and handle result;
  var updated = UrlFetchApp.fetch(ScriptApp.getService().getUrl(),params);
  Logger.log(updated); //should log "Success";
}