Google script to work across multiple sheets

Question

I am new to Google script so apologies if this question has already been answered.

I have a spreadsheet with multiple worksheets a number of which have a status column on them somewhere.

Using code I have already found I am able set the entire row colour depending on the value in the cell in the the status column.

The problem I have is that I can only get the code to work on a single sheet.

The following code (by ScampMichael) works exactly as I want upating an entire row based on the value in the Status column but I cannot get it to work on multiple sheets (with different names) in the same workbook.

I have tried the code as seperate scripts with edited sheet names and column numbers so that each refers to an individual sheet but still only 1 sheet is updated.

Please can someone advise how I can edit this code or how I can duplicate it so that works across multiple sheets ?

function onEdit(e) {

  var statusCol = 2; // replace with the column index of Status column A=1,B=2,etc

  var sheetName = "Services"; // replace with actual name of sheet containing Status

  var cell = e.source.getActiveCell();
  var sheet = cell.getSheet();
  if(cell.getColumnIndex() != statusCol || sheet.getName() != sheetName) return;

  var row = cell.getRowIndex();
  var status = cell.getValue();

  // change colors to meet your needs
  var color;
  switch(status ) {
    case "Down":
      color = "red";
      break;
    case "":
      color = "White";
      break;
    case "Up":
      color = "green";
      break;
  }
  sheet.getRange(row + ":" + row ).setBackgroundColor(color);
}

Thank you.

Srik Srik · Accepted Answer · 2012-09-11T04:21:12

There can be only one onEdit() function for a spreadsheet. So, edits made to any worksheet has to be handled in this same function.

One way to do it is not to hardcode the value of statusCol but instead get it at runtime. Of the many possible approaches, I'll give two here

The simpler approach

var statusCols = { 'Sheet1' : 1,
                   'Sheet2' : 2,
                   'Sheet3' : 7  //etc.
                 };


function onEdit(e){
  var cell = e.source.getActiveCell();
  var sheet = cell.getSheet();

  var sheetName = sheet.getName(); 
  var statusCol = statusCols[sheetName];

  /* Whatever code you already have */
}

The second approach is more versatile

function onEdit(e){   
  var cell = e.source.getActiveCell();   
  var sheet = cell.getSheet();
  var headers= sheet.getDataRange().getValues()[0]; //Assuming headers on the first row only   
  var statusCol = headers.indexOf('Status') + 1 ; // Replace Status by the actual column header 
  /* Your existing code here */
}

Google script to work across multiple sheets

2 Answers