Can't read date/time column

[simonc6372]

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.

[daniellee]

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

[ryantxu]

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

[daniellee]

Yeah, that should do it. Thanks Ryan.

[ryantxu]

significanly improved... still more to do, but open a new issue if it is still a problem with 7+