Compare the values in two Google worksheets in the same document

Question

I have a Google spreadsheet which contains two worksheets. Each sheet contains a column of URLs followed by 6 columns of data. I would like to highlight on the second sheet if the value is different, ideally styling the particular cell with a red or green background depending on the value.

Sheet 1

╔═════════════╦═════╦════╦═════╦════╦════╦════╗
║     URL     ║  d1 ║ d2 ║ d3  ║ d4 ║ d5 ║ d6 ║
╠═════════════╬═════╬════╬═════╬════╬════╬════╣
║ example.com ║ 400 ║ 11 ║ 2.2 ║ 50 ║ 60 ║ 70 ║
╚═════════════╩═════╩════╩═════╩════╩════╩════╝

Sheet 2

╔═════════════╦═════╦════╦═════╦════╦════╦════╗
║     URL     ║  d1 ║ d2 ║ d3  ║ d4 ║ d5 ║ d6 ║
╠═════════════╬═════╬════╬═════╬════╬════╬════╣
║ example.com ║ 400 ║ 11 ║ 2.2 ║ 55 ║ 68 ║ 90 ║
╚═════════════╩═════╩════╩═════╩════╩════╩════╝

In this case, I'd like the 55, 68 and 90 value to be highlighted as they are different from sheet 1.

My quick solution was to export each sheet as a CSV and then run a diff over them to highlight changes, but this doesn't fully achieve what I'd like.

Answer 1

Conditional formatting

The conditional formatting can do that, but it can only reference cells within the same sheet. To get around this restriction, place the command such as

=arrayformula(Sheet1!B2:H2)

somewhere on the second sheet (e.g., in cell AB2). Then use conditional formatting for the range B2:H2 with the rule "value is not equal to ... =AB2".

(The conditional formatting formula is entered as it should be read for the upper left corner of the range being formatted).

Apps Script

Another approach, avoiding duplication of data, is to use an Apps script. Here is sample code that sets backgrounds according to the situation you described. Note this will not update automatically unless it's set to trigger on every edit.

The aforementioned approach with conditional formatting is to be preferred.

function compare() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName('Sheet1');
  var sheet2 = ss.getSheetByName('Sheet2');
  var values1 = sheet1.getRange('B2:H2').getValues()[0];
  var values2 = sheet2.getRange('B2:H2').getValues()[0];
  var backgrounds = []; 
  for (var k = 0; k < values1.length; k++) {
    backgrounds.push(values1[k] == values2[k] ? 'white' : 'red');
  }
  sheet2.getRange('B2:H2').setBackgrounds([backgrounds]);
}

Answer 2

Overview

Use INDIRECT in the custom formula of the conditional formatting rule like the following one:

=INDIRECT("Sheet1!R"&ROW()&"C"&COLUMN(),false)<>B2

Explanation

The Google Sheets built-in conditional formatting feature doesn't allow the use of references to different sheets directly, but it allows the use of INDIRECT.

The above formula includes two "hacks". The first was mentioned in the overview, the use of INDIRECT. The second "hacks" is to use the R1C1 notation together with ROW() and COLUMN() to make the conditional formatting rule very flexible compared with the use of a hardcoded reference and to behave like a "normal" reference.