A Google Sheets add-on for synchronizing events with a Google Calendar.
Want to easily add a large number of events to a Google Calendar? With this add-on you can enter them into a spreadsheet and copy them to the calendar with a click. Have a lot of events in a calendar, but need to modify them? This add-on will make that easy as well. Sync from calendar to spreadsheet, make your changes, and then sync back to the calendar.
Multiple calendars now supported! Each sheet can sync with a separate calendar to keep all of your events together.
Note: As of May 26, 2020, this add-on is awaiting approval and is not yet available in the GSuite Marketplace. Check back soon.
Within Google Sheets access the GSuite Marketplace by selecting the "Add-ons" menu and then "Get add-ons". Search for "GCalendar Sync" and install it. Three commands are added in a "GCalendar Sync" menu. One copies events from a calendar into a spreadsheet. Another command goes in the opposite direction, spreadsheet to calendar. And the third command is for changing settings.
The set up involves preparing a Google Calendar and Google Sheet.
Set up the calendar:
You have 3 options for the Google Sheet: let the add-on create the spreadsheet columns for you, copy an example spreadsheet, or use your own existing spreadsheet. The first two options are easier. Using your own spreadsheet will just take a little extra attention when setting up the column headers.
Step 1. All of the options below require the following actions first:
Option 2a. Let the add-on create the columns. This is a great option for an existing calendar and an empty spreadsheet.
Option 2b. Copy and modify the example spreadsheet.
Option 2c. Create a new spreadsheet yourself, or use one you already have.
That's it. Start entering and modifying events. Any extra columns that you add with other names will be ignored. Also, only sheets that have "Calendar ID" in cell A1 will be synced, so you can have extra sheets as well. See the "How to Sync" section below for how to sync the calendar(s).
The following settings can be accessed from the GCalendar Sync menu, Settings entry. They affect how events are synchronized:
Look for a "GCalendar Sync" menu. Choose "Update from Calendar" or "Update to Calendar" depending on the direction you want to sync. The first time you do this, an "Authorization Required" dialog will pop up. Click "Continue" and select your account in the next dialog.
Depending on the number of changes, the script runs in a few seconds to a few minutes.
When an error occurs, the sync can generally be tried again without any bad side effects.
There are some data checks in the script for correctly formatted dates and times. If you see a pop-up dialog, it will tell you which event has the error. Fix the error and run it again.
If the script runs more than several minutes, it will run out of time and be stopped. You should be able to run it again and it will do the next batch of changes. About 900 calendar operations can be done in one run, where an operation is updating one event field, adding an event, or removing an event.
If you get an error about too many Calendar events being added or removed in a short amount of time, try setting start/end date in settings to reduce the amount of events being synced. Or file an issue.
The project started as a simple script to publish swim team practice times on a Google Calendar and has evolved from there. Fixes and improvements are done by volunteers, so please be patient.
You're also welcome to dive into the code and send suggested changes and fixes via Github.
We do not collect, store, or process your personal information on our servers. The add-on will read your sheet data and transmit it to your calendar, but that data is not sent to our servers. It will also read your calendar events and write them to your spreadsheet, again not sending anything to our servers. The add-on settings (start date, end date, etc.) are stored in the Google Apps Script Properties Service, which is data owned by you and associated with the sheet where the add-on is used.
Find a problem with the add-on? Please create a new "issue" on GitHub. Or send me an email at the address below. Note that this is a volunteer project, so it may take a while to get a fix.
Or if you want to try fixing the problem yourself, the source code is on Github. Feel free to send me a pull request.
Let me know how you're using the add-on: Dave.Terms of Service