Using Google Sheets scripts, why does my if statement always return false when comparing cell values?

Question

I need to compare two cell values and act on them if they are different. My "if" statement always returns false when comparing the cell contents however, and I can't figure out why:

function onEdit() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet1'); // apply to sheet name only 
  var testvalues = sheet.getRange('A1:A2').getValues(); // array of values to be tested
  var testvalue1 = testvalues[0]; // The contents from A1
  var testvalue2 = testvalues[1]; // The contents from A2
  var test1 = testvalue1 == testvalue2; // True False test
  var test2 = testvalue1 === testvalue2;// True False test
  Browser.msgBox(testvalue1 + " and " + testvalue2 + " being the same is " + test1 + " and " + test2); // Sentence revealing outcome
};

test1 and test2 always return false no matter what the contents of cell A1 and A2 are (empty, number, text, different). If I edit the testvalues to this:

  var testvalue1 = "We are the same value";
  var testvalue2 = "We are the same value";

or

  var testvalue1 = testvalues[0];
  var testvalue2 = testvalues[0];

then suddenly the tests both return True as expected. Can anyone tell me what I'm missing in this, it's infuriating? Currently in A1 and A2 is the value 1 (before I potentially get asked about that).

The end goal is to use a border to partition different cell contents. I have the rest of it working perfectly but I've isolated the problem to the above concept.

Is it comparing objects under the hood? What are the types of testvalue1 and testvalue2? — Ollie, Apr 06 '20 at 15:14
Does this answer your question? [How to compare arrays in JavaScript?](https://stackoverflow.com/questions/7837456/how-to-compare-arrays-in-javascript) — TheMaster, Apr 06 '20 at 15:49
`getValues()` always returns a 2D array: `[[A1],[A2]]`. [Related question](https://stackoverflow.com/a/60445654/) — TheMaster, Apr 06 '20 at 15:49

score 4 · Accepted Answer · edited Oct 31 '21 at 10:04

Answer:

The .getValues() method returns a 2-dimensional array, and so you aren't referencing the values within each cell correctly in your array.

More Information:

Let's assume that both A1 and A2 contain the string "THIS". Running the following line:

var testvalues = sheet.getRange('A1:A2').getValues();

will assign the array [[THIS], [THIS]] to testvalues.

Code fix:

You need to change:

var testvalue1 = testvalues[0]; // The contents from A1
var testvalue2 = testvalues[1]; // The contents from A2

to:

var testvalue1 = testvalues[0][0]; // The contents from A1
var testvalue2 = testvalues[1][0]; // The contents from A2

References:

Class Range | Apps Script - Method getValues()

score 1 · Answer 2 · answered Apr 06 '20 at 15:57

The following line of code is incorrect, below it is corrected.

  var testvalue1 = testvalues[0][0]; // The contents from A1
  var testvalue2 = testvalues[1][0]; // The contents from A2

You are originally grabbing the entire row instead of a single cell.

Using Google Sheets scripts, why does my if statement always return false when comparing cell values?

2 Answers2

Answer:

More Information:

Code fix:

References:

Linked