Google API How to connect to receive values from spreadsheet

19,664

Solution 1

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!

Solution 2

This answer is meant to be an add-on to jrgd`s answer, and I post it here, as an answer, because it contains code.

The connection problems themselves hung me up. Here is what I had to do to resolve that: Trying to Get Google accessToken . Also, the spreadsheet has to be shared with the Google-generated e-mail that you find in your code.

Also, jrgd, to answer your question about that Request object: it doesn't exist with the Google Spreadsheet API; /* I couldn't find it when I used Composer to download the library */ Instead, I ended up doing what GingerDog did:

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

There is also a significant probability of the code throwing an Exception when using getSpreadsheets(), due to an HTTP error code being returned that is higher than 300 . The SpreadsheetService class has that method (here is its code: /** * Fetches a list of spreadhsheet spreadsheets from google drive. * * @return \Google\Spreadsheet\SpreadsheetFeed */ public function getSpreadsheets() { return new SpreadsheetFeed( ServiceRequestFactory::getInstance()->get('feeds/spreadsheets/private/full') ); } Notice that there is another class that is "doing the dirty work here": the DefaultServiceRequest class. Here is the get() that is being used:

/**
     * Perform a get request
     * 
     * @param string $url
     * 
     * @return string
     */
    public function get($url)
    {
        $ch = $this->initRequest($url);
        curl_setopt($ch, CURLOPT_CUSTOMREQUEST, 'GET');
        return $this->execute($ch);
    }
    /**
     * Executes the api request.
     * 
     * @return string the xml response
     *
     * @throws \Google\Spreadsheet\Exception If the was a problem with the request.
     *                                       Will throw an exception if the response
     *                                       code is 300 or greater
     *                                       
     * @throws \Google\Spreadsheet\UnauthorizedException
     */
    protected function execute($ch)
    {
        $ret = curl_exec($ch);

        $info = curl_getinfo($ch);
        $httpCode = (int)$info['http_code'];

        if($httpCode > 299) {
            if($httpCode === 401) {
                throw new UnauthorizedException('Access token is invalid', 401);
            } else {
                throw new Exception('Error in Google Request', $info['http_code']);
            }
        }

        return $ret;
    }

Notice that the function, from its innermost helper, has a chance to give back an http_code that will cause the code to throw an exception. Not good for business.

The Solution

The way I remedied that is to change the following line of code: $spreadsheetFeed = $spreadsheetService->getSpreadsheets();

to this while-loop:

/* my way of "making" it work; // I just getSpreadsheets() until there stops being an exception thrown */
    $googleException = new Exception();
    while ($googleException != null)
    {
        try
        {
            $spreadsheetFeed = $spreadsheetService->getSpreadsheets();  # This line randomly throws exception, for some reason. 
            $googleException = null;
        }
        catch (Exception $e)
        {
            $googleException = $e;
        }
    }
    //var_dump($spreadsheetFeed->getArrayCopy());   // test line

Solution 3

I have written a wrapper class for the Google Sheets authentication and sheet cell editing in my recent project. Tested working as of 02 Sep 2015 so it's very up-to-date!

Prerequisites:

To use the wrapper class:

// Initialise Google Sheets instance
$sheets = new GoogleSheets();

$sheets->clientID      = 'YOUR CLIENT ID FROM GOOGLE DEV CONSOLE';
$sheets->clientEmail   = 'YOUR CLIENT EMAIL FROM GOOGLE DEV CONSOLE';
$sheets->clientKeyPath = 'PATH TO THE P12 FILE YOU DOWNLOADED FROM GOOGLE DEV CONSOLE';
$sheets->clientKeyPw   = 'IT IS USUALLY notasecret';

$sheets->appName          = 'WHATEVER NAME YOU WANT';
$sheets->spreadsheetTitle = 'TITLE FOR THE SPREADSHEET YOU WANT TO EDIT';
$sheets->worksheetTitle   = 'WORKSHEET TITLE IN THAT SPREADSHEET';

// Authenticate with Google
$sheets->authenticate();

// Now update the specific row cell
$sheets->updateListEntry(ROW_HEADER, $submissionID, CELL_HEADER, CELL_VALUE);
echo "updated!";

And here goes the wrapper class - feel free to modify it - most of the code is pretty much standard, boilerplate code. Tested working!

// Autoload classes thanks to Composer
require_once('vendor/autoload.php');

class GoogleSheets {
  // Instance variables
  public $clientID, $clientEmail, $clientKeyPath, $clientKeyPw, $appName, $spreadsheetTitle, $worksheetTitle;
  private $spreadsheetFeed;

  // connect to Google using OAuth2, boilerplate code...
  public function authenticate() {
    $obj_client_auth  = new Google_Client ();
    $obj_client_auth -> setApplicationName ($this->appName);
    $obj_client_auth -> setClientId ($this->clientID);
    $obj_client_auth -> setAssertionCredentials (new Google_Auth_AssertionCredentials (
        $this->clientEmail, 
        array('https://spreadsheets.google.com/feeds','https://docs.google.com/feeds'), 
        file_get_contents ($this->clientKeyPath), 
        $this->clientKeyPw
    ));
    $obj_client_auth -> getAuth () -> refreshTokenWithAssertion ();
    $obj_token  = json_decode ($obj_client_auth -> getAccessToken ());
    $accessToken = $obj_token->access_token;

    $serviceRequest = new Google\Spreadsheet\DefaultServiceRequest($accessToken);
    Google\Spreadsheet\ServiceRequestFactory::setInstance($serviceRequest);
    $spreadsheetService = new Google\Spreadsheet\SpreadsheetService();
    $this->spreadsheetFeed = $spreadsheetService->getSpreadsheets();
}

// Find matching row with header $field and cell value $value, and update cell with header $cellHeader to $cellValue
public function updateListEntry($field, $value, $cellHeader, $cellValue) {
    // Get the required spreadsheet, then worksheet by title
    $spreadsheet = $this->spreadsheetFeed->getByTitle($this->spreadsheetTitle);
    $worksheetFeed = $spreadsheet->getWorksheets();
    $worksheet = $worksheetFeed->getByTitle($this->worksheetTitle);

    // sq stands for structured query
    $listFeed = $worksheet->getListFeed(array("sq" => $field . " = " . $value));
    $entries = $listFeed->getEntries();
    $listEntry = $entries[0];

    $values = $listEntry->getValues();
    $values[$cellHeader] = $cellValue;
    $listEntry->update($values);
}

}

Share:
19,664

Related videos on Youtube

user2029890
Author by

user2029890

Updated on September 23, 2022

Comments

  • user2029890
    user2029890 about 1 year

    I embarked on this project thinking it would be simple. Many hours later I'm realizing the Google API is a bit of a labyrinth with multiple APIs and libraries. I really need please clear directions on how to go about doing this.

    I created several Google Doc spreadsheets which I granted permission to other users to edit.

    All I need is to programatically retrieve the info from these spreadsheets using PHP. However, I can't figure out how to connect to even start the retrieval.

    Here is what I did so far:

    1 - Installed the Google PHP API libraries.

    2 - Created a Google API project in the same account. I have no idea which API I need and which oAuth keys I need.

    3 - Installed the Google API Spreadsheet client from https://github.com/asimlqt/php-google-spreadsheet-client.

    Well, now what? How do I send the API command to retrieve the spreadsheet I want. I'm unsure how to authenticate and how to retrieve. So far, I tried the below using the API Server Key for Google Drive....this was just a guess. I copied and pasted the below from the example in the Google API Spreadsheet client:

    <?php
    require_once 'php-google-spreadsheet-client-master\src\Google\Spreadsheet\Autoloader.php';
    
    $accessToken = 'xxxxxxxxxxxxxxxxxxxxxxx';
    $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();
    ?>
    

    I receive the following error:

    Fatal error: Uncaught exception 'Exception' with message 'String could not be parsed as XML' in C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php:43 Stack trace: #0 C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php(43): SimpleXMLElement->__construct('') #1 C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetService.php(39): Google\Spreadsheet\SpreadsheetFeed->__construct(false) #2 C:\php\google_docd.php(11): Google\Spreadsheet\SpreadsheetService->getSpreadsheets() #3 {main} thrown in C:\php\php-google-spreadsheet-client-master\src\Google\Spreadsheet\SpreadsheetFeed.php on line 43  
    

    Please, please. Clear instructions. I'm a complete Google API newbie. thanks. Examples on how to test in SOAPUI or via bash would also be helpful as I can then use that to figure out how to issue Curl request. Many thanks!

  • David Goodwin
    David Goodwin about 9 years
    Remove : "$request = new Google\Spreadsheet\Request($accessToken);" Change: "...Google\Spreadsheet\DefaultServiceRequest(...)" to take $accessToken as it's parameter.
  • jrgd
    jrgd about 9 years
    hey @GingerDog thanks but not sure why i would do this nor what it should look like
  • David Goodwin
    David Goodwin about 9 years
    I tried using the code you provided (thank you!) and found I needed to change it slightly for it to work.
  • Mike Warren
    Mike Warren almost 9 years
    I am also getting the "String cannot be parsed as XML" error. What do I have to do to get access to the Spreadsheet here: docs.google.com/spreadsheets/d/… ?
  • Mike Warren
    Mike Warren almost 9 years
    @GingerDog What modifications did you end up doing?
  • jrgd
    jrgd almost 9 years
    @mike-warren did you try removing the spaces in your first row? i remember having some weird errors because of characters; might be worth trying to stick to basic ascii?
  • Mike Warren
    Mike Warren almost 9 years
    Yep, that is exactly what I did. I made sure to modify the array that was being insert()ed so that the keys were actually equal to the lowercase of the column name (the first row's value). I also made sure to share the spreadsheet with the Google e-mail address. Also, the code threw random exceptions, and I worked around that with a while-loop. I will post the while-loop in the answer section.
  • ZurabWeb
    ZurabWeb almost 9 years
    The getSpreadsheets() only returns spreadsheets shared explicitly with my service account. Can I get all spreadsheets owned my main account?
  • jrgd
    jrgd almost 9 years
    wow - it feels like i worked on this ages ago but it looks you nailed it; nice! thanks!
  • David Goodwin
    David Goodwin almost 9 years
    That's a horrible hack - I'd suggest you add in something to limit the number of retries you make.
  • David Goodwin
    David Goodwin almost 9 years
    My code is published on palepurple.co.uk/integrating-google-doc-spreadsheet-wordpres‌​s ... hope it's of some use.
  • Mike Warren
    Mike Warren almost 9 years
    @GingerDog How many retries would you set the maximum to? /* There is not a finite amount of requests per day on this, that I know of, unlike the Google Custom Search API that I am using */
  • David Goodwin
    David Goodwin almost 9 years
    @MikeWarren - five? I'm more against the idea of just blindly retrying. Your code could loop trying forever when it'll never succeed, and presumably you risk annoying the service provide.
  • jrgd
    jrgd over 8 years
    @gingerdog and mike: i got a report about the solution above being (possibly) broken with latest google spreadsheet update—what's your experience with this: did it break?
  • jrgd
    jrgd over 8 years
    @gingerdog , mike sincere apologies: all is fine with the script—the error was in between the chair and the keyboard
  • David Goodwin
    David Goodwin over 8 years
    @jrgd - no problem ... ( I have a site using the code, which still works ... ) See palepurple.co.uk/integrating-google-doc-spreadsheet-wordpres‌​s for some example code etc.
  • Laci
    Laci over 8 years
    I would still get Fatal error: Uncaught exception 'Exception' with message 'String could not be parsed as XML' . The code helped me though to obtain the access token. That is fine. However none of the answers or comments (provided links) helped with the error @TheGingerDog . Any developements?
  • David Goodwin
    David Goodwin over 8 years
    @Laci - suggest you var_dump($string) and see what's going on ... presumably you've not got an XML response somewhere.
  • jrgd
    jrgd about 8 years
    looks neat; as i understand it: it updates cell by cell; so if i want to push a whole row, i iterate on $sheets->updateListEntry(ROW_HEADER, $submissionID, CELL_HEADER, CELL_VALUE); —correct?
  • w.stoettinger
    w.stoettinger about 8 years
    Unfortunately the provided by @asimlqt is a pain in the ass to use!! why have all these interfaces and Factories when you then only implement the most basic functionality??? This looks like an engineering blueprint overkill with very little useful functionality. (no, update functionality for example like in the ZEND framework)
  • w.stoettinger
    w.stoettinger about 8 years
    plus he doesn't use the Google_Http_Request classes which are intended by the API ... this is the reason why it won't work on Google App Engine ... too bad, i'm desperately waiting for some improvements here!
  • jrgd
    jrgd over 6 years
    @MArgusChopinGyver looks like Google updated the library to v3 and as usual it's not backward compatible—not sure what the state of play will be with the solution above. This is what appears of the GitHub page: Note: This library has been updated to v3 and it is not backwards compatible with v2 so please test/update your code appropriately before upgrading.