Get row block with createTextFinder() from column A with dates with same M and Y when locale is en_US

Question

I have a sheet where column A has dates. I need to get the row number of a block of rows with the same month and year. If the Locale of the SpreadSheet is en_US then the date format is something like:

"M/d/YYYY"

So d is between M and Y. As you can see:

var getDayMonthYear = Utilities.formatDate(rowBlock, Session.getScriptTimeZone(), "M/d/YYYY");

So in order to define the block of rows I need to find/filter from the column A all rows with date like M/YYYY. This is easy do to with locale as for ex. en_GB where the date format is dd/MM/YYYY. Here I just search for MM/YYYY without errors.

But because in en_US the d is between M and Y that is M/d/YYYY, I always get an error:

Exception: The parameters (null,number) don't match the method signature for SpreadsheetApp.Sheet.getRowGroup.

I tried:

Utilities.formatDate(rowBlock, Session.getScriptTimeZone(), "M,yyyy").split(",");

I get same error:

Exception: The parameters (null,number) don't match the method signature for SpreadsheetApp.Sheet.getRowGroup.

So this an example of the code I've got. It works ok if SpreadSheet locale is set to United Kingdom which is en_GB:

function testTimeFormat() {
var logSheetNameYR = "LOG";
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(logSheetNameYR);
var rowBlock = sheet.getRange(sheet.getLastRow() - 1, 1).getValue();
var getMonthYear = Utilities.formatDate(rowBlock, Session.getScriptTimeZone(), "MM/YYYY");
var cells = sheet.getRange("A5:A").createTextFinder(getMonthYear).findAll().map(x => x.getRowIndex());
var firstRowOfBlock = cells[0];
console.log(firstRowOfBlock);
}

But when locale is en_US it does not work.

How can I do the same when locale is United States en_US or any other date format that separates M from Y with d or any other character in between?

Dummy file:

https://docs.google.com/spreadsheets/d/1ExXtmQ8nyuV1o_UtabVJ-TifIbORItFMWjtN6ZlruWc/edit?usp=sharing

Answer 1

So I ended up giving up on text finder tricks and went with @ASyntuBU's suggestion and used his code with a change on the return part using AND that is && to separate d from m from y this way the filter will not enforce a particular order like dd/MM/YYYY but will return into an array all the row numbers with cells on column A which meet the conditions regardless of the order, date format etc. At least I think that's what it does. Thanks @ASyntuBU!

// Used for getting the arrays with blocks of rows of `d`+ `m`+ `y`
let range = sheet.getRange("A5:A");
let startRow = range.getRow();
let [y, m, d] = Utilities.formatDate(new Date(), timeZone, "yyyy,MM,dd").split(",");


  let cells = range.getValues().map((x, i) => x.concat(i + startRow)).filter(x => {   // filter "dd"
        let date = new Date(x[0]);
        let day = date.getDate();
        let month = ("0" + (date.getMonth() + 1)).slice(-2);
        let year = date.getFullYear();
        return (`${d}` === `${day}` && `${m}` === `${month}` && `${y}` === `${year}`)
    }).map(x => x[1]);

When I wan't to group by month, the return will be:

return (`${m}` === `${month}` && `${y}` === `${year}`)

And by group by year:

return (`${y}` === `${year}`)

This returns row numbers into an array, then we get the 0 (first) position with cells[0] to find the row number where the group handle will be placed.