Sheets to Calendar Apps Script

Sheets Setup

We want a basic setup → event names, dates, times and a guest list. (video walkthrough at the end of this article 👇)

From this, we want to click a button and have these added to our Google Calendar.

Because of some quirks with using the CalendarApp Class in Apps Script and the desire to have the Sheets portion very user friendly by using drop down validation for dates and times in columns D, E and F, we need to combine those dates and times into useable date/time objects.

For the event Start and event End times, we concatenate the dropdown selected values.

Calendar Setup

In Google Calendar, open the calendar settings and scroll down till you see the integrate calendar section. Copy the calendar ID to use in Apps Script…

Open Apps Script from Google Sheets and input the code below using the calendar ID you copied in the CalendarApp.getCalendarById part.

function createCalendarEvent(){
    let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

    events.forEach(function(e){
          CalendarApp.getCalendarById("f7574e7b4d1ad00c9ecd7f1eba5bed329e8600e317cd387a400748d67f301d06@group.calendar.google.com").createEvent(
      e[0],
      new Date(e[1]),
      new Date(e[2]),
      {guests: e[6],sendInvites: true}
    );

}

This short script creates an events variable to begin with by getting all the values in our named range, “events”. This range is A3:B8 where all our events are, but you can make it as big a range as you’d like and all the values will pull into this variable which is an array of arrays.

Then in the forEach loop, we grab our calendar by its ID and plug in the name of the event, the start and end times and an optional argument of guests which will email the email addresses in our invite column.

Visualize each of our columns in our spreadsheet as an array. We access each element in it by using the e[##] syntax where e is the iteration of the forEach loop and ## is the number of the element we’re looking for.

Simple Automation

That’s all there is to it. Drop a drawing into the Google Sheet and add the script to it (the name of the function- in our case createCalendarEvent). Now by clicking the “button” the script will run, and all the events will be added to your calendar!

Hope this has been useful for you!

Please forward this newsletter, or this subscribe link, to anyone who would find it beneficial. Have a great one!

Here’s that video walkthrough too