谷歌脚本跨多张工作表 [英] Google script to work across multiple sheets

问题描述

我有一个包含多个工作表的电子表格，其中有许多工作表都有一个状态栏他们在某处。

使用代码我已经发现我可以设置整个行颜色，取决于状态列中单元格的值。 b
$ b

我遇到的问题是我只能让代码在单张纸上工作。

以下代码（由ScampMichael ）的工作原理与我希望根据Status列中的值更新整行相同，但我无法在同一工作簿中使用多个工作表（具有不同的名称）。

我已经将代码作为带有编辑表名和列号的独立脚本进行了尝试，以便每个表单都引用单个表单，但仍然只更新一个表单。

请有人可以告诉我如何编辑此代码或如何复制它，以便跨越多张工作表？ p>

  function onEdit（e）{
 
 var statusCol = 2; //替换为Status列A = 1，B = 2等的列索引
 
 var sheetName =Services; //替换为包含状态
 
的表单的实际名称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（）; 
 
 //改变颜色以满足您的需求
 var color; 
 switch（status）{
 caseDown：
 color =red; 
休息; 
 case：
 color =White; 
休息; 
 caseUp：
 color =green; 
休息; 
 
 sheet.getRange（row +：+ row）.setBackgroundColor（color）; 
} 
  

谢谢。

解决方案

电子表格只能有一个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 */
}

这篇关于谷歌脚本跨多张工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！