How do I include a date column in a google visualisation datatable column taken from a spreadsheet

Question

I have a Google sheet that look like this:

   Date     Sales
31/03/2017  1000
30/06/2017  2000
30/09/2017  1500
31/12/2017  2500
31/03/2018  4000

If I import this using .arrayToDataTable in Google charts and set the type of column 0 to 'date' or 'string' - it doesn't like it, because the date is only a formatted number i.e 43190 = 31/3/2018. so the type has to be 'number' which then only displays the underlying number on the chart and not the date

I can loop through each of the row values and I've tried changing them with this

function formatDate(date) {
   var d = new Date((date - 25569)*86400*1000),
   locale = "en-us",
   month = d.toLocaleString(locale, { month: "short" });
   var formattedDate = d.getDay() + "/" + month + "/" + d.getFullYear();
   return formattedDate
}

this converts the number to a date string - so with an input of, say, 43190 it returns 31/Mar/2018 but I still can't change the column type to 'date'

I could output the dates as strings but they don't sort in chronological order but alphabetical - I want them chronological.

can anybody show me where I'm going wrong?

EDIT:

If I change the function to

function formatDate(date) {
  var d = new Date((date - 25569)*86400*1000)
  return d
}

this now sets the column values to the full date object

but I get an error a saying "Value Sun Jun 30 2013 01:00:00 GMT+0100 (GMT Summer Time) does not match type number in column index 0"

Now this must be referring to the datatable column zero, which in the original table, is a date number. I've tried adding:

data.setColumnProperty(0, 'type' , 'date')

but seem to get the same error.

perhaps I'm not changing the type at the correct point in the code.

to clarify: I'm getting the original values from a range in the google sheet then converting that range to a datatable using .arrayToDataTable. So I'm not sure I can define the column type at the point of making the datatable hence the separate attempt with:

data.setColumnProperty(0, 'type' , 'date')

Mike Mike · Accepted Answer · 2017-07-06T15:23:45

You have to supply the complete date object in the date-type column.

Good documentation about dates can be found here https://developers.google.com/chart/interactive/docs/datesandtimes#axesgridlinesticks

An example of dates and sales could be

var data = new google.visualization.DataTable();
        data.addColumn('date', 'Date');
        data.addColumn('number', 'Sales');

        data.addRows([
          [new Date(2015, 0, 1), 5],  [new Date(2015, 0, 2), 7],  [new Date(2015, 0, 3), 3],
          [new Date(2015, 0, 4), 1],  [new Date(2015, 0, 5), 3],  [new Date(2015, 0, 6), 4],
          [new Date(2015, 0, 7), 3],  [new Date(2015, 0, 8), 4],  [new Date(2015, 0, 9), 2],
          [new Date(2015, 0, 10), 5], [new Date(2015, 0, 11), 8], [new Date(2015, 0, 12), 6],
          [new Date(2015, 0, 13), 3], [new Date(2015, 0, 14), 3], [new Date(2015, 0, 15), 5],
          [new Date(2015, 0, 16), 7], [new Date(2015, 0, 17), 6], [new Date(2015, 0, 18), 6],
          [new Date(2015, 0, 19), 3], [new Date(2015, 0, 20), 1], [new Date(2015, 0, 21), 2],
          [new Date(2015, 0, 22), 4], [new Date(2015, 0, 23), 6], [new Date(2015, 0, 24), 5],
          [new Date(2015, 0, 25), 9], [new Date(2015, 0, 26), 4], [new Date(2015, 0, 27), 9],
          [new Date(2015, 0, 28), 8], [new Date(2015, 0, 29), 6], [new Date(2015, 0, 30), 4],
          [new Date(2015, 0, 31), 6], [new Date(2015, 1, 1), 7],  [new Date(2015, 1, 2), 9]
        ]);

You could than add the following to options to format your date

hAxis: {
    format: 'MM/dd/yyyy'
}

How do I include a date column in a google visualisation datatable column taken from a spreadsheet

1 Answers