Building on the APIs provided by Google Sheets and Google Calendar, I demonstrate how to automate the addition and change of multiple events.
I have been spending some time developing resources to support schools with timetables and reports. One of the things that occurred to me is the challenge of remembering to complete a number of key steps throughout the year. I therefore thought of creating a digital calendar that could be shared with schools that would help remind them.
In my search for a tool that would help with this process, I discovered From Sheets to Calendar, a Google Sheets add-on, that would allow me to create a series of events from a spreadsheet. As I explored this, it occurred to me that this might have ramifications for other groups in my organisation, especially those coordinating professional development. I have seen many plan things out visually in a spreadsheet. However, this means then creating these events again in a calendar. Here then is a guide to managing events with GSuite and sharing them with others.
Setup Calendars
Before setting up the various events, you need to make sure that you have created the various calendars. For example, you might have one for ‘meetings’, ‘professional development’ and ‘events’.
First Start
With everything set up in Google Calendar, install the add-on in Google Sheets. Once this is done, go to Add-ons menu and run ‘FirstStart’ to populate the template to work with.
Add in Events
With all the headings provided, enter the various information, such as title, time, location and description. Also, make sure ‘Add’ is listed against each of the events in the Action/Status column.
Import to Calendar
Once the events have been added, go to the Add-ons menu and run the ‘Import to Calendar’ to create events. Once created, there is an option to update and delete by changing the request in the Action/Status column.
Share the Calendars with Others
Although it is possible to send invites via the sheet, the other option is to share the particular calendars. For schools using Microsoft Outlook, Google Calendars can be shared as an ‘internet calendar’. The other option for users without a Google Account is to download and share a copy of the iCal file. The problem with this is that recipients will not be able to receive updates if there are any updates.
One of the challenges with a solution like this is that it is dependent on someone else’s scripts and support. In addition to this, to create more than 20 events, you need to pay a subscription of a $1 US a year. For those wanting an enterprise option, Zapier provides the connections, but it comes at a cost of $25 per month. There are also a range of scripts to build upon in Github. Another option is to manually import a CSV spreadsheet. So what about you? Are there any processes that you use when automating the creation of calendar events? As always, comments welcome.
Postscript
The Add-on, From Sheets to Calendar, has been removed from the Google Workspace Marketplace, however there are a number of other options. For me it highlights the benefits of solutions like Zapier which manage the connects and any changes seamlessly in the background.
If you enjoy what you read here, feel free to sign up for my monthly newsletter to catch up on all things learning, edtech and storytelling.
Sheets, Calendars, Events by Aaron Davis is licensed under a Creative Commons Attribution-ShareAlike 4.0 International License.
Mentions