PHP: Google Spreadsheet with OAuth (Add, Edit, Delete, View data)

I was using ClientLogin method to access and update Google Spreadsheet. The ClientLogin method was in a deprecated state and now it has been turned off. We now need to use OAuth for authentication.

Google APIs Client Library for PHP enables you to work with Google APIs such as Google+, Drive, or YouTube on your server. In the example code below, I am using version 1.1.4 of this library.

There is a PHP library for accessing and manipulating Google Spreadsheets. It helps to create & delete spreadsheet and insert & update data. In the example code below, I am using version 2.3.5 of this library.

First of all, you need to generate new client ID from Google developer console.

– Go to Google Developer Console
– Go to API & auth -> Credentials

– Click Create new Client ID button
– A new popup will appear. Select Service Account and then click Create Client ID button

– Then generate new P12 key. The P12 key will be downloaded to your computer.

– Now, in google developer console, you will also see Client ID, Client Email, and Certificate fingerprints.

Here is the full code to OAuth authenticate and access and update Google Spreadsheet:

<?php

/**
 * Autoload files of https://github.com/google/google-api-php-client
 *
 */ 
require_once 'google-api-php-client/src/Google/autoload.php';


/**
 * If you install https://github.com/asimlqt/php-google-spreadsheet-client through composer
 * Then you can just do:
 * require 'vendor/autoload.php';
 *
 * If you just download the zip file of https://github.com/asimlqt/php-google-spreadsheet-client
 * Then, you need to load the following files:
 *
 */
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/ServiceRequestInterface.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/DefaultServiceRequest.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/Exception.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/UnauthorizedException.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/ServiceRequestFactory.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/SpreadsheetService.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/SpreadsheetFeed.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/Spreadsheet.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/WorksheetFeed.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/Worksheet.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/ListFeed.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/ListEntry.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/CellFeed.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/CellEntry.php';
require_once 'php-google-spreadsheet-client/src/Google/Spreadsheet/Util.php';


/**
 * AUTHENTICATE
 *
 */
// These settings are found on google developer console
const CLIENT_APP_NAME = 'Whatever name you want';
const CLIENT_ID       = 'YOUR-CLIENT-ID.apps.googleusercontent.com';
const CLIENT_EMAIL    = 'YOUR-CLIENT-EMAIL@developer.gserviceaccount.com';
const CLIENT_KEY_PATH = 'PATH_TO_KEY/YOUR-KEY-NAME.p12'; // PATH_TO_KEY = where you keep your key file
const CLIENT_KEY_PW   = 'notasecret';

$objClientAuth  = new Google_Client ();
$objClientAuth -> setApplicationName (CLIENT_APP_NAME);
$objClientAuth -> setClientId (CLIENT_ID);
$objClientAuth -> setAssertionCredentials (new Google_Auth_AssertionCredentials (
    CLIENT_EMAIL, 
    array('https://spreadsheets.google.com/feeds','https://docs.google.com/feeds'), 
    file_get_contents (CLIENT_KEY_PATH), 
    CLIENT_KEY_PW
));
$objClientAuth->getAuth()->refreshTokenWithAssertion();
$objToken  = json_decode($objClientAuth->getAccessToken());
$accessToken = $objToken->access_token;


/**
 * Initialize the service request factory
 */ 
use Google\Spreadsheet\DefaultServiceRequest;
use Google\Spreadsheet\ServiceRequestFactory;

$serviceRequest = new DefaultServiceRequest($accessToken);
ServiceRequestFactory::setInstance($serviceRequest);


/**
 * Get spreadsheet by title
 */
$spreadsheetTitle = 'YOUR-SPREADSHEET-TITLE';
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle($spreadsheetTitle);


/**
 * Add new worksheet to the spreadsheet
 */
$worksheetTitle = 'New Worksheet';
$spreadsheet->addWorksheet($worksheetTitle, 50, 20); // 50 rows & 20 columns


/**
 * Get particular worksheet of the selected spreadsheet
 */
$worksheetTitle = 'YOUR-WORKSHEET-TITLE'; // it's generally named 'Sheet1' 
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle($worksheetTitle);


/**
 * Delete a particular worksheet from the spreadsheet
 * I have commented out the worksheet delete code 
 * but it works well
 */
//$worksheetTitle = 'YOUR-WORKSHEET-TITLE'; 
//$worksheet = $worksheetFeed->getByTitle($worksheetTitle);
//$worksheet->delete();


/** 
 * Add/update headers of worksheet
 */
$cellFeed = $worksheet->getCellFeed();
$cellFeed->editCell(1,3, "name"); // 1st row, 3rd column
$cellFeed->editCell(1,4, "age"); // 1st row, 4th column


/** 
 * Insert row entries
 * Supposing, that there are two headers 'name' and 'age'
 */
$row = array('name'=>'John', 'age'=>25);
$listFeed->insert($row);


/**
 * Get row lists of worksheet
 */
$listFeed = $worksheet->getListFeed();


/**
 * Print row lists
 */
foreach ($listFeed->getEntries() as $entries) {
    print_r($entries->getValues()); 
}


/**
 * Update row entries
 * Supposing, that there are two headers 'name' and 'age'
 */
$entries = $listFeed->getEntries();
$listEntry = $entries[0]; // 0 = 1st row (editing value of first row)
$values = $listEntry->getValues(); 
$values['name'] = 'Bob';
$values['age'] = '45';
$listEntry->update($values);

?>

Note: Currently, Google Spreadsheet doesn’t support headers with capital letter and spaces. You can see that when you use $listFeed to print the entries (see above code to listFeed).

– Capital letters are converted to lowercase.
– Spaces are removed.
– Special characters like comma, slash, etc. are removed.
– Hyphens are not removed.

– First Name becomes firstname
– Age becomes age
– New,header becomes newheader
– dd/mm/yyyy becomes ddmmyyyy
– TEST-This becomes test-this

See the insert entries example in the above code. If your header is First Name and Date (dd/mm/yyyy) then your array data should be as under:

$row = array('firstname'=>'Tom', 'dateddmmyyyy'=>'14/08/2015');
$listFeed->insert($row);

Hope this helps. Thanks.