Share Apps Script source code with Sheets installed via add-on

[Felipe Mattos]

Hi everyone, 

I have a couple of script files that are currently being used inside a Sheet bound script via libraries. However, when a user that does not have access to the independent script files tries to open the spreadsheet and utilize it, he is not able to. For this reason, I am trying to deploy the code as an add on, instead of as a library. 

Is it possible to get access to the functions inside the independent scripts via the add-on, just as I would do with a library? Would this solve the permissions issue? Is there a better alternative? 

Thank you for your time!

[Alan Wells]

An addon requires the person (account) that installs the addon to authorize permissions to anything that accesses their account. The user who installs the addon can't access the code in your Apps Script file. And the addon runs as if it was their code. So any quota limits go against the account that installed the addon. Using an addon is the only way that I know of to protect your code from being seen by the user. If that's your priority, then you'd need to use an addon. I believe that you can add a library to your Apps Script file that the addon is based on, but I don't have any experience with that so I'm not certain about how the permissions work in that situation.

[Felipe Mattos]

Thank you once again for the fast response. The Sheets file will have to be copied, is there any way I can keep the add-on installed for the user of the new spreadsheet to enable it? The add-on is supposed to be private also, of course.

[Alan Wells]

An addon can be published as unlisted so that it doesn't show up in the Workspace Marketplace. It can also be published for use only within your organization. If you need users outside of your organization to be able to install it, then you'd need to publish it as public but unlisted.

I'm not sure if the addon somehow stays associated with copied files. I think that if you share a file with a user, that the addon will show up in the addon list even though the different user would need to authorize it. 
You could have both an Apps Script file that is bound to the Sheets file, AND also require the user to install an addon. The bound Apps Script file could have nothing but some simple code in it that maybe shows an alert to the user about needing to install the addon, and/or create a custom menu. So, the bound Apps Script file will always be copied and be bound to the new Sheet file. 

[Felipe Mattos]

If the add on would have to be enabled/installed on every new temporary file, this would prohibit this approach from being implemented.

Therefore I am thinking about deploying several scripts as web applications and using them through regular HTTP requests (with doGet/doPost). I am not sure if this would be a good approach, since it would result in a large number of scripts (you can only do one doGet per script file), but in my opinion this would be more flexible and result in a better user experience.

What I am trying to achieve is: a user clicks a link inside a website, the website requests a new temporary file from apps script (this is already working) and then it redirects to the new spreadsheet URL. This spreadsheet should already contain everything, or almost everything, that the end user needs to operate it. Is should all happen in less than 20 seconds, preferably.

[Alan Wells]

Even though an Apps Script file can only have one doGet or doPost function per file, the doGet or doPost function can branch to different code depending upon some parameter passed into the event object and then return the desired result. However this would be more usage on a single bottleneck. So, having multiple Apps Script files would distribute the load. But it all depends on what the usage is. The Apps Script file that you are effectively using as an API will run as you, only have access to your account and use up your quota. The bound script will run as the user and have access to the users account. If that bound script needs to do anything to the Sheet that requires authorization, then the user must authorize the permissions needed. It's not possible to "get around" having the user being prompted for authorization if that Sheet is owned by the user and needs to do something in their account.

How long it takes to complete the process will depend on how big the Sheet is and how long it takes to copy it. Is the copied Sheet going to end up in the users account or are all these new Sheets residing in your account?

[Felipe Mattos]

All of the Sheets will reside in my own Google Workspace account. I have already implemented the new methodology with the separate Apps Script files using doGet/doPost and it is working. However, it is not as fast as it should be.

Thank you very much for your help!

[Alan Wells]

Since the Sheets all reside in your account you might be able to pre-copy Sheets before they are needed, but you'd need a system of checking how many Sheets are available and creating more. So, like an inventory system. Once your inventory gets down, make some more. That would increase the speed at which the user gets results.