In this article we'll link two Google services -> Google Sheets and Google Calendar.

By using a very short custom function in Google Apps Script, we can add a list of events from a Google Sheet to a Google Calendar. 🀯

And we'll even have it email our guests as well. πŸ”₯

Here's the video walkthrough to accompany the article:

Google Sheets Setup

Our sheet is quite straightforward. We have event names, dates, start times, end times and guest emails.

The only curious thing is the formatting of our dates and times - I'll cover this as we go on, but you can see that columns B and C are repeating information from columns D, E and F...

Screenshot of Google Sheet event information

Google Calendar needs to receive the start and end times in the form of a full date/time object. But in the Google Sheet, there's not an easy way to create a dropdown data validation for users to select a date/time object.

In column D, I've put data validation to select a valid date.

Screenshot of data validation for a valid date

And in columns E and F, I've created a dropdown list of valid times.

Screenshot of data validation for a valid time

Columns B and C combine these together into a format that's useable to send to Google Calendar by using the =TEXT() function to concatenate the date and times together.

Screenshot of google sheets text concatenation

I promise it'll make more sense in a second! πŸ˜ƒ

Calendar Setup

Let's make a new calendar in Google Calendar.

Screenshot of new calendar options

Underneath your calendars on the left sidebar of Google Calendar, click the plus icon to add a new one.

Give it a name and a description if you want, and then we're ready to roll.

Screenshot of creating a new calendar

Scroll down a bit in the calendar's settings to the Integrate Calendar section. Copy the calendar ID. This is how we'll get Apps Script talking to Calendar!

Screenshot of calendar ID

Apps Script + CalendarApp

Apps Script is awesome. πŸ‘

The Class CalendarApp allows a script to access a user's Google Calendar and make changes to it.

Here is the full script, and we'll walk through what's going on below.

// Creates an events variable which is an array of arrays
function createCalendarEvent() {
	let events = SpreadsheetApp.getActiveSpreadsheet().getRangeByName("events").getValues();

	// Creates an event for each item in events array
      	new Date(e[1]),
      	new Date(e[2]),
      	{guests: e[6],sendInvites: true}

I've named the range A3:B8 as "events". Then in Apps Script, we create a variable named events that grabs all the values in that whole range. We used a small range, but you could make this as many rows long as need be.

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

Then, we loop through each item and add the events to our calendar.

The first part is where we use that calendar ID string we grabbed from Google Calendar for the getCalendarById method.

Then we use the createEvent method to pull data from each row in our Google Sheet and make new events.

Here's the createEvent description from the developers page:

Screenshot of createEvent method

You can think of each row of data in the Google Sheet as an array of values. In the zero position is the event name, in the one position is the event date and start time, and so on.

Screenshot of array of arrays illustrated

By using e[0] we can access the element that is in the zero position for every time we loop through the forEach loop...effectively looping through each row of data.

And this is where the funky stuff we did with the start and end times comes into play.

Because the values in columns B and C are strings since we concatenated them together, we need to turn them back into complete date objects now.

That's why we're passing new Date(e[1]) and new Date (e[2]) into our createEvent function.

It's a bit of a cumbersome way to allow ourselves to use those dropdown selections in Google Sheets rather than painfully typing in a full date/time object.

User experience > code. πŸ‘

And lastly, we add an optional parameter to send invites to guests.

Send with Button

That's all there is to the Apps Script. πŸŽ‰

As an added feature, we've attached a script to the rounded rectangle drawing to make it function like a button. Any time this is pressed, the events in the Google Sheet will populate the Google Calendar.

Screenshot of assigning a script to a drawing in Google Sheets

I hope this has been useful for you!

Please come check out and subscribe to my YouTube channel where I'm making weekly videos on coding and spreadsheets.

If you'd like my newsletter in your inbox, check it out here.