AppScript: Compare values in 2 Google worksheets 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.

function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  var result1 = ui.prompt("Please enter 1st Sheet Name");
  var result2 = ui.prompt("Please enter 2nd Sheet Name");
  compare(result1.getResponseText(),result2.getResponseText());
}
 
function compare(result1,result2) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(result1);
  var sheet2 = ss.getSheetByName(result2);
  var values1 = sheet1.getDataRange().getValues();
  var range1 = sheet1.getDataRange();
  var range2 = sheet2.getDataRange();
  var values2 = range2.getValues();
  var maxRow = values1.length > values2.length ? values1.length : values2.length;
  var maxCol = values1[0].length > values2[0].length ? values1[0].length : values2[0].length;
  var backgrounds = [...Array(maxRow)].map((_, i) => [...Array(maxCol)].map((_, j) => {
    if (values1[i] && values1[i][j] && values2[i] && values2[i][j]) {
      return values1[i][j] == values2[i][j] ? 'white' : 'red';
    }
    return 'red', (values2[i] && values2[i][j]) ? 'red' : 'white'; // If you want to set "white" when values2[i][j] is empty, please use return (values2[i] && values2[i][j]) ? 'red' : 'white';
 
  }));
  range1.offset(0, 0, backgrounds.length, backgrounds[0].length).setBackgrounds(backgrounds);
  range2.offset(0, 0, backgrounds.length, backgrounds[0].length).setBackgrounds(backgrounds);
}

The only issue is that when I export data from SQL -> Google Sheet, the rows are not always in order which would render the code above useless since almost every value will be different and therefore red. Is there a way to make the code work regardless of change in rows?

Google Sheet Example

Answer 1

From your question and your provided Spreadsheet, I understood that you wanted to set the background colors to both 2 sheets when the values are different. And, you want to use the column "A" of both sheets as the search values.

In your current script, the same cell coordinates are compared. But, in the case of this question, it seems that you want to use the values of column "A" as the search values.

In this case, how about the following modified script?

Modified script:

In this modification, I modified your compare function as follows.

function menuItem1() {
  var ui = SpreadsheetApp.getUi();
  var result1 = ui.prompt("Please enter 1st Sheet Name");
  var result2 = ui.prompt("Please enter 2nd Sheet Name");
  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);
}

Testing:

When this modified script is used on your provided Spreadsheet, the following result is obtained. In this case, the values of result1,result2 are "2023" (right side) and "2022" (left side), respectively.

Note:

• This modified script is for your provided Spreadsheet. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.