AppScript: Compare values in two 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 to highlight any values that changed in red. I found a solution on Stackoverflow but it's only comparing 1 row at a time. I'm new to Appscript and I'm trying to change the script to ask the user what the worksheets names are and also compare the whole sheet (not just 1 row). I'm not sure why my script is not running. Here is an example Google Sheet if needed.

Thank you 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");
  compare(result1,result2);
}


function compare() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName(result1);
  var sheet2 = ss.getSheetByName(result2);
  var values1 = sheet1.getRangeList(['A1:Z1000']).getValues()[0];
  var values2 = sheet2.getRangeList(['A1:Z1000']).getValues()[0];
  
  var backgrounds = []; 
  for (var k = 0; k < values1.length; k++) {
    backgrounds.push(values1[k] == values2[k] ? 'white' : 'red');
  }
  sheet2.getRangeList('A1:Z1000').setBackgrounds([backgrounds]);
}

Answer 1

Modification points:

• In your menuItem1(), you call compare(result1,result2);. But, at the function compare, the arguments are not used like compare().
• And, even when function compare(result1,result2) { is used, the values result1,result2 are PromptResponse object. Please be careful about this.
• Unfortunately, Class RangeList has no method of getValues and setBackgrounds. Please be careful about this.

When these points are reflected in your script, how about the following modification?

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");
  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 range2 = sheet2.getDataRange();
  var values2 = range2.getValues();
  var backgrounds = values1.map((r, i) => r.map((c, j) => c == values2[i][j] ? 'white' : 'red'));
  range2.setBackgrounds(backgrounds);
}

• In this modification, when you run "Original" and "Copy" are put to the 1st and the 2nd prompt, respectively, the cells of "Copy" sheet are highlighted.

Testing:

When your provided Spreadsheet is used, the following result is obtained.

Note:

• From your script of var values1 = sheet1.getRangeList(['A1:Z1000']).getValues()[0]; and var values2 = sheet2.getRangeList(['A1:Z1000']).getValues()[0];, I used the data range.

References:

• Class RangeList
• map()

Added:

From your following reply,

By any chance do you happen to know why I am getting Error message “TypeError: Cannot read properties of undefined (reading ‘0’)”? pastebin.com/c6DYgvAM

How about the following script?

Sample 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");
  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 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'; // If you want to set "white" when values2[i][j] is empty, please use return (values2[i] && values2[i][j]) ? 'red' : 'white';
  }));
  range2.offset(0, 0, backgrounds.length, backgrounds[0].length).setBackgrounds(backgrounds);
}