In this article:

Export Google Calendar to Google Sheets (Easiest Way in 2024)

In this article we will show you how to export google calendar to google sheets in just a few clicks using google apps script. Simply follow the steps below.

How to Export Google Calendar to Google Sheets

1. Add The Start and End Dates to the Sheet

The Script we will use for exporting Google Calendar data to Google Sheets requires us to set  cells for starting date and end date for the calendar entries we want to export.

export google calendar to google sheets, start and end dates added to sheet

In the example we have added the labels on the following cells:

A3: “Starting date”

C3: “End date”

You can now add the starting date to cell B3 and the end date to cell D3.

For our example, we set the starting date to October 9, 2022 and the end date to October 24, 2022. Our script will use these B3 and D3 Values later.

This means you can add additional information in the first two rows. You can use these rows to add headers to your sheet, for example. 

While adding information to the sheet, make sure you keep Row 5 and below empty as the code that we will be using will list the Google Calendar events starting from Row 5. 

2. Click Extensions then select Apps Script

The Extensions is found along the main menu at the top of the page. 

How to export google calendar to google sheets, click extensions, select apps script

The main page of Google Apps Script will be loaded in a new tab.

How to export google calendar to google sheets, google apps script loaded

3. Copy The Script to Google Apps Script

Copy the script from the following page:

https://gist.github.com/rvbautista/60674ec7f4b4bd1e1efff6c562992fe9

Script to use to connect google calendar to google sheets

Go back to the Apps Script tab and paste the code. 

Script to use to connect google calendar to google sheets pasted to google apps script

The script will automatically detect the Google account you are using and read the starting and end dates stored at cells B3 and D3. If you want to change the cells, check the FAQs at the end of this tutorial. 

4. Save and Authorize Project

Click the Save project button to save the script. 

export google calendar to sheets, google apps script, save project

Afterwards, click Run the selected function button. This is for us to check if the script is working correctly. 

export google calendar to sheets, run the selected function 

As this is the first time you are running the script, Google Apps Script will ask for authorization to access your data. Click Review permissions.

Authorization required to connect google calendar to google sheets

A new window will appear where you will be asked to select the Google account to use. Click the right Google account from the list.

connect google calendar to google sheets, choose account to use the method


Another page will load reminding you that Google hasn’t verified the code yet. Click Advanced

how to export google calendar to google sheets, google has not verified the app


A link labeled Go to Untitled project (unsafe) will appear. Click it. 

how to export google calendar to google sheets, google has not verified the app

Another page will be loaded listing the permissions needed by the code. Click Allow

how to export google calendar to google sheets, permissions necessary listed

The pop-up window will disappear. An Execution log will appear below your code. Two messages should appear: Execution started and Execution completed. 

how to export google calendar to google sheets, execution log

Once you see these messages, go back to your active sheet and you will see your google calendar data has been exported. (Calendar entries redacted in the example below.)

How to export google calendar to google sheets, results redacted

5. Add a Button

A button will help you export Google Calendar data whenever you need the latest data. It can be an image or a drawing you draw by yourself. Here are the steps:

1. Click Insert on the main menu, then click Drawing in the drop-down list. 

2. Click the Shape option.

3. Several types of shapes will appear. Select your preferred shape. For this example it’s Rounded Rectangle.

4. Draw it across the canvas to whatever size you want it to be.

5. Click the Text box option to add a textbox over the button. Drag it to a certain size within the shape and then type the label that you want. For this example the label will be Load Calendar.

6. Click Save and Close.

This inserts a button to the sheet. 

export google calendar to google sheets, button for loading added


You can get more details about this step here. 

6. Connect Script to the Button

It is not efficient to always open Google Apps Script anytime you need to load entries from your Google Calendar to Google Sheets. This is why we added a button in Step 5. Now is the time to connect the script to the button. Click the button.

Button selected to connect google calendar to google sheets

A set of three dots will appear on its upper-right corner. Click it. 

Button selected to connect google calendar to google sheets, right-click

A set of options will appear. Click Assign script. A box will appear prompting you to identify the script to assign to the button. Type export_gcal_to_gsheet, then click OK.

Assign script name to connect google calendar to google sheets

Once you click the button, the calendar will be exported to Google Sheets. 

FAQs

How Can I Set the Start and End Dates to Different Cells?

You just need to edit the script. The only line you need to modify is Line 9:

Original script to export google calendar to google sheets

The original line is

var events = cal.getEvents(new SpreadsheetApp.getActiveSheet().getRange('B3').getValue(), new SpreadsheetApp.getActiveSheet().getRange('D3').getValue(), {search: '-project123'});

Just change B3 to the cell where you want to store the start date and D3 to the cell where you want to store the end date. For example, imagine that you store the start and end dates to B1 and B2. Then that line will become:

var events = cal.getEvents(new SpreadsheetApp.getActiveSheet().getRange('B1').getValue(), new SpreadsheetApp.getActiveSheet().getRange('B2').getValue(), {search: '-project123'});

Make the changes then save the script.  When you run the script, the dates stored in B1 and B2 will be read.

We hope this article has helped you and given you a better understanding of how to export Google Calendar to Google Sheets. You might also like our articles on how to convert time to decimal in Google Sheets and how to add script to Google Sheets.

To optimize your workflow, we recommend reading our guide on how to get email notifications when a sheet is updated in Google Sheets and trying our software to help you manage expiration dates.

Automate repetitive tasks with Lido

Save hours on repetitive and tedious work. Lido is a new spreadsheet that connects your spreadsheets, forms, PDFs, and email inbox.

Trigger
Action
Select trigger
When a cell value in Google Sheets changes
Check Google Sheets for today's date
When a new row is added to Google Sheets
On new Google Form submission
Send me a daily reminder
On new Typeform submission
When a cell value in database changes
Check database for today's date
When a new row is added in database
When a new HubSpot customer is created
Select action
ADDCALENDAREVENT
Creates a one- or multiple-day calendar event with optional attendees
ADDHUBSPOT
Adds an object to Hubspot
ADDSLACKCHANNEL
Create a Slack channel, and optionally add a topic or members
CALLURL
Makes any HTTP request
CREATEGOOGLEDOC
Replaces [@column_name] values in a Google Doc with the corresponding table row's values
CREATEPDF
Replaces [@column_name] values in a Google Doc with the corresponding table row's values, then export it as a PDF
FETCH
Makes any HTTP request and returns the response
INSERTROWS
Inserts given array below defined values in given worksheet
SENDGMAIL
Sends an email using your Google account
SENDOUTLOOK
Sends an email using your Microsoft Outlook account
SENDSLACK
Sends a Slack message
SENDSMS
Sends an SMS message using your Twilio account
UPDATECELL
Updates cells with given values
UPDATEHUBSPOT
Updates a property of a Hubspot object
Trigger is required
Action is required
Get started