Automate sync from Google Sheet to Google Agenda

Syncing a google sheet to your agenda can be useful and time-saving if you have a lot of events to add to the agenda at once. All can be done with google’s tools.

Here are the 4 simples steps that will have to do :

1. Create your spreadsheet in Google Sheets and add your events
2. Create a new Agenda
3. Create a script in Google Apps Script
4. Execute the script to synchronise

Format Google Sheets

Here is the format I will use :

Of course, you can have both the date and the time in the same cells, for example, “Wednesday, 1 December 2021 at 10:00:00”. Or maybe you don’t need to have a start time and end time, because your event is a full days event. We will just need to tweak a little the script depending on your needs.

Small tip, here is where you can choose the dates and times formats.

Creating an Agenda

Now let’s create the Agenda, on https://calendar.google.com/

We will need the Calendar ID, which can be found in the settings of the calendar in the “Integrate calendar” section.

Add a Script

Back to our Google Sheet. We can add a script by clicking Tools > Script editor

First, get our two components, the agenda and the sheet.

function myFunction() {
    var spreadsheet = SpreadsheetApp.getActiveSheet();
    var agenda = CalendarApp.getCalendarById("CALENDAR_ID");
}

Now, we have to retrieve all the events located in the columns from A to D, except the first row.

var sheetsEvents = spreadsheet.getRange("A2:D100").getValues();

Then we need for each of our events to add it to the calendar

// For each row of the Google Sheet (for each of our events)
for (x = 0; x<sheetsEvents.length;x++) {
    var event = sheetsEvents[x];
    
    // the date is in the column A
    // so it's at the first index of our row
    var day = event[0];
    
    // Extract the hours from the "Start time" & "End time" columns
    var startTime = new Date(event[1]).getHours();
    var endTime = new Date(event[2]).getHours();
    // Create date objects with the day of the event
    var startDate = new Date(day);
    var endDate = new Date(day);
    // Change the hours of the dates
    startDate.setHours(startTime);
    endDate.setHours(endTime);
    // The message that will be displayed in the Agenda
    var eventMessage = event[3];
    //create the event in the Agenda
    agenda.createEvent(eventMessage, startDate, endDate);
}

For full-day events, you can do :

agenda.createAllDayEvent(message, eventDate);
// If the event cover several days
agenda.createAllDayEvent(message, startDate, endDate);

Running the script

Simply hit the Run button at the top

When running the script for the first time, Google Apps Script will ask for permission to edit your calendar.

If there are no errors, your events should appear on your Agenda!

If you press the button a second time, the events will be double. We will fix this by adding a few lines of scripts, by first deleting all the events in the calendar before adding the new one.

Put these lines just before doing calling createEvent

// Get all events currently in the Agenda for that date
var calendarList = agenda.getEventsForDay(startDate);
// loop through the events
for (var i = 0; i < calendarList.length; i++) {
    //delete the event
    calendarList[i].deleteEvent();
}

For more details and information, see the documentation

Full Script

Here is the full script. Don’t forget to use your Calendar ID

function myFunction() {
  // Get the Google Sheet and The Google Agenda
  var spreadsheet = SpreadsheetApp.getActiveSheet();
  var agenda = CalendarApp.getCalendarById("CALENDAR_ID");
  // Get all the events in the columns from A to D
  var sheetsEvents = spreadsheet.getRange("A2:D100").getValues();
  // For each row of the Google Sheet (for each of our events)
  for (x=0; x<sheetsEvents.length;x++)
  {
      var event = sheetsEvents[x];
    // the date is in the column A
    // so it's at the first index of our row
      var day = event[0];
      // Extract the hours from the "Start time" & "End time" columns
      var startTime = new Date(event[1]).getHours();
      var endTime = new Date(event[2]).getHours();
      // Create date objects with the day of the event
      var startDate = new Date(day);
      var endDate = new Date(day);
      // Change the hours of the dates
      startDate.setHours(startTime);
      endDate.setHours(endTime);
      // The message that will be displayed in the Agenda
      var eventMessage = event[3];
      // Get all events currently in the Agenda for that date
      var calendarList = agenda.getEventsForDay(startDate);
      // loop through the events
      for (var i = 0; i < calendarList.length; i++) {
          //delete the event
          calendarList[i].deleteEvent();
      }
      //create the event in the Agenda
      agenda.createEvent(eventMessage, startDate, endDate);
  }
}

Automate the synchronisation

You can run your script automatically by using Triggers.

You can run your function each time the Google Sheet is edited, or you can define a frequency and run it each hour.

It is also possible to do the other way, and retrieve events from your agenda and save them in a Google Sheet. It could be useful to do some reports and analysis.