Can't read date/time column
simonc6372 opened this issue · 4 comments
I've just take a look at this plugin, and I'm struggling to get any date/time formated columns to be read. Either with a table visualization, or a graph. I'm using this as a test sheet.
https://docs.google.com/spreadsheets/d/1cXWCkhQ_5kxQi3-oXsQxKTFGb7L9eQDv8JQejHnQKVw/edit?usp=sharing
Neither A1B10, or E1F10 ranges work. The "value" column is read, but the data/time" one is not.
I've tried several date formats, and tried clearing all formatting for the relevant columns but they just aren't being shown.
Thanks.
It is the date formatting that is not working. I took a copy of your spreadsheet and if I change the dates to 2020-03-29 instead of 29/03/2020 then it works. I have UK set in the spreadsheet settings as the locale (same as your sheet) but probably don't have the same region settings as you. Looks like a datetime parsing bug.
Here is my version of your sheet: https://docs.google.com/spreadsheets/d/1sO5KbU8_gBUzQqEZcKhq3eJMlVCSmsq35KTC-QBlMZw/edit?usp=sharing
interesting -- thanks @daniellee -- looks like we do not do anything special for the locale setup:
https://github.com/grafana/google-sheets-datasource/blob/master/pkg/googlesheets/googlesheets.go#L166
Looks like we should initialize our parser with the sheet locale:
https://github.com/grafana/google-sheets-datasource/blob/master/pkg/googlesheets/testdata/mixed-data.json#L7
And try to parse the date using the pattern defined in:
https://github.com/grafana/google-sheets-datasource/blob/master/pkg/googlesheets/testdata/mixed-data.json#L3793
Yeah, that should do it. Thanks Ryan.
significanly improved... still more to do, but open a new issue if it is still a problem with 7+