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?
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.
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.
