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

1. Check to see if todays date is in the array
2. 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

1. Attempted to find an answer similar to mine on stackoverflow but was unable to find a related enough question
2. Tried to use indexOf with <-1 to see if my transformDate would process that way with my array, but to no avail sadly
3. 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


}//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

1. unable to get index of date in array with apps script

2. Find and match a date in array

3. Google App Script formatted date Match in Array without iterating

CodePudding user response：

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 => [dateString === todayString ? colors[index] : null]);
  codeDateRange.offset(0, 1).setValues(result);
}

CodePudding user response：

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