2
votes

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.

2
Is it comparing objects under the hood? What are the types of testvalue1 and testvalue2?Ollie
Does this answer your question? How to compare arrays in JavaScript?TheMaster
getValues() always returns a 2D array: [[A1],[A2]]. Related questionTheMaster

2 Answers

4
votes

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:

1
votes

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.