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, to import only changes from Google calendar 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.
Formulas are no longer replaced (as of Sept 2023) 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 checked automatically when rows are modified. IMPORTANT: Please note that this option 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. By default Sheets2GCal displays every event at the original time in the time zone for the event, not the Sheet’s time zone. To add a column that displays local times please see the bottom of this Help page.
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.
Displaying Event Dates/Times in the Sheet Time Zone
Sheets2GCal displays the data exactly as it is sent by Google Calendar. This means that the Start/Start Time and End/End Time columns display the Dates/Times of events in the events’ time zones.
If you manage events across multiple time zones you might, however, want to see events displayed in the Sheet’s time zone instead – as they do on your calendar.
The solution is to create a combined Date/Time column that displays the local date and time for events, based on the date, the time, the event’s time zone and the Sheet time zone in each row. In the example below I’ll explain how to set up “Local Start” but you can use this to create “Local End” too if you need it.
For a newly created Sheets2GCal Sheet the columns are: D = Start Dates, F = Start Times, O = Time Zones.
Step 1) Add a new empty column by right-clicking the D column and choosing “Insert 1 column to the left”, then select the column and set the alignment “Left” and open the “Format” menu > Number > Date Time (or Custom and set your preferred format for date + time).
If you put the new column elsewhere you’ll have to adjust the letters for the ranges in the formula accordingly. With the default Sheet layout once this is done you’ll have these columns because everything has shifted right:
(D = Our new Column)
E = Start Date
G = Start Time
P = Time Zone
To select the entire Start Date column we use the expression E2:E (all column E from row 2 since row 1 is our header row). So we now have the following ranges for each column:
E2:E = Start Dates Column
G2:G = Start Times Column
P2:P = Time Zones Column
Step 2) If you are using the default layout you can ignore this paragraph and just copy/paste the formula below into the Header Cell for your new column. If you have added extra columns (such as “Local End“) then you will need to adjust the RANGES in this formula to suit the layout your Sheet. For example if Start is column G you’ll replace every E2:E with G2:G. If your Time Zone is S then replace ever P2:P with S2:S and so on. If you are doing “Local End” then E2:E becomes F2:F and G2:G becomes H2:H etc.
To fill our new “Local Start” column (or whatever name you want) with the new data and header name you can now double-click the cell D1 (i.e the empty Header Cell for our new column) and copy/paste this formula into the cell and hit enter:
={“Local Start”; ARRAYFORMULA( IF(E2:E = “”,, IF(G2:G = “”, LEFT(convertTZ(TEXT(E2:E, “yyyy-mm-dd”) & ” ” & TEXT(VALUE(G2:G), “HH:mm”), E2:E, G2:G, P2:P), 10), convertTZ(TEXT(E2:E, “yyyy-mm-dd”) & ” ” & TEXT(VALUE(G2:G), “HH:mm”), E2:E, G2:G, P2:P) )) )}
If you see an error “#NAME” or “#REF” then either your locale requires semicolons, or there’s an error with your column letters in the Ranges. Here’s the semicolon version:
={“Local Start”; ARRAYFORMULA( IF(E2:E = “”;; IF(G2:G = “”; LEFT(convertTZ(TEXT(E2:E; “yyyy-mm-dd”) & ” ” & TEXT(VALUE(G2:G); “HH:mm”); E2:E; G2:G; P2:P); 10); convertTZ(TEXT(E2:E; “yyyy-mm-dd”) & ” ” & TEXT(VALUE(G2:G); “HH:mm”); E2:E; G2:G; P2:P) )) )}
If all goes well you’ll get something like this for a Sheet at Eastern Time.
If you aren’t editing events in the Sheet you can hide, but not delete, the “Start” column (right click / hide), and if you want the same for end dates/times you should set up and position your two new blank columns before inserting the formulas so that the column letters don’t change again.
Important: Sheets2GCal needs to be able to identify the “Start” and “End” columns by name, so do not call your new column “Start”!
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