TL;DR : To avoid having to use OAuth, I create a script that fetches calendar events to a spreadsheet every night, publish the Google Sheet as a CSV web application and consume it in my front-end.
You may have seen it on Twitter already, but lately I've been toying around with the idea of creating a dashboard for my house using an e-paper screen.
I'll write up a complete tutorial when I'm done but one of the first things I wanted to do was integrate calendar events from the different members of the family. This article is mainly graphical so get ready for loads of screenshots
The problem using the Calendar API
I have a few requirements for my dashboard :
- I don't want to have a backend, to reduce maintenance
- I want to do everything in Javascript as a webapp, so that I can just implement locally and deploy online on Netlify or an equivalent service.
- I don't have any kind of touch / keyboard support for the dashboard, it will just refresh every other minute.
- Ideally I'd like to be able to host the repository publicly so I can share it with others. That means API keys have to be hidden :).
When looking at the Google Calendar API, the typical Javascript tutorial starts with a Python requirement (🥳🤷) . But most importantly, it uses OAuth, scopes and authorizations to access the Calendar. This is a very good idea if we want to interact heavily with the calendar, search, edit or delete events, .... but in my use case I thought it was way too convoluted. I just want to see the events in the coming week, nothing more. There has to be a better way!
Accessing Calendar data in a Google spreadsheet
Setup and data source
As part of my last job, we were heavily relying on Google Docs as a company and I quickly realized how powerful Google Scripts were for automation. Those scripts essentially allow you to access any Google API and document you own as long as you are logged in. Let's try to harness that power to achieve our goal : Having an always up to date list of the 10 upcoming Calendar Events.
Let's start by creating a new Google Spreadsheet. If you're on Chrome, you can type spreadsheet.new in your browser. Otherwise, go to https://drive.google.com/. Name the spreadsheet as you wish and then open the Script Editor.
Then, add the Calendar and Sheets services to your project and save it under a name you like.
Right, we are now ready to rock. You will need the unique identifiers from the calendars you want to see events for. You can pick several calendars, in this case find the id of each calendar.
To find the id of a calendar, first go to https://calendar.google.com/. You should see a list of calendars available in the bottom left part of the screen.
Click on the one you want to use, select settings and search for calendar Id, which looks like an email address. (An extra tip you might not know : If this is a calendar you own, you can add events by sending emails to this Id 😊).
You can use any calendar you have access to, not only the ones you own. In this tutorial I will use the Dutch Holidays and French Holidays calendars from Google.
In your script, create an array to store all those references, they will be the core of our script.
Preparing and Gathering the data
For our script to do what we want, we will rely on mainly 2 functions from the Google APIS :
- Calendar.Events.list in the Calendar API that will fetch the events
- Spreadsheet.getRange().setValues() in the Sheets API to set data in the spreadsheet.
The rest is essentially glue around the methods. Here is how it looks like :
Note1: The intellisense of the Google Script environment will tell you that functions like flatMap() don't exist. They do. Ignore the warnings
Note2: The first time you want to run that script, you will get requested for approval. This is normal since you are trying to access things on your account.
If you run this code, you should see your spreadsheet populate itself with the data coming from the calendars:
Triggering the function on a schedule
It is up to you to decide how often or how you want your script to run. In my use case, I set it up to run once a day during the night (since I run a dashboard and my events have a day granularity).
You can do this by pressing on the stopwatch on the left side bar of the editor and creating a new timer. I use a Day timer here but there are many options available, for example on every calendar update.
Exposing the received data to your application
Now that you have the data you want in your spreadsheet, it's time to make it available on the internet for your app to consume!
Surprisingly, it is possible to expose spreadsheets on the web. You can do this by pressing File -> Publish on the web on your spreadsheet (not the script) and then follow the pop up that comes up. You can choose between a few different formats and decide to publish part or all of the sheet. As an output you will get a link like this one.
Consuming the resulting data in your (javascript) app
The last step of this process is to consume the resulting CSV data in your Javascript front-end. This can be done many ways but we will choose a quick and dirty, dependency free method; using a regular expression 🙀
In the above snippet, we use the Fetch API, split the data to an array using a regular expression (well actually the regexp is only here to get rid of the white lines) and map it to an Object.
At this point, you should see your data ready to be ingested. At the time of writing, the next 'holiday' in the Netherlands is Valentine's day.
And that fixes it! Over 1000 words, and a few steps to get there, but no OAuth as promised and actually only about 10 lines of code total. Not too bad.
Closing words
Keep it in mind that anything you publish on the internet will be available for anyone to see! So be careful with what you put out there. To be frank, this method is not something I would recommend in many use cases at all; so please be careful with your data!
I do like the fact that with a little bit of creativity we can get around limitations though. With such a script, it is quite easy to export a lot of data off of the platform should you want it.
Take care! If you have thoughts or questions, you can find me on Twitter.
🙏Finally, I also wanna thank Adam Morris and Sourabh Choraria for helping me discover that es6 is a thing on Google Scripts in this thread 🙏.
Top comments (0)