Making of Webhooks for Sheets Workspace Add-on

ICYMI: You can access the add-on from this link and know more about what it does here

Architecture

The add-on does the following activities using the Apps Script REST API —

1. Creates a container-bound script (ref: endpoint)
2. Updates the script (ref: endpoint) with all the files listed in this GitHub repo's Webhooks folder
3. Creates a new project version (ref: endpoint) which is required to deploy a script
4. And finally, deploys the project (ref: endpoint) and since the manifest file contains the following configuration, it is done so as a Web App —

"webapp": {
  "executeAs": "USER_DEPLOYING",
  "access": "ANYONE_ANONYMOUS"
}

Learnings

Here are the top 5 things I hadn't known about until I'd actually started working on this add-on:

1. Saving the sheet is important — when you're trying to create a container-bound script using the Apps Script REST API (ref: endpoint) on a freshly created Google Sheet, its important to first have the sheet saved on the Drive.
   • Usually, renaming the sheet or making some edits to it will do the trick. In case you don't save the sheet and try creating a script anyway, you'll run into the following error —

{
  "error": {
    "code": 404,
    "message": "Requested entity was not found.",
    "status": "NOT_FOUND"
  }
}

To avoid triggering the API for detecting whether or not the sheet has been saved and to make sure that the add-on doesn't proceed to next steps before renaming the sheet, I make use of getDocumentProperties() method inside a global variable within the add-on, which throws an error if its unable to instantiate (that happens to be the case if the sheet is not saved).

To assist user with this step, I created the following prompt for them to easily understand what needs to be done and what changes they can expect to see —

2. Deploying the script/project as a web app using the Apps Script REST API (ref: endpoint) doesn't mean that its authorized to accept incoming requests.
   • The obvious answer here would seem for us to use the script.run endpoint but this only works if the script is made to be API Executable. Funny thing about that is you can only make a script API Executable if it’s a user-managed project (and not the default one which is assigned for container-bound scripts). And, to have the script assigned to a user-managed project, users will need to go through the entire process of creating a GCP account (if they don't already have one), enabling necessary APIs, setting up OAuth consent screen etc.

3. Authorizing script form a custom menu doesn't run the actual script function — I'm unsure if this is a bug or an intended behavior but this was the only workaround I could think of to authorize the newly minted container-bound script without having the users open the Apps Script editor.

   • For the time being, users are required to —
     • refresh the sheet once they try creating a webhook from the add-on and that shows them a new custom menu
     • click on the custom menu option as that would prompt them to authorize the container-bound script
     • re-run the option again as that would store a document prop indicating that the custom menu is no longer needed (along with a simple toast indicating that the script if "finally" authorized)
     • and then reload the sheet one last time to have the custom menu disappear

4. To fully collaborate, its not enough to share the add-on script with another user. You'll also need to invite them to the entire GCP project so the collaborators can view logs from add-on execution —

5. Document Properties are not shared between the add-on and the container-bound script associated with a sheet — perhaps if I'd thoroughly RTFM'd, I'd not have been surprised but I went into the dev mode assuming that whatever props I store using this method from the add-on that's associated with a sheet, would also persist when I create a new container-bound script for that sheet, using the add-on.

getDocumentProperties()
Gets a property store (for this script only) that all users can access within the open document, spreadsheet, or form. It is only available if the script is published and executing as an add-on or if it is bound to a Google file type. When document properties are not available this method returns null. Document properties created by a script are not accessible outside that script, even by other scripts accessing the same document.

One of the top reasons why it took so long for me to get here is that for some reason (I can't remember why), I had it stuck in my head that in order to use the Apps Script REST APIs, a user would absolutely need to create a GCP account / project and go through the entire process of setting up the OAuth consent screen, enable the right APIs etc. — but as it turns out, users literally just need to enable the API from the Apps Script dashboard here and everything else would go through the project which this Add-on is associated with –

Credits

1. Thanks to this project, I also went down another rabbit hole to Unnest an object with array values using cartesian product
2. The API OAuth Dev Verification team who verified my app in less than a day (and, on a weekend)
3. The Google Workspace Marketplace Review team who approved my Add-on within ~3 hours of submitting it
4. All the folks who supported me through my Product Hunt launch, some great reviews on the Workspace Add-on Marketplace and plenty of DMs/comments encouraging me / appreciating the work