Building a Google Calendar add-on and publishing it in the Workspace marketplace
I work four days a week in a “real job” and I spend about a day a week solving problems that interest me with tiny software applications. I’ve been assembling a tool-kit to quickly build against architectural patterns that I find keep coming up in tiny apps. The technology I choose has to solve these problems:
- I only want to pay for what I use (scale-to-zero)
- I don’t have a lot of time available for learning or building (no steep learning curves without substantial time-savings)
- I don’t have time for maintenance activities (no patching servers, automated scale-up)
- I’m not a good UI designer or front end engineer (design systems are great)
I’ve long been interested in using one of the big office productivity platforms to build an embedded add-on. For a solo developer it’s a no-brainer: the platform takes care of the requirements above, and when the platform has a marketplace, there’s even a ready to go distribution channel to take it to market! The only real reason I’ve resisted it so far is fear of the slog of platform approval processes and required collateral. In this story I’ll explain how I felt the fear and did it anyway 😀. Below is the production listing for JIT Time.
Google Workspace
Google Workspace is the latest name for the product formerly known as GSuite, formerly known as Google Apps. Gmail is the part of the suite most people are familiar with. It also includes Calendar, Google Drive and the Google Docs sub-suite. I use Workspace on my own domain for email, chat, and calendar for my family. We also use it in my day job as our core office productivity suite.
Google Workplace has a marketplace where users can find and install add-ons that extend functionality of all of the applications. Most of the content in the marketplace is integrations with popular third-party applications like CRMs and other productivity tools, but there are purpose-built add-ons as well.
The business problem
Busy people struggle to make time at work for regular needs like eating lunch, going to the gym, or just taking time to reflect. I find it easy to turn up on a Monday only to discover that thanks to other people requesting meetings, I have no time aside for lunch in the week ahead. Some people deal with this by setting a fixed time for lunch every day. I find this a problem though, because it reduces flexibility and makes it hard to coordinate with colleagues’ and friends’ schedules. What I want is something that:
- Takes an ideal time, a latest possible time, and an earliest possible time
- Looks ahead a fixed period of time (e.g. the next 14 days)
- Works around any existing bookings
- Prefers the ideal time, but will slot in a different time if necessary, respecting the earliest and latest possible times
Organisations are getting more and more cautious with the privacy of user data, and my employer is no exception. The only way I can extend my calendar’s behaviour is by using a legitimate, approved add-on that is published in the Google Workspace marketplace.
The solution
I built a standalone server-side Node.js application that solves my problem a number of years back. It ran on an EC2 instance and held the tokens for the OAuth connection to a calendar and the user preferences in a MongoDB database. It had a basic web app for the configuration. I decided to:
- Take the logic from this application and re-architect it as a Google Workspace add-on for Calendar
- Face my fear and have a go at getting through the marketplace approval and publishing process
Guess which one of those two things took longer?
Building a Workspace add-on
I came across a very good blog by the team at Lucid Chart called “The 6 Deadly Sins of Google Apps Script Add-on Development”. I respected most of these, but I ignored the most important one: “Coding your Google Apps Script add-on in the built-in script editor”. It’s been fine so far, but I suspect I’ll regret it at some point. That said, the article is five years old, and some of the chief complaints in the blog article have actually been addressed. The online editor has most of the basic features you find in editors like VS Code with auto parenthesis completion, “intellisense”, syntax error detection and so on.
The official “getting started” tutorial is very good and it only took fifteen minutes to have a working (rather silly cat-themed) “hello world” add-on up and installed in the default Google Calendar on my personal Google account. The only gotcha in the tutorial was when I went to deploy the add-on, I found the deployment menu item was disabled until I chose to run a function. There aren’t actually any standalone functions in the tutorial to run (it’s all based on reacting to events in Calendar) but by clicking the run button and “executing” the common script file and all was well.
Workspace add-ons run in an Apps Script runtime. Apps Script is JavaScript-based, runs in V8, and supports all the modern ECMAScript language features. My original application is pretty straightforward. The algorithm works on one day at a time, starts with the ideal time and then moves later and earlier in five minute increments to try and find sufficient space to schedule the event. I had used Lodash and Moment, but to keep things simple, I rewrote the Lodash calls to use built-in ES calls, and removed the Moment references, sticking with integer arithmetic on since-epoch timestamp values.
The user interface is built using a “card” system. A fluid interface uses method chaining to build up the allowed components into a cross-platform UI that will work within the constraints of the Workspace sidebar. I love being constrained in the UI. As someone with no visual design talent, it’s very helpful to be kept within patterns that will “just work”.
Integrating with Calendar
The Apps Script platform supports the standard public Google Workspace APIs as “advanced” APIs. There is some minimal documentation on how to access an advanced API, but there are also “basic” APIs that are much simpler to use. I started with the basic Calendar API. No extra steps are necessary for this in the code beyond calling a built-in class, e.g.:
Making a call like the one above does require the correct OAuth scope(s) to be configured. These live in a metadata file called appsscript.json in the Apps Script project. My scopes block looks like this:
The basic API almost met all my needs. Unfortunately the cut-down event objects returned by it were missing one really important attribute for my purposes — the field that shows whether or not the user has marked an existing event as “busy” or “free”. Google Calendar users often add all-day events that are “free” so others booking in know they can still use that time. E.g. I might have an all-day event called “Mum’s birthday” marked as free. It’s there to remind me to do the necessary things on such an important day, but I don’t intend it to act as a blocker for adding other events that day.
One of the pre-requisites of using the advanced calendar API is converting from the default Apps Script Google Cloud Platform (GCP) project to a standard GCP project. Doing this is also a pre-requisite for publishing the project, so it was time to get on with that.
Converting to a standard GCP project
As I mentioned earlier, getting started with Apps Script is quick and easy. Part of the reason for that, is that it automagically creates a special kind of GCP project in the background. That project is locked down and doesn’t function like a normal GCP project, so it’s not possible to add standard Google APIs like the full calendar API or the Workspace marketplace API to it.
I followed Google’s documentation for switching to a standard project, starting with creating a new empty project. The gotcha here is that when you follow links out of the Apps Script environment it tends to go to the special default project. I was wondering why I still couldn’t start configuring the OAuth consent screen for my new project when I realised I was sitting in the Apps Script default project with a very similar name!
Using the advanced Calendar API was no different to adding any other API to a GCP project. I enabled it from the Library in the API console in my new project and then I was able to make calls to the full API from Apps Script, like so:
The approval processes
There are two steps to gaining approval for publishing a listing in the Workplace marketplace. The first is a generic OAuth verification and the second is approval for publishing and listing the actual add-on.
Google maintains a list of OAuth scopes, some of which are classified as “sensitive”. Because users’ calendars are considered private information, the OAuth scopes to access and manipulate them are sensitive. The GCP OAuth login consent configuration provides a wizard to walk through to provide Google with information, which is then reviewed by humans. They are basically making sure that you are not trying to pull a fast one and steal data without a user realising.
The first time around I received the approval very quickly, but when I submitted the marketplace listing, it was rejected because my app didn’t work. It didn’t work because I hadn’t configured the necessary OAuth scopes for the calendar in the GCP console. “But how could you not notice that?”, I hear you say. The tricky thing about Apps Script add-ons is that they use the OAuth scope defined in the appsscript.json metadata, not the scopes in the OAuth consent screen config in GCP. But… while that works during testing, it doesn’t work on a published app. It’s necessary to manually configure the GCP scopes to match the list in appsscript.json.
I copied and pasted the scopes out of appsscript.json into the GCP OAuth consent config and resubmitted.
One of the requirements for the approval process is to upload a screen capture video to Youtube that demonstrates the purpose of the OAuth scopes. My demo showed this, but after a couple of days it was rejected because the reviewing couldn’t see the OAuth client ID. My app does not control how the client ID is displayed: the callback URL for a Workspace add-on is generated by the Workspace application. Calendar generates a very long URL and it the client ID wasn’t visible in the first 100 or so characters:
I was able to get the client ID to show by selecting some text from the URL and using the end of line keyboard shortcut to jump to the end. This time after I resubmitted it was accepted.
I had taken the advice of the six deadly sins article and prepared all of my marketing assets for the marketplace listing ahead of time. The minimal requirements are reasonable, I’d pulled together:
- A “card” for the listing (I used a Canva business card template to do that one)
- Icons of 32 and 128 pixels square
- A screenshot of the running app (of annoying dimensions for a Calendar add-on, but I got there with some trial and error)
- Long and short descriptions
- Terms of use — I used a template that I found, cutting out anything irrelevant
- Privacy policy — I had created a very minimal one of these for the earlier server-based version
The approval came through in a couple of days.
Because I only have a day or two a week to work on my side projects, and the turnaround for approval is usually at least 24 hours, it took a month to go all the way from the very first OAuth submission to the final publication. Worth bearing in mind if you’re in a hurry!
Next steps
I was able to install the add-on in my day job Google account, so now I’ll be testing it to make sure it is as glitch-free as possible. The logging statements from the Apps Script environment appear in the GCP logging console, and I have very detailed debugging statements for my algorithm: time for some monitoring!
