Google API How to connect to receive values from spreadsheet

google-api, google-sheets, php

Solution

I finally got it working, despite all attempts from the documents to make it look like impossible, here is my setup:

- google api for OAuth2 php lib; here: https://code.google.com/p/google-api-php-client/

- google spreadsheet api php lib; here: https://github.com/asimlqt/php-google-spreadsheet-client

You will need to create the credentials on the API Console: https://console.developers.google.com/ There you will need to first create a project and create set of authentified information for your app: in the left menu, click on API & Auth, then Credentials. Click on Create New Client ID (red button), then select Service Account. You will download a file, keep it safe. you will upload it with your script.

Also, please note: it never worked unless: my document was the 'old spreadsheet'. I also needed to share the spreadsheet document with the user ID (the one that could be an email) generated on Google's console. The document contains a top row that is frozen with the appropriate columns name (name, age).

Here is the php script I used in combination with the above:

<?php
require_once 'php-google-spreadsheet/src/Google/Spreadsheet/Autoloader.php';
require_once 'google-api-php-client/src/Google_Client.php';

const G_CLIENT_ID       = 'fill_with_info_from_console.apps.googleusercontent.com';
const G_CLIENT_EMAIL    = 'fill_with_info_from_console@developer.gserviceaccount.com';
const G_CLIENT_KEY_PATH = 'key/keep_the_complex_filename_here_privatekey.p12';
const G_CLIENT_KEY_PW   = 'notasecret';

$obj_client_auth  = new Google_Client ();
$obj_client_auth -> setApplicationName ('test_or_whatever_you_like');
$obj_client_auth -> setClientId (G_CLIENT_ID);
$obj_client_auth -> setAssertionCredentials (new Google_AssertionCredentials (
    G_CLIENT_EMAIL, 
    array('https://spreadsheets.google.com/feeds','https://docs.google.com/feeds'), 
    file_get_contents (G_CLIENT_KEY_PATH), 
    G_CLIENT_KEY_PW
));
$obj_client_auth -> getAuth () -> refreshTokenWithAssertion ();
$obj_token  = json_decode ($obj_client_auth -> getAccessToken ());
$accessToken = $obj_token->access_token;

$request = new Google\Spreadsheet\Request($accessToken);
$serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($request);
Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
$spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
$spreadsheetFeed = $spreadsheetService->getSpreadsheets();
$spreadsheet = $spreadsheetFeed->getByTitle('title_of_the_spreadsheet_doc');
$worksheetFeed = $spreadsheet->getWorksheets();
$worksheet = $worksheetFeed->getByTitle('title_of_the_tab');
$listFeed = $worksheet->getListFeed();

// this bit below will create a new row, only if you have a frozen first row adequatly labelled

$row = array('name'=>'John', 'age'=>25);
$listFeed->insert($row);

?>

I should also note:

All this is still very much work in progress, but hopefully will help someone write amazing instructions for anyone to better understand the nitty gritty

It's a compilation of bits and pieces from both the google documentations, some answers on stackoverflow and info from the 2 api libraries

It has been an awfully painful to get this working and it really shouldn't; i'm assuming this is because google is transitioning authentification, console interface and api versions all at the same time.

EDIT: It seems the columns names in the google doc are restricted: no space allowed(?), no underscore allowed (?), CamelCase seems troublesome. I only managed to get the dashes to work, like in "placement-other", otherwise the api throws some "Uncaught exception"

EDIT: I used the exact same setup for a new project and still does work, with the new Spreadsheet model introduced by Google recently. What hold me that i had forgotten: no blank cell allowed, headers must be frozen without spaces, and they are lowercase when queried from PHP.

Hope this helps!

Problem

Original source