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.
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.
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.
The Extensions is found along the main menu at the top of the page.
The main page of Google Apps Script will be loaded in a new tab.
Copy the script from the following page:
https://gist.github.com/rvbautista/60674ec7f4b4bd1e1efff6c562992fe9
Go back to the Apps Script tab and paste the code.
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.
Click the Save project button to save the script.
Afterwards, click Run the selected function button. This is for us to check if the script is working correctly.
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.
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.
Another page will load reminding you that Google hasn’t verified the code yet. Click Advanced.
A link labeled Go to Untitled project (unsafe) will appear. Click it.
Another page will be loaded listing the permissions needed by the code. Click Allow.
The pop-up window will disappear. An Execution log will appear below your code. Two messages should appear: Execution started and Execution completed.
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.)
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.
You can get more details about this step here.
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.
A set of three dots will appear on its upper-right corner. Click it.
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.
Once you click the button, the calendar will be exported to Google Sheets.
You just need to edit the script. The only line you need to modify is Line 9:
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.
