Comparing '==' in an IF statement is not working Google Sheets script

Question

The objective is to compare ColA in the Orders sheet with ColF in the Ordered Items sheet, if they match grab the email from ColB.

The script outputs no errors, but it doesn't match the highlighted cells in either sheet.

(note: the items are automatically added by an app, so the formatting of the cells are default and need to keep it that way as I'm using the last 6 digits as the order reff eg; 49.263Z)

Orders sheet

Ordered Items sheet

function getEmailFromOrderedItemToOrders(){

  var orders = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders');

  var lr = getLastRow(orders, "A1:G");

  Logger.log(lr); //LastRow index

  //Get last 'OrderID (Paid at)' value from 'Orders' sheet
  var orderIdCol = 1;
  var orderId = orders.getRange(lr, orderIdCol).getValue(); 
  Logger.log(orderId); //LastRow 'orderId' value

  //Match 'orderId' to 'orderId' in 'Ordered Items' and return col 1
  var items = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Ordered Items');
  var itemsData = items.getDataRange().getValues();
  Logger.log(itemsData[0][1]); //'Purchase Email' col
  Logger.log(itemsData[0][5]); //'Paid at' col

  for(var i = 0; i<itemsData.length;i++){


    if(itemsData[i][5] == orderId){ //Issue here: not comparing values as a match
      var email = itemsData[i][1];
      Logger.log(email); //Does not print
      return i+1;
    }
  }

}

If the condition never passes, the values are never the same. You've to check the values, make sure you check the length is equal too, leading/trailing white-space and invisible garbage characters are often causing troubles. — Teemu, May 15 '20 at 06:21
column 5 is `Paid at` - how is that an "orderId"? looks like a date - and new Date(123) !== new Date(123) for example - if you are dealing with date objects, compare the `.toString()` values, or `.getTime()` values ... or better yet compare `+orderID` to `+itemsData[i][5]` — Jaromanda X, May 15 '20 at 06:21
They look like dates to me. If they're dates you can compare them as numbers using the valueOf() method or the getTime() method both of which return millliseconds. Or you can turn them into strings with the Utilities.formatDate() function and post them as strings. — Cooper, May 15 '20 at 06:23
@Cooper you're correct, yes they are dates, how do I use the `valueOf()` or `getTime()`...Im getting error:getTime is not a function `itemsData[i][5].getTime()` — Roggie, May 15 '20 at 06:32
worked it out, need to use `new Date(itemsData[i][5]).getTime()` instead — Roggie, May 15 '20 at 06:39
Does this answer your question? [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) — TheMaster, May 15 '20 at 08:11

score 1 · Answer 1 · answered May 15 '20 at 06:39

This can be simply solved by using a Sheets Formula which. What you would do is:

=INDEX( 'Ordered Items!B:B', MATCH(A2, 'Ordered Items!:F:F,0) )

The formula basically says:

Return the Email Column
Find the Index (# of the row) where the value of A2 is in column Ordered Items F:F

See here for a tutorial on it: https://www.youtube.com/watch?v=9sLWDjAEuyc

score 1 · Accepted Answer · answered May 15 '20 at 06:47

In javascript when you compare two new Date, you will be getting the value as false.

Reason is when you have two dates, its basically two different objects. So One object is not equal to another object when you use == or ===. So the simple and better way is converting the date to a number using .getTime() that will return how many milliseconds passed from that date and 00:00:00 of Jan 1, 1970.

Sample code snippet

console.log(new Date() === new Date())

console.log(new Date().getTime() === new Date().getTime())

Comparing '==' in an IF statement is not working Google Sheets script

2 Answers2