Filter date range on a chart in Google Apps Script

Question

I have a spreadsheet with monthly data going back nearly a decade and am creating a dashboard for this and other sheets like it.

I am attempting to filter the data table for a chart showing the last 12 months of data. I've attempted to use a number range filter on the dates using getTime() or valueOf(), but both refuse to to build a filter when I set a minimum value using date.valueOf() (I assume because of size). I've also tried the solution in the link below, using viewWindow, but that failed. Date Range Google Chart Tools

I know this can be done with the visualization APIs, but unfortunately I'm stuck with Google Apps Script for this. I'm trying to use one data table for everything, but if this is impossible I can create a separate one with just 12 rows. I was really hoping to let users load custom ranges of time, but there's no ChartRangeFilter in GAS.

Eric Koleda Eric Koleda · Accepted Answer · 2012-10-19T21:20:44

As you've found, the NumberRangeFilter doesn't work on dates, and Apps Script currently doesn't support any other range filters. One workaround I've employed is to convert your dates to numbers, and use a NumberRangeFilter to filter them. For example, "October 19, 2012" could be converted to "20121019".

In order to avoid weird jumps in the graph due to the whole between 20121031 and 20121101, you need to set the filter on the numerical date but use a DataViewDefinition to only show the actual date in the graph.

Here's a rough piece of sample code. Column 0 is the actual date, 1 is the numerical date, and 2 & 3 are values I want to plot.

var dateFilter = Charts.newNumberRangeFilter()
    .setFilterColumnIndex(1)
    .build();
var view = Charts.newDataViewDefinition()
    .setColumns([0, 2, 3]);
var areaChart = Charts.newAreaChart()
    .setDataViewDefinition(view)
    .setStacked()
    .setDimensions(800, 400)
    .build();

Filter date range on a chart in Google Apps Script

1 Answers