：匹配从一个工作表到另一个工作表的值，然后如果条件满足设置背景

Question

我是应用程序脚本的新手，并且还在学习如何做事情。下面是我想做的:我有两张单子，第一张是基于每种人的语言的列中的名字(“列表”)，第二张是时间表(“花名册”)。我想把单元格背景改为绿色，当人在8-5点按计划进行时，用人名命名的细胞。

任何帮助或想法都是非常感谢的。提前谢谢你。

以下是我迄今所做的努力：

function addShiftColor() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var actSheet = ss.getSheetByName('List');
  var rosSheet = ss.getSheetByName('Roster');
  var lastCol = actSheet.getLastColumn() +1;
  var lastRow = actSheet.getLastRow() +1;
  var end = rosSheet.getLastRow();
    for (var column = 1; column < lastCol; column++) {
      for (var row = 1; row < lastRow; row++) {
        var cellget = actSheet.getRange(row, column).getValue();
        var cellset = actSheet.getRange(row, column);
          for(i=1, j=5; i <= end, j <= end; i++,j++){
            var cell1 = rosSheet.getRange(i, 1).getValue();
            var cell2 = rosSheet.getRange(j, 253).getValue();
          if(cell1 === cellget && cell2 === "08 -- 17"){
          cellset.setBackground("green");
          }
      }
    }   
  }
}

第二张“名册”

​

第一页“名单”

​

这是我想要达到的结果。

​

以下是指向文件KRBp7v56sPV5wlciHEHA7IS0MaI/编辑?usp=共享的链接

谢谢!

Answer 1

在您的情况下，下面的示例脚本如何？

示例脚本：

function myFunction() {
  // 1. Retrieve 2 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = ss.getSheetByName("List");
  const rosterSheet = ss.getSheetByName("Roster");

  // 2. Retrieve values.
  const listRange = listSheet.getRange(2, 1, listSheet.getLastRow() - 1, listSheet.getLastColumn());
  const [, , dates, , ...rosterValues] = rosterSheet.getDataRange().getDisplayValues();

  // 3. Create an object for searching names.
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd MMM");
  const col = dates.indexOf(today);
  if (col == -1) {
    throw new Error(`Date of ${today} was not found.`);
  }
  const obj = rosterValues.reduce((o, r) => {
    if (r[0]) o[r[0]] = r[col];
    return o;
  }, {});

  // 4. Create an array for changing the background color of cells.
  const colors = listRange.getValues().map((r, i) => r.map((c, j) => obj[c] && obj[c] == "08 -- 17" ? "green" : null));

  // 5. Change the background color of cells.
  listRange.setBackgrounds(colors);
}

• 此脚本的流程如下所示。
  1. 取回两张床单。
  2. 检索值。
  3. 创建一个用于搜索名称的对象。
  4. 创建一个数组来更改单元格的背景色。
  5. 更改单元格的背景色。

测试：

当此脚本运行到示例电子表格时，将获得以下结果。

发自：

​

​

至：

​

​

注意：

• 在此示例中，使用green作为背景颜色。这是你剧本里的。如果您想要更改这一点，请修改"green"。
• 此示例脚本用于示例电子表格。因此，当电子表格的结构发生更改时，可能无法使用此脚本。所以，请小心点。

参考文献：

• 减少()
• 地图()
• setBackgrounds(彩色)

添加：

关于你下面的补充问题，

我有一个问题，如果我想为11-20和17-20添加不同的颜色，应该在脚本中修改什么？

在本例中，下面的示例脚本如何？

示例脚本：

function myFunction() {
  const colorObj = { "08 -- 17": "green", "17 -- 02": "blue", "11 -- 20": "red" }; // Please modify this for your actual situation.

  // 1. Retrieve 2 sheets.
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const listSheet = ss.getSheetByName("List");
  const rosterSheet = ss.getSheetByName("Roster");

  // 2. Retrieve values.
  const listRange = listSheet.getRange(2, 1, listSheet.getLastRow() - 1, listSheet.getLastColumn());
  const [, , dates, , ...rosterValues] = rosterSheet.getDataRange().getDisplayValues();

  // 3. Create an object for searching names.
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), "dd MMM");
  const col = dates.indexOf(today);
  if (col == -1) {
    throw new Error(`Date of ${today} was not found.`);
  }
  const obj = rosterValues.reduce((o, r) => {
    if (r[0]) o[r[0]] = r[col];
    return o;
  }, {});

  // 4. Create an array for changing the background color of cells.
  const keys = Object.keys(colorObj);
  const colors = listRange.getValues().map((r, i) => r.map((c, j) => obj[c] && keys.includes(obj[c]) ? colorObj[obj[c]] : null));

  // 5. Change the background color of cells.
  listRange.setBackgrounds(colors);
}