Google Sheet , Time column null

Grafana Configuration
1

Hi all.
I try show data from my Google Sheet .
If explore my data , then I see in table all data , but in column A where I stored datetime show me null . My format is : day , month, year , hours , minutes ( 17.4.2022 11:01 ) for example .
Here are my sample data .

MyData
MyData1132×116 18.4 KB

And here is what I see in Grafana:
GrafanaData
GrafanaData1831×393 41.6 KB

You can see so column A : Time is null , and all other column where is predefined format time ( hour:minute ) for exmample T-Cas is not correct format , in grafana is 0.0375 etc …
What is wrong ? In my google sheet redefine time format … stil same problem…
Here are correct show …
My Data for 48 hour

best regards.

2

Welcome to the forum. Try changing the time in col. A to be in Unix Time format.

image
image1432×665 121 KB

3

Hi.
Thanks for reply , but ehm …
Try convert it , but not working , show error … in my Sheet formula is error…
But step by step …

  1. you have tested it if working in your project ?
  2. can you test it your self , you have access to my Sheet (for read only) and can try convert self .
  3. or genrate easy file - data … I try test it self …

I format column for Date Time format … still not working …

4

I go to my scrypt and there is this :

var dateTime = Utilities.formatDate(new Date(), "GMT+2", "dd-MM-yyyy HH:mm");// get actual date time

This is function and variable what I write to my Sheet … rowData[0] = dateTime; // Date time
[0] is A column , [1] is B column …etc…
Where I do mystake …

5

Hi.
I do some test.
I make new Google Form with one question only , for example temperature… and fill Form with temporary data , and then generate Sheet where Google automatically add Time Stamp . Then I test it again… and have same error … column with Time Stamp can’t parse … I don’t know why … but same error … here is JSON from Explore

{
  "state": "Done",
  "series": [
    {
      "name": "A",
      "refId": "A",
      "meta": {
        "custom": {
          "expires": 1650288746,
          "hit": true,
          "range": "",
          "spreadsheetId": "1CzC9IeQAmuep7EKRIEN2ana9tDIIp20QphNF58JC-HQ",
          "warnings": [
            "Error while parsing date '18.4.2022 15:25:45'",
            "Error while parsing date '18.4.2022 15:25:54'",
            "Error while parsing date '18.4.2022 15:26:09'",
            "Error while parsing date '18.4.2022 15:26:17'",
            "Error while parsing date '18.4.2022 15:26:29'"
          ]
        }
      },
      "fields": [
        {
          "name": "Časová pečiatka",
          "type": "time",
          "typeInfo": {
            "frame": "time.Time",
            "nullable": true
          },
          "config": {
            "displayName": "Časová pečiatka"
          },
          "values": [
            null,
            null,
            null,
            null,
            null
          ],
          "entities": {},
          "state": null
        },
        {
          "name": "Zadaj Teplotu",
          "type": "number",
          "typeInfo": {
            "frame": "float64",
            "nullable": true
          },
          "config": {
            "displayName": "Zadaj Teplotu"
          },
          "values": [
            10,
            12,
            13,
            18,
            22
          ],
          "entities": {},
          "state": {
            "calcs": {
              "sum": 75,
              "max": 22,
              "min": 10,
              "logmin": 10,
              "mean": 15,
              "last": 22,
              "first": 10,
              "lastNotNull": 22,
              "firstNotNull": 10,
              "count": 5,
              "nonNullCount": 5,
              "allIsNull": false,
              "allIsZero": false,
              "range": 12,
              "diff": 12,
              "delta": 12,
              "step": 1,
              "diffperc": 1.2,
              "previousDeltaUp": true
            },
            "displayName": "Zadaj Teplotu",
            "multipleFrames": false
          }
        }
      ],
      "length": 5
    }
  ],
  "annotations": [],
  "request": {
    "app": "explore",
    "dashboardId": 0,
    "timezone": "browser",
    "startTime": 1650288877069,
    "interval": "2s",
    "intervalMs": 2000,
    "panelId": "Q-30ce531a-82dc-4352-981e-3542a6639cec-0",
    "targets": [
      {
        "refId": "A",
        "key": "Q-30ce531a-82dc-4352-981e-3542a6639cec-0",
        "cacheDurationSeconds": 300,
        "spreadsheet": "1CzC9IeQAmuep7EKRIEN2ana9tDIIp20QphNF58JC-HQ",
        "datasource": {
          "type": "grafana-googlesheets-datasource",
          "uid": "Kb2AA68nz"
        }
      }
    ],
    "range": {
      "from": "2022-04-18T12:34:37.058Z",
      "to": "2022-04-18T13:34:37.058Z",
      "raw": {
        "from": "now-1h",
        "to": "now"
      }
    },
    "requestId": "explore_left",
    "rangeRaw": {
      "from": "now-1h",
      "to": "now"
    },
    "scopedVars": {
      "__interval": {
        "text": "2s",
        "value": "2s"
      },
      "__interval_ms": {
        "text": 2000,
        "value": 2000
      }
    },
    "maxDataPoints": 1872,
    "liveStreaming": false,
    "endTime": 1650288877633
  },
  "timeRange": {
    "from": "2022-04-18T12:34:37.058Z",
    "to": "2022-04-18T13:34:37.058Z",
    "raw": {
      "from": "now-1h",
      "to": "now"
    }
  },
  "timings": {
    "dataProcessingTime": 0.10000000149011612
  },
  "graphFrames": [
    {
      "name": "A",
      "refId": "A",
      "meta": {
        "custom": {
          "expires": 1650288746,
          "hit": true,
          "range": "",
          "spreadsheetId": "1CzC9IeQAmuep7EKRIEN2ana9tDIIp20QphNF58JC-HQ",
          "warnings": [
            "Error while parsing date '18.4.2022 15:25:45'",
            "Error while parsing date '18.4.2022 15:25:54'",
            "Error while parsing date '18.4.2022 15:26:09'",
            "Error while parsing date '18.4.2022 15:26:17'",
            "Error while parsing date '18.4.2022 15:26:29'"
          ]
        }
      },
      "fields": [
        {
          "name": "Časová pečiatka",
          "type": "time",
          "typeInfo": {
            "frame": "time.Time",
            "nullable": true
          },
          "config": {
            "displayName": "Časová pečiatka"
          },
          "values": [
            null,
            null,
            null,
            null,
            null
          ],
          "entities": {},
          "state": null
        },
        {
          "name": "Zadaj Teplotu",
          "type": "number",
          "typeInfo": {
            "frame": "float64",
            "nullable": true
          },
          "config": {
            "displayName": "Zadaj Teplotu"
          },
          "values": [
            10,
            12,
            13,
            18,
            22
          ],
          "entities": {},
          "state": {
            "calcs": {
              "sum": 75,
              "max": 22,
              "min": 10,
              "logmin": 10,
              "mean": 15,
              "last": 22,
              "first": 10,
              "lastNotNull": 22,
              "firstNotNull": 10,
              "count": 5,
              "nonNullCount": 5,
              "allIsNull": false,
              "allIsZero": false,
              "range": 12,
              "diff": 12,
              "delta": 12,
              "step": 1,
              "diffperc": 1.2,
              "previousDeltaUp": true
            },
            "displayName": "Zadaj Teplotu",
            "multipleFrames": false
          }
        }
      ],
      "length": 5
    }
  ],
  "tableFrames": [
    {
      "name": "A",
      "refId": "A",
      "meta": {
        "custom": {
          "expires": 1650288746,
          "hit": true,
          "range": "",
          "spreadsheetId": "1CzC9IeQAmuep7EKRIEN2ana9tDIIp20QphNF58JC-HQ",
          "warnings": [
            "Error while parsing date '18.4.2022 15:25:45'",
            "Error while parsing date '18.4.2022 15:25:54'",
            "Error while parsing date '18.4.2022 15:26:09'",
            "Error while parsing date '18.4.2022 15:26:17'",
            "Error while parsing date '18.4.2022 15:26:29'"
          ]
        }
      },
      "fields": [
        {
          "name": "Časová pečiatka",
          "type": "time",
          "typeInfo": {
            "frame": "time.Time",
            "nullable": true
          },
          "config": {
            "displayName": "Časová pečiatka"
          },
          "values": [
            null,
            null,
            null,
            null,
            null
          ],
          "entities": {},
          "state": null
        },
        {
          "name": "Zadaj Teplotu",
          "type": "number",
          "typeInfo": {
            "frame": "float64",
            "nullable": true
          },
          "config": {
            "displayName": "Zadaj Teplotu"
          },
          "values": [
            10,
            12,
            13,
            18,
            22
          ],
          "entities": {},
          "state": {
            "calcs": {
              "sum": 75,
              "max": 22,
              "min": 10,
              "logmin": 10,
              "mean": 15,
              "last": 22,
              "first": 10,
              "lastNotNull": 22,
              "firstNotNull": 10,
              "count": 5,
              "nonNullCount": 5,
              "allIsNull": false,
              "allIsZero": false,
              "range": 12,
              "diff": 12,
              "delta": 12,
              "step": 1,
              "diffperc": 1.2,
              "previousDeltaUp": true
            },
            "displayName": "Zadaj Teplotu",
            "multipleFrames": false
          }
        }
      ],
      "length": 5
    }
  ],
  "logsFrames": [],
  "traceFrames": [],
  "nodeGraphFrames": [],
  "graphResult": [
    {
      "name": "A",
      "refId": "A",
      "meta": {
        "custom": {
          "expires": 1650288746,
          "hit": true,
          "range": "",
          "spreadsheetId": "1CzC9IeQAmuep7EKRIEN2ana9tDIIp20QphNF58JC-HQ",
          "warnings": [
            "Error while parsing date '18.4.2022 15:25:45'",
            "Error while parsing date '18.4.2022 15:25:54'",
            "Error while parsing date '18.4.2022 15:26:09'",
            "Error while parsing date '18.4.2022 15:26:17'",
            "Error while parsing date '18.4.2022 15:26:29'"
          ]
        }
      },
      "fields": [
        {
          "name": "Časová pečiatka",
          "type": "time",
          "typeInfo": {
            "frame": "time.Time",
            "nullable": true
          },
          "config": {
            "displayName": "Časová pečiatka"
          },
          "values": [
            null,
            null,
            null,
            null,
            null
          ],
          "entities": {},
          "state": null
        },
        {
          "name": "Zadaj Teplotu",
          "type": "number",
          "typeInfo": {
            "frame": "float64",
            "nullable": true
          },
          "config": {
            "displayName": "Zadaj Teplotu"
          },
          "values": [
            10,
            12,
            13,
            18,
            22
          ],
          "entities": {},
          "state": {
            "calcs": {
              "sum": 75,
              "max": 22,
              "min": 10,
              "logmin": 10,
              "mean": 15,
              "last": 22,
              "first": 10,
              "lastNotNull": 22,
              "firstNotNull": 10,
              "count": 5,
              "nonNullCount": 5,
              "allIsNull": false,
              "allIsZero": false,
              "range": 12,
              "diff": 12,
              "delta": 12,
              "step": 1,
              "diffperc": 1.2,
              "previousDeltaUp": true
            },
            "displayName": "Zadaj Teplotu",
            "multipleFrames": false
          }
        }
      ],
      "length": 5
    }
  ],
  "tableResult": {
    "name": "A",
    "refId": "A",
    "meta": {
      "custom": {
        "expires": 1650288746,
        "hit": true,
        "range": "",
        "spreadsheetId": "1CzC9IeQAmuep7EKRIEN2ana9tDIIp20QphNF58JC-HQ",
        "warnings": [
          "Error while parsing date '18.4.2022 15:25:45'",
          "Error while parsing date '18.4.2022 15:25:54'",
          "Error while parsing date '18.4.2022 15:26:09'",
          "Error while parsing date '18.4.2022 15:26:17'",
          "Error while parsing date '18.4.2022 15:26:29'"
        ]
      }
    },
    "fields": [
      {
        "name": "Časová pečiatka",
        "type": "time",
        "typeInfo": {
          "frame": "time.Time",
          "nullable": true
        },
        "config": {
          "displayName": "Časová pečiatka"
        },
        "values": [
          null,
          null,
          null,
          null,
          null
        ],
        "entities": {},
        "state": null
      },
      {
        "name": "Zadaj Teplotu",
        "type": "number",
        "typeInfo": {
          "frame": "float64",
          "nullable": true
        },
        "config": {
          "displayName": "Zadaj Teplotu"
        },
        "values": [
          10,
          12,
          13,
          18,
          22
        ],
        "entities": {},
        "state": {
          "calcs": {
            "sum": 75,
            "max": 22,
            "min": 10,
            "logmin": 10,
            "mean": 15,
            "last": 22,
            "first": 10,
            "lastNotNull": 22,
            "firstNotNull": 10,
            "count": 5,
            "nonNullCount": 5,
            "allIsNull": false,
            "allIsZero": false,
            "range": 12,
            "diff": 12,
            "delta": 12,
            "step": 1,
            "diffperc": 1.2,
            "previousDeltaUp": true
          },
          "displayName": "Zadaj Teplotu",
          "multipleFrames": false
        }
      }
    ],
    "length": 5
  },
  "logsResult": null
}
6

Just to see if it will work, try hardcoding a Unix timestamp value in col. A and then see what appears in Grafana for that row. It should no longer be Null, but instead a timestamp. You can then use Grafana’s formatting to make it display in a human friendly format.

7

Yes, I try it , and do nothing , just show number … But I try again… and result is … not working.
I fill manually cell with actual UNIX time and not working…

8

Does it show a number or null? Please post a screenshot of the same data in both Google Sheets and Grafana.

9

Here is it …

Unix time
Unix time1231×207 27.7 KB

Grafana Unix Time
Grafana Unix Time1731×653 42.4 KB

10

So you now have solved the problem of the Null appearing in the time column in Grafana. I believe all you need now is to display the value in the Time column to be in human readable format, correct? If yes, you can use a transformation like this:

image
image2380×757 79.2 KB

11

Ehm.
No , I don’t need human date time format , I need so grafana show chart , and do nothing .
Not working… how grafana know so data in column A are date time in Unix format?
Not working… and don’t know why , you test it self ?

12

Hi all.
I solve problem with Google Spread Sheet.
Now all working.
From ESP32 send data to SpreadSheet , and first column is date time in UNIX format. Then open sheet in google drive and define format for first column , then All begin working … Thanks all

2 Likes
13

Hello. Tell me please.
I have such a problem. There is a google sheet that has a date column.

But when displaying this data in grafana, I see only part of the data from the date column.

image
image677×717 69.6 KB

image
image1202×722 51.8 KB

14

I was having a similar problem, with a number instead of a time, and found a workaround. Not sure why I need the workaround, but at least something is working.

In my case, the misbehaving column had version numbers in the form X.Y or X.Y.Z. Because GSheet would truncate “3.10” (my use of that is between 3.9 and 3.11) as 3.1 (mathematically correct, but meaningfully undesired), in the GSheet I selected that column and did Format → Number → Plain Text, to preserve the text behavior in GSheet. When I included this sheet/range in my Grafana Query, the fields in that column would be null, verified by enabling “Cell value inspect” on the Grafana table and clicking on the eyeball icon in empty cells in the Grafana table.

On a hunch, I went back into the GSheet, and for every cell in that “version number” column, I prepended a single quote to the value, so that GSheet would treat the cell value as text. For example: '3.10

Then after doing that for all the cells in the column, I could select the column and do Format → Number → Automatic, and the “3.10” behavior worked as desired (not truncated to “3.1”). Now when I refresh the query in Grafana, the value is no longer null and instead displays the desired values, such as “3.10”.

The other thing I noticed was that if the configured Column Alignment was left as Auto, if the value was being interpreted as a number then it would be right-justified in the Grafana table, but if it was interpreted as a string then it would be left-justified in the Grafana table. I could also use the Grafana Query Inspector to drill down and see how it was being typed: Object → response → results → A → frames → 0 → schema → fields → (column offset) → type.

Wanted to share this for the benefit of anyone else that is running into this issue. My wild guess is that Grafana may not like it when GSheet has Format → Number → Plain Text for numbers (i.e., float64).

1 Like