2

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.

Community
  • 1
  • 1
Fred
  • 1,081
  • 12
  • 21

1 Answers1

2

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();
Eric Koleda
  • 12,420
  • 1
  • 33
  • 51
  • Thanks, that mostly works. I set the min and max values to include the last 12 months of data (August 2012 back to August 2011). It works perfectly without August 2012, giving columns of proper width in the chart. When I add August 2012, however, the columns become really thin with lots of empty space. Because my data is monthly, I've got it set to the last day of every month (and JavaScript assumes 9pm local time). So my formula for numeric dates is 100*year + month – Fred Oct 22 '12 at 15:04
  • Actually, my formula is (date.getFullYear() - 2003) * 12 + date.getMonth() because my data starts in 2003. It does the same thing as the other one. – Fred Oct 22 '12 at 15:09
  • That sounds like an issue with how charts choose to render the data. If you increase the width the columns should scale differently. – Eric Koleda Oct 23 '12 at 15:54
  • The columns scale just fine, except when I include the last data entry. I'll report it to the issue tracker. – Fred Oct 23 '12 at 18:28
  • Turns out it was because I had multiple entries with the exact same date. – Fred Oct 25 '12 at 14:53