<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

How to export your Google Calendar into a SpreadsheetHave 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.

Last week I opened my inbox to find an email from Justin Gale, a blogger and IT strategist whose spoken at Cloudbakers events and is a fellow Google Apps evangelist. He shared a script he 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. His 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.

Right off the bat, Justin’s wife found value in it when she needed help finding and reporting her volunteering hours that she logged in her Google Calendar. Because of the script’s ability to calculate event duration, it was simple for her to pull up her total, without going through every block of time.

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, Justin explains, “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.

Google

 

Need more in-depth solutions?  The team at Cloudbakers would be happy to talk to you about either custom application development, integration projects, or even talk about implementing additional products like Zoho CRM.

Originally published on January 24, 2014

If you enjoyed this post, please consider sharing

Cloud colaboration
New Call-to-action

Subscribe to
Crumbs From the Cloud

Topics

Posts by Topic

see all