Working with version 4 of the Google Sheets API in php

This article will show you how to work with the Google Sheets API in php. We will look at the requirements for using the API, show you how to create a separate project with access to the Google Sheets API and service credentials with an access key. And of course, we will give examples of working with the Google Sheets API in php.

Requirements for working with the Google Sheets API in php

The minimum requirements for working with APIs using PHP are:

• Google account
• PHP version 5.4 and higher
• php-json extension
• composer dependency management tool (optional)

To check php version:

$ php -v
PHP 7.1.30 (cli) (built: Jul  2 2019 17:22:20) ( NTS )
Copyright (c) 1997-2018 The PHP Group

To install the php-json extension execute on CentOS (RedHat like):

$ sudo yum install php-json

On Ubuntu similar systems:

$ sudo apt-get install php-json

If you have composer installed, then install the extension using it:

$ composer require google/apiclient:^2.0

I’ll just download it. To do this, create a folder for the project and go to it:

$ mkdir sheets
$ cd sheets

On this page, select the latest version for our php version and copy the link. In my case, this is client version 2.7.2 for php 7.0. After that, download the archive:

$ wget https://github.com/googleapis/google-api-php-client/releases/download/v2.7.2/google-api-php-client-v2.7.2-PHP7.0.zip

And unzip it:

$ unzip google-api-php-client-v2.7.2-PHP7.0.zip

This completes the preparatory stage, let’s move on to gaining access to the API.

Accessing the Google Sheets API

First, let’s create a separate project, in which we will later enable the Google Sheets API to work. Follow the link and click CREATE PROJECT (create a project):

In the Project name* field, enter the name of the new project, for example Sheets and click CREATE:

It can take up to several minutes to create a new project. After that it will appear in the manager panel:

Next, you need to enable work with the Google Sheets API for our project. Go through the console (three dashes in the upper left corner) to APIs & Services and then to Dashboards:

Check that our Sheets project is selected and click ENABLE APIS AND SERVICES:

A page with all API services will open. Let’s use the search specifying the Google Sheets API. Click on:

And click the ENABLE button:

Next, you need to create a service account to access the Google page API. Select APIs & Services and Credentials via the console:

In the window that appears, select CREATE CREDNTIALS and select Service Account:

At the first step, specify the name of the service account and press CREATE, at the second, select the Project -> Owner role and press CONTINUE. At the last step, press DONE:

After that, open the newly created account and create a new one in the keys section by selecting Create new key:

In the window that opens, select JSON and press CREATE and save the new generated key. It needs to be saved in our sheets working directory under the name service_key.json. Also, from the account data, copy the email from the Email field, we will need it in the future.

Examples of working with the Google Sheets API in php

Let’s create a spreadsheet. To do this, go to the Google Sheets section using the link and select an empty document. After the spreadsheet has been created, it is necessary to grant access to our service account to edit it. To do this, click the Access Settings button in the upper right corner and indicate the service account mail there, which we copied in the previous step, specifying the Editor role.

Get spreadsheet id and sheet id

Pay attention to the address bar, from which we will need to write two parameters, the spreadsheet ID ($spreadsheetId) and the sheet ID ($sheetId):

Connect a client to work with the Google Sheets API

Let’s get started with the php code and create a sheet.php file in our working directory. The file structure should look like this:

$ ls -l
total 204
-rw-r--r--.  1 user user   1981 Sep 18 23:04 CODE_OF_CONDUCT.md
-rw-r--r--.  1 user user   1315 Sep 18 23:04 composer.json
-rw-r--r--.  1 user user 133719 Sep 18 23:04 composer.lock
-rw-rw-r--.  1 user user    431 Oct 21 10:39 credentials.json
-rw-r--r--.  1 user user  10256 Sep 18 23:04 LICENSE
-rw-rw-r--.  1 user user   2986 Oct 21 11:11 quickstart.php
-rw-r--r--.  1 user user  17309 Sep 18 23:04 README.md
-rw-rw-r--.  1 user user   2292 Oct 22 12:09 service_key.json
-rw-rw-r--.  1 user user   1056 Oct 22 14:32 sheet.php
drwxr-xr-x.  3 user user     20 Sep 18 23:04 src
-rw-rw-r--.  1 user user    442 Oct 21 11:01 token.json
-rw-r--r--.  1 user user  11563 Sep 18 23:04 UPGRADING.md
drwxr-xr-x. 13 user user    193 Sep 18 23:04 vendor

where sheet.php is our work file, service_key.json is the service account key, and the rest of the files and directories are the Google Sheets client that we got from the archive.

Open sheet.php file and write the following code (replace $spreadsheetId with your own):

<?php

// Connect the Google Sheets API client
require_once __DIR__ . '/vendor/autoload.php';

// 
Our service account access key
$googleAccountKeyFilePath = __DIR__ . '/service_key.json';
putenv('GOOGLE_APPLICATION_CREDENTIALS=' . $googleAccountKeyFilePath);

// Create new client
$client = new Google_Client();
// Set credentials
$client->useApplicationDefaultCredentials();

// 
Adding an access area for reading, editing, creating and deleting tables
$client->addScope('https://www.googleapis.com/auth/spreadsheets');

$service = new Google_Service_Sheets($client);

// you spreadsheet ID
$spreadsheetId = 'ваш идентификатор таблицы';

This code for connecting the client, allowing access and specifying the spreadsheet ID will be required to run all examples. But in order not to duplicate it, in the future it will be omitted.

The Google Sheets API has a limit of 500 requests per project in 100 seconds and 100 requests in 100 seconds per service account! Keep this in mind when accessing the service, pause in the code if necessary!

Checking access to the Google Sheets API

Let’s add the following lines to our code:

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

var_dump($response);

And let’s run it:

$ php sheet.php

If everything is done correctly, then after running the script, information about the spreadsheet will be displayed on the screen. But the first time there might be the following error:

PHP Fatal error:  Uncaught DomainException: Could not load the default credentials. Browse to https://developers.google.com/accounts/docs/application-default-credentials for more information in /home/user/sheets/vendor/google/auth/src/ApplicationDefaultCredentials.php:180
Stack trace:
#0 /home/user/sheets/src/Google/Client.php(1197): Google\Auth\ApplicationDefaultCredentials::getCredentials('https://www.goo...', Object(Google\Auth\HttpHandler\Guzzle6HttpHandler), NULL, NULL, NULL)
#1 /home/user/sheets/src/Google/Client.php(402): Google_Client->createApplicationDefaultCredentials()
#2 /home/user/sheets/src/Google/Client.php(872): Google_Client->authorize()
#3 /home/user/sheets/src/Google/Service/Resource.php(232): Google_Client->execute(Object(GuzzleHttp\Psr7\Request), 'Google_Service_...')
#4 /home/user/sheets/vendor/google/apiclient-services/src/Google/Service/Sheets/Resource/Spreadsheets.php(92): Google_Service_Resource->call('get', Array, 'Google_Service_...')
#5 /home/user/sheets/sheet.php(25): Google_Service_Sheets_Resour in /home/user/sheets/vendor/google/auth/src/ApplicationDefaultCredentials.php on line 180

It is related to the incorrect structure of the service_key.json file: check that it contains line breaks, and in the private_key parameter in the description of the beginning and end of the key (BEGIN PRIVATE KEY and END PRIVATE KEY), there is exactly one space between words.

Get spreadsheet data, sheets and their contents

For example, we will use a spreadsheet named My project with one sheet 2020-10 and data:

Let’s see this data via the API Google sheets php:

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

// spreadsheet ID
var_dump($response->spreadsheetId);

// URL spreadsheet
var_dump($response->spreadsheetUrl);

// Get properties of spreadsheet
$spreadsheetProperties = $response->getProperties();

// spreadsheet name
var_dump($spreadsheetProperties->title);

// All sheets
foreach($response->getSheets() as $sheet) {

        // Properties of sheet
        $sheetProperties = $sheet->getProperties();
        // sheet ID
        var_dump($sheetProperties->index);
        // sheet name
        var_dump($sheetProperties->title);
}

The result of the work will be the following data:

$ php sheet.php
string(44) "1ftopfUv4ymp78IdNa1268Cc8iC4Kd7LHgCzCNPAOavq"
string(88) "https://docs.google.com/spreadsheets/d/1ftopfUv4ymp78IdNa1268Cc8iC4Kd7LHgCzCNPAOavq/edit"
string(10) "My project"
int(0)
string(7) "2020-10"

Creating a new spreadsheet

For example, the method for creating a new spreadsheet will be given:

// Object - spreadsheet properties
$SpreadsheetProperties = new Google_Service_Sheets_SpreadsheetProperties();
// spreadsheet name
$SpreadsheetProperties->setTitle('NewSpreadsheet');
// Object - spreadsheet
$Spreadsheet = new Google_Service_Sheets_Spreadsheet();
$Spreadsheet->setProperties($SpreadsheetProperties);
// Take request
$response = $service->spreadsheets->create($Spreadsheet);

// Show id and url new spreadsheet
var_dump($response->spreadsheetId);
var_dump($response->spreadsheetUrl);

However, if you go to the url you will be denied access. You need to allow reading/writing from your Google account.

Spreadsheet access permissions

To do this, you need to enable the use of the Google Drive API in our project. This is done in the same way as enabling the Google Sheets API (see above): find it in the search and press ENABLE. And in the original example, change the scope of $client-> addScope(‘https://www.googleapis.com/auth/spreadsheets’) to:

$client->addScope(['https://www.googleapis.com/auth/drive', 'https://www.googleapis.com/auth/spreadsheets']);

Now we can add permissions:

// Object - drive
$Drive = new Google_Service_Drive($client);
// Object - permission drive
$DrivePermisson = new Google_Service_Drive_Permission();
// Type permission
$DrivePermisson->setType('user');
// You email
$DrivePermisson->setEmailAddress('you_email@gmail.com');
// Role
$DrivePermisson->setRole('writer');
// Send request with you spreadsheetId
$response = $Drive->permissions->create('you_spreadsheetId', $DrivePermisson);

var_dump($response);

Now we can follow the url from the previous example and edit the spreadsheet.

Checking permissions to a table / file

To check permissions (roles) we have to table (file), you can use the following example:

$Drive = new Google_Service_Drive($client);
$DrivePermissions = $Drive->permissions->listPermissions($spreadsheetId);

foreach ($DrivePermissions as $key => $value) {
    $role = $value->role;
    
    var_dump($role);
}

The code will return the roles of the $spreadsheetId table for our account:

string(6) "writer"
string(5) "owner"

Create a new sheet

Let’s create a new sheet named NEW in our spreadsheet:

// Object - sheet properties
$SheetProperties = new Google_Service_Sheets_SheetProperties();
// Sheet name
$SheetProperties->setTitle('NEW');

// Object - request to add sheet
$AddSheetRequests = new Google_Service_Sheets_AddSheetRequest();
$AddSheetRequests->setProperties($SheetProperties);

// Object - request
$SheetRequests = new Google_Service_Sheets_Request();
$SheetRequests->setAddSheet($AddSheetRequests);

// Object - request spreadsheet update
$requests = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$requests->setRequests($SheetRequests);

// Send request
$response = $service->spreadsheets->BatchUpdate($spreadsheetId, $requests);

// Show result
var_dump($response);

Change sheet properties

Let’s change the properties of the new sheet, for example, rename it:

// Get our spreadsheet
$spreadsheets = $service->spreadsheets->get($spreadsheetId);
// We get the current properties of the previously created sheet, indicating its identifier - 1
$SheetProperties = $spreadsheets->getSheets()[1]->getProperties();
// Set new name
$SheetProperties->setTitle('RENAME');

// Object - request to update sheet properties
$UpdateSheetRequests = new Google_Service_Sheets_UpdateSheetPropertiesRequest();
$UpdateSheetRequests->setProperties($SheetProperties);
// We indicate which property we want to update
$UpdateSheetRequests->setFields('title');

// Object - sheet request
$SheetRequests = new Google_Service_Sheets_Request();
$SheetRequests->setUpdateSheetProperties($UpdateSheetRequests);

// Object - request to update the table
$requests = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest();
$requests->setRequests($SheetRequests);
// Execute the request
$response = $service->spreadsheets->BatchUpdate($spreadsheetId, $requests);

// See the result
var_dump($response);

Copy sheet

The method allows you to copy a sheet of a spreadsheet to the same spreadsheet or another:

// The ID of the sheet to be copied
$sheetId = 0;

// Object - copy sheet request
$CopySheetRequest = new Google_Service_Sheets_CopySheetToAnotherSpreadsheetRequest();
$CopySheetRequest->setDestinationSpreadsheetId($spreadsheetId);
// Copy sheet $sheetId of spreadsheet $spreadsheetId
$response = $service->spreadsheets_sheets->copyTo($spreadsheetId, $sheetId, $CopySheetRequest);

// Show id and title of new sheet
var_dump($response->index);
var_dump($response->title);

A new sheet with the ID 1 and the name 2020-10 (copy) will appear in our spreadsheet.

Get sheet content

To get the contents of the entire sheet, you must specify its name:

// Get the contents of an entire sheet by its name
$range = '2020-10';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);
var_dump($response);

We will receive the following response:

object(Google_Service_Sheets_ValueRange)#75 (7) {
  ["collection_key":protected]=>
  string(6) "values"
  ["majorDimension"]=>
  string(4) "ROWS"
  ["range"]=>
  string(18) "'2020-10'!A1:Z1000"
  ["values"]=>
  array(4) {
    [0]=>
    array(5) {
      [0]=>
      string(4) "Name"
      [1]=>
      string(7) "Russian"
      [2]=>
      string(7) "English"
      [3]=>
      string(5) "Maths"
      [4]=>
      string(9) "Chemistry"
    }
    [1]=>
    array(5) {
      [0]=>
      string(4) "Ivan"
      [1]=>
      string(1) "4"
      [2]=>
      string(1) "5"
      [3]=>
      string(1) "4"
      [4]=>
      string(1) "3"
    }
    [2]=>
    array(5) {
      [0]=>
      string(4) "Alex"
      [1]=>
      string(1) "5"
      [2]=>
      string(1) "3"
      [3]=>
      string(1) "5"
      [4]=>
      string(1) "4"
    }
    [3]=>
    array(5) {
      [0]=>
      string(5) "Peter"
      [1]=>
      string(1) "4"
      [2]=>
      string(1) "4"
      [3]=>
      string(1) "4"
      [4]=>
      string(1) "3"
    }
  }
  ["internal_gapi_mappings":protected]=>
  array(0) {
  }
  ["modelData":protected]=>
  array(0) {
  }
  ["processed":protected]=>
  array(0) {
  }
}

Get the contents of a range of cells

To get the contents of a range of cells, you need to specify an exclamation mark in the $range variable along with the sheet name, followed by a range of cells:

// Get the contents a range of cells
$range = '2020-10!A1:B4';
$response = $service->spreadsheets_values->get($spreadsheetId, $range);

var_dump($response);

Updating a range of cells

Let’s say we want to add one more row with data to the range of cells A5: E5:

// Data to update
$values = [
  ["Eric", "3", "3", "3", "3"],
];

// Object - range of values
$ValueRange = new Google_Service_Sheets_ValueRange();
// Setting our data
$ValueRange->setValues($values);
// We specify in the options to process user data
$options = ['valueInputOption' => 'USER_ENTERED'];
// We make a request indicating in the second parameter the name of the sheet and the starting cell to fill
$service->spreadsheets_values->update($spreadsheetId, '2020-10!A5', $ValueRange, $options);

By default, when inserting a range, majorDimension = ROWS is used, i.e. the range is filled line by line. If insertion is required by columns, you need to change its value to COLUMNS:

// Object - range of values
$ValueRange = new Google_Service_Sheets_ValueRange();
// Specify the direction of insertion - by columns
$ValueRange->setMajorDimension('COLUMNS');
// Setting our data
$ValueRange->setValues($values);
// We specify in the options to process user data
$options = ['valueInputOption' => 'USER_ENTERED'];
// We make a request indicating in the second parameter the name of the sheet and the starting cell to fill
$service->spreadsheets_values->update($spreadsheetId, '2020-10!A5', $ValueRange, $options);

If it is necessary to skip (not fill) a row / column, then an empty array is specified in the array of values:

$values = [
  ["Eric", "3", "3", "3", "3"],
  [],
  ["Stan", "4", "3", "4", "3"]
];

In this case, the row / column values ​​will remain original.

Adding values ​​(rows) to the end of the sheet

Let’s say we already have a sheet filled with values ​​and we need to add a new line to the end of this sheet.

// The range in which we define the filled data. For example, if you specify the range A1:A10
// and if the cell A2 is empty, then the new value will be written to the string,
// starting from A2. Therefore, it is better to play it safe and specify a larger range:
$range = '2020-10!A1:Z';
// Data to update
$values = [
  ["Eric", "3", "3", "3", "3"],
];
// Object - range of values
$ValueRange = new Google_Service_Sheets_ValueRange();
// Setting our data
$ValueRange->setValues($values);
// We specify in the options to process user data
$options = ['valueInputOption' => 'USER_ENTERED'];
// Add our values ​​to the last row (where in the range A1: Z all cells are empty)
$service->spreadsheets_values->append($spreadsheetId, $range, $ValueRange, $options);

Clearing a range of cells

Let’s clear the range of cells that we updated in the previous example:

// Object - request to clear values
$clear = new Google_Service_Sheets_ClearValuesRequest();
// We make a request indicating in the second parameter the name of the sheet and the range of cells to clear
$response = $service->spreadsheets_values->clear($spreadsheetId, '2020-10!A5:E5', $clear);

Conclusion

This article discusses only a small part of the possibilities for working with the Google page API in php. If necessary, I will try to add new examples of interaction with the service. If someone has a need for specific – write, I will be glad to help.

Useful Links

• Google Sheets API v4 classes documentation.

• Google Sheets API v4 Rest documentation.