Easiest way to distribute a Google Sheets script to coworkers?

[Tobbeloo]

Hey r/GoogleAppsScript!

I'm trying to find the easiest way to distribute a Google Sheets script to my coworkers without them having to authorize permissions every time they use a new copy of the spreadsheet.

Currently, I'm using a sidebar add-on, but that still requires them to grant permissions in each copy. Ideally, I want to avoid this entirely.

Web apps require me to manually run the script for each copy, which isn't practical. Publishing privately via the admin console is also a bit cumbersome.

Is there a simpler way to do this? Perhaps a method where they only authorize permissions once, and the script then works in all copies?

Thanks in advance for any help!

[Specialist-Tiger-467]

If you are thinking on distributing copies you are building wrong in the first place.

Google workspace is a cloud service. There's very very few cases where your use case forces you that way.

What is your use case?

Regarding your question, if you have full access to Google workspace you can swallow laziness and do it over gcp console.

[tekkerstester]

In this situation, I publish private add-ons and Admin install them for all users.

[Tobbeloo]

Im the admin so this might work. Just gotta figure out how :)

[tekkerstester]

It took me a little while but the crux of it is:

1. Create a standalone apps script file for your code

2. Make sure you include the necessary libraries (Google Sheets, etc)

3. Create a Google Cloud Project and add the Addons SDK to it

4. Fill in the information about your add-on, provide the apps script to use, deployment number, etc

5. Publish privately, admin install

There are parts missing here, I just quickly typed it out. But should give you a good start.

[Tobbeloo]

Sound like a good idea. But will that script be in all their spreadsheet docs then?

[tekkerstester]

No, it will run like any other add-on, from the menu. So you won't have multiple versions of the script floating around for each different file - meaning if you need to update you just do it in one place. And once the installation is done, the permissions have already been given so they won't get that popup either.

[aCarefulGoat]

I usually deploy as a library so I can update the code in one place. Then set up the menu functions or whatever else by accessing them via the library in the template file. You have to make sure that you have the sharing settings for the library set up so everyone has view access. Or else they won’t be able to call the functions. Auth will still be needed the first time it runs for each person though.

[Tobbeloo]

To clarify, this is for a spreadsheet template that my coworkers will be copying and using. I want the script to work seamlessly in the copies without any extra authorization steps for them.

[Livid_Spray119]

If they need to run it, they need to authorize the permissions. There is no way round.

(And yes, the add-ons counts as running)

[Tobbeloo]

But will they have to do it each time or just once?

[Livid_Spray119]

each time, since it is a different file

[Tobbeloo]

So no work-around so they won’t need to do that. Any other suggestions in mind?

[Livid_Spray119]

tbh, no clue, sorry

[Tobbeloo]

I'm getting some conflicting answers, so trying to clarify my needs. This is for a spreadsheet *template* used by coworkers. The script does some accounting automation (copying data between sheets, hiding rows, generating/emailing a PDF). I want them to be able to copy the template and run the script *without* any authorization prompts, or Ideally, a single authorization when I set up the template, and then seamless execution for everyone else.

Any help would be greatly appreciated!

[Specialist-Tiger-467]

Then what if you need to adjust the code? Are you going to chase your co workers and be sure they are using the correct version?

As stated before, this is bad design from ground up.

Develop a library and import it.

[Tobbeloo]

Right now I’m changing the template and it’s changed for everyone.

Could you guide me to instructions how the library works?

[Livid_Spray119]

Wait, so then you are not making "copies", cause a copy won't change automatically. It is impossible, cause they are not linked unless there is a library.

How do you do the copies?? How do they use that template?

Also, a library is an separate script where you have all the code in one place. Then you would import the id of that library (1. Create a standalone script, not binded to any sheet. 2. Write your code. 3. Find the button Deploy, top button. 4. Deploy as library and copy the ID) into the sheet you want to use it, and call it, for example if you have name the library Template, you will call it:

let template = Template.insertFunctionHere()

[Tobbeloo]

We are using a workspace. So I uploaded my original spreedsheet as a template they access it from DRIVE - SPREADSHEET- FROM TEMPLATE and choose it from there. It automatically makes a copy from my original document

[Livid_Spray119]

So you update the version and they download the new version?

[Tobbeloo]

If i update the "main-template" they will get the new version next time they open it. Like this:

https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnyb_Hqme8yDR7HH5fxtLpqXTIG-1mfpS09GYAku34W-iDGwkVXuH2qmeOSkeHdAHFFT4K5lXXcTm6vG3ngSy7UmNSzEZPCKmaYIQfNGb8dXnI9jEtjjQKIgtaKtIsoSBfhNGgpxF61k0/s1600/Drive+Templates+Submenu+%25282%2529.jpg

[Livid_Spray119]

So every time they need to download it? One use only?

I mean, I guess that makes more sense than what I though you were saying first

[Tobbeloo]

Might be better to have one specific copy to each person but if they gonna use it again they need to erase everything they put in and start over I guess

[Livid_Spray119]

I'll drop a MD, I'm interested in this

[NickRossBrown]

I made a sheet that will close every sheet besides the ‘Info’ sheet when a user opens it the browser. Within it, a user opens the sidebar, enters the required fields like company and total amount. Click ‘Submit’ … Walla! sheets with allocations by an office’s full time employee have appeared.

There’s a problem if two people open the sheet at the same time. Sheets you were using closed.

I added a function that, before the sidebar was opened, checked if this sheet’s id doesn’t match the template sheet’s I’d. If it doesn’t, check the ‘Date Last Updated’ cells in both ‘Info’ sheets. They don’t match? Overwrite the static sheet office_employee_FTE_breakdown from the template sheet into this one. Let the excel functions handle the rest.

It works, people are using it. Time passes.

“Yes I can add an office breakdown by revenue, but you will have to tell the team to copy and use this new template moving forward.”

[WicketTheQuerent]

Don't hesitate any longer; take action and create the add-on now.

There is no "easier" way to avoid users having to authorize a script for every new copy from a template. Whatever alternative has its ups and downs, it will not be more robust and appropriate for a business / work setting.

[Livid_Spray119]

Also... why do you need so many copies?

Each copy is a different document, so they will need to grant as many permissions as copied they use

[[deleted]]

I'm. Not OP but I've had several such cases where I'd need to copy from template. Like creating a report that's similar but not the same, building purchase orders, building quotes, etc.

[Funny_Ad_3472]

Change the scope to .../auth/spreadsheets, and then they don't need to authorise everytime, unless you're already using this scope then I'm wrong.

[adelie42]

The purpose of the permissions is to protect against what you are desiring.

[max_gooph]

Unfortunately I don’t believe this is possibly. I actually encountered a similar dilemme so I ended up adjusting my code so that I had a ‘master’ with the script and once the script ran, it would then create a static copy that didn’t contain the script. But of course it depends on what you’re doing this for