How to Create Google Calendar Event from Google Sheets

Google Calendar can be connected with Google Sheets for event management. You need to use the Apps Script to automatically add an event from Google Sheets to Google Calendar.

Creating a single event in Google Calendar is pretty straightforward.

However, what if you have several tasks, meetings, and important events scheduled in a single week?

Adding all of them to your Google Calendar is going to be a tedious task.

In this article, we will learn to save a significant amount of time by adding events in bulk to Google Calendar using Google Sheets. It includes using the Apps Script to sync events from Google Sheets into the calendar. Make sure to read this article till the end, and don’t miss any critical steps.

Benefits of scheduling events from Google Sheets

Google Calendar is ideal when you have a single event.

Things get complicated and time-consuming while scheduling multiple events, as you have to follow the same process each time.

Quick scheduling: All you need to do is create a table for all your events in Google Sheets with start and end times and names. Then, once you connect Google Sheets with Calendar, you can schedule any number of events with just one click.
Team collaboration: Google Sheets is an online spreadsheet program that can be shared with unlimited users. You can add your colleagues, friends, and family members. They will be able to add the events to a table that is connected to Google Calendar.
Less time-consuming: It will take a lot of time for you and your teammates to manually add tons of events to a shared Google Calendar. Instead, you can create an event table in Google Sheets that you can quickly add to Google Calendar without much effort.
Ease of use: Using the Apps Script for connecting Google Sheets with Calendar doesn’t require expert knowledge. You can copy and paste a simple script from this article or lots of other online articles. Once the connection has been established, you can sync Google Sheets with Calendar with just one click.

Overall, it all boils down to saving your time by avoiding the repeated process of adding multiple events to Google Calendar.

The Apps Script is a powerful tool in Google Sheets capable of automating tasks.

It is used to develop rapid applications for advanced task management and organization in Google Sheets.

However, you should not be alarmed by this information because we have a little JavaScript code ready to link Google Sheets and Calendar. You just have to copy and paste it.

Steps to Create Google Calendar Event from Google Sheets

Though numerous third-party add-ons and service providers can be used to connect and sync data from Google Sheets to Google Calendar, we will focus on the built-in capabilities of Google Sheets.

We will use the Apps Script and simple code to schedule events.

It is a simple process and doesn’t require expert knowledge. So, beginners can follow it without any worry.

Step #1: Create a calendar

First, let us create a separate calendar for scheduling tasks, events, meetings, and more. We will be able to share it with any number of users, including your colleagues.

Open Google Calendar
Hover to the left side of the screen to see the calendar section
Click on the “+” icon beside the “Other calendar” as shown below
Choose the “Create new calendar” option from the popup
A set of new options will be displayed below
Enter the name of the Calendar and fill in the other details as per your preference
Click on the “Create calendar” button at the bottom

Step #2: Get the Calendar ID

Calendar ID is the most crucial thing for syncing Google Calendar data with Google Sheets.

Here’s how you can find it:

Go to the calendar section on the left side of the screen
Hover over the “Events Demo” calendar we created in the previous step
Click on the three dots as shown below
Select “Settings and sharing” from the popup
You will be taken to the edit window of the calendar
Scroll down until you see the section named “Integrate calendar”
Copy the Calendar ID

We will copy and keep this ID for future reference.

Step #3: Create an event table in Google Sheets

In this step, we will create an event table in Google Sheets, which will be shared with your teammates for future collaboration.

Open a new Google Sheet by clicking here
Add the header
Add personal or brand details
Next, we will paste the Calendar ID copied in the previous step
Create a table as below

(Note that the headers should be similar. We need Start Time, End Time, and Event Name)

Note the Event Start Time and Event End Time columns contain timestamps. It is nothing but the DateValue function in Google Sheets. It is the combination of date and time.

How to quickly create a timestamp in Google Sheets

Let’s discuss the fastest way of creating a timestamp in Google Sheets. You need to start by entering the desired date in an acceptable format. Refer to the following image.

Then, go to the main menu and click on the “Format” tab. Select the “Number” option from the popup. Next, choose “Date and time” from the list of available options.

Here is what the timestamp will look like:

Step #4 – Open Apps Script in Google Sheets

Once you create the table, you need access to Apps Script in Google Sheets. Follow the steps below:

Go to the main menu
Click on the “Extensions” tab
Select the “Apps Script” from the popup
A new tab will be opened in your current browser, as shown below

Step #5 – Copy and paste Apps Script Code

We won’t dive deep into the actual code and understand how each element will affect the syncing.

Instead, simply copy and paste the following code in Apps Script.

function scheduleShifts() {  var spreadsheet = SpreadsheetApp.getActiveSheet();  var calendarID = spreadsheet.getRange("B5").getValue();  var eventCal = CalendarApp.getCalendarById(calendarID);  var signups = spreadsheet.getRange("A8:C13").getValues();for (x=0; x<signups.length;x++){    var shift = signups[x];    var startTime = shift[0];    var endTime = shift[1];    var volunteer= shift[2];    eventCal.createEvent(volunteer, startTime, endTime);    }}

After copying the above code, go back to your Apps Script editor window and copy and paste the code as shown below.

Step #6 – Save the script

This is the crucial step without which you won’t be able to run the script.

You will find the Save icon in the toolbar section of the Apps Script window, as shown below.

Step #7 – Run the script

After saving the script, you will find that the range of options will be enabled in the toolbar section.

On the right side of the Save icon, you can find the Run icon. Refer to the following image.

Here are the steps to execute the code,

Click on the Run button
A new dialog box will be displayed as shown below,
The script is asking for permission to access the data from the Google Sheets. Click on the “Review permissions” button
Select your Google Account

(If you will be using multiple Google accounts in your browser, then please ensure to select the same Google account you are using for Google Sheets and Google Calendar)
In a new window, a warning will be displayed, as shown below
Click on the “Advanced” button
Choose “Go to Untitled project (unsafe)”
Grant further permissions by clicking on the “Allow” button, as shown in the following image

You will be taken back to the Apps Script tab of your browser. The execution will begin, and once it’s done, you will see the following message.

Step #8 – See the results

You can open your Google Calendar and check if the events from Google Sheets are added to the Google Calendar.

In the above image, three new events have been added on the 1st, 13th, and 30th of December, 2023.

It means that Google Sheets has successfully added the events to Google Calendar.

Step #8 – Create a button

Let’s create a button in the same spreadsheet where we will be adding the events.

This button will execute the script, and events will be added to Google Calendar.

Here are the steps to create a button in Google Sheets,

Go to the main menu
Click on “Insert” tab
Select the “Drawing”
You will see a new dialog box as shown below
Click on the Shape icon from the toolbar section
Select the first option, “Shapes” from the popup
Choose the second shape to create a rectangle shape as below
Draw a shape using your mouse cursor
Change the background and border color to green (Hex Code – #188038ff)
Next, click on the Text box icon from the toolbar section
Use your mouse cursor to place that text box over the rectangular shape, as shown below
Type “Upload”
Change the font color to white (Hex Code – #ffffff)
Click on the “Save and Close”