David Stockdale's Scrapcode

Schedule Google & Outlook Calendar Events Using Google Sheets

I was tasked recently with figuring out a way to automatically schedule calendar events using a Google Sheet as a starting point.

This was something I had never done before and having found the guides to be mostly outdated and confusing here’s my much simpler step by step guide.

This guide will go through setting up a Google Calendar, a Google Sheet, Apps Script and even importing a Google Calendar into an Outlook Calendar.

Google Calendar Setup

First off you will want to create a new calendar on Google Calendar.

Simply click the “+” symbol and then “Create new calendar”.

It doesn’t matter what your calendar is named so long as the time zone is correct.

Now click the dots which appear when you hover over the name of your new calendar and select “Settings and sharing”.

Here you will find your “Calendar ID” which you will need in the Google Sheet setup.

Google Sheet Setup

You should set up your spreadsheet to look something like this (the exact columns and rows can be changed if you don’t want to just copy my code exactly but you will need start and end dates along with event names and a calendar I’d).

After filling in your spreadsheet click “Extensions” and open “Apps Script”.

This will open a new Apps Script project connected to this spreadsheet.

Now input this code (making changes if you didn’t copy the position of everything in my example) and save the project.

function scheduleEvents() {
  var ss = SpreadsheetApp.getActive();
  var spreadsheet = ss.getSheetByName("test");
  var eventCal = CalendarApp.getCalendarById(spreadsheet.getRange("B3").getValue());
  var list = spreadsheet.getRange("!A5:C").getValues();
  var filtered = list.filter(function(r){
    return r.join("").length>0;
  });
  Logger.log(filtered);
  for (x=0; x<filtered.length;x++)
  {
    var event = filtered[x];
    var startTime = event[0];
    var endTime = event[1];
    var details= event[2];
    eventCal.createEvent(details, new Date(startTime), new Date(endTime));
  }
}

Now simply run the project.

And you should find some new events in your calendar.

This code allows an unlimited amount of events so if this is all you want you’re done.

Or if you want a version of this script that allows the calendar events to be sent to multiple recipients check out my updated version here:

Updated Sheet to Calendar

Outlook Calendar Setup

Next if you want to convert your Google Calendar to an Outlook Calendar you first have to get your secret address.

You will need to once again hover over the name of your new calendar and select “Settings and sharing”.

Then copy the “Secret address in iCal format”.

Now in your Outlook Calendar right click “My Calendars” and under “Add Calendar” select “From Internet…”.

Here’s where you input your secret address.

This may take a minute.

And obviously when asked if you want to add this internet calendar to outlook and subscribe to updates you click yes.

Which again may take a minute or two.

Finally you should see a new calendar matching the earlier Google Calendar.

If you want to refresh the calendar quickly while testing the connection simply update the folder.

And remember to make sure that your Google Spreadsheet, Google Account, Google Calendar and Outlook Account are all in the same time zone!

Otherwise you may find your Outlook Calendar events 1 hour earlier than your Google Calendar events, or even your Google Calendar events 1 hour earlier than your Spreadsheet entries.

Note: For my fellow UK programmers I recommend these time zones:

  • Spreadsheet: GMT+00:00 London
  • Google Calendar: GMT+00:00 United Kingdom Time
  • Outlook/PC: UTC+00:00 Dublin, Lisbon, London + Adjust for daylight saving time automatically

Leave a Reply