Im trying to check if the dates in column A are this week, then show the row.
my problem is my variable "t" is undefined in the debugger and when run i get the following error...
Exception: Invalid argument: date. Should be of type: Date
function weekNumberTest()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Test");
var v = s.getRange("A1:A").getValues();
var data = Utilities.formatDate(new Date(), "GMT", "w");//Exception: Invalid argument: date. Should be of type: Date
for (var i = s.getLastRow(); i > 2; i--)
{
var t = v[i ];
if (t != "")
{
var testDateCell=Utilities.formatDate(t, "GMT", "w");
if (testDateCell==data )
{
s.showRows(i);
}
}
}
}
CodePudding user response:
Dates in current week
function weekNumberTest() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Test");
const vs = sh.getRange(2,1,sh.getLastRow()-1).getValues().flat();
const wk = parseInt(Utilities.formatDate(new Date(), ss.getSpreadsheetTimeZone(), "w"));
vs.forEach((d,i) => {
let w =parseInt(Utilities.formatDate(new Date(d),ss.getSpreadsheetTimeZone(),"w"));
if(w == wk || w == wk - 1) {//try this for last two weeks. This may not work at beginning of year...I don't know for sure.
sh.showRows(i 2);
} else (
sh.hideRows(i 2)
)
});
}
CodePudding user response:
function weekNumberTest() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getSheetByName("Test");
var v = s.getRange("A1:A").getValues();
var data = Utilities.formatDate(new Date(), "GMT", "w");
for (var i = s.getLastRow(); i > 2; i--) {
var t = new Date(v[i][0]); // <---------------- the error was here
if (t != "") {
var testDateCell = Utilities.formatDate(t, "GMT", "w");
if (testDateCell == data) {
s.showRows(i);
}
}
}
}
To get the dates from this and last week you can change one line this way:
if (testDateCell == data || testDateCell == data-1) {
Explanation
var v = s.getRange("A1:A").getValues();
it gets you a 2D array [[a],[b],[c]]
. To get values from the a 2D array you need to use two indexes: v[0][0]
, v[0][1]
, v[0][2]
.
Alternatively you can use the flat()
method to convert a 2D array into 1D array this way: var v = s.getRange("A1:A").getValues().flat();
. In this case you can address the values of the array with one index: v[0]
, v[1]
, v[2]
.