Filter array by date with google app script

Question

i've a google spreadsheet that is a 2 columns table that has on column A dates (i'm sure that are dates, and all the date functions works fine on all the column) and on column B text.

With google app script i've get all the values of the range with:

var sheet = SpreadsheetApp.openById(".........");
var ss = sheet.getSheetByName(".......");
var range =ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn());
var data =  range.getValues();

now i should have a 2d array with dates on data[0] and text on data[1]

i can filter the array on data[1] with:

var filter = data.filter(function (dataRow) {
return dataRow[1] == 'sometext';
});

what i need is to filter data[0] by some date value (like year, or month or a period)

i've tried:

var filter = data.filter(function (dataRow) {
return dataRow[0].getFullYear == 2016;
});

but any kind of date function gets me an error that states that is impossible to find the function (like getFullYear()) in the object.

What am i doing wrong?

Thank for any help

Best regards

Solution 1

I've created a similar spreadsheet (first column with text, second with dates) and the following script:

function myFunction() {
  var sheet =  SpreadsheetApp.openById("…");
  var ss    = sheet.getSheetByName("testing");
  var range = ss.getRange(1,1,ss.getLastRow(),ss.getLastColumn());
  var data  = range.getValues();

  var filtered = data.filter(function (row) {
    return row[1].getFullYear() === 2016;
  });

  Logger.log(filtered);
}

And it works as long as the second column is of Date type (Format→Number→Date). I get the error you've described if I change the type to Text. If there are no errors in your code (like .getFullYear instead of .getFullYear() as it's a function, not a property), I think the data (not the variable, but the spreadsheet's data) is not of Date type.

Solution 2

Try this

var filtered = data.filter(function (row) {

  var d = new Date( row[1] ); // change text to Date object
  return  d.getFullYear() === 2016; // then compare

});

Comments

[d4rk5h4rk]

Hi, it's true that in dataRow[1] i have text, but i want to filter by dataRow[0] that is a date

[Sujay Phadke]

@d4rk5h4rk well that is not a date object. It's still just a text string when you do "getvalues()"

[d4rk5h4rk]

Hi, thank you for your test. Following your suggestion, i've checked the format of the column on the spreadsheet, but i was already set it to date (now i've even set up a data convalidation). So i've thinkered to put a test for the data format in the script with: var filtered = data.filter(function (dataRow) { return dataRow[2] instanceof Date; }); before the discussed filter and now it works. the fun thing is that i can't spot what the istanceOf filter as actually removed, i've run the istanceOf filter alone, and i've got the same number of rows as the original spreadsheet

[d4rk5h4rk]

Hi, after some test i'm sure that the getvalues() doesn't change the data type into string, as you can read in the other answer we have managed to filter by the year extracted form the date.

[gsc]

If you used only instanceof filter and all the rows had a Date in third column, the filter would keep all of the rows. But (unless I've missed something) there isn't explanation why something is instance of Date and you can't use Date methods on it… BTW, is the date in 0 (as in question) column or in 2 column (as in your comment)?

[d4rk5h4rk]

sorry, it was a typing error, i was meaning dataRow[0], in my table the Date column is the first. I'm sure that somewere in the spreadsheet maybe there is an incorrect date value but i cant't find it... anyway thank's a lot for the support ^_^