How I Published a Google Sheets Add-on… or WTF Google!
I recently had the experience of publishing a Google Sheets Add-on in both the Chrome Web Store and the G Suite Marketplace. Since you’ve read the title of this post, you might conclude this was not a straightforward process. I’m going to organize things as a road map for the perplexed but reserve the right to add some snark when necessary. I wish I had this information when I was starting out so here it is!
Introduction to Apps Script
The first place to start is with Apps Script which is somewhat analogous to VBA for MS Office applications. This is a language like JavaScript but not JavaScript. Your experience may be different, but I found that I was often wrongfooted by the differences from JavaScript. Having said that the Google part of the API is rich enough that you can write spreadsheet functions, menus for your Add-on and general user interface items like dialog boxes. In short what you can do in VBA in Excel you can pretty much do in Google Sheets through Apps Script
We first need to enable Apps Script for the account we are using for development. Go to the URL https://script.google.com/home/usersettings first. To enable Apps Script click on Google Apps Script API. If you’re successful at activating the API you should see
We’ll start building our Add-on by opening a blank spreadsheet. We can get to the script editor from clicking “Script editor” in the Tools menu
This will bring you to a web-based development environment for Apps Script. The project also needs to be named so we’ll call it “Publish Me.”
If you look at the G Suite Developer Hub for this user (https://script.google.com/home) you should see the code project here as well
The IDE for Apps Script is not particularly functional, but you can work with it. This is quite analogous to VBA where the IDE has languished far behind the state of the art. If you can’t live with the IDE, there are some options for using your preferred development editor. Google provides a utility named clasp that allows remote management of Google Script code. This is not as seamless as I’d like but I was able to use it effectively with Visual Studio Code.
Create and Configure the Add-on Project
Actually writing the Add-on is not the point of this post so we’ll assume we’ve just written an awesome Add-on. Now that we’ve built it, we’d like to publish it to the Google Store. In order to do this it is actually necessary to wrap this in a Google Cloud Platform project. I’ve set up a GCP project named Publish Me. If you’ve successfully set up your project you should see something like
Write down or copy the Project Number!
Next we need to enable the G Suite Marketplace SDK. On the Cloud Platform sidebar click Library under APIs & Services. There should be a search page there. Search for the G Suite Marketplace SDK select it and click enable.
Now we need to configure the Credentials and OAuth consent screen. First, we need to configure the consent screen. Under APIs & Services select Credentials and click the entry for OAuth consent screen. For my project I needed several special scopes which you can think of as API specific permissions. My Add-on called out to my company website to retrieve financial data, had menus for the Add-on and the menu items modified the spreadsheets. One way to see what scopes you needed to enable is to go back to the Apps Script project. Under the File menu you should see a menu entry “Project properties.” If you open this and go to the Scopes tab you should see the scopes required by your script. For me this looked like
On the OAuth consent screen page there is a button that says “Add Scopes.” This will give you many scopes to choose from but the easiest way is to copy the scopes defined in the project properties and use the manual paste option to enter them. For me this looked like
With a heavy dose of foreshadowing the OAuth consent screen page now has a warning
If you successfully saved your OAuth consent screen you will be asked to create credentials. My project didn’t fit into the standard categories, so I selected “Other.”
This will bring up a screen requesting the type of application
To check the created credentials, click the Credentials item in the sidebar. You should see something that looks like
Now we go back to our Apps Script project and select “Google Platform project…” under the Resources menu
Remember that Cloud Platform project number I told you to copy previously, well we need it here. In this image I’ve already entered it
While we’re in the Apps Script project let’s set the version of the Add-on. This is necessary before publishing. In the Apps Script project select Manage Versions… under the File menu
In the dialog enter a description for the version and click Save new version. After you should see something like
Now back to the Cloud Platform dashboard. Click APIs and Services in the sidebar. On the API page click G Suite Marketplace SDK
The API Configuration page should look something like
There are a number of things to fill in here including various icons and website information. There is another item below that’s necessary to connect the Add-on to your Apps Script project
Here I’ve already filled this in. The version number is straightforward as it is just what we defined above. The project ID is a source of confusion because what are we talking about? Well we’ll find it by going back to the Apps Script Project properties dialog
The dialog says that this key is deprecated (because of course) yet this is the ID that is required in the Google Cloud Platform project in order to publish this Add-on. WTF Google! Be ready for changes that break your Add-on.
Publishing the Add-on
Hooray! Now we’re ready (kind of) to publish our Add-on. We start from the Apps Script project
This will bring up a dialog with some information to fill out
You will need to make sure the Add-on version matches the version you used to configure the Google Cloud Platform project above. When you create a Web Store draft there will be required icons, screenshots and company URLs that you’ll need to fill out. The graphics don’t match at all what you had to fill out for the Google Cloud Platform because of course. Once this information has been entered and accepted by Google, you’re ready to publish! Well not really… (Major WTF coming!)
OAuth Review Process Hell
Remember our foreshadowing from before? Well if the complicated process of configuring this Add-on is not sufficiently WTF for you here is where it gets strange. I’m giving a shout out to Christopher Prince and Cloudsponge for blogging about their experiences. Otherwise I might still be cursing the day I decided to publish my Add-on.
So, Google wants to verify me? No problem. I work for a popular startup and I had already gone through a detailed verification process with DigiCert so I thought I knew what to expect. Then I got this email
Dear Developer,
Thank you for submitting an OAuth App Verification request.
In order to continue with the verification process, you’ll need to create and provide a link to a YouTube video that shows how you’ll use the data you access using OAuth scopes. Specifically, the demo video should detail, in English:
1. How to log into your project (ensuring that the URL bar with the client ID is clearly visible)
2. How to request an OAuth token (OAuth Consent Screen/Permissions Page)
3. How your project’s functionality utilizes the requested scopes:
o https://www.googleapis.com/auth/script.container.ui
o https://www.googleapis.com/auth/script.external_request
o https://www.googleapis.com/auth/spreadsheets
Note: You don’t need to be personally visible in the demo or narrate the video. Demonstrating the process from the keyboard/screen view is sufficient.
You can send us the video by replying to this email with the link. You can find more information in the OAuth Application Verification FAQ. To ensure your communications are not missed, please respond directly to this thread to continue with the verification process. Note that any new email sent to api-oauth-dev-verification@google.com will not be received by our team.
Eh… WTF?!
I couldn’t understand what they were asking for. Show me logging into my project? Did they want me to show what I described above where I set up the OAuth consent screen? Really, I couldn’t understand how what they were asking for. Also, it would have been much easier to supply this information in a document. A video? What’s the point?
Well my first instinct was that they wanted to see how I was using the scopes in my code to ensure that I wasn’t up to no good. I know I’d be interested in confirming that if I were them. So I got to work making a video that showed screenshots of my code as well as examples of how the scopes would actually be used by the end user. I had no idea what they meant by a client ID in the URL bar but I thought I really nailed this. After submission however I got this email
Dear Developer,
Thank you for providing us with the test account and demo video!
Unfortunately, we do not see your project number: XXXXXXXXXXXXX(in the email this was the actual GCP project number) in your demo video or when we test the user login to Gmail within your app.
To proceed with the approval process, please reply back to this email and provide a YouTube link with the demo video that showcases the process to
1. log into your project
2. request OAuth token
3. use your project’s functionality
Thank you for your patience. Please let us know if you have any questions.
Well damn! I had a lot of questions. Unfortunately, they did not respond to my questions at all. Not cool Google! And anyway don’t you already know the project number?
This is the point where I started to think very bad things about Google. I did several web searches with sentences from the emails I got and found that my experience was not unique. It seemed that what Google really wanted was a video of a user installing the Add-on and approving the scopes in the OAuth consent screen. There were no project numbers to be had in the URL bar but without another course of action I proceeded to do this. If you have multiple accounts in a domain like I do you can publish to your own domain without the verification and go through the consent screen with a different login. If you don’t have this you will have to publish your Add-on as unlisted and use the explicit URL to install to another account. Prepare for many security warnings along the way.
After I submitted this second video, I got a response from Google saying I was approved. Really Google, why not just ask for what you actually want in plain English instead of sending OAuth word salad emails?
Conclusion
Well your Add-on is now verified and ready to publish to the Chrome Web Store and the G Suite Marketplace. You’re not sure why you had to do what you did and you’re not sure what the verification process had to do with protecting end users, but you’ve crossed the finish line. I’d be thrilled to hear from someone who can explain a simpler way to do this. However for those of you who want to publish an Add-on this is a road map.
WTF Google!
