G Suite Pro Tips: how to automatically add a schedule from Google Sheets into Calendar

Editorial note: G Suite is now Google Workspace. Catch up on the latest with Google Sheets.

Between holidays and vacations, keeping track of your schedule (or your team’s schedule) can get tricky this time of year. For our latest G Suite Pro Tip, we explain how to automatically add a schedule from Google Sheets into a team Calendar. This tip is also handy if you need to auto-schedule reminders in Calendar based on tasks you have in a project tracker or spreadsheet.

Getting started in Apps Script

If you’re new to using Apps Script, it’s an easy-to-use, low-code platform that can help you tailor your G Suite applications. Don’t worry, you don’t need a Computer Science degree to get started. We’ll break it down for you step-by-step.

Before you dig into the code, we recommend that you reformat dates in your spreadsheet  to make it easier to program. Go to Format > Number > Date Time.

With that out of the way, we’re ready to port information from our Sheet into Calendar!

Coding is made simple when you break down the steps you need to complete a task into “sub-tasks.” We have four key actions that need to happen in order to sync data to a Calendar via code. Those sub-tasks are:

1. Identify the calendar
   
2. Import data from the spreadsheet
   
3. Create events
   

1. Make the script shareable for others to use

In this example, I set up skeleton code in Apps Script to make it easier for Kam to learn the G Suite service calls (you can see it in the video). Now we can input simple Apps Script calls to facilitate each sub-task!

Step 1: Identify the calendar

First, we need to decide which Calendar we want to add information into. In this example, we want to add information from a spreadsheet into a team calendar. We use SpreadsheetApp to retrieve information from the spreadsheet that we’re working in. Then, the code will help us retrieve the value of the calendar ID from the cell that it lives in.

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.

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.

Step 2: Select data from the Google Sheet

We need to identify the specific cells that we want to grab data from within our spreadsheet. In this Sheet, the shifts are in column A-C, rows 8-12.

We use this code to port over the data:

This command grabs the values living inside the range of cells from A8-C12. If you don’t call getValues(), your code will actually be working with the object of the cell, not the value inside of that cell. We want to work with the value.

Step 3: Create events

Now, we’re ready to use Apps Script to automatically create events in our team Calendar. This is another place where I set some skeleton code for Kam that uses a for loop. If you’re new to how loops work in programming, check out this post for a deeper explanation.

Essentially, when we import the skeleton code (see video), we create a data object that looks like this:

What we have here is a list of lists. Because a computer can only understand locations and values stored in those locations, this data object makes it easier for my skeleton code to iterate through each list and retrieve the values. The important point to grasp here is that: for each list, we have a value of the volunteer’s name, the start time and the end time for each shift.

Next we input this into Apps Script:

The idea here is that we have written something as simple as “Hey Calendar service, get this calendar and then make an event for each row of data.”

Step 4: Make the script shareable for others to use

As a bonus, I created a “Custom menu” in Apps Script so that Kam can share the script with the rest of the team (this way it doesn’t always have to be him 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 learn how I did this, check out this Medium post.

You can use Apps Script to automate lots of different tasks within G Suite, and ultimately, improve your workday by programming away tedious tasks. For inspiration, visit this website to see what you can build with Apps Script, or check out our YouTube channel for more Pro Tips.