In the past, I have worked on a project where we wanted to write data dynamically on the Google Spreadsheet. We achieved it by interacting with the Google Sheets API. I found it an interesting topic that can be helpful for readers. So, I decided to write an article on it. In this tutorial, we study how to integrate Google Sheets API using PHP. We are going to perform create, write, append, and read operations on Google spreadsheets through the Sheets 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 may want to use Google Sheets for numerous reasons. One can store their user’s information, payment details, event registrations, activities, etc in Google Sheets. On the website, you may want an automated system that performs these read-and-write operations on spreadsheets. It can be done through the Sheets API. Having said that, let’s start with integrating Google Sheets API into your application.

Meanwhile, if you are running a WordPress website with Contact Form 7 plugin, you might want to check my plugin – Connect Contact Form 7 with Google Sheets.

Register an Application and Create Credentials

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

• Go to the Google Developer Console https://console.developers.google.com .
• Create a new project. You can also select existing projects.
• Give a name to your project. Google Console will generate a unique project ID for it.
• Your project will appear on top of the left sidebar.
• Click on Library. You will see a list of Google APIs.
• Enable Google Sheets API.
• Click on the Credentials. Select Oauth Client id under Create credentials. Choose the radio button for the 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 the Create button. You will get the client ID and client secret. Copy these details. We will need it in a moment.

Basic Configuration

While using OAuth, you have to generate an access token that 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 because 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. We’ll handle this stuff in our code.

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

{
    "require": {
        "google/apiclient": "^2.12.1",
        "hybridauth/hybridauth" : "~3.0"
    },
    "scripts": {
        "pre-autoload-dump": "Google\\Task\\Composer::cleanup"
    },
    "extra": {
        "google/apiclient-services": [
            "Sheets"
        ]
    }
}

Here we are installing the “google/apiclient” library. There are over 200 Google API services and we need only the ‘Sheets’ service. So I used Google\Task\Composer::cleanup task to clean up and keep only the ‘Sheets’ service.

To perform the OAuth, we’ll use the Hybridauth library which I have added to the composer.json file.

Next, run the command below for the installation of provided 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 SQL to create a table in the database.

CREATE TABLE `oauth_tokens` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `provider` varchar(255) NOT NULL,
  `provider_value` text NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Now, create a DB class that will interact with the database to retrieve, store, and update token details 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 oauth_tokens WHERE provider = 'google'");
        if($result->num_rows) {
            return false;
        }
  
        return true;
    }
  
    public function get_access_token() {
        $sql = $this->db->query("SELECT provider_value FROM oauth_tokens WHERE provider = 'google'");
        $result = $sql->fetch_assoc();
        return json_decode($result['provider_value']);
    }
  
    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()) {
            $sql = sprintf("INSERT INTO oauth_tokens(provider, provider_value) VALUES('%s', '%s')", 'google', $this->db->real_escape_string($token));
            $this->db->query($sql);
        } else {
            $sql = sprintf("UPDATE oauth_tokens SET provider_value = '%s' WHERE provider = '%s'", $this->db->real_escape_string($token), 'google');
            $this->db->query($sql);
        }
    }
}

Make sure to replace the placeholders with your 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 for Google Sheet API

We are ready with the packages and database. We can now authorize the Google account and store the access token in the ‘oauth_tokens’ table.

Create a config.php file and write a configuration as per the guidelines of the 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 );

You have to define the values of constants GOOGLE_CLIENT_ID, and GOOGLE_CLIENT_SECRET. Also, pass the callback URL for the ‘callback’ key. When the user completes the authorization, they will redirect to this callback URL.

Inside the callback.php file, we will fetch the access token and store them in the database as follows.

callback.php

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

Now, go to the browser and run YOUR_DOMAIN_URL/callback.php, it will redirect you to the Google account. Complete the authorization process. Upon completing the process, check the ‘oauth_tokens’ table. It should have stored the token details.

Create a Spreadsheet using Sheets API and PHP

Hope you have stored the access token in your table. If not, go through the above steps again and recheck what you’ve missed. The API operations will not be performed without having a valid access token.

Let’s start with creating a spreadsheet programmatically.

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 sheet is not created.
        }
    }
}

This code gets the token details from the database. It calls the Google Sheet service along with the access token and creates a spreadsheet. I printed the ID of a created spreadsheet.

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

The same code with a few changes will be used for other operations on spreadsheets.

Write Data on Spreadsheet

To perform the write operations, you should pass a range of cells to be written. For instance, you want to add the headings Name and Email at the top of a sheet. In that case, your range is ‘A1:B1’. That 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 data is not added.
        }
    }
}

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

Append Data to Google Sheet

If you are looking to add more data to the same Google Sheets, you need to append it. There might be cases where you want to append one or more rows at a time. 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 data is not appended.
        }
    }
}

I have used the range ‘A1:B1’ and passed 2 separate array elements. 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 Google Sheet

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

<?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 {
            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 data is not read.
        }
    }
}

It’s all about interacting with the Sheets API. 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

• How to use YouTube API to Upload Video on YouTube Channel
• How to Add Google OAuth Login in Website with PHP
• How to Create a Meeting on Zoom using Zoom API and PHP

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