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!

2 Comments

Leave a Comment

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