AppScript: Compare values in 2 Google Sheets in the same document and highlight differences

Question

I have a Google Spreadsheet which contains two worksheets. My goal is to have the AppScript highlight any values that changed in red. A friendly user from Stackoverflow was able to help me create a script that would highlight changes in red. The issue is that the script only works when "Player Name" is in Column A. If "Player Name" is in Column D, then the script will highlight values in red incorrectly. I want the script to ask the user which Column is "Player Name" located and use that Column as a unique identifier. Thanks in advance.

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);
}

Answer 1

In your situation, how about the following modified script?

Modified script:

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);
}

Testing:

When this script is run with the values result1, result2, playerNameColumnNum of 2022, 2023, 4 to your provided Spreadsheet, the following result is obtained.