Home » Google, PHP15 August 2015

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

Google Developer Console

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

Google Developer Console Service Account

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

Google Developer Console P12 Key

– 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:

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:

Hope this helps. Thanks.

PHP

Get New Post by Email

Find me on

FacebookTwitterGoogle+LinkedInRSS Feed
  • David Blanchet

    Thank you for this great post ;) !!!
    I was struggling a lot with other tutorials but this one is the most complete !
    Also do not forget to mention that you need to share you spreadsheet with CLIENT_EMAIL !

  • MULLAINATHAN M

    I got following error,
    PHP Fatal error: Uncaught exception ‘Google_Auth_Exception’ with message ‘Error refreshing the OAuth2 token, message: ‘{n “error” : “invalid_grant”n}”…
    How to resolve this. Please help.
    Thanks in advance.

  • Romel

    1 missing line in the inserting row entries

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

    it should be

    $row = array(‘name’=>’John’, ‘age’=>25);

    $listFeed = $worksheet->getListFeed();
    $listFeed->insert($row)

  • MULLAINATHAN M

    Hi Thanks for the post. Is it possible to insert multiple rows.? Please me.

  • anu

    Call to undefined method Google_Client::setAssertionCredentials() error occur . how can i overcome this ?

  • Script hangs on the: $spreadsheetFeed = $spreadsheetService->getSpreadsheets(); line. Has someone a fix for this or knows what the issue is?

    My goal is to use Google Spreadsheet as a inventory database.
    So if the pc is not in the inventory then add it. But if the entry is found update the info.
    Adding all info to a spreadsheet with forms gives as result that te spreadsheet to slow to work with. So I must rebuild it that it updates its own record and only add an new record when it is a new pc.

    If there are better ways, ideas are always welcome.

  • Ashish Patil

    HI Mukesh I am using your above script but got an ” Fatal error: Class ‘Google_Auth_AssertionCredentials’ not found ”

    Can you help me in to solve this fatal error?

  • Alastair

    Google_Auth_AssertionCredentials has been depricated in v2 of the google-api-php-client. Follow the link on the github page to v1 and use this library instead and everything should work nicely!

  • Sunil Parmar

    hi

    I want to create spreadSheet File and write into that in google drive.

    i have done same things as describe in your code snippet but it gives me the following error.

    Can you please help me.

    it occurs at :- $spreadsheetFeed = $spreadsheetService->getSpreadsheetFeed();

    Fatal error: Uncaught exception ‘GoogleSpreadsheetExceptionUnauthorizedException’ with message ‘Access token is invalid’
    google-api-php-client/src/Google/Spreadsheet/DefaultServiceRequest.php on line 351

  • Indresh Tayal

    Hi

    I am following your post for creating google spreadsheet.In between i am getting this error can you please solve my this problem

    it is showing this error
    “Uncaught exception ‘Exception’ with message ‘String could not be parsed as XML’ in C:wamp64wwwgoogleeightphp-google-spreadsheet-clientsrcGoogleSpreadsheetSpreadsheetFeed.php on line 45”

    Thanks

  • babar

    where is the link for v1