电子表格只能有一个onEdit（）函数。所以，对任何工作表的编辑都必须在这个相同的函数中处理。

执行此操作的一种方法不是硬编码statusCol的值，而是在运行时获取它。
在许多可能的方法中，我会在这里给出两个

更简单的方法

  var statusCols = {'Sheet1'：1，
'Sheet2'：2，
'Sheet3'：7 //等。 
}; 


 function onEdit（e）{
 var cell = e.source.getActiveCell（）; 
 var sheet = cell.getSheet（）; 

 var sheetName = sheet.getName（）; 
 var statusCol = statusCols [sheetName]; 

 / *无论你已经拥有哪些代码* / 
} 
 

第二种方法更通用

  onEdit（e）{
 var cell = e.source.getActiveCell（ ）; 
 var sheet = cell.getSheet（）; 
 var headers = sheet.getDataRange（）。getValues（）[0]; //仅在第一行假设标题
 var statusCol = headers.indexOf（'Status'）+ 1; //将状态替换为实际的列标题
 / *您现有的代码在这里* / 
} 
 

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.

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 */
}