How to match a formatted date to an Array within Google Apps Script

Question

Problem

I'm unable to have a formatted date match an array of dates in Google Apps Script. More specifically for me, I am having my script pull todays date, and see if it's in an array of data (to match). See below for log results as it seems like I do have a match.

I am also unsure how to pull in data if there is a match to the array item from a corresponding column. I'm assuming I'd use some form of for statement that I have in my code, but unsure how to get it working properly.

For the example below, for simplicity, let's say todays date is 12/06/2022.

Solution Sought

Check to see if todays date is in the array
if yes, pull in the code color (from column A) into column C next to the matched date.

Expected Results

Actual Results

What I've Tried

Attempted to find an answer similar to mine on stackoverflow but was unable to find a related enough question
Tried to use indexOf with <-1 to see if my transformDate would process that way with my array, but to no avail sadly
Uncertain how to use .map() and if that would help with my scenario

CODE

function tDate(){

  const ss = SpreadsheetApp.getActive();
  const ssName = SpreadsheetApp.getActive().getSheetByName("Sheet1");
  let date = new Date();//creates dates
  var transformDate = Utilities.formatDate(date,"EST","MM/dd/yyyy");//reformats into month/2 digit day/4 digit year 

  var codeDateArray = ssName.getRange(2,2,ssName.getLastRow()).getDisplayValues().flat(); //getting the values using DisplayValues and then making the 2-d array into 1, as I think that's supposed to help with my function

  for (let a=0;a<codeDateArray.length;a++){
      if (transformDate in codeDateArray){
        var codeVal = ssName.getRange(a+2,2).getValue();
        ssName.getRange(a+2,3).setValue(codeVal)

      }//end of IF statement
      else {ssName.getRange(2,4).setValue(-1);}//end of ELSE statement...here to showcase if my IF fails for testing
  }//end of FOR statement, derived from (https://stackoverflow.com/questions/47423183/google-app-script-formatted-date-match-in-array-without-iterating) @Tanaike & (https://stackoverflow.com/questions/48207471/find-and-match-a-date-in-array) @christophebazin


}//end of function

LOG RESULTS

The first dates are the "code date" and the second date is todays date (transformDate).

EXAMPLE DATA

code color	code date	code pull in	negative test results
blue	12/06/2022
red	12/10/2022
orange	12/11/2025

Relatable questions on StackOverflow

On first look, your `if` is wrong. It should be something like `if (transformDate === codeDateArray[a]){`. Also checkout https://stackoverflow.com/questions/63720612/what-does-the-range-method-getvalues-return-and-setvalues-accept — TheMaster, Dec 06 '22 at 17:09
so this is very interesting, thank you for the direction to the question...I changed this part: `if (transformDate in codeDateArray){` to `if (transformDate === codeDateArray[a]){` however, now the function is providing both my solution and the `else` statement. is that correct? Seems like it shouldn't be performing the `else` statement — User1938985, Dec 06 '22 at 17:40
valueOf() and getTime() are methods of Date() object that are used to compare date values numerically. — Cooper, Dec 06 '22 at 21:14
ok thank you, I wasn't sure! Tried to look it up in the Google developers section and didn't see it — User1938985, Dec 06 '22 at 21:16

doubleunary · Accepted Answer · 2022-12-07T18:20:03.043

2

The logic of putting if (transformDate in codeDateArray) inside a for loop sounds broken.

The whole thing may be easiest to do by collecting results with map(), like this:

function tDate() {
  const ss = SpreadsheetApp.getActive();
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const codeDateRange = sheet.getRange('B2:B');
  const colors = codeDateRange.offset(0, -1).getDisplayValues().flat();
  const codeDateArray = codeDateRange.getDisplayValues().flat();
  const timezone = ss.getSpreadsheetTimeZone();
  const todayString = Utilities.formatDate(new Date(), timezone, 'MM/dd/yyyy');
  const result = codeDateArray
    .map((dateString, index) => [dateString === todayString ? colors[index] : null]);
  codeDateRange.offset(0, 1).setValues(result);
}

edited Dec 07 '22 at 18:20

answered Dec 06 '22 at 22:43

doubleunary

13,842
3
18
51

thank you @doubleunary this was very helpful, and I was just looking into `.map()` as i'm still beginning to learn how to use it. for `colors[index]` I had to use `colors[0]`, does that seem right? I was getting an error that said index was not defined. Also what does the `?` do in the map function, is that like `&&` to chain further conditions? Thanks!! – User1938985 Dec 07 '22 at 18:00
1

Edited the answer to replace `dateString` with `(dateString, index)`. See [ternary operator](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Operators/Conditional_Operator). – doubleunary Dec 07 '22 at 18:24

score 2 · Answer 2 · answered Dec 07 '22 at 03:49

The if logic is wrong. Use

if (transformDate === codeDateArray[a]){

instead. You would also need to break out of the loop, once the condition is satisfied and move the else block outside the loop, else the else block will execute on every loop.

for (let a = 0; a < codeDateArray.length; a++) {
  if (transformDate === codeDateArray[a]) {
    var codeVal = ssName.getRange(a + 2, 2).getValue();
    ssName.getRange(a + 2, 3).setValue(codeVal);
    break;
  } //end of IF statement
} //end of FOR statement
if (a === codeDateArray.length /*Counter is at the end*/) {
  ssName.getRange(2, 4).setValue(-1);
} //end of ELSE statement...here to showcase if my IF fails for testing

To read:

Control Flow

thank you @TheMaster as always super helpful! Appreciate the links to the documentation on 2-d arrays w/the `getDisplayValues()` as I didn't know that was a thing. Also good to know about the `else` statement execution. — User1938985, Dec 07 '22 at 18:01

How to match a formatted date to an Array within Google Apps Script

2 Answers2

To read: