代码之家 › 专栏 › 技术社区 › Ken

AppScript:比较同一文档中两个Google Sheets中的值并突出显示差异

google-sheets google-apps-script

Ken · 技术社区 · 2 年前

我有一个 Google Spreadsheet 其中包含两个工作表。我的目标是让AppScript突出显示任何更改为红色的值。Stackoverflow的一位友好用户能够帮助我创建一个以红色突出显示更改的脚本。问题是,该脚本仅在“玩家名称”位于A列时有效。如果“玩家名称”位于D列,则该脚本将错误地突出显示红色值。我希望脚本询问用户“玩家名称”位于哪个列,并将该列用作唯一标识符。提前谢谢。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  // Or DocumentApp or FormApp.
  ui.createMenu('Find Changes')
      .addItem('Click here to find Changes', 'menuItem1')
      .addToUi();
}

function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  var result1 = ui.prompt("Please enter 1st Sheet Name");
  var result2 = ui.prompt("Please enter 2nd Sheet Name");
  var playerNameColumnNum = ui.prompt("Please enter Player Column Number (example: Column A = 1)");
  compare(result1.getResponseText(),result2.getResponseText());
}

function compare(result1,result2) {
  // Retrieve 2 sheets.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(result1);
  var sheet2 = ss.getSheetByName(result2);

  // Retrieve values from 2 sheets.
  var range1 = sheet1.getDataRange();
  var values1 = range1.getValues();
  var range2 = sheet2.getDataRange();
  var values2 = range2.getValues();

  // Create 2 arrays for background colors to 2 sheets.
  var [obj1, obj2] = [values1, values2].map(e => e.reduce((o, [h, ...v]) => (o[h] = v, o), {}));
  var getColors = (ar, obj) =>
    ar.map(([h, ...v]) => !obj[h] ? Array(v.length + 1).fill("red") : ["white", ...obj[h].map((e, i) => e == v[i] ? "white" : "red")]);
  var colors1 = getColors(values1, obj2);
  var colors2 = getColors(values2, obj1);

  // Set backgrounds to 2 sheets.
  range1.setBackgrounds(colors1);
  range2.setBackgrounds(colors2);
}

0 回复 | 直到 2 年前

Tanaike 2 年前

在您的情况下,以下修改后的脚本如何?

修改的脚本:

function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  var result1 = ui.prompt("Please enter 1st Sheet Name");
  var result2 = ui.prompt("Please enter 2nd Sheet Name");
  var playerNameColumnNum = ui.prompt("Please enter Player Column Number (example: Column A = 1)");
  compare(result1.getResponseText(), result2.getResponseText(), Number(playerNameColumnNum.getResponseText()));
}

function compare(result1, result2, playerNameColumnNum) {
  // Retrieve 2 sheets.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(result1);
  var sheet2 = ss.getSheetByName(result2);

  // Retrieve values from 2 sheets.
  var range1 = sheet1.getDataRange();
  var values1 = range1.getValues();
  var range2 = sheet2.getDataRange();
  var values2 = range2.getValues();

  // Create 2 arrays for background colors to 2 sheets.
  var col = playerNameColumnNum; // column "D"
  var [obj1, obj2] = [values1, values2].map(([, ...e]) => e.reduce((o, r) => (o[r[col - 1]] = r, o), {}));

  var getColors = ([, ...ar], obj) =>
    [Array(4).fill(null), ...ar.map(v => !obj[v[col - 1]] ? Array(v.length).fill("red") : obj[v[col - 1]].map((e, i) => e == v[i] ? "white" : "red"))];
  var colors1 = getColors(values1, obj2);
  var colors2 = getColors(values2, obj1);

  // Set backgrounds to 2 sheets.
  range1.setBackgrounds(colors1);
  range2.setBackgrounds(colors2);
}

测试:

使用值运行此脚本时 result1, result2, playerNameColumnNum 属于 2022, 2023, 4 到您提供的电子表格,将获得以下结果。