0

What I want to do within my gsuite account:

To (dynamically) associate a script with multiple sheets so I can maintain their common code in one file. (e.g. populate a custom menu with options that display when that spreadsheet opens.)

What I've tried:

  • created a script using the script editor at https://script.google.com , but was unable to associate the script with a sheet
  • loading the externally created script through the script editor's "File > open" option
  • creating a script directly from a sheet using the script editor option from the sheet's menu but found no option to share it
  • publishing the script, but it seems like this makes the script accessible to the general public
  • I've read about container bound scripts, but I find the documentation unclear and am unsure how to proceed.
Shane
  • 4,921
  • 5
  • 37
  • 53
  • 1
    Related: [Distribute Google Apps Script and push updates](https://stackoverflow.com/q/16997274/1595451) – Rubén Dec 08 '17 at 19:04
  • 1
    Related: [Google Spreadsheet Scripts shared across spreadsheets (not libraries)](https://stackoverflow.com/q/14473368/1595451) – Rubén Dec 08 '17 at 19:09
  • 1
    Related: [One Google Apps Script referenced by many documents](https://stackoverflow.com/q/43479038/1595451) – Rubén Dec 08 '17 at 19:21

1 Answers1

1

your first idea was a good one:

created a script using the script editor at https://script.google.com , but was unable to associate the script with a sheet

From a script, you can edit multiple sheets using their IDs. For example:

// this function edits two sheets at once
function editBothCells() {

  // assigns a variable to a spreadsheet
  var ss1 = SpreadsheetApp.openById("1CMLVG3Wnk4C-Ck0j7YKY_vgZZNj58mip_VRNUdrjpy8");
  var ss2 = SpreadsheetApp.openById("1kxC_0AwCwapVWG5grRNBLSHYhKpcVOHbrXTZdG9fOc4");

  // runs a function on both spreadsheets
  boldenA4(ss1,ss2);
}

// this function takes cell A4 and makes it bold for all arguments
function boldenA4(spreadsheet1, spreadsheet2, and_so_on){

  for(var i = 0; i < arguments.length; i++){
    arguments[i].getSheets()[0]
    .getRange("A4")
    .setValue("hello stackoverflow") // could be any string 
    .setFontWeight("bold"); 
  }
}

Below are the links to both sheets and the script:

Antoine Colson
  • 660
  • 5
  • 8
  • I had to change my question because this answers what I asked, but I failed to be specific about what I meant! :) I am hoping to create a menu with common functions that are meant to operate on any sheet opened in a particular folder. – Shane Jan 04 '18 at 03:50