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

LeetCode Algorithm Challenge: Plus One —I Wish It Could Be That Simple

Show the number of jobs available

Internationalazation in React Native App

Customize UI for Drone

What I have done in the 2019 year and I would like to do in the 2020 year

How to create Navbar component using React router dom v6 and React-sidenav library

MDX Embed for Storybook

MDX Embed logo

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

How to virtualize amd64 architecture on Apple M1

Alerting using SMTP

New Age Automation App to Clone/Copy Records in Dynamics 365 CRM with just 1 Click

How to retrieve Company info in Google Sheets with Clearbit and Apollo API