<img height="1" width="1" style="display:none" src="https://www.facebook.com/tr?id=1870732089876948&amp;ev=PageView&amp;noscript=1">

Export Google Calendar Entries to a Google Spreadsheet

Image by Nadeena Granville

Have you ever needed a way to see everything on your calendar without clicking into each event separately? Imagine a list of your activities that tells you the exact start and end times, where your event takes place, and a calculation of the duration -- for any range you want.

In this post, we share a script created through Google Apps Script that allows you to find and export your Google Calendar for a range of dates to a Google Spreadsheet for further reporting or processing. The idea for the script was initially designed to help list some events for Admin tasks, but it didn’t take long to see its usefulness to any end user.

Here's what we thought:

  • The script is extremely useful for summing how much time every week you spend in meetings or at specific events. It allows you to look back and see exactly where you're spending (or not spending) your time, which can help you plan for the weeks ahead.
  • It could especially benefit companies with part-time employees that are required to turn in a time sheet at the end of every week – assuming they use Google Calendar.
  • One of our favorite features of the script is the addition of the &newcopy to the URL link, which makes you automatically create a copy - no click needed on your part, making it easy and saving you time.
  • Giving automatic instructions when the script launches is also very convenient.

Exporting Instructions in Google Spreadsheet

Want to try the script out for yourself? To run it, simply:

  1. Use this link to open, and make a copy of the spreadsheet and script: https://docs.google.com/spreadsheet/ccc?key=0AkUblBlbq6UEdDVlRXVRclF1amMtYnhHX0oteExBSFE&newcopy
  2. Then open your copy of the spreadsheet and click Tools--Script Editor
  3. Replace Justin’s email address with your own (or your calendar email address) on line 23 (var mycal =...)
  4. From the Script Editor Screen, click Run--export_gcal_to_gsheet and see the results (in the spreadsheet)

Be aware that the very first time, you will be asked to authorize the script which wants to read your calendar, and write to your spreadsheet (please grant these permissions when requested)

Optionally, you can change the dates and search criteria on line 39 (var events =...) It should read: var events = cal.getEvents(new Date("August 1, 2016 00:00:00 CST"), new Date("December 1, 2016 23:59:59 CST"), {search: ''}); 

There are a number of practical uses for this script from basic time-tracking to project management functions. In addition, people learning to write Google Apps Script can learn some of the less documented tricks for querying Google Calendar via script, learn how to code a time difference formula, and even learn how to script creating/updating formulas in spreadsheets. Let us know how you will use this script by commenting below!

Now try it with a shared calendar - Click here for further instructions.

Need more in-depth solutions? The team at Cloudbakers would be happy to talk to you about anything cloud, from Google Workspace to custom application development, infrastructure modernization, and Data Analytics.

Originally published on July 13, 2021

If you enjoyed this post, please consider sharing

Want more tech tips?
Subscribe to our IT Superhero Newsletter!