Could I use one Google script to multiple spreadsheets

Question

Here I am asking a question of google spreadsheet and google script. As when we copy the spreadsheet, the google script code will also be copied. While I have several templates for different cases and the structure of them is quite similar. What I want to ask is it possible to link all the script code for all the template together, like temp A's code is A1 and temp B's code is A2, A1 and A2 are same and if I make some change in A1, A2 will be updated automatically.

Thank you.

Answer 1

If I understood your question right, you could use Libraries. The idea is that you create one script that you use as a library. Let us say it has a function like the following:

function myAppend()
{
  SpreadsheetApp.getActive().getActiveSheet.appendRow(["Dummy Text"]);
}

Then you save a version of it going to File -> Manage Versions. Give it a name and save new version. Next you go to File -> Project Properties and copy the Script ID - value. I will refer it as the Library ID.

Next, when you create a new Spreadsheet and a new script, you go to Resources -> Libraries. Enter the Library ID to the input labeled "Add a library" and Add.

Now, for the next phase to work, everyone using your script need to have editing rights to the Library Script, otherwise the updating feature you need will not work.

From the recently added Library you select "Development mode = on". Give a Identifier to the Library. I will use "TestLib" as my example.

Now, in your TemplateSpreadsheet script you call the function from the library as follows:

function append()
{
  TetsLib.myAppend();
}

Ok, now if your development mode in your sheet scripts is on and all the users have editing access to the library script, you should be set. Now when you copy the template sheet it will have the same script calling your library. Also, when you edit the function just in the library, all the scripts using it should update too.

Answer 2

You can use a standalone script and then call spreadsheets by id

https://developers.google.com/apps-script/guides/standalone