How to Integrate Google Sheets API with PHP

In the past, I have worked on a project where we wanted to write data dynamically to the Google Spreadsheet. To achieve this, we had interacted with the Sheets API. I found it an interesting topic for the blog post. So, in this article, we study how to integrate Google Sheets API using PHP. We are going to perform create, write, append, and read operations on spreadsheets through the API.

Google Spreadsheet is a free, web-based software office suite maintained by Google. One can use the spreadsheet for their own purpose within the Google Drive service.

When it comes to websites, you can use Google sheets for various purposes. One can store their user’s contact information (phone and email), payment details, event registrations, activities, and much more in the Google sheet. On the website, you may want an automated system that does all read, write operations on spreadsheets when specific action triggers. It can be done through the Sheets API.

Register an Application and Create Credentials

I am going to use OAuth to interact with the API. OAuth is a more secure and recommended way of doing API operations. Follow the steps below which are required for OAuth integration.

  • Go to the Google Developer Console https://console.developers.google.com
  • Create a new project. Alternately, you can also select existing project.
  • Give a name to your project. Google Console will generate unique project ID for it.
  • Your project will appear on top of the left sidebar.
  • Click on Library. You will see list of Google APIs.
  • Enable Google Sheets API.
  • Click on the Credentials. Select Oauth Client id under Create credentials. Choose the radio button for Web Application.
  • Give the Name. Under Authorized JavaScript origins enter your domain URL. In the Authorized redirect URIs add the link of the redirect URL. In my case I passed the URL http://localhost/google-sheets-api/callback.php
  • Click on Create button. You will get client ID and client secret in the pop-up. Copy these details. We will need it in a moment.

OAuth Client

Basic Configuration

As said, we will use OAuth to perform the API operations. It requires you to generate an access token which acts as an identifier for your account. Using this access token, Google APIs validate whether the incoming request is valid and authorized.

The access token has a short life. They expire soon. And once expired, we can’t do API calls as Google treats the request with the expired token as an unauthorized request. To resolve this issue, we use the ‘refresh_token’ to regenerate the access token in the background. This is how OAuth works.

Let’s start building the OAuth flow. Create the composer.json file and add below lines in it.

{
    "require": {
        "google/apiclient": "2.0",
        "hybridauth/hybridauth" : "~3.0"
    }
}

Next, run the command below for the installation of these libraries.

composer install

When we generate the access token, it needs to be stored in a database. By storing it, you can fetch a token anytime and do the API calls. Run the below query to create a table in the database.

CREATE TABLE `token` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `access_token` text NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now, create a DB class that will interact with the database and retrieves, store, update token information in the table.

class-db.php

<?php
class DB {
    private $dbHost     = "DB_HOST";
    private $dbUsername = "DB_USERNAME";
    private $dbPassword = "DB_PASSWORD";
    private $dbName     = "DB_NAME";
     
    public function __construct(){
        if(!isset($this->db)){
            // Connect to the database
            $conn = new mysqli($this->dbHost, $this->dbUsername, $this->dbPassword, $this->dbName);
            if($conn->connect_error){
                die("Failed to connect with MySQL: " . $conn->connect_error);
            }else{
                $this->db = $conn;
            }
        }
    }
 
    public function is_table_empty() {
        $result = $this->db->query("SELECT id FROM token");
        if($result->num_rows) {
            return false;
        }
 
        return true;
    }
 
    public function get_access_token() {
        $sql = $this->db->query("SELECT access_token FROM token");
        $result = $sql->fetch_assoc();
        return json_decode($result['access_token']);
    }
 
    public function get_refersh_token() {
        $result = $this->get_access_token();
        return $result->refresh_token;
    }
 
    public function update_access_token($token) {
        if($this->is_table_empty()) {
            $this->db->query("INSERT INTO token(access_token) VALUES('$token')");
        } else {
            $this->db->query("UPDATE token SET access_token = '$token' WHERE id = (SELECT id FROM token)");
        }
    }
}

Replace the placeholders in the above code with your actual database credentials. Here I am assuming you want to use a single Google account. If you intend to use multiple accounts then modify the queries as per your requirements.

Generate Access Token

We are done with creating a table and installing libraries. The next thing needs to do is authorize the Google account and store the access token in the ‘token’ table.

Create a config.php file and write a configuration as per guidelines of HybridAuth library.

config.php

<?php
require_once 'vendor/autoload.php';
require_once 'class-db.php';
 
define('GOOGLE_CLIENT_ID', 'PASTE_CLIENT_ID_HERE');
define('GOOGLE_CLIENT_SECRET', 'PASTE_CLIENT_SECRET_HERE');
 
$config = [
    'callback' => 'YOUR_DOMAIN_URL/callback.php',
    'keys'     => [
                    'id' => GOOGLE_CLIENT_ID,
                    'secret' => GOOGLE_CLIENT_SECRET
                ],
    'scope'    => 'https://www.googleapis.com/auth/spreadsheets',
    'authorize_url_parameters' => [
            'approval_prompt' => 'force', // to pass only when you need to acquire a new refresh token.
            'access_type' => 'offline'
    ]
];
 
$adapter = new Hybridauth\Provider\Google( $config );

Make sure to define values of the constants GOOGLE_CLIENT_ID, GOOGLE_CLIENT_SECRET. Also, pass the callback URL in the above code. When the user completes the authorization, they will redirect to the callback URL.

Using callback.php file, we will fetch the access token details and store it in the database as follows.

callback.php

<?php
require_once 'config.php';
 
try {
    $adapter->authenticate();
    $token = $adapter->getAccessToken();
    $db = new DB();
    if($db->is_table_empty()) {
        $db->update_access_token(json_encode($token));
        echo "Access token inserted successfully.";
    }
}
catch( Exception $e ){
    echo $e->getMessage() ;
}

At this stage, go to the browser and run YOUR_DOMAIN_URL/callback.php, it will redirect you to the Google account. Complete the authorization process. After completing the process, check the ‘token’ table. It should have stored the token information.

Create a Spreadsheet using Sheets API and PHP

You have now stored the access token in your table. It means you can proceed with the further operations on Google sheets of your account. Let’s first start with the creating spreadsheet using the Sheets API.

create-sheet.php

<?php
require_once 'config.php';

create_spreadsheet();

function create_spreadsheet() {

    $client = new Google_Client();

    $db = new DB();

    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );

    $client->setAccessToken($accessToken);

    $service = new Google_Service_Sheets($client);

    try {
        $spreadsheet = new Google_Service_Sheets_Spreadsheet([
            'properties' => [
                'title' => 'API Sheet'
            ]
        ]);
        $spreadsheet = $service->spreadsheets->create($spreadsheet, [
            'fields' => 'spreadsheetId'
        ]);
        printf("Spreadsheet ID: %s\n", $spreadsheet->spreadsheetId);
    } catch(Exception $e) {
        if( 401 == $e->getCode() ) {
            $refresh_token = $db->get_refersh_token();

            $client = new GuzzleHttp\Client(['base_uri' => 'https://accounts.google.com']);

            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);

            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;

            $db->update_access_token(json_encode($data));

            create_spreadsheet();
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

This code first gets the token details from the DB. Using this token it calls the Google sheet service and creates a spreadsheet. I printed the ID of a created spreadsheet.

If an access token is expired, then code goes to the catch block. In the catch block, it regenerates the access token, updates it in the database, and continues the API operation.

The same code with little bit changes will be used for other operations on spreadsheets.

Write data to a Spreadsheet

To perform the write operations, you need to pass a range of cells to be written. For instance, you want to add headings Name and Email at the top. In that case, your range becomes ‘A1:B1’. It means we will write Name and Email headings in A1 and B1 cells respectively.

<?php
require_once 'config.php';

write_to_sheet('SPREADSHEET_ID');

function write_to_sheet($spreadsheetId = '') {
 
    $client = new Google_Client();
 
    $db = new DB();
 
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
 
    $client->setAccessToken($accessToken);
 
    $service = new Google_Service_Sheets($client);
 
    try {
        $range = 'A1:B1';
        $values = [
            [
                'Name',
                'Email',
            ],
        ];
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => 'USER_ENTERED'
        ];
        $result = $service->spreadsheets_values->update($spreadsheetId, $range, $body, $params);
        printf("%d cells updated.", $result->getUpdatedCells());
    } catch(Exception $e) {
        if( 401 == $e->getCode() ) {
            $refresh_token = $db->get_refersh_token();
 
            $client = new GuzzleHttp\Client(['base_uri' => 'https://accounts.google.com']);
 
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
 
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
 
            $db->update_access_token(json_encode($data));
 
            write_to_sheet($spreadsheetId);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

Here, I have passed the spreadsheet ID to the method. You can modify this function and pass additional parameters. In the previous code, we have returned the spreadsheet ID which can be used in the above code. Alternatively, you can get the spreadsheet ID from your sheet URL. See the screenshot below.

spreadsheet id

The string appearing between ‘d’ and ‘edit’ is the ID of a Google spreadsheet.

Append data to a Google Sheet

If you are looking to store specific information in Google sheet then you need to append it to the existing spreadsheet. There might be cases where you want to append one or more rows in one go. The user can pass the single or multiple array elements and append data to the sheet as shown below.

<?php
require_once 'config.php';

append_to_sheet('SPREADSHEET_ID');
 
function append_to_sheet($spreadsheetId = '') {
 
    $client = new Google_Client();
 
    $db = new DB();
 
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
 
    $client->setAccessToken($accessToken);
 
    $service = new Google_Service_Sheets($client);
 
    try {
        $range = 'A1:B1';
        $values = [
            [
                'John Doe',
                'john@test.com',
            ],
            [
                'Jack Waugh',
                'jack@test.com',
            ],
            // Additional rows ...
        ];
        $body = new Google_Service_Sheets_ValueRange([
            'values' => $values
        ]);
        $params = [
            'valueInputOption' => 'USER_ENTERED'
        ];
        $result = $service->spreadsheets_values->append($spreadsheetId, $range, $body, $params);
        printf("%d cells appended.", $result->getUpdates()->getUpdatedCells());
    } catch(Exception $e) {
        if( 401 == $e->getCode() ) {
            $refresh_token = $db->get_refersh_token();
 
            $client = new GuzzleHttp\Client(['base_uri' => 'https://accounts.google.com']);
 
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
 
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
 
            $db->update_access_token(json_encode($data));
 
            append_to_sheet($spreadsheetId);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

I have used the range ‘A1:B1’ and passed 2 separate array elements. Sheets API append these details after the table data. Keep note though we are passing ‘A1:B1’ in range, it will not replace values already written in these cells. Instead, it writes data to the next available cells.

Read data from a Google Sheet

We have written a code for creating a spreadsheet, writing to a spreadsheet, and appending data to the spreadsheet. Finally, let’s see how to read data from a Google Sheet.

<?php
require_once 'config.php';

read_sheet('SPREADSHEET_ID');
 
function read_sheet($spreadsheetId = '') {
 
    $client = new Google_Client();
 
    $db = new DB();
 
    $arr_token = (array) $db->get_access_token();
    $accessToken = array(
        'access_token' => $arr_token['access_token'],
        'expires_in' => $arr_token['expires_in'],
    );
 
    $client->setAccessToken($accessToken);
 
    $service = new Google_Service_Sheets($client);
 
    try {
        $range = 'A:B';
        $response = $service->spreadsheets_values->get($spreadsheetId, $range);
        $values = $response->getValues();

        if (empty($values)) {
            print "No data found.\n";
        } else {
            print "Name, Email:\n";
            foreach ($values as $row) {
                // Print columns A and E, which correspond to indices 0 and 4.
                printf("%s, %s\n", $row[0], $row[1]);
            }
        }
    } catch(Exception $e) {
        if( 401 == $e->getCode() ) {
            $refresh_token = $db->get_refersh_token();
 
            $client = new GuzzleHttp\Client(['base_uri' => 'https://accounts.google.com']);
 
            $response = $client->request('POST', '/o/oauth2/token', [
                'form_params' => [
                    "grant_type" => "refresh_token",
                    "refresh_token" => $refresh_token,
                    "client_id" => GOOGLE_CLIENT_ID,
                    "client_secret" => GOOGLE_CLIENT_SECRET,
                ],
            ]);
 
            $data = (array) json_decode($response->getBody());
            $data['refresh_token'] = $refresh_token;
 
            $db->update_access_token(json_encode($data));
 
            read_sheet($spreadsheetId);
        } else {
            echo $e->getMessage(); //print the error just in case your video is not uploaded.
        }
    }
}

I hope you understand how to integrate Google Sheets API using PHP. I would like to hear your thoughts and suggestions in the comment section below.

Related Articles

If you liked this article, then please subscribe to our YouTube Channel for video tutorials.

5 thoughts on “How to Integrate Google Sheets API with PHP

  1. Hi. Nice tutorials. How do I use variables to do CRUD operations on google sheet….how to use I php form
    Thank you

Leave a Reply

Your email address will not be published. Required fields are marked *