Apps 脚本：如果工作表 A、B、C 满足条件，则对工作表 X、Y、Z 应用操作

我有一个 Google Sheets 脚本，如果在另一张工作表中满足条件，则该脚本会在一张工作表的单元格中设置单元格的背景颜色

如果在“Client TDL”工作表上满足条件，它将更改“Client”工作表中指定单元格的背景。

这是我的代码：

    function tasks() {
var sh1 = ss.getSheetByName("Client TDL");
var sh4 = ss.getSheetByName("Client");
var tasks1 = sh1.getRange("A2:C11").getValues();

for (var i = 0; i < tasks1.length; i++) {
    if (tasks1[i][0] === 'Y')
    {       
      var sh1Tasks1 = sh4.createTextFinder(tasks1[i][2]).findNext().getCell(1,1).
      getA1Notation()                 

      sh4.getRange(sh1Tasks1).offset(0,1).setBackground('#d9ead3')
    } 
                                      
}
}

我想要实现的目标：

目前，该脚本仅适用于“Client”工作表和“Client TDL”工作表。

我想在 2 组工作表上应用此操作。第一组称为“客户名称”表，第二组称为“客户名称 TDL”

因此，如果满足条件，我希望每个“客户名称 TDL”工作表在每个相应的“客户名称”工作表上应用该操作。

例如，如果客户端名称是“X”。它将有一个名为“X”的工作表和另一个名为“X TDL”的工作表。如果“X TDL”中满足条件，则会在工作表“X”上执行更改。

知道“客户端 TDL 表”/“客户端表”对的数量是动态的，我如何在 Apps 脚本中编写它？

• 该脚本应通过按钮运行，因此简单的触发器无法完成该工作。
• getActiveSheet()无法使用，因为我需要定义“客户端”和“客户端 TDL”表。

下面是我修改后的脚本：

我尝试做的事情：

1. 定义包含文本“TDL”的所有工作表
2. 定义排除文本“TDL”的所有工作表AND包括客户的姓名（这是因为我的电子表格中有其他名称的工作表，所以我不能only调用不包含文本“TDL”的工作表，否则也会调用那些不需要的工作表。

脚本已执行，但没有执行任何操作。

修改后的脚本：

function tasksV2() {
  var ss = SpreadsheetApp.getActive()
  var sheets = ss.getSheets();
  var sh0 = sheets.filter(s => s.getSheetName().includes('TDL'));
  var clientsheetname = sh0.forEach(cs=>cs.getSheetName())
  var sh1 = sheets.filter(s => !s.getSheetName().includes('TDL') && s.getSheetName().includes(clientsheetname));
  sh0.forEach(s0=>{
    sh1.forEach(s1=>{
var tasks1 = s0.getRange("A2:C11").getValues();
var tasks2 = s0.getRange("D2:F11").getValues();
var tasks3 = s0.getRange("G2:I11").getValues();
var tasks4 = s0.getRange("J2:L11").getValues();
var tasks5 = s0.getRange("M2:O11").getValues();

for (var i = 0; i < tasks1.length; i++) {
    if (tasks1[i][0] === 'Y')
    {       
      var sh1Tasks1 = s1.createTextFinder(tasks1[i][2]).findNext().getCell(1,1).
      getA1Notation()                 

      s1.getRange(sh1Tasks1).offset(0,1).setBackground('#d9ead3')
    } 
                               
}    
  })   
  }) 
}

这是示例电子表格： https://docs.google.com/spreadsheets/d/1mhMsTCyjWMWWkUF6INk0_XulNTjZLks1GpVZzdVJkyg/edit#gid=0 https://docs.google.com/spreadsheets/d/1mhMsTCyjWMWWkUF6INk0_XulNTjZLks1GpVZzdVJkyg/edit#gid=0

建议：组装一个 JavaScript 对象

我保留了大部分工作代码并添加了一个 JavaScript 对象来动态存储 JSON 对象中的所有工作表名称。请注意，这假设有一对模式“客户端 1 TLD”对应“客户端 1”。

脚本 1：一键处理一张 TLD 表

function amextdl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Step1: Assemble the JavaScript Object
  var listTDL = ss.getSheets().map(x => x.getName()).filter(y => y.match(/TDL/));
  var listNoTDL = listTDL.map(z => z.replace(' TDL', ''))
  var sheetsDictionary = listTDL.reduce((x, i, j) => {
    x[i] = listNoTDL[j];
    return x;
  }, {});

  //Step2: Access the key-value pair for the active sheet
  var sh0 = ss.getActiveSheet();
  var sh1 = ss.getSheetByName(sheetsDictionary[sh0.getName()]);

  //Step3: Process accordingly
  //from original script:
  var tasks1 = sh0.getRange("A2:C11").getValues();
  var tasks2 = sh0.getRange("D2:F11").getValues();
  var tasks3 = sh0.getRange("G2:I11").getValues();
  var tasks4 = sh0.getRange("J2:L11").getValues();
  var tasks5 = sh0.getRange("M2:O11").getValues();

  for (var i = 0; i < tasks1.length; i++) {
    if (tasks1[i][0] === 'Y') {
      var sh1Tasks1 = sh1.createTextFinder(tasks1[i][2]).findNext().getCell(1, 1).getA1Notation()
      sh1.getRange(sh1Tasks1).offset(0, 1).setBackground('#d9ead3')
    }

    if (tasks2[i][0] === 'Y') {
      var sh1Tasks2 = sh1.createTextFinder(tasks2[i][2]).findNext().getCell(1, 1).getA1Notation()
      sh1.getRange(sh1Tasks2).offset(0, 2).setBackground('#d9ead3')
    }

    if (tasks3[i][0] === 'Y') {
      var sh1Tasks3 = sh1.createTextFinder(tasks3[i][2]).findNext().getCell(1, 1).getA1Notation()
      sh1.getRange(sh1Tasks3).offset(0, 3).setBackground('#d9ead3')
    }
    if (tasks4[i][0] === 'Y') {
      var sh1Tasks4 = sh1.createTextFinder(tasks4[i][2]).findNext().getCell(1, 1).getA1Notation()
      sh1.getRange(sh1Tasks4).offset(0, 4).setBackground('#d9ead3')
    }
    if (tasks5[i][0] === 'Y') {
      var sh1Tasks5 = sh1.createTextFinder(tasks5[i][2]).findNext().getCell(1, 1).getA1Notation()
      sh1.getRange(sh1Tasks5).offset(0, 5).setBackground('#d9ead3')
    }
  }
}

脚本2：一键处理所有纸张

function amextdl() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Step 1: Group the sheet names into 2 different arrays
  var listTDL = ss.getSheets().map(x => x.getName()).filter(y => y.match(/TDL/));
  var listNoTDL = listTDL.map(z => z.replace(' TDL', ''));

  //Step 2: Assemble the JavaScript Object using the 2 arrays
  var sheetsDictionary = listTDL.reduce((x, i, j) => {
    x[i] = listNoTDL[j];
    return x;
  }, {});

  //Step3: Process accordingly by using map() on listTDL array
  listTDL.map(x => {
    var sh1 = ss.getSheetByName(sheetsDictionary[x]);

    //from original script:
    var tasks1 = ss.getSheetByName(x).getRange("A2:C11").getValues();
    var tasks2 = ss.getSheetByName(x).getRange("D2:F11").getValues();
    var tasks3 = ss.getSheetByName(x).getRange("G2:I11").getValues();
    var tasks4 = ss.getSheetByName(x).getRange("J2:L11").getValues();
    var tasks5 = ss.getSheetByName(x).getRange("M2:O11").getValues();

    for (var i = 0; i < tasks1.length; i++) {
      if (tasks1[i][0] === 'Y') {
        var sh1Tasks1 = sh1.createTextFinder(tasks1[i][2]).findNext().getCell(1, 1).getA1Notation()
        sh1.getRange(sh1Tasks1).offset(0, 1).setBackground('#d9ead3')
      }

      if (tasks2[i][0] === 'Y') {
        var sh1Tasks2 = sh1.createTextFinder(tasks2[i][2]).findNext().getCell(1, 1).getA1Notation()
        sh1.getRange(sh1Tasks2).offset(0, 2).setBackground('#d9ead3')
      }

      if (tasks3[i][0] === 'Y') {
        var sh1Tasks3 = sh1.createTextFinder(tasks3[i][2]).findNext().getCell(1, 1).getA1Notation()
        sh1.getRange(sh1Tasks3).offset(0, 3).setBackground('#d9ead3')
      }
      if (tasks4[i][0] === 'Y') {
        var sh1Tasks4 = sh1.createTextFinder(tasks4[i][2]).findNext().getCell(1, 1).getA1Notation()
        sh1.getRange(sh1Tasks4).offset(0, 4).setBackground('#d9ead3')
      }
      if (tasks5[i][0] === 'Y') {
        var sh1Tasks5 = sh1.createTextFinder(tasks5[i][2]).findNext().getCell(1, 1).getA1Notation()
        sh1.getRange(sh1Tasks5).offset(0, 5).setBackground('#d9ead3')
      }
    }
  });
}

参考：

• JavaScript 对象 https://www.w3schools.com/js/js_objects.asp
• map() https://www.w3schools.com/jsref/jsref_map.asp
• filter() https://www.w3schools.com/jsref/jsref_filter.asp
• reduce() https://www.w3schools.com/jsref/jsref_reduce.asp
• match() https://www.w3schools.com/jsref/jsref_match.asp