javascript - Distributing Google Apps Scripts for Sheets in your company network

Question

Developing a simple script.

• My company uses Google Drive to store its documents, etc.
• We trawl through Google product datafiles in my company by loading them into spreadsheets, but ImportXML wasn't meeting all our needs.
• So I made a simple Google Apps Script file that handles the data, and spits it out to the currently active spread in an opened Spreadsheet.
• The mechanics are controlled by a UI menu/modal combo.

Finally, it works.

• I've been developing it as a script attached to a spreadsheet, but now I want to let others, with access to the Google Drive, use the thing.

But how do I get it to my co-workers?

• How do I distribute this script, and get it to run onOpen() when someone loads a new Google Sheet document?
• I just want to store the script on the company-wide Google Drive, and let people 'install' it or something, then run it through the menu UI.
• Ideally that'd mean I can always tweak the script as people are loading the script from the same source.

Is my only solution really getting people to copy-paste the code into their own documents' Scripts Editor, and run onOpen() manually? (Not at all maintainable + hard to teach).

Answer 1

If your organization is using a G Suite account, an add-on can be installed "domain wide" by the domain administrator.

To find out who your domain administrator is, use the following link:

https://support.google.com/a/answer/6208960

There is a difference between domain wide installation, and an individual installing an add-on in their user account.

For the add-on to be installed domain wide, the domain administrator must be signed in, and install the add-on as the domain administrator. First the domain administrator must open the admin console.

To log into the G Suite admin home page use the link:

https://admin.google.com/AdminHome

The administrator must navigate to the G Suite Marketplace, find the add-on, and install it.

Before an add-on can be installed, the Administrator must either enable Google add-ons from the G Suite Marketplace, or whitelist a specific add-on if you don't want to allow all add-ons to be installed.

Allow add-ons to be installed:

Navigate from the admin home page:

• Apps
• G Suite
• Drive and Docs
• Features and Applications
• Add Ons
• Turn ON

WHITELIST:

A specific Add-on can be whitelisted if add-on installation has been restricted in general. In order to whitelist an add-on the administrator will need the App ID of the add-on script. The App ID of a script must be provided by the owner of the script.

Note that the instructions provided here for domain wide installation, assume that the add-on is already published to the G Suite Marketplace. Getting an add-on verified and approved for the G Suite Marketplace is explained in the documentation.

Apply to Publish

Install Marketplace Apps Control User Installation of Marketplace Apps

An alternative to an add-on is a library:

The users who want to use the library need to do some installation. They would need to open the Apps Script code editor from the spreadsheet, and enter the library key.

Documentation - Library

NOTE:

The Apps Script API can be used to create new project (script) files and overwrite existing project files. But the problem is, that the script would need to be bound to the Sheet, and there is no current way to programmatically get the script ID of projects bound to Sheets. (If this changes, and you notice, then make a comment to update the answer) If you know the project ID that is bound to the Sheet, then you could use the Apps Script API to overwrite the script file, or you could programatically update the library version number in the project file's appsscript.json file. That would provide a way to deploy bound scripts to Sheet's files, or update library version numbers. You could manually get and save which script IDs were bound to what Sheets files, and then use the Apps Script API to overwrite the project file. The user would either need to create a script that was bound to the Sheet, and then provide the project file ID, or the user could copy a template Sheet's file with a script bound to it.