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 interacted with the Google Sheets API. I found it an interesting topic for the blog post that can be helpful for readers. 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 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 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 on triggers of a specific action. It can be done through the Sheets API. Having said that, let’s start with integrating Google Sheets API in your application.

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. Alternatively, 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 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 client ID and client secret in the pop-up. Copy these details. We will need it in a moment.
OAuth Client

Basic Configuration

In order to use OAuth to perform the API operations, it requires you 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 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 the below lines in it.

{
    "require": {
        "google/apiclient": "^2.10",
        "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 kept only the ‘Sheets’ service.

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 `google_oauth` (
 `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 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 google_oauth WHERE provider = 'google'");
        if($result->num_rows) {
            return false;
        }
 
        return true;
    }
 
    public function get_access_token() {
        $sql = $this->db->query("SELECT provider_value FROM google_oauth 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()) {
            $this->db->query("INSERT INTO google_oauth(provider, provider_value) VALUES('google', '$token')");
        } else {
            $this->db->query("UPDATE google_oauth SET provider_value = '$token' WHERE provider = 'google'");
        }
    }
}

Replace the placeholders in the above code with 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 for Google Sheet API

We are done with creating a table and installing libraries. Next, we need to authorize the Google account and store the access token in the ‘google_oauth’ table.

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

Make sure to define values of the constants GOOGLE_CLIENT_ID, 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.

Using callback.php file, we will fetch the access token details 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 ‘google_oauth’ table. It should have stored the token information.

Create a Spreadsheet using Sheets API and PHP

You have stored the access token in your table. Once we have the access token we are able to interact with Google Sheets API. 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. 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 the 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 a few changes will be used for other operations on spreadsheets.

Write Data to 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 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 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

Append Data to Google Sheet

If you are looking to add information in Google Sheet continuously, 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 data is not appended.
        }
    }
}

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 Google Sheet

We have written a code for creating a spreadsheet, writing 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 {
            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.
        }
    }
}

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.

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

  1. hi i tried it successfully, but my auth code expires every 2 hours, is there any way to make it permanent i have linked my account with visa but it still expires token

  2. I got the same problem reported above. The first time, and actually for a couple of time for some days it worked just perfect, now, about 10 days later I cannot make it work.
    I get this:

    Fatal error: Uncaught GuzzleHttp\Exception\ClientException: Client error: `POST https://accounts.google.com/o/oauth2/token` resulted in a `400 Bad Request` response: { “error”: “invalid_grant”, “error_description”: “Bad Request” } in /home/nielsent/public_html/pdp/import/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php:113 Stack trace: #0 /home/nielsent/public_html/pdp/import/vendor/guzzlehttp/guzzle/src/Middleware.php(69): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response), NULL, Array, NULL) #1 /home/nielsent/public_html/pdp/import/vendor/guzzlehttp/promises/src/Promise.php(204): GuzzleHttp\Middleware::GuzzleHttp\{closure}(Object(GuzzleHttp\Psr7\Response)) #2 /home/nielsent/public_html/pdp/import/vendor/guzzlehttp/promises/src/Promise.php(153): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\Psr7\Response), NULL) #3 /home/nielsent/public_html/pdp/import/vendor/guzzlehttp/promises/src/TaskQueue.php(48): GuzzleHttp\Promise\Promise::G in /home/nielsent/public_html/pdp/import/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php on line 113

    Any idea what to do to make it work fine again?

    1. It seems you run authentication twice which makes the previous access token invalid. You have to run authentication only once. Now truncate the table and follow the authentication again.

  3. I try to create sheet. But it push me again and again in exception. and after that i got too many connection error.
    {
    “error”: {
    “code”: 401,
    “message”: “Request had invalid authentication credentials. Expected OAuth 2 access token, login cookie or other valid authentication credential. See https://developers.google.com/identity/sign-in/web/devconsole-project.”,
    “errors”: [
    {
    “message”: “Invalid Credentials”,
    “domain”: “global”,
    “reason”: “authError”,
    “location”: “Authorization”,
    “locationType”: “header”
    }
    ],
    “status”: “UNAUTHENTICATED”
    }
    }

    1. It seems you run authentication twice. Truncate table, follow the oauth process again. It should be fixed it. Remember you need to run oauth once.

  4. 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 *