Goal: I need to iterate through rows and match the values within column 'A2:A' with cell 'C2', and then console.log only the matching values from the column.

Problem: The values in 'C2' and 'A2:A' are set to be in 'MM/dd/yyyy' format in Google Sheets. But as I try to retrieve them via Apps Script, sheet1.getRange('C2).getValue() seems to return a different date format than sheet2.getRange('A2:A').getValues() (see snippet comment), so the 'if' statement failed to find any matching value.

What I need help with: Coming up with the best way to unify the date formats after retrieving the values such that the 'if' statement can recognize them and validate the condition.

The snippet:

function matchDates() {
  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var database = ss.getSheetByName("Database");
  var form = ss.getSheetByName("Form");

  var formDate = form.getRange("C2").getValue(); // Console.log returns: 'Fri Jan 07 2022 12:00:00 GMT-0500 (Eastern Standard Time)'
  var dbDate = database.getRange("A2:A").getValues(); // Console.log returns: [ Sat Jan 08 2022 12:00:00 GMT-0500 (Eastern Standard Time) ],[ Fri Jan 07 2022 12:00:00 GMT-0500 (Eastern Standard Time) ],..

  for(let i = dbDate.length - 1 ; i >= 0 ; i--){
    if(dbDate[i] == formDate){
      console.log(i 1,dbDate[i]) // Console.log all rows in 'Date' column of 'Database' sheet that match the value stored in formDate
    }
  }
}

// Console.log this whole function returns nothing because the 'if' statement cannot find any matching dates (due to the stated problem).

Research results so far:

1. Use getDisplayValue() instead of getValue() - Still didn't work for the 'if' statement. The display value retrieved from 'C2' is different from 'A2:A', something to do with array dimension (red it somewhere but lost the reference), as shown here;

var formDate = form.getRange('C2').getDisplayValue(); // Console.log returns 1/8/2022
var dbDate = database.getRange('A2:A').getDisplayValues(); // Console.log returns [ '1/8/2022' ],[ '1/7/2022' ],..

2. Using Utilities.formatDate() - Only works for 'C2', even then the value returned by console.log is 1 day behind what's shown on the sheet. I don't know how to format an entire column of 'A2:A' using this because doing it the same way returned me only a single row while it should be the whole range.

3. Using new Date() as suggested by @Cooper in the comment. Again, worked for 'C2', but not for 'A2:A'. Snippet;

var formDate = new Date(form.getRange("C2").getValue()).valueOf(); // Returns the date in this format : 1641574800000
var dbDate = new Date(database.getRange("A2:A").getValues()).valueOf(); //Returns NaN

I'm a complete newbie and running out of references for this so please, really appreciate the help!

CodePudding user response：

Transform your dates in epoch individually

valueOf()

var dbDate = [[ 'Sat Jan 08 2022 12:00:00 GMT-0500 (Eastern Standard Time)' ],[ 'Fri Jan 07 2022 12:00:00 GMT-0500 (Eastern Standard Time)' ]]
var dbDateEpoch = []
dbDate.forEach(function(d){
  dbDateEpoch.push(new Date(d).valueOf().toString())
})
console.log(dbDateEpoch)