Store data to Google Sheets using PHP

Google SheetsIn some cases, when you need to dump some data to somewhere easily accessible, pushing to Google Sheets can be a good option. You can store data to Google Spreadsheet from cron jobs, background workers or even simple form submission. For example, in my case, I am logging some information from mailgun hooks.

While I were doing that, it seems like this easy task took more time then expected only because of lack of documentation. So, trying to write down an easy to follow tutorial that can make you set and pushing data to goggle Spreadsheet within 20 minutes.

Check Prerequisites

We’ll be using PHP and Google Sheets API v4. So, let’s check if the ground is fit for the mission.

Setting up Google Sheets API

If you don’t already have prepared a project and authentication, follow the steps to prepare one –

  1. Go through this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Then continue to Go to credentials.
  2. On the Add credentials to your project page, set the following values –
    Which API are you using? Google Sheets API
    Where will you be calling the API from? Other non-UI
    What data will you be accessing? Application Data

    Note: We are selecting the above choices because our intention is to push data from some background script that runs in a server without user interaction. Your choices may vary if your plan is different.

  3. Create a service account with owner privilege
  4. In final step, you should get 2 things, a JSON file (API Client Secret) and an API Key ID for created service account. Download the json file (if not automatically downloaded) and rename it to sheets_api_secret.json. Then move it to project directory.

Now we are ready to access and manipulate Google sheets programatically. Let’s go .

Ping – check if Sheets API Authentication is working

Create a Spreadsheet on Google for our test, name it “Access Log” or something and take it’s sheet ID. If you’re not sure about the sheet ID, copy the marked segment from sheet url –
Google Sheet ID

Then, run the following command in you project directory to install the Google Client Library for PHP:

php composer.phar require google/apiclient:^2.0

At last, create a file app.php and put the following in code in it. Assuming that the composer vendor directory and the sheets_api_secret.json file are in the same directory of this file.

<?php
require_once __DIR__ . '/vendor/autoload.php';

if (php_sapi_name() != 'cli') {
    throw new Exception('try running "php app.php" from command line.');
}

// This Sheet MUST BE shared with service account email
define('SHEET_ID', 'TheHashStringOfYourGoogleSheet');
define('APPLICATION_NAME', 'Manipulating Google Sheets from PHP');

define('CLIENT_SECRET_PATH', __DIR__ . '/sheets_api_secret.json');
define('ACCESS_TOKEN', 'TheAPIKeyIDOfServiceAccount');
define('SCOPES', implode(' ', [Google_Service_Sheets::SPREADSHEETS]));

// Create Google API Client
$client = new Google_Client();
$client->setApplicationName(APPLICATION_NAME);
$client->setScopes(SCOPES);
$client->setAuthConfig(CLIENT_SECRET_PATH);
$client->setAccessToken(ACCESS_TOKEN);

$service = new Google_Service_Sheets($client);

$sheetInfo = $service->spreadsheets->get(SHEET_ID)->getProperties();

print($sheetInfo['title']. PHP_EOL);

Before running, ensure the following points to make it work –

  • You have replaced the ACCESS_TOKEN value with the Key ID of service account. Find it here if not sure what it is.
  • Replaced SHEET_ID value with your sheet ID.
  • CLIENT_SECRET_PATH is set correctly to the client secret file.
  • At last, the Sheet is shared with the email of service account given Edit permission.

Now, run this script from command line.

php app.php

If it’s printing the name of sheet (e,g, Access Log), Congratulations! Your authentication is working and you are now ready to manipulate this spreadsheet!

Add data rows to Google Spreadsheet

Now we’ll write a few rows to the spreadsheet. Remove all the lines after getting the $service (line 23) and add the following lines.

$options = array('valueInputOption' => 'RAW');
$values = [
    ["Name", "Roll No.", "Contact"],
    ["Anis", "001", "+88017300112233"],
    ["Ashik", "002", "+88017300445566"]
];
$body   = new Google_Service_Sheets_ValueRange(['values' => $values]);

$result = $service->spreadsheets_values->update(SHEET_ID, 'A1:C3', $body, $options);
print($result->updatedRange. PHP_EOL);

Here, in the $option array, we are setting that the values should be treated as raw text, no formula or reference. In $values, each array of 1st level is a row and each value in 2nd level is a column value. And the last important thing is the cell range to update, the 2nd param of $service->spreadsheets_values->update method. If the range don’t cover the required space for input values, you’ll get an error.

Ok, let’s run it again. If it prints something like “Sheet1!A1:C3” maybe we’ve updated the sheet successfully! Go check the sheet to get the feeling of being .

Append data after last filled row of Google Spreadsheet

Now, the biggest challange you may see is setting the range. How will you track of filled rows? How to find next empty row? How to get the…

Okay, the solution is as easy as bigger the problem seems to be. Set the header row of sheet manually and always use the range of the header rows only. And use append function instead of update. This will automatically find the next empty row and insert the columns there.

$values = [["Anis", "001", "+88017300112233"]];
...
$result = $service->spreadsheets_values->append(SHEET_ID, 'A1:C1', $body, $options);

So, now every time you insert a row, that will be appended to the existing rows.

It was easy right?

BTW, don’t forget to check the Google Sheets API Reference and Client library for PHP.

AssalamuA’laikum-Wa-Rahmatullah!

18 Comments

  1. Hi,

    How do I remove the SSL Certificate?

    I’m receiving a “Fatal error: Uncaught exception ‘GuzzleHttp\Exception\RequestException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate” when testing on localhost

    Thank you! Great tutorial btw.

    1. So, the error message says, “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.”

      I guess you may have either not set the credentials properly or your application settings are not appropriate for running from command line. As you are trying to run as php app.php from the command line, check your application is registered as non UI, backend app.

      For this type of app, static credentials should be provided for Authentication and need to set them in your code before calling google’s API. For example, in app settings on google app console, the selection of “Where will you be calling the API from?” should be “Other non-UI“.

      1. API keys ->
        Key :- AIzaSyCo3__7MgOJBeqPMaTeocYS1M314w1QClY
        Name:- formDataToGoogleSheet3
        Creation date :- Feb 9, 2017
        Restriction :- None
        Key :- AIzaSyCo3__7MgOJBeqPMaTeocYS1M314w1QClY

        OAuth 2.0 client IDs ->

        Name:- formDataToGoogleSheet3
        Creation date:- Feb 9, 2017
        Type:- Web application
        Client ID :- 1004247683933a8smr6vq966i92naeijd2fuelqmltlsp.apps.googleusercontent.com

        Service account keys ->
        ID :- df53717b85940bd7ea35893276938ec6f553cc30
        Creation date:- Feb 9, 2017
        Service account:- formDataToGoogleSheet3

        1. Here Is My Code:

          define(‘SHEET_ID’, ‘1X_UeT6EA6HE0O4wSe-ZzAElPVJcLupEAMNM8fWMqpGA’);
          define(‘APPLICATION_NAME’, ‘Manipulating Google Sheets from PHP’);
          define(‘CLIENT_SECRET_PATH’, __DIR__ . ‘/sheets_api_secret.json’);

          //define(‘ACCESS_TOKEN’, ‘df53717b85940bd7ea35893276938ec6f553cc30’); //Key ID of service account.
          define(‘ACCESS_TOKEN’, ‘AIzaSyCo3__7MgOJBeqPMaTeocYS1M314w1QClY’); //API KEY
          define(‘SCOPES’, implode(‘ ‘, [Google_Service_Sheets::SPREADSHEETS]));
          // Create Google API Client
          $client = new Google_Client();
          $client->setApplicationName(APPLICATION_NAME);
          $client->setScopes(SCOPES);
          $client->setAuthConfig(CLIENT_SECRET_PATH);
          $client->setAccessToken(ACCESS_TOKEN);

          $service = new Google_Service_Sheets($client);

          $sheetInfo = $service->spreadsheets->get(SHEET_ID)->getProperties();

          print($sheetInfo[‘title’]. PHP_EOL);

          1. After Firing Command :-
            aministrator@Chrysalis-22:/opt/lampp/htdocs/formDataToGoogleSheet3$ php app.php

            Gives Error:-
            PHP Fatal error: Uncaught exception ‘Google_Service_Exception’ with message ‘{
            “error”: {
            “code”: 400,
            “message”: “Client project not found. Please pass a valid project.”,
            “errors”: [
            {
            “message”: “Client project not found. Please pass a valid project.”,
            “domain”: “global”,
            “reason”: “badRequest”
            }
            ],
            “status”: “INVALID_ARGUMENT”
            }
            }
            ‘ in /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Http/REST.php:118
            Stack trace:
            #0 /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Http/REST.php(94): Google_Http_REST::decodeHttpResponse(Object(GuzzleHttp\Psr7\Response), Object(GuzzleHttp\Psr7\Request), ‘Google_Service_…’)
            #1 [internal function]: Google_Http_REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Request), ‘Google_Service_…’)
            #2 /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Task/Runner.php(181): call_user_func_array(Array, Array)
            #3 /opt/lampp/htdocs/formDataToGoogleSh in /opt/lampp/htdocs/formDataToGoogleSheet3/vendor/google/apiclient/src/Google/Http/REST.php on line 118

        2. Thanks for sharing more context. Before digging much, I see your type of “OAuth 2.0 client ID” is “Web application”. So it won’t work from command line.

          You should create new Authentication keys for command line app. Have you followed the “Setting up Google Sheets API” of this post?

          BTW, I recommend sharing long debug text or error messages using external snippet. You may use pastiebin (for code), justpaste.it (for text) or even gist.

  2. yes, from this post only I have followed. I Have Created “Authentication keys for command line app” twice. anyway thanks for support.

Leave a Comment

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