4

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.

Rubén
  • 34,714
  • 9
  • 70
  • 166
MellyCopter
  • 51
  • 2
  • 6
  • Sounds like a valid reason to use a library. But did you post this question just for such an answer? The only question I can take out of this is whether you should use a library or not. – Casper Jun 27 '17 at 15:20
  • 1
    @casper "But I don't understand, very simply, how to trigger the library script within the new spreadsheet... Any advice or links to solid info is appreciated." - Thanks! – MellyCopter Jun 27 '17 at 15:22
  • the table on [this page](https://developers.google.com/apps-script/guides/libraries) shows an overview of shared and not-shared resources. You'll have to include the library and setup a trigger for every sheet as the triggers are not-shared. – Casper Jun 27 '17 at 15:26
  • 1
    @Casper Ah ha... so that's still quite labor intensive. I suppose an Add-On might work better? – MellyCopter Jun 27 '17 at 15:56
  • With cooperation from your domain administrator, you could deploy your code as an add-on that is available to users in your domain: https://developers.google.com/apps-script/add-ons/ – terrywb Jun 27 '17 at 15:58

3 Answers3

5

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.

Zig Mandel
  • 19,571
  • 5
  • 26
  • 36
  • But if you create a new version of the library won't you will have to go into each project and select the new version? If so, you can't remove the possibility of tedious work. – Cooper Jun 27 '17 at 16:49
  • 1
    no, because as op said, you set it to use the development version. – Zig Mandel Jun 27 '17 at 19:54
  • Cool. I missed that. – Cooper Jun 27 '17 at 20:16
  • I was so confused by Google's lack of documentation. Hours later, I found your answer, and this was key for me, so thank you: "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)." – Ryan Sep 01 '19 at 23:29
5

Sorry if I am repeating other answers, but I would like to sum up and add something:

You can access your library functions as follows:

From the app using the library you go to the Resources/Libraries. You can see the library name under "Identifier". On the same line where you can select Development mode.

Library name found in resources

Now in your library you have for example a function 

    function onOpen(e)
   {
     Browser.msgBox("HELLO!"); 
   }

In the spreadsheet app you wish to access it you use the library name found in the resources, for example "testlibrary"

function onOpen(e)
{
  testlibrary.onOpen(e);
}

Now if you have development mode on, the modifications to functions in the library update automatically to your application (spreadsheet) as long as the user using the application has edit access in your library script.

If anyone using your spreadsheet has a restricted access to your library script (meaning only view access) or development selection is off in the application, you have to go to the application's script, Resources/Libraries and select the most recent version of your library to be used in the app everytime you update the library and save a new version of it.

Still, especially if you are using mostly only the onOpen function , I would recommend using the library rather than copy-pasting the function to the script of each spreadsheet, as it is easier to track which scripts are up to date and it is easier to avoid errors and differences between the scripts.

Even in the more restricted case, if you update function in library - as long as you are already calling it in the app - all you have to do is select the new version of the library used.

I hope I had anything to give in this conversation and my language was appropriate, this was my first answer..

FatFingersJackson
  • 772
  • 5
  • 15
  • I am looking for this feature of updates being picked by clients automatically. Can you provide your inputs on my case? https://stackoverflow.com/questions/46136435/apps-script-library-automatic-updation-on-client-side-on-doc-refresh?noredirect=1#comment79235289_46136435 – user362953 Sep 10 '17 at 01:50
  • I did not understand "using the library rather than copy-pasting the function to the script of each spreadsheet, as it is easier to track which scripts are up to date and it is easier to avoid errors and differences between the scripts." – aNewb Feb 20 '21 at 22:53
-1

A good question Melly. I've been through a bunch of the documentation and some tutorials on this subject but haven't tried adding any libraries yet. My understanding is that once you are connected to a library all you have to do is call the applicable functions. That once a library is connected the functions in the library become an extension of all the other Apps Script classes available in the script editor.

RobertL
  • 1