Using Google Apps Script Libraries

Question

I have read all Google documentation on managing and creating libraries, yet I still do not know if they are an appropriate option for the problem I am trying to solve.

I know how to save a version of a standalone script. I know how to add the library to a spreadsheet via the script editor. But I don't understand, very simply, how to trigger the library script within the new spreadsheet.

I have a spreadsheet that serves as an often-copied template within my organization. The template contains a script that (onOpen) accesses data on a separate spreadsheet (a master database) and sets those values on a tab called "admin." The desired result is to have a copy of the master database living within the template sheet (and every subsequent copy of the template sheet). At this point, there are thousands of copies of the template sheet, each running that same script.

Whenever I have to change the script, I have to change it within thousands of sheets. Can I use a library instead? I'd like to be able to create a new version of the script in the library and have all sheets connected to that library experience the change. I understand that the library needs to be in development mode (within each sheet) to do this. I also understand that in order to make this switch, I will probably still have to go into each sheet to add the library. I'm just hoping it will be the last time I have to do such a tedious task.

Any advice or links to solid info is appreciated.

Answer 1

besides making an add-on (already covered in another answer) I will answer your libraries question. They will work for you. What you are missing is the "connect" part.

For this you want to trigger the library code from say, onOpen. The onOpen in the library is not enough and not detected by apps script. Instead each of your spreadsheet's script needs an onOpen(e) which just calls yourlibrary.onOpen(e).

since those "hook" calls rarely change, specially once you stabilize your library api, and using it in "development" mode will let you modify just the library.

whenever one of those hooks needs to change (say a callback from an html GUI needs a new parameter) you need to update all the spreadsheets. to avoid this, make all your callbacks receive a single json object instead of multiple parameters.