Google Script - Sync Google Calendar to Spreadsheet by Specific Date Range

(This is modified from Davepar’s project and examples at GitHub https://github.com/Davepar/gcalendarsync which can do a sync either direction.)

I had a colleague ask if she could easily copy calendar events into a spreadsheet for a log. We discussed using the Agenda view in calendar, but she needed to do some calculations and data analysis with the information so a spreadsheet was the obvious landing place for the information. After finding (and previously using) Davepar's script to create Calendar events from a Sheet, it made sense to revisit it and break it down the other way. After doing that, it made sense to let the user input the desired date range on the spreadsheet.

Here's the result:

(Feel free to grab the Script if you want to make your own modifications- this one is an easy one with only 1 line of code to update.)

Directions:

Part 1 - Set Up the Calendar:

1. Create a new Google Calendar (in the dropdown next to "My calendars" in the left sidebar of Calendar).
2. Give the calendar a name and change other fields as desired, i.e. set up sharing.
3. Open the new calendar's settings ("Settings and sharing" in the dropdown next to the calendar name).
4. Scroll down to the "Integrate calendar" section. Copy the "Calendar ID". It should look like an email address.

Part 2 - Copy and Modify the Sample Spreadsheet:

1. Make a copy of this spreadsheet (use File -> Make a copy).
2. In the Tools menu, select Script Editor.
3. Replace the "calendarId" value near the beginning of the script with the Calendar ID from Part 1, above.
4. Save the script. (use File -> Save)
5. Close the script window/tab

Part 3 - Setup the Spreadsheet

1. Set the correct time zone in File, Spreadsheet settings.
2. Enter in the date you want to start pulling events in D1 (use format MM/DD/YYYY)
3. Enter in the date you want to stop pulling events in E1 (use format MM/DD/YYYY)
4. Click on the custom menu “Calendar Sync” at the top of the spreadsheet
5. Select “Update from Calendar”

• The first time you will be asked to authorize the script to run

• Hit Continue, select your account, then click Allow

Part 4 - Updating

• To select different dates, enter in new begin and end dates in D1 & E1 respectively
• To start with a clear spreadsheet, select “Clear Spreadsheet” from the custom meu “Calendar Sync”