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...

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.

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

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.

I promise it'll make more sense in a second! 😃

Calendar Setup

Let's make a new calendar in Google Calendar.

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.

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!

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

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:

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.

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.

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.