How to determine the data type of values returned by Google Sheets API

Question

I am using the Google sheets API to get values from data sets with unknown values. I need to figure out the data type of each returned value (number, boolean, string or datetime).

The API will return numbers as number. I can check for a boolean value in a string and otherwise return the value as type string. I need a robust way to determine if the string should be of type datetime.

Google Sheets seems to know the the data type of each cell, but I can't get the API to return the cell data type. Does anyone know if I can do this, is it possible? Or do I need to check each string using regular expressions for possible datetime formats and coerce the string to a date if it matches?

Answer 1

You can retrieve the cell format using sheets.spreadsheets.get of Sheets API v4. In your case, you can use sheets(data(rowData(values(userEnteredFormat/numberFormat,userEnteredValue)),startColumn,startRow)) as the fields.

For example, when you want to retrieve the cell format of "A1", you can use the endpoint as follows.

Endpoint :

GET https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###?ranges=sheet1!a1%3Aa1&fields=sheets(data(rowData(values(userEnteredFormat%2FnumberFormat%2CuserEnteredValue))%2CstartColumn%2CstartRow))

Following results are the responses retrieved for number, boolean, string and datetime.

Result 1 :

At this sample, the value and format are "123" and number, respectively.

{
 "sheets": [
  {
   "data": [
    {
     "rowData": [
      {
       "values": [
        {
         "userEnteredValue": {
          "numberValue": 123
         },
         "userEnteredFormat": {
          "numberFormat": {
           "type": "NUMBER",
           "pattern": "#,##0.00"
          }
         }
        }
       ]
      }
     ]
    }
   ]
  }
 ]
}

Result 2 :

At this sample, the value and format are "true" and the automatically detected boolean, respectively.

{
 "sheets": [
  {
   "data": [
    {
     "rowData": [
      {
       "values": [
        {
         "userEnteredValue": {
          "boolValue": true
         }
        }
       ]
      }
     ]
    }
   ]
  }
 ]
}

Result 3 :

At this sample, the value and format are "123" and string, respectively.

{
 "sheets": [
  {
   "data": [
    {
     "rowData": [
      {
       "values": [
        {
         "userEnteredValue": {
          "stringValue": "123"
         },
         "userEnteredFormat": {
          "numberFormat": {
           "type": "TEXT"
          }
         }
        }
       ]
      }
     ]
    }
   ]
  }
 ]
}

Result 4 :

At this sample, the value and format are "2017/10/10 1:23:45" and datetime, respectively.

{
 "sheets": [
  {
   "data": [
    {
     "rowData": [
      {
       "values": [
        {
         "userEnteredValue": {
          "numberValue": 43018.05815972222
         },
         "userEnteredFormat": {
          "numberFormat": {
           "type": "DATE",
           "pattern": "yyyy/MM/dd"
          }
         }
        }
       ]
      }
     ]
    }
   ]
  }
 ]
}

numberValue is the serial number.

If I misunderstand your question, I'm sorry.