Google API How to connect to receive values from spreadsheet

by

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

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    = '[email protected]';
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!

More Related Contents:

  1. How to refresh token with Google API client?
  2. Service Applications and Google Analytics API V3: Server-to-server OAuth2 authentication?
  3. Token must be a short-lived token and in a reasonable timeframe
  4. Edit Google calendar events from Google service account: 403
  5. Google Calendar API v3 – authenticate with hardcoded credentials
  6. How can I access an object property named as a variable in php?
  7. Google API Client “refresh token must be passed in or set as part of setAccessToken”
  8. Automatically refresh token using google drive api with php script
  9. Gmail API: 400 bad request when trying to send an email (PHP code)
  10. How can i loop in ordered list tage with php while [closed]
  11. How do I check if a string contains a specific word?
  12. Simplest way to detect a mobile device in PHP
  13. php $_POST array empty upon form submission
  14. DOMDocument in php
  15. Are PHP Variables passed by value or by reference?
  16. Best solution to protect PHP code without encryption
  17. Check file extension in upload form in PHP [duplicate]
  18. php: loop through json array
  19. PHP Background Processes
  20. MVC (Laravel) where to add logic
  21. Force Browser to download Image with Javascript window.open?
  22. Ajax post request in laravel 5 return error 500 (Internal Server Error)
  23. Use one Laravel migrations table per database
  24. Codeigniter send email with attach file
  25. Validating US phone number with php/regex
  26. Composer cannot download files
  27. mysqli prepared statement num_rows returns 0 while query returns greater than 0 [duplicate]
  28. What unit testing in PHP to start [duplicate]
  29. Save array in mysql database
  30. Best way to internationalize simple PHP website

Leave a Comment