Get Row Block With Createtextfinder() From Column A With Dates With Same M And Y When Locale Is En_us

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 forma

Solution 1:

The createTextFinder won't be ideal for this because this method will only work on exact text values shown on every cells of range A5:A on your sheet.

Recommendation

You can try this sample script below using string manipulation of the array. This will return the rows of all cells that matches the getMonthYear on range A5:A:

Sample Script:

functiontest(){
  var logSheetNameYR = "LOG";
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(logSheetNameYR);
  var rowBlock = sheet.getRange(sheet.getLastRow() - 1, 1).getValue();
  var getMonthYear = Utilities.formatDate(rowBlock, "GMT+1", "MM/yyyy"); //using UK timezonevar range = sheet.getRange("A5:A");
  var startRow = range.getRow();
  var cells = range.getValues().map((x,i) => x.concat(i + startRow))
                               .filter(x => getMonthYear == ("0" + (newDate(x[0]).getMonth() + 1)).slice(-2) + "/" + newDate(x[0]).getFullYear())
                               .map(x => x[1]);
  Logger.log(cells);
}

Sample Result:

Sheet:

Solution 2:

You do not need to specify the format, you just need to separately get the details (day, month, year) for easy comparison.

Another thing to note, never sacrifice readability for shorter code. Use variables if necessary.

// get all details of the datevar mmddyyyy = Utilities.formatDate(rowBlock, "GMT+1", "MM/dd/yyyy"); //using UK timezone// split to each category for easier accessvar [mm, dd, yyyy] = mmddyyyy.split('/');

  var range = sheet.getRange("A5:A");
  var startRow = range.getRow();
  var cells = range.getValues().map((x,i) => x.concat(i + startRow))
                               .filter(x => {
                                 var date = newDate(x[0]);
                                 var month = ("0" + (date.getMonth() + 1)).slice(-2);
                                 var year = date.getFullYear();
                                 var day = date.getDate();
                                 // date properties are always the same on different formats// because these are outputs of date methods// just create a combination of the variables// e.g. you want to match the exact datereturn (`${dd}${mm}${yyyy}` == `${day}${month}${year}`)
                                })
                               .map(x => x[1]);

Solution 3:

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(newDate(), timeZone, "yyyy,MM,dd").split(",");


  let cells = range.getValues().map((x, i) => x.concat(i + startRow)).filter(x => {   // filter "dd"let date = newDate(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.