Hack: Exporting Google Calendar Data to Spreadsheet

Who needs this?

Have you ever thought it would be nice to be able to export your calendar events, and then discovered that exports come as .ics files which you have no clue what to do with?  This hack is for people who want to extract their calendar information in spreadsheet format.  I’ve used this a few times for:

• getting lists of events & meetings
• backtracking business mileage expenses

I’m sure there are other uses as well. And I know I’m not the only one who finds .ics files unruly and practically ineligible.

Here’s How

Let’s dig in and walk through how to export your google calendar into spreadsheet format. We’re going to use this great source – because someone’s already written a script and shared for re-use. Thanks!

Step 1: Click this link to create a copy of the spreadsheet containing the script.

Step 2: Once you’ve made a copy, a blank calendar will appear on your screen.

NOTE: You are not uploading your entire calendar to an external site, just using a spreadsheet that can format your calendar events for you.

Step 3: An instruction will pop-up on your screen. Follow the instructions by going to Tools then script editor.

Step 5: In the script editor, you’ll make some changes to adapt the script so it works on your accounts.  First, locate line 23 and enter your email address like this.

Step 6: Go to line 39 and enter the dates that you wish to include in the spreadsheet. Follow this example. This is where you can specify the range of dates you want to pull out. Click save.

Step 7: A pop-up message will appear again, saying “Export_GCal_to_GSheet wants to access your Google Account”. Yes, you need to grant Export_GCal_to_GSheet permission to access your calendars.

Step 8: Once you grant permission, it should be successful!  Check by going back into your spreadsheet. The calendar of events for the calendar and date range you specified should all be there.

Step 9: Now that it’s in a legible format, do whatever you want!  You can add filters, delete rows, add columns, whatever your big hear desires.

There you have it, I hope this step by step tutorial will help you realize that in just a few minutes you can make use of your calendar data!