Tom Woodward on APIs and automation

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.


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.

The #edublogsclub challenge this week relates to books. I am always reading. However, instead of writing another review, I thought that it might be more pertinent to review a review of my reviews I made with Awesome Tables.

Awesome Table allows you to take data recorded in a Sheet and use App Scripts to create dynamic visualisations. As the website explains:

Awesome Table lets you display the content of a Google Sheet into various types of views: From a simple table to people directories, Gantt chart views, Google Maps, card views… There are many possibilities to suit your personal and professional needs. With it, data in Sheets are shown in a more functional way and can be shared with viewers.

I had started exploring options for dynamic displays with the help and guidance of Tom Woodward, who shared a template for my blog roll with me. However, he also directed me to Awesome Tables as another possibility.

Even with tags and categories, one of the challenges of having a blog with hundreds of posts is how to make it easier to find what you might be looking for. One example is finding my reviews of books and podcasts. I am often asked for suggestions and short of giving people a long list, wondered if there was a way of refining this process. This led me to Awesome Tables.

 


For those interested in making your own Awesome Table, I have created a basic guide:

1. Go to Awesome-tables.com and sign in with your Google account. This will then link with Google Sheets.

2. Choose a template from those provided. Preview it and once happy, click Use Template to create a copy.

3. Open the Spreadsheet attached to the template. There are two tabs: data and template. To use the basic template as is, delete the dummy data and add your own.

4. If there are fields that you do not want built into the template, then you can change the headings in the Data sheet. However, make sure that you do the same within the Template sheet.

It is possible really customise an Awesome Table or even start from scratch. However, it can get rather fiddly and involve a lot of abstraction. For those wanting to go down this path, there is a support site with a range of documentation.


So what about you? What strategies do you use to track the books you read? Have you used Awesome Tables in any other ways? As always, comments welcome.


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.