Mass deploy Google Apps Script powered spreadsheet to limited users

2
votes

We've 100+ users, and we created a google spreadsheet for them one by one and ask them to input raw data in the spreadsheet.

The current workflow is we've a program to export the spreadsheets one by one into CSV file and import them to our backend system. That's ok.

Now, we've learned the Google Apps Script might be a better solution, e.g. user can discover the add-ons in the spreadsheet via "Add-ons -> Get Add-ons", so we can create multiple features and fully automate the import process by calling our APIs, e.g. validation, dryrun, import etc.

The problems:

  • We will keep updating feature in the apps script, so we don't want to update all these 100+ sheets documents every time.

  • We don't want all public users can install in the add-ons, only our clients (they have Google auth)

  • Would be better if we can protect user from viewing / modifying our script

Are they possible?

Currently I think the best way is I can create a new library project and put most of the codes inside it, and create a container bounded apps script that use this library. Any better way?

2
google-apps-scriptgoogle-sheetsgoogle-apps-script-addon
A script can be either container bound, or stand alone. Is your script bound to a document or deployed on it's own? Either way, the user needs a URL address to access the file in your Google Drive. And the file can have it's Share Settings set. It would be possible for someone to discover the URL to the file who you don't want to access it, so you would need an authorization system. Either they need a Google account, so you can give them permission, or you need some other authorization system. – Alan Wells
The first question is whether or not all the people you want to give access to, have a Google account or not? If you want to be able to give access to people whether they have a Google account or not, then you can't use Sharing Settings. – Alan Wells
@SandyGood, I don't want to bound to the document as they are frequently updated, and if I distribute 100+ documents I shared with my clients, it would be a nightmare. So I want to have something like addon as in the market place, client can install/update/uninstall themselves. For the permission part, I assume they all have a valid Google account, but not necessary in my own Google Apps domain. – Ryan
An Add On to what? To the browser? I don't understand. You can publish an Apps Script as a web application, so it's basically a website, but it doesn't have a typical URL. Then from the website, users can access the document, which can be updated anytime, and the user will always see the updated version. Do you email your users, or do your users request the document; or visit a website to see the document? – Alan Wells
@SandyGood, I have edited my question to provide more background information. Thanks. – Ryan

2 Answers

0
votes

Add-ons would fulfil your requirements in protecting your code and allowing updates but currently they need to be published to the Add-on store. A solution around this would be to include a function in your add-on which would mean only whitelisted users or sheets would have the full functionality of the add-on. Whitelisting could be maintained by something as simple as a master spreadsheet. Given that all add-on's need to be approved by Google they may take the view that limiting their use is detrimental to the add-on philosophy and not publish your add-on.

Edit: When an add-on is approved by Google there are visibility options to restrict access. As per add-on publication step #12:

In the "Visibility options" section, select which users will be able to find your add-on — usually either all users or only users with an account in your Google Apps domain.

A feature request has also been opened for Google Add-on store for Google Apps domains with whitelisting and publish permissions[/edit]

Alternative solutions are very dependent on your use case. For example, if the 100+ sheets are being used for data collection and needed no additional Apps Script powered functionality you can query the sheets using their document id and extract data from them. To do the the individual sheets would have to be shared with you even with basic view access.

Managed Libraries are another option but given the user needs read access to the library and has to manually update the library version if changes are made it is not suitable in your case.

0
votes

Add-on users are not able to see its code, and once a new version is published it will automatically deployed to all users that have installed it and to all documents where the add-on is enabled.

We can somehow limit the visibility of our add-ons by choosing "Unlisted" or "Private" but if a document that uses an add-on is shared, the document editors could install that add-on, so in order to enforce who has access to the add-on features we should include someway to whitelist access to add-on features.

We could hardcode the whitelist, use the Properties service or set a way to check an external whitelist.