Get Cell Format from Google Sheet API

I’d like to retrieve the format/type of the cell that set in google sheet, for example Plain text, Number, Date, Time and etc from the Google Sheet API. Google Sheet format screenshot here. Google Sheet Sample

I’m using the API from this Srijan’s reference, it only able to pull out the values of the cells, but I need the format as well.

The code reference as below:

$response = $service->spreadsheets_values->get($spreadsheetId, $getRange);
$values = $response->getValues();

Output as below:

{
header 1: "A1", - text
header 2: "100", - number
header 3: "888.88", - currency
header 4: "10 Apr 2020", - date
header 5: "E1", - text
header 6: "F1", - text
header 7: "G1", - text
header 8: "H1", - text
header 9: "I1", - text
header 10: "J1", - text
header 11: "K1", - text
header 12: "L1", - text
header 13: "M1" - text
},

Desired Output:

{
header 1: {
    "value" : "A1",
    "type" : "text"
},
header 2: {
    "value" : "100",
    "type" : "number"
}
},

According to the Google Sheet API reference, it seems we can actually get the format of the cells. But how can we integrate with the API I used above?

Answer

To get data about specific cells, you need to call the method get of spreadsheets. You can Try this API, setting includeGridData to true and explore the result to check if there is the information that you are looking for. Once you find it, let’s get it with php.

How to get cell data in php (including cell format)

1. Follow the quickstart guide to php and check that you get the desired result
2. Call the method get of a spreadsheet including the parameter includeGridData
3. Call the method getSheets of spreadsheets and store the result
4. Search your desired data in your result

$service = new Google_Service_Sheets($client);
$spreadsheetId = 'your spreadsheet id';
$spreadsheet = $service->spreadsheets->get($spreadsheetId,['includeGridData' => true]);
$sheet = $spreadsheet->getSheets();
$formats = $sheet[0]['data'][0]['rowData'][0]['values'];

Considerations

• The last zero indicates the row number, if you have header you can set it to 1
• formats is an array that includes all the columns of a given row.
• Access to the first column and display its value with:

  $myJson = json_encode($formats[0]);
  echo $myJson;
  

• There is an object (effectiveValue) that indicates if the value is boolValue, formulaValue, numberValue or stringValue. However, dates are considered numberValue. To differentiate between types of numberValue there is another object (effectiveFormat) that displays cellFormat. It has one property called numberFormat that indicates the kind of numberValue that is in the cell.

With this information you should be able to achieve your goal.

Work around

Use Apps Script and getnumberformats instead of php.