Google Sheets API: auth, reading, writing, and spreadsheet metadata | Dorokhov.codes

Documentation:

Installation

composer require google/apiclient:^2.0

open_in_new google-api-php-client .

Service object model

Everything starts from Google_Service_Sheets:

$service = new Google_Service_Sheets($google_client);

That service exposes four resource properties (each ends with Resource):

Property	Class	Purpose
`$spreadsheets`	`Google_Service_Sheets_Spreadsheets_Resource`	Spreadsheets, sheets, metadata, styles (e.g. cell background)
`$spreadsheets_developerMetadata`	`Google_Service_Sheets_SpreadsheetsDeveloperMetadata_Resource`	Developer metadata
`$spreadsheets_sheets`	`Google_Service_Sheets_SpreadsheetsSheets_Resource`	Sheet-level operations
`$spreadsheets_values`	`Google_Service_Sheets_SpreadsheetsValues_Resource`	The data matrix (rows and cells)

Get a spreadsheet:

$spreadsheet = $service->spreadsheets->get($spreadsheetId);

Get a value range:

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

Spreadsheet and sheet properties

$properties = $spreadsheet->getProperties();
$sheets = $spreadsheet->getSheets();

$properties = $sheet->getProperties();
$properties->title;

$grid_properties = $properties->getGridProperties();
$grid_properties->rowCount;
$grid_properties->columnCount;

Authentication

Some Google APIs accept an API key alone; others require OAuth. For server-side work with private sheets, use a service account .

API key:

$client->setDeveloperKey($api_key);

Reading

<?php

require __DIR__ . '/../vendor/autoload.php';

$client = new Google_Client();
$client->setDeveloperKey('YOUR_API_KEY');

$service = new Google_Service_Sheets($client);

$spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';
$range = 'Class Data!A2:E';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

if (empty($values)) {
    print "No data found.\n";
} else {
    print "Name, Major:\n";
    foreach ($values as $row) {
        // Print columns A and E (indices 0 and 4).
        printf("%s, %s\n", $row[0], $row[4]);
    }
}

Grid row count from sheet properties:

public function getGridRowsCount()
{
    $sheet_properties = $this->service->spreadsheets->get($this->spreadsheet_id)->getSheets()[0]->getProperties();
    $grid_properties = $sheet_properties->getGridProperties();
    return $grid_properties->getRowCount();
}

Writing

Any write — even to a publicly editable sheet — requires an OAuth flow (or a service account with access).

<?php

require __DIR__ . '/../vendor/autoload.php';

$client = new Google_Client();
$service = new Google_Service_Sheets($client);

$spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms';

$body = new Google_Service_Sheets_ValueRange([ 'values' => [[$count]] ]);

$service->spreadsheets_values->update($spreadsheetId, $cell_address, $body, [
    'valueInputOption' => 'USER_ENTERED',
]);

Batch update:

$first_range = new \Google_Service_Sheets_ValueRange([
    'range' => 'M12',
    'values' => [['first_range']],
]);

$second_range = new \Google_Service_Sheets_ValueRange([
    'range' => 'M13',
    'values' => [['second_range']],
]);

$body = new \Google_Service_Sheets_BatchUpdateValuesRequest([
    'valueInputOption' => 'USER_ENTERED',
    'data' => [$first_range, $second_range],
]);

$this->service->spreadsheets_values->batchUpdate($this->spreadsheet_id, $body);

Auto-resize columns

public function autoResizeColumns()
{
    $dimensions = new Google_Service_Sheets_DimensionRange([
        'dimension'  => 'COLUMNS',
        'sheetId'    => 0,
        'startIndex' => 0,
        'endIndex'   => 9,
    ]);

    $request = new Google_Service_Sheets_AutoResizeDimensionsRequest();
    $request->setDimensions($dimensions);

    $post_body = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
    $post_body->setRequests([
        'autoResizeDimensions' => $request,
    ]);

    $this->service->spreadsheets->batchUpdate($this->spreadsheet_id, $post_body);
}

Getting information

Count filled rows by reading values (more reliable than grid properties alone):

public function getRecordsCount()
{
    $sheet_title = $this->service->spreadsheets->get($this->spreadsheet_id)->getSheets()[0]->getProperties()->title;
    $spreadsheets_values = $this->service->spreadsheets_values->get($this->spreadsheet_id, $sheet_title);
    return count($spreadsheets_values->getValues());
}

You can also read rowCount / columnCount from grid properties, but those numbers are often larger than the used range:

$spreadsheet = $service->spreadsheets->get($spreadsheetId);

foreach ($spreadsheet->getSheets() as $sheet) {
    $sheet_properties = $sheet->getProperties();
    $gridProperties = $sheet_properties->getGridProperties();
    $gridProperties->columnCount;
    $gridProperties->rowCount;
}

Extract spreadsheet ID from a URL

private function fillSpreadsheetId()
{
    if (preg_match('{/spreadsheets/d/([a-zA-Z0-9-_]+)}', $this->url, $matches)) {
        $this->spreadsheet_id = $matches[1];
        return true;
    }
    return false;
}