21

I have one master spreadsheet and a number of copies. This master spreadsheet uses some scripting.

Is it possible to link all the copies of this master spreadsheet to the same script as in the master spreadsheet?

Objective:

  • changes in the scripting in the master spreadsheet are automatically used by the copies
  • aka: low maintenance
Rubén
  • 34,714
  • 9
  • 70
  • 166
Stefan van Aalst
  • 788
  • 1
  • 7
  • 19

6 Answers6

18

amleczko is right: you should use the new library feature in Google Apps script.

However, as of today, you won't be able to do exactly what you want (using the same script for several spreadsheets). What you can do instead is save a version of your script (Files > Manage Versions...), in order to create a library. Then, import this library in the other spreadsheets (Resources > Manage Libraries...). Switch on the "development mode" so every change made do the library will immediately take affect in the spreadsheets using this library. Otherwise, you will have to save a new version of the library for every change, and manually update the version number of the library in every spreadsheets using it.

The problem is, you need to write a script in every spreadsheets using your library, with skeleton functions like this:

function doSomething(){
   myLibrary.doSomething();
}
antoine
  • 2,036
  • 25
  • 17
  • Seems like it is easier to just copy and paste the script into the new spreadsheet... – Kurt Leadley Sep 22 '16 at 19:39
  • 4
    Yes, copy-pasting code seems easier than creating a function in a shared libary... until you have to change something in your code :) – antoine Sep 27 '16 at 23:15
  • I actually just reversed how I am doing things. I now have a skeleton file with the script and I just paste sheets into the skeleton file. The script does its thing and deletes the sheet. So now, if I have to modify code, it is all in the same place. Luckily, in my case, I don't have to worry about the hundreds of past files I have already ran the script on. – Kurt Leadley Sep 28 '16 at 02:21
  • 2
    2021: I don't see a (Files > Manage Versions) in the Apps Script, nor do I see a (Resources > Manage Libraries) in Sheets. I would imagine that "reusing my own script across multiple docs or sheets" would be one of the most sought-for use-case scenarios for scripts. Shame on Google to make it so hard to figure out or impossible. – Pierre D Aug 28 '21 at 15:07
3

best way is to publish as add-on, then install the add-on, it will appears in every spreadsheet you open. and you can publish as private, which only seen by yourself.

gogi
  • 31
  • 2
  • 1
    "publish": is it nowadays under "Deploy"? I did deploy a `Code.gs` script, shared with all members of my GSuite. But then, even I don't see it under Add-ons > Manage add-ons nor Get add-ons. **How** do you publish and **how** do you install a script? All I see is "There are no private apps published for your domain" (and the plethora of apps in the market place). – Pierre D Aug 28 '21 at 14:57
2

I think this has changed. According to Issue 40 starting from 22 May 2012 there is such a possibility. Please check:

amleczko
  • 170
  • 9
1

It's not possible in this way that you're thinking. At least, not yet (see issue 40).

But, depending on your script usage, you may connect them "the hard way" or even better, use only one script. The script on the master spreadsheet can open the other spreadsheet files and do its job "remotely". It's not required that script to be hosted on a spreadsheet to interact with it (read/write on it). You only need a script hosted on the spreadsheet if you're going to use spreadsheet events triggers i.e. on-open, on-edit and on-form-submit.

Maybe you can develop a nice UI for the script on the master sheet and publish it as service. Then only have a link on the copies to access the same UI on a different browser tab. Adding parameters to the link the script UI can even adapt to the particular spreadsheet that is "triggering" it.

Well, that's all I can imagine now. But, unfortunately, there's some use cases that just don't fit this nice "workarounds". For those, one can only star issue 40 (to kind of vote and keep track of updates) and hope it's developed soon.

Henrique G. Abreu
  • 17,406
  • 3
  • 56
  • 65
  • Thanx, maybe I need to clarify the setting. Each copy has an onEdit event and several buttons that do something (mostly copying ranges) for that particular spreadsheet. I notice that I need to update some of the script, how to do this from one place to many copies (I have a list of all the keys of the spreadsheets involved). – Stefan van Aalst May 09 '12 at 06:49
  • It's not possible to do that automatically now. You have to manually open each script, paste the new code and save. There's some workarounds using UrlFetch to retrieve the code remotely and `eval` that might suit you, they're described in issue 40 comments. – Henrique G. Abreu May 09 '12 at 15:24
  • Issue 40 is fixed and closed. – Rubén Jan 23 '16 at 03:31
0

The solution I put in place in this context was to have a Google Site, where the Master Script is embedded, and where the Spreadsheet is embedded too

Then, the script, refering to a dedicated spreadsheet, looks for the Google Site Page's name, looks in the Master spreadsheet and get the ID of the spreadsheet which is embedded in the Page.

Cartman
  • 498
  • 8
  • 10
0

I have solved this problem when using a script which auto generates spreadsheets.

Typically, I will add a sheet to any spreadsheet with a script called "Info." I'll use that to store information that it important to the script. In my script which auto generates more spreadsheets, I keep track of the ID of the created sheet. This way, I can then quickly call up all of the "linked" sheets, and interact with them with using the same script. It might even be worth writing the script in one sheet, and keeping it totally separate from your Master sheet or it's children.

Take a look at this function, it might give you some ideas.

SpreadsheetApp.openById(id)

fooby
  • 851
  • 5
  • 6