0
220
VistasGet 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 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
3 Respuestas
Responde la pregunta0
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:
function test(){
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 timezone
var range = sheet.getRange("A5:A");
var startRow = range.getRow();
var cells = range.getValues().map((x,i) => x.concat(i + startRow))
.filter(x => getMonthYear == ("0" + (new Date(x[0]).getMonth() + 1)).slice(-2) + "/" + new Date(x[0]).getFullYear())
.map(x => x[1]);
Logger.log(cells);
}
Sample Result:
Sheet:
0
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.
0
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 date
var mmddyyyy = Utilities.formatDate(rowBlock, "GMT+1", "MM/dd/yyyy"); //using UK timezone
// split to each category for easier access
var [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 = new Date(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 date
return (`${dd}${mm}${yyyy}` == `${day}${month}${year}`)
})
.map(x => x[1]);
