Google-sheets – How to use a script across multiple pages of a Google Sheet

google sheetsgoogle-apps-script

I'll start by saying I'm very new to this. Here's my problem. I have a GoogleSheet I created to use as a seating chart in my classes. It is designed so that when I click the blue check button the cell where a child's name will appear turns green. It can then be reset by clicking the recycle button in the corner. All of that works fine but only on the first tab of the sheet. I would like to use it across all six tabs, one for each class I teach. Can anyone show me how to share the script across all six sections.
Here is the basic code I used:

function A3Green() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = ss.getSheets()[0];

  var colors = [
    ["#00ff40"]// These are hex equivalents
  ];

   var cell = sheet.getRange("A3:A3");
   cell.setBackground (colors);
}

Best Answer

The script is already "shared across" all the sheets in the workbook. The 0 on the following line returns the first sheet

var sheet = ss.getSheets()[0];

You could replace the 0 by 1 to get the second sheet, by 2 to get the third sheet and so on.

Reference

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

Related Solutions

Google-sheets – Insert column across multiple sheets

You could always create the column in one sheet, and copy-paste it to the other sheets, one sheet at the time. But I guess that's what you're trying to avoid.

It is possible to do this with a Google Apps Script. The following script adds a menu in a spreadsheet, which allows you to insert or remove a column on all sheets.

function addColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Insert column right of column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.insertColumnAfter(colIdx);
  }
}

function removeColumnOnAllSheets() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var colIdxStr = Browser.inputBox("Remove column no");
  if (colIdxStr == 'cancel') {
    return;
  }
  var colIdx = parseInt(colIdxStr);
  if (colIdx == NaN) {
    Browser.msgBox("You must enter a number or 'cancel'");
    return;
  }
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    var sheet = sheets[i];
    sheet.deleteColumn(colIdx);
  }
}

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name: "Add column on all sheets", functionName: "addColumnOnAllSheets"}, 
    {name: 'Remove column on all sheets', functionName: 'removeColumnOnAllSheets'}
  ];
  sheet.addMenu("Columns", entries);
}

To install this script in your own spreadsheet, click Tools -> Script editor, and paste the code into the script editor. Save the script, and reload the spreadsheet in order to show the menu.

I have set up an example spreadsheet to demonstrate the script.

Google-sheets – How to Modify Matched Cells Across Multiple Sheets with Google Script

From the question

Could a script be run when the user sets the delete column to Y

Yes, it's possible by using an on edit simple/installable or on change installable triggers.

References

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

Best Answer

Related Solutions

Google-sheets – Insert column across multiple sheets

Google-sheets – How to Modify Matched Cells Across Multiple Sheets with Google Script

Related Topic