Sharing a Google Spreadsheet containing a script with a workgroup so they can use it, but not modify it

Question

I have written a Google Spreadsheet script which reads data from a user provided CSV file, populates a sheet with the data, makes a copy of the spreadsheet with a different name, and provides a link for the user to click to see the new spreadsheet. After making the new copy, the original spreadsheet is then cleared of user data and restored to its original state so other users can use it.

I want to share this spreadsheet with the workgroup (several hundred users), but I don't want them to be able to modify either the sheet or the script. If I share it read-only the script won't run, but to get the script to run I have to allow users edit capability and that is a bad thing.

I have googled myself silly trying to find a direction in which to go, but I am still wandering in the wilderness.

Can any of you point me in the right direction? If I need to be more specific or provide more information I will be glad to do so.

Thanks,

Larry

P.S. Other questions of note:

o Is it possible for a app-script in a spreadsheet to make the new copy active and 'close' the original shared spreadsheet?

o Can the user be made the owner of the new copy?

Answer 1

There is the option to put the code that you don't want modified, into a Stand Alone Apps Script file, then use that code as a "Library" in your script bound to the sheet. You can set the permissions on the Stand Alone Apps Script to VIEW only. That will keep people from changing it. When you share the Apps Script file with the users, they will get an email notification. So, they'll have the URL to the file, and can view it, but they can't edit it. (If you set it to VIEW only)

So, you'll be using both your spreadsheet file, and a Apps Script file. Two files, and setting the sharing to VIEW only on the Apps Script file. You can still give people EDIT access to the spreadsheet.

You'll need to go through a process of making the function available to your spreadsheet. This is called a "Library", but don't pay much attention to that name when creating the file.

• Create a Stand Alone Apps Script
• Create the function you want to share.
• Get the Project Key --> FILE, PROJECT PROPERTIES

• Share the Apps Script file with users you want to give access to, but only give them VIEW access to the file. NOT Edit.
• Give the Project Key to the user, (In this case that's just you.) and have them add the library, or if you have access to the spreadsheet, you can probably add the key yourself. So, in your case, you will not be sharing the Project Key with anyone. You don't need to.
• In the script file that is bound to the spreadsheet, click the RESOURCES menu, and the LIBRARIES menu item.
• Enter the Project Key

• Click SELECT button, and turn the library ON.
• Make sure to set the library Identifier to a key word you'd like to use.
• Go to the code editor, create a function, and type the Library Identifier.
• All the functions that are available from the Library will show up

Google Documentation - Gaining Access to a Library

Keep in mind, that creating a library is nothing more than writing code in an Apps Script in a stand alone Apps Script file. If you've done that, then all you need to share is the Project Key. The user of the Library won't see a list of all the available Libraries when Resources, Libraries is opened. You need the Key.