Insert a script into multiple Google Spreadsheets

Question

I am an amateur coder - I really only do a few things to make my life easier. I set up Google Forms and Spreadsheets for tracking discipline problems in different grades at my school. I wrote a short script that notifies the appropriate people by e-mail of any submission and that can filter and create reports about selected learners.

Because I do DIY coding (English and French teacher, so I did not study programming), I often come up with some improvement that should have been obvious from the start. I really do not want to copy and paste the script improvement into every spreadsheet's script editor. I have copied this script as a stand-alone file in Google Drive but I cannot insert it into any of the spreadsheets because the gallery only has access to published scripts/apps. Is there a way around this? I've seen mentions of using libraries, but I have no clue even where to begin. It feels a bit silly that you can create a script as a stand-alone in Google Drive but not actually use it.

Any help would be appreciated.

Answer 1

This was initially raised as a problem a while ago, and Google's solution was the "libraries" feature.

So you will want to

1. You must save a version of your project. File > Manage Versions...
2. Then go into File > Project Properties and copy the project key.
3. Then in all other spreadsheets, you must go into the Script Editor and to go Resources > Manage Libraries. Then paste the project key from above and add it to that spreadsheet script.

There is not an automated way to accomplish this. In order to have a script run on any spreadsheet, you must go into the Script Editor for each spreadsheet.

Source

Answer 2

The other answer (Phil Bozak) is not bad at all since it gives a general overview of how to build a library but I'm afraid it won't be sufficient to put you really 'on the right track'.

As mentioned in the documentation You can use your included library just as you would use a default service which means that library functions are not directly available as a script but should rather be considered as a toolbox that script functions can call.

So in each of your spreadsheet you should have a number of elementary functions that actually 'call' the library service, this latter taking care of the real action.

Let me take a simple example : let's suppose you want to change the background color of a sheet, you have a function in yourLibrary that changes the color of the current sheet (yourLibrary is the name you gave to the library), this function would be called changeColor.

Now in your script you can use yourLibrary.changeColor but this "service call" must be present in the spreadsheet's script itself if you want it to be executed on a special action (onEdit, onOpen... or in a menu call) and the library must be referenced in the ressources of that same script editor as well.

All this to explain that the library feature is very useful and very easy but you still need a 'skeleton script' in each spreadsheet and you will have to think about how to rebuild your script with a basic structure that calls the 'updatable parts that do the real stuf'.

Not so simple but (after that is done) quite comfortable ;-)

Hoping this is clear enough.

Answer 3

You may also want to think about whether your individual spreadsheets even need their own container scripts. Why can't your one standalone just read in and run reports on all the sheets?

Answer 4

Another solution may helps you, You can create Script Apps File in drive and put all spreadsheets ID you want in Array and run script with loop trick