Sync Google Calendars and Google Sheets using the Sheets2GCal Sheets add-on.
To get started you first need to install Sheets2GCal here:
Google Marketplace
When installed click “Extensions” > “Sheets2GCal“, and select either:
1) Import Calendar to Sheet: set up a new Sheet and Import a Google Calendar
2) Configure Sheet > Clear & Link Sheet: set up a new Sheet to create events to Export
You will have to authorize to grant permissions for Sheets2GCal to edit your Sheets and Calendars.
A free account is needed to import large calendars, and a paid subscription is required for importing a range of dates instead of entire calendars, and for heavy usage.
Once your spreadsheet is linked with a Google Calendar you can manage the calendar from the Sheet, or manage the Sheet from Google Calendar using the various “Import” and “Export” options. This makes it easy to automatically populate calendars with data from your spreadsheets, or easily convert calendar events to spreadsheets for analysis, printing or sharing.
NEW VERSION (September 2023) Several new features have been added due to popular demand.
Formulas are no longer replaced when importing changes from Google Calendar, and ANY cell containing a formula will be skipped. This means you can now use formulas to configure your events, edit the event in Google Calendar and import changes from the calendar without affecting the data generated by the formula.
A new menu item “Check Sheet vs Calendar” now allows you to automatically detect rows which are different from the corresponding calendar event. This can be useful because when using formulas or the API the “Update” checkbox cannot be automatically enabled when rows are modified. IMPORTANT: Please note that this will also check the Update box for events that have been edited in Google Calendar but not in the Sheet, so if you edit in both Google Calendar AND Sheets, be careful not to overwrite your calendar edits!
Automatic Import has become “Automatic Import/Export” with an option for each Sheet:
Automatic Import > Export: the default option imports changes to the Google Calendar since the previous import, then exports updates to the Sheet to Google Calendar. See “Export” below for more details on what is exported.
Automatic Export > Import: this exports updates to the Sheet then imports changes to Google Calendar since the previous import. WARNING: Be careful not to overwrite unimported calendar edits with this option because it will export any data in the Sheet which doesn’t match the corresponding event in Google Calendar, meaning that it will overwrite calendar edits even if they are more recent. See “Export” below for more details on what is exported.
Automatic Import: this will only import changes to Google Calendar since the previous import. Please note that this will not overwrite any cells that use formulas.
Automatic Export: when used automatically this will process any rows that have been manually checked for update or delete, autodetect/update any rows which are different than the corresponding events in Google Calendar using “Check Sheet vs Calendar“, and delete events which no longer contain a valid Title and Start Date in the Sheet. It will, however, not delete the entire row data for events which are deleted (only the “eventID” and “HTML Link”), allowing you to preserve any formulas that might be present in the row for deleted events, and other data.
Google Quota restricts the amount of data and the time that scripts can run, so you will probably run into problems with this option if you have Sheets linked to large calendars, or too many sheets with too many Import/Export options enabled.
Each Sheet in a Google Spreadsheet is associated with a single Google Calendar for which you have read/write access. Create new Sheets for each calendar you will be using, and click either “Import Calendar to Sheet” to import the events from a calendar (preferred), or “Configure Sheet” > “Clear & Link Sheet” to setup and link an existing Sheet with any Google calendar directly, without importing any events. You can use the tabs at the bottom to create multiple “Sheets” in the same “Spreadsheet” and each Sheet will be linked to a separate Google Calendar.
To create new events you must enter at least the Title and Start Date for All-Day events, or Title, Start Date and Start Time for timed events. All other columns are optional. Any rows without both Title and Start Date will simply be ignored.
Enter your events manually, or copy/paste rows/columns from other spreadsheets into the correct columns, check the “Update” checkbox for the events you wish to create/edit, then click “Update {Calendar Name}” in the “Export Sheet to Calendar” sidebar to save the events to that Google Calendar. You can also use formulas to pull in data from other Columns or Sheets, and add extra columns or even change the order of the columns – except for columns A and B which must always be the “Delete” and “Update” columns.
Events in the Sheet are connected to Google Calendar using the hidden Event ID column, so they can be updated or deleted directly from the Sheet. Click on an event’s “Link” to open the event in Google Calendar. If you want to duplicate events you can duplicate rows then delete the event ID for the new rows, then “Update” to create new copies of the event.
The “Update” and “Delete” cells for event rows that are successfully created, edited or deleted are coloured to indicate the status: deleted events will be marked red, created/edited events coloured green. To reset the colours you can remove colours for the row manually, or use the “Reset Colours” button in the sidebars for “Export Sheet to Calendar” and “Import Calendar to Sheet“
The two main Sheets2GCal windows are “Import Calendar to Sheet” and “Export Sheet to Calendar“. Use these to Import and Export events with various options. Once you have used these sidebars to set your options you can then use “Quick Import” and “Quick Export” to perform the same operations with the options that you chose – without even having to open the sidebars. “Quick Export/Import” runs these two operations in succession, and “Export All Sheets” or “Import All Sheets” runs the same operations on all Sheets in the current Spreadsheet which are set up to work with Sheets2GCal.
TIME ZONES
The “Timezone” field is optional, and will default to the main time zone setting for your Google Calendar account. To use a different time zone for an event either change your general Google Calendar time zone, or insert the IANA timezone code for the event in the “Timezone” column. When sharing calendars you should not need to worry about this because other users will see the times automatically adjusted for their own time zone.
GUESTS
Add a list of emails separated by commas in the ‘Guests‘ field and these Guests will be added to the event. Once uploaded to Google this field will show the full Guests JSON data including guest responses. This data can be edited and saved to Google, but you must preserve JSON formatting and you cannot change the reply status for guests. Check the “Send Notifications” checkboxes if you want notifications sent out to Guests for new events (future events only), or updates to existing events.
Please note that Google restricts the number of invitations you can send depending on your account type: free Google accounts can only add 10 guests per event, and send a limited number of invitations per hour/day. Paid Google Workspace users have higher limits.
ADD VIDEO CONFERENCE:
Type any text in the “Meet” column (I use “YES”) before creating an event and it will be replaced by the Meet ID as soon as the event is created. If you have added email addresses to the “Guest” column the Meet invite will be sent to the guests, again subject to the limits Google imposes.
REPEAT COLUMNS:
Repeat – must be Daily, Weekly, Monthly or Yearly.
By Day – for weekly repeats (only) you can use a comma separated list of two letter day names e.g Mo,We,Fr. The initial event should be on one of the listed days.
Until – this is date on which the last event in the repeat series occurs.
OR
Count – the number of times the event should repeat.
Interval – this is the interval of the repeat occurrences, so if “Repeat” is set to “Weekly” and Interval to 2 this will create an event every 2 weeks.
You can also use a Calendar ID to link the Sheet without setting it up (for advanced users only who understand why they might use it!) by using “Configure Sheet” > “Link Sheet“. This can be used to change the linked calendar for a Sheet that already contains events, but you will have to delete all the data in the hidden “EventID” column before creating new copies of the events. Doing this will automatically check the “Update” column for every row, and the events can then be recreated in the newly linked Google calendar and automatically given new Event IDs for future import/export.
The full iCal specifications allow other more advanced options, but to keep things simple Sheets2GCal uses these basic parameters. For a full explanation of how to build an RRULE please refer to this page:
https://icalendar.org/iCalendar-RFC-5545/3-8-5-3-recurrence-rule.html and please consider using GCalToolkit if you need to set RRULEs directly for events.
The developer may not be held responsible for any problems caused directly or indirectly by the software. It is the user’s responsibility to ensure they have regular backups of Google Calendar / Sheets.
Made by GCalTools Calendar Tools