Google Script: Comparison of cells with === always gives out cells are different

Question

I want to compare two cells and depending on whether they are equal or not send out different e-mails. After having read several articles on this topic I unfortunately do not see why the the following comparison with === always gives out as a result that the cells are different, even if I compare the same cells:

function SendEmail() {
 var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var ui = SpreadsheetApp.getUi(); //  var file =     SpreadsheetApp.getActiveSheet(); 
  if (sheet.getRange(2,10) === sheet.getRange(2,10)) {
    MailApp.sendEmail("ab@gmail.com", "test", "not equal!");
  } else {
    MailApp.sendEmail("ab@gmail.com", "test", "equal!");
  }
  }

It also does not work if I use !==.

Any hint is highly aprreciated - thanks!

The `getRange()` calls return objects, and two distinct objects are always unequal. — Pointy, Mar 09 '17 at 14:59
That helps a bit, how do I have to address the cells in order to get the comparison I am looking for? — JSS, Mar 09 '17 at 15:02
Change it to ge the values: ` if (sheet.getRange(2,10).getValue() === sheet.getRange(2,10).getValue()) {` See [this thread](http://stackoverflow.com/questions/19520642/get-and-set-value-of-a-cell-do-not-work) for more details — Karl_S, Mar 09 '17 at 15:05

score 2 · Answer 1 · answered Mar 09 '17 at 15:04

You need to compare the values in the cells rather than the Ranges themselves.

If you are dealing with single cells, this is straightforward:

if(sheet.getRange(2,10).getValue() === sheet.getRange(2,10).getValue())

However if you want to compare ranges with multiple cells it is more complex, as you'll need to compare arrays of values.

score 1 · Accepted Answer · answered Mar 09 '17 at 15:19

I noticed you were comparing the same cells? I changed it to compare cells J2 & K2 and to log the differences. I hope this helps.

function SendEmail() {
 var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow(); 
  var ui = SpreadsheetApp.getUi(); //  var file =     SpreadsheetApp.getActiveSheet(); 
  Logger.log(sheet.getRange(2,10).getValue());// logs value of J2
    Logger.log(sheet.getRange(2,11).getValue());// Logs the value of K2
 if(sheet.getRange(2,10).getValue() === sheet.getRange(2,11).getValue()) {
    // MailApp.sendEmail("ab@gmail.com", "test", "equal!");
    Logger.log('equal');
  } else {
   //  MailApp.sendEmail("ab@gmail.com", "test", "not equal!");
    Logger.log('not equal');
  }
  }

score 0 · Answer 3 · answered Mar 09 '17 at 17:28

The return type for the getRange() method is Range, which inherits from Object. Objects are reference types and will never be considered equal unless the variables being compared point to the same object instance.

As mentioned, you need to call the getValue() method on the range that will return the contents of the cell and compare the values.

Google Script: Comparison of cells with === always gives out cells are different

3 Answers3