Automate sync from Google Sheet to Google Agenda

  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

Creating an Agenda

Add a Script

function myFunction() {    var spreadsheet = SpreadsheetApp.getActiveSheet();
var agenda = CalendarApp.getCalendarById("CALENDAR_ID");
}
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];
//create the event in the Agenda
agenda.createEvent(eventMessage, startDate, endDate);
}
agenda.createAllDayEvent(message, eventDate);// If the event cover several days
agenda.createAllDayEvent(message, startDate, endDate);

Running the script

// 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();
}

Full Script

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

--

--

--

I work at yper. I’m a python developer, learning data science. I’ve made a www.blindfoldchesstactic.com app

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

How to read Business Insider Prime Articles for free

Disney Clone (React, Redux, Firebase)

Big O in small words

Angular Schematics: Integrating With Other Schematics

NestJS — Validate requests with custom decorators

AV Ecosystem Review: Adding NodeJS Tests

Prototype Design Pattern simplified

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Alexis Gomes

Alexis Gomes

I work at yper. I’m a python developer, learning data science. I’ve made a www.blindfoldchesstactic.com app

More from Medium

6 Integrated Development Environment For CodeIgnitor Web Development

How to Open a Speech that People Will Listen To

Worksection: Updated Gantt Chart, Tasks by people and Calendar

Mover.io vs. CloudFuze: A Better Mover.io Alternative for Business Migrations