Google Sheet , Time column null

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 .


And here is what I see in Grafana:

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.

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

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 …

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 …

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
}

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.

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…

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

Here is it …


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:

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 ?

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

1 Like