Creating Calendar events using Google Sheets data with AppScript

Among occasions and excursions, monitoring your schedule can get dubious every once in a while. 

Today I’m going to show you how to automatically add Meetings/Events data from Google Sheets into a Google Calendar. This trick came really handy for me so I hope this will serve you the same.

Ok, let’s skip long boring introductions and jump on to our Project.

So you might be probably thinking what would be the connection platform between these two right? Is that Python or any other programming language like JavaScript? It’s neither.

Have you ever came across Google’s App Script? If not, Take a 5minute Quickstart by clicking on this link, It's really worth it.

Google AppScript

Let’s take a look at the data that I’ve created in a google sheet.

Figure 1: Google Sheet Event Details

So I’ve got Six(6) Columns here and If you’re familiar with Calendar Events you know that these are the primary things that you have to enter when creating an event/meeting with your colleagues.

Meeting two(2) is a team meeting and the Google Calendar should make this with two attendees and need to be notified by Email.

Before you dig into the code, we recommend that you reformat dates in your spreadsheet to make it easier to program (In my case, It’s StartTime and EndTime). Go to Format > Number > Date Time.

Alrighty! Now, In order to sync these data into Google Calendar via code, we should perform the following tasks.

• Identify Which Calendar
• Import data from Spreadsheet
• Create Events
• Make the script available from the front end of the Google Spreadsheet

1: Identify the Calendar

Photo by Estée Janssens on Unsplash

First, we have to decide which calendar we want to use for our data importing purpose. In my example, I want to add these Meetings to my Primary Calendar because these are high-priority meetings at this moment.

1 Begin AppScript 

In your Spreadsheet, click on Tools > Script Editor, this will take you to a new AppScript Project and here we go! Create a new script and name it as CreateEvent.

We use SpreadsheetApp to retrieve information from the spreadsheet that we’re working in.

var spreadsheet = SpreadsheetApp.getActiveSheet();

Then, this code will help us retrieve the value of the calendar ID from the cell that it lives in. *Note in Figure1 My CalendarID at E4 Cell.*

var calendarId = spreadsheet.getRange('E4').getValue();

Next, to access our Calendar of choice, the call looks just like the one we used to open our spreadsheet. We use the CalendarApp service to open a calendar in our code.

var eventCal = CalendarApp.getCalendarById(calendarId);

Since we aren’t working within that calendar, we need its ID so that the Apps Script service can find the correct one. To find this, you have to go to the specific calendar’s settings page to get the accurate ID.

Copy Calendar CalendarID from here to a particular cell value.

2. Import data from Spreadsheet

If you have specific cells that we want to grab data from within our spreadsheet. Let’s say for an example you have data in column A-C, rows 8–12. Then you can use a code like this:

var signups = spreadsheet.getRange("A8:C12").getValues();

But, In my case, I might’ve add more meetings and events to my Spreadsheet (Simply, I don’t know how many meetings I have to create so that might be only one or couple of dozen meetings so I don’t want to hard code that range in to my code) so I want my code to figure out the last row that has data in it and I’ll pass that value into our getRange(“X:Y”) method.

var lr = spreadsheet.getLastRow();

var count = spreadsheet.getRange("A8:F"+lr+"").getValues();

3. Create Events

I'm going to use a for-loop to iterate through all the rows and columns. I’ve enabled ‘sendInvites’: ‘True’ because I want my code to send an email notification to every individual attached to a particular event.

for (x=0; x<count.length; x++) {
          var shift = count[x];
      
      var summary = shift[0];
      var startTime = shift[1];
      var endTime = shift[2];
      var guests = shift[3];
      var description = shift[4];
      var location = shift[5]; 
  
      var event = {
          'location': location,
          'description': description,
          'guests':guests +',',
          'sendInvites': 'True',
}

Now there are TWO inbuilt methods in AppScript to create an event.

1.createEvent(title, startTime, endTime)

Now note that these 3 variables are acting as basic parameters for our CreateEvent method and there’s no way for us to direct pass Attendee list, description, and location of the events. To do that we have to use this other method: Click here to learn more about Calendar Class.

2.createEvent(title, startTime, endTime, options)

We’re using the second method for our Event Creation:

eventCal.createEvent(summary, startTime, endTime, event)

Alright so we’re all good to go at the moment. So Just run the AppScript and you’ll see all the events are getting created in less than a second! Whoosh!

Photo by Bill Jelen on Unsplash

Let’s kick open our Calendar now. :)

Figure 2: Created meetings using AppScript

Let’s click on Meeting 2 to see the details of it because I was wondering whether the next Person’s email is also included in the event or not.

Ok, Fair Enough!

This is the screenshot of the email notification the other person received (It’s also me. Duh! My personal email).

Email Notification

Ok, We’re 90% Done here. Time for a little celebration!

Photo by Jayson Hinrichsen on Unsplash

---

Let’s take a final step and this will all be over in a minute.

4. Make the script available from the front end

As a bonus, I created a “Custom menu” in Apps Script so I can share the script with the rest of the team (this way it doesn’t always have to be me to run the script). When you create a custom menu, it appears in your Sheets app as an option on your toolbar. This makes it possible to run the same script in Apps Script as easily as clicking File > Print

To do that open a new script file in AppScript and write the following Code:

function onOpen() {

  var ui = SpreadsheetApp.getUi();

  ui.createMenu('Sync to Calendar')
      .addItem('Create Events Now', 'CreateEvent')
      .addToUi();

}

Simply run this function and go back to your sheets and you’ll find a brand new Menu Item right next to ‘Help’. The one we’ve created!

Custom Menu ‘Sync to Calendar’

Simply click on Sync to Calendar > Create Events Now. PIECE OF CAKE!

That’s it. So Thank you very much for reading this post till the end, Click here to access the full code for CreateEvent function. If you run into any kind of trouble while coding please let me know as a response and I’ll be more than happy to answer. Meantime, Check how I used Python to Access Google Calendar Data and Google SpreadsheetData.

---

Also, Contact me if you want to develop this for your organization (Providing iOS and Android app to update the google sheet from your mobile device as well.)

 

Keen on getting to know me and my work? Click here for more!

Dilan Jayasekara
Technology-wise, I'm passionate about software design and development and data analytical perspective pieces of works…www.djayasekara.com

If you stayed till the end, Here are two of my favorite articles. Who knows! Maybe you'd enjoy!

Becoming a Data Scientist with Online Education (Zero to hero )
Text Mining and Natural Language Processing Here's the perfect Beginner Guide for NLP: Random Updates…www.jayasekara.blog

How to think like a Data Scientist (Secret to a data-driven mindset)
If you have read my previous article "Becoming a Data Scientist (Zero to Hero)", Consider this as the second part of…www.jayasekara.blog