Home > OS >  How can I check if a date in a cell is this week? Script not sheet function
How can I check if a date in a cell is this week? Script not sheet function

Time:02-18

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].

  • Related