Paste the script into multiple google tables

Question

I am an amateur coder - I really do a few things to make my life easier. I created Google Forms and spreadsheets to track discipline issues across grades in my school. I wrote a short script that notifies relevant people via email of any submission and can filter and generate reports about selected students.

Since I do DIY coding (a teacher in English and French, so I did not study programming), I often come up with some improvements that should have been obvious from the start. I really don't want to copy and paste script improvements into every script table editor. I copied this script as a separate file in Google Drive, but I can’t paste it into any of the spreadsheets, because the gallery has access only to published scripts / applications. Is there any way around this? I have seen references to the use of libraries, but I do not know where to start. It's a little silly that you can create a script as standalone in Google Drive, but not really use it.

Any help would be appreciated.

Answer 1

This initially arose as a problem, and Google's solution was a "library."

So you want

• You must save the version of your project. File> Version Control ...
• Then go to File> Project Properties and copy the project key.
• Then, in all other spreadsheets, you must enter the Script editor and go to Resources> Manage Libraries. Then paste the project key on top and add it to this script table.

There is no automated way to accomplish this. To be able to run Script on any spreadsheet, you must go to the Script editor for each spreadsheet.

A source

Answer 2

The other answer (Phil Bozak) is not bad, as it gives a general overview of how to build a library, but I am afraid that this is not enough to put you on the “right path”.

As mentioned in the documentation, you can use your included library in the same way as using the default service , which means that library functions are not directly accessible as a script, but rather should be considered as a toolbar that can call script functions.

So, in each of your spreadsheets you should have a number of elementary functions that actually “call” the library service, and the latter takes care of the real action.

Let me give you a simple example: 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 the library), this function will be called changeColor .

Now in the script you can use yourLibrary.changeColor , but this "utility call" must be present in the script spreadsheet itself if you want it to be performed by a special action (onEdit, onOpen ... or in the menu call), and the library should be listed in the registry of the same script editor.

All this explains that the library function is very useful and very simple, but you still need a “skeleton script” in each table, and you will have to think about how to rebuild your script with a basic structure that calls the “ updatable parts that make the real stuf ".

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

Hoping this is clear enough.

Answer 3

You can also think about whether your personal tables even need their own container scripts. Why can't your separate version read and run reports on all sheets?

Answer 4

Another solution may help you. You can create a Script Application File on disk and put the entire spreadsheet in an Array and run the script using a loop trick