Deploying Data Pipeline Google Sheets Add-On

This guide outlines how to deploy a Google Sheets Add-On for the data pipeline so that everyone in your organization has access to the tool.

Clone the project from GitHub

Clone the idlekit-data-pipeline project from GitHub into your machine.

Set up a Google Cloud Platform Project

Create a Google Cloud Platform project that will be used to publish your add-on to the Google marketplace.

Once it has been created, navigate to https://console.cloud.google.com/, select the correct project, and you should see your project info in the top left corner.

Note down the project number.

Set up OAuth

On the top left navigation menu, go to “APIs & Services” and “OAuth consent screen”

Choose the user type to be Internal. Set up your App name to be your project name, select your email as the user support email, and optionally insert any logo that is 120px by 120px so that the add-on is easy to see later. Add your email into the “Developer contact information” box, and save and continue

In the Scopes page, enable the following scopes:

"https://www.googleapis.com/auth/userinfo.email", "https://www.googleapis.com/auth/userinfo.profile", "https://www.googleapis.com/auth/spreadsheets", "https://www.googleapis.com/auth/script.container.ui"

Press “Save and continue” and OAuth is now set up.

Set up a Google Apps Script Project

Create a new Google Apps Script project.

On the left menu bar, go to “Project Settings”.

Enable the Show “appsscript.json” manifest file in editor option.

On the bottom of the page, link your Google Cloud Platform (GCP) project by pasting in the project number found on your GCP settings page.

Keep this page open as you will need the Apps Script ID later on.

On the left menu bar, go to “Editor”. Copy the files from the apps script folder in the data pipeline repo and add them to the list of files in the AppsScript project. Copy the contents of the appsscript.json file into the manifest file that was automatically generated for you, but replace the name and logoUrl.

On the top right corner, there is an option to “Share this project with others”.

Deploy the Apps Script Project

In the Apps Script project on Google, select “Deploy” → “New deployment”

Select the type to be “Add-On”, give it a description, and the “Add-on” section should say “Application(s): Sheets, Docs, Slides”. If it doesn’t, check that your manifest has been updated to reflect the one from the repo.

Press “Deploy”

Note down the deployment version number.

We have encountered an issue with Google where a new deployment wipes the appsscript.json manifest file. If this occurs, follow the steps below to deploy using the legacy editor.

In the top right corner of the Apps Script project, select “Use Legacy Editor”.

Ensure your manifest file is the proper version. If you’ve lost the manifest, you can restore it by going to “File” → “See version history”.

To deploy, select “Publish” → “Deploy from manifest”.

Enable and configure the Google Workspace Marketplace SDK

Enable the Google Workspace Marketplace SDK for your Google Cloud Platform project.

In the Google Workspace Marketplace SDK, click “App Configuration” on the left menu.

On this page, enable the “Sheets Add-On” option. Paste in the project script ID found in the Apps Script project settings as the “Sheets Add_on Project Script ID”, and enter the deployment version number into the “Sheets Add-on script version”.

Add all the OAuth scopes mentioned before into the “OAuth Scopes” section

Fill out the Developer Links section.

For Installation Settings, choose “Individual + Admin Install”.

For App Visibility, choose “Private”. This will allow users in your domain to use your app and the app does not have to go through Google’s approval process.

Save.

Create a Google Workspace Marketplace Store Listing

In the Google Workspace Marketplace SDK, click “Store Listing” on the left menu.

Click the “English” language and fill out the relevant details.

Upload your application icons that will be used in the store listing.

Fill out all the required fields and enable “All Regions” for Distribution.

Click “Publish”.

The page should now refresh and on the top, there should be a green checkmark and the status “Approved”.

Clicking the App URL should bring you to your store listing. You could also look for the add-on in Google Sheets by clicking “Add Add-Ons” and searching for your add-on in the marketplace.

It could take around 5 to 10 minutes for the app to actually publish to the Google Marketplace, and another 5 to 10 minutes for the change to propagate to other users. Do not be alarmed if you get error 400 when trying to look for your app.

Further Updates

If you need to change your Apps Script code, simply update the code on the Apps Script project, and deploy the new version. Next, head over to the Google Workspace Marketplace store listing and update the “Sheets Add-on script version” in the app configuration. We’ve found that unpublishing and republishing the app ensures that users have the proper version. If users are stuck on old versions, then they can try re-installing the add-on and refreshing their sheets tabs.