0

I'm reading values from Google Sheets using google-api-php-client and google-api-php-client-services libraries this way:

   $service = new \Google_Service_Sheets($this->calendarService->getClient());
        $spreadsheetId = 'abhslJcKXjIqS8bAJ1lojekXuOk0WOSrdtVtJ2C512jQ';

        $range = 'Sheet Name';

        $response = $service->spreadsheets_values->get($spreadsheetId, $range);

        $values = $response->getValues(); // returns all cells as array

How can I get information on format of each cell? (eg. number, date, email etc.).

takeshin
  • 49,108
  • 32
  • 120
  • 164
  • I apologize for my poor English skill. Can I ask you about your question? I cannot understand the relationship between `information on format of each cell` and `number, date, email etc`. In order to correctly understand your goal, can you provide a sample Spreadsheet and the sample output you expect? Of course, please remove your personal information. – Tanaike Nov 13 '19 at 22:31

2 Answers2

1

None of the responses you can get from the Spradsheets.batchUpdate [1] includes information about the cells format. Neither the Spreadsheets.values.get request [2] includes this information in its response (ValueRange object [3]). You're able to change the cells format using UpdateCells request [4], but you can't retrieve it using Sheets API.

Using Apps Script there's a function which retrieves the number/date format [5], you could do a workaround with it.

[1] https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/response#Response

[2] https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get

[3] https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange

[4] https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#UpdateCellsRequest

[5] https://developers.google.com/apps-script/reference/spreadsheet/range.html#getNumberFormat()

Andres Duarte
  • 3,166
  • 1
  • 7
  • 14
1

You can get cell formatting also get textFormat (bold,strikethrough etc) Beside, you can search the object there are more specs related to each cell.

Cell Documentation of google api

To be able to get this you should use Google_Service_Sheets -> spreadsheets method by including gridData like below :

$sheets = new \Google_Service_Sheets($client); //callling sheet api with authorized client 
$spreadsheet = $sheets->spreadsheets->get($spreadsheetId,['includeGridData' => true]);

And i assume spreadsheet has 1 sheet to show you correct place of formatting :

$sheet = $spreadsheet->getSheets();
$format = $sheet[0]['data'][0]['rowData']['values'][0]['effectiveFormat']['numberFormat]; //this is one of example from that object

Hope it helps

O.k
  • 111
  • 1
  • 2
  • 11