Google Sheets API access with PHP

api-test

Note on 8 February 2017: Google has released the Sheets API v4 and is now providing PHP sample code and improved PHP support for Sheets in the PHP API client.  My series of articles is now largely obsolete.  See https://developers.google.com/sheets/api/quickstart/php

==========================

Google has been sending out messages saying that several of their APIs are going to be discontinued (for example, the Document List API).  As a result, I tried to modify my custom Google Spreadsheet-populating PHP script to use the new Drive API.  After way too much wasted time, I discovered that the Drive API can create sheets and read their metadata, but can’t add rows — for that we can keep using the Spreadsheets API.

I was successful at converting the authentication portion of my script from the old ClientLogin to OAuth with a Service Account (the script runs behind the scenes of a web site and populates a Google spreadsheet that the web site user has no knowledge of).

It wasn’t as simple as I’d hoped because the Google-provided PHP client for the Drive API doesn’t know about the Spreadsheets API.  I had to dig around the PHP libraries to piece together authentication codes and Google_Http_Requests.  Then Google changed the PHP client to use Guzzle, so I recently made that change.

So, if anyone else out there is interested in the combination of PHP, the Spreadsheets API, and OAuth with Service Accounts, this could save you some time.

The code is available in a public Gist at https://gist.github.com/karlkranich/de225928665dc6b83667

Here are the step-by-step instructions to get this to work:

Create the Service Account

  1. Browse to https://console.developers.google.com
  2. Click “Create Project”, make up a name, and click “Create”
  3. Click “APIs” in the left column, then click “Drive API” and then “Enable API”. This is not needed for the Spreadsheets API, but you might want to use the Drive API also.
  4. Click “Credentials” in the left column.
  5. Click “Add credentials -> Service account
  6. Leave JSON selected and click “Create”
  7. Save the generated key file in your project directory as service-account-credentials.json
  8. Note the email address of the service account

Create a Spreadsheet and share with the Service Account

  1. Create a Google Spreadsheet to play with
  2. Share the Google sheet with the email address from above (give edit permissions).

Get the PHP API Client

  1. Create a directory on your machine for your project
  2. git clone https://github.com/google/google-api-php-client.git google-api-php-client

Install the API Client Dependencies with Composer

Follow instructions in the downloaded README.md to install the client’s dependencies.  On my Mac, installing Composer was as simple as ‘curl -sS https://getcomposer.org/installer | php’ and then ‘sudo mv composer.phar /usr/local/bin/composer’.  See https://getcomposer.org/

Then I ran ‘composer require google/apiclient:^2.0’ as specified in README.md to install the dependencies.

Modify and run apitest.php

  1. Get the script from https://gist.github.com/karlkranich/de225928665dc6b83667
  2. Fill in the File ID of your spreadsheet from the URL in Chrome (see image below)
  3. Uncomment various sections of the script to see how they work

To learn more about the Guzzle PHP HTTP client used in the client, click here.

If you want to do more with the Sheets API or run into cURL error 60, please check out my other Google Apps posts.

Let me know if any of this doesn’t work or doesn’t make sense!

file-id

269 thoughts on “Google Sheets API access with PHP

  1. lab

    Hi.

    Code returned from below code is 400 but should be 200, is there any specific reason for this?

    $request = $this->client->getIo()->makeRequest($http);
    $code = $request->getResponseHttpCode();
    [src/Google/Auth/OAuth2.php]

    Regards,
    Lab

    Reply
    1. karl.kranich Post author

      I don’t know, but you should be able to get more detail about the error with something like json_decode($response->getResponseBody(), true);

      Karl

      Reply
  2. Cyril

    Hi,

    I don’t understand, I try to add a row, and I have : “400 Error: Blank rows cannot be written; use delete instead.”

    Any idea ?

    Thank you,

    Reply
    1. karl.kranich Post author

      Cyril,
      Sorry for my original reply — I was mixing up some stuff. I have read that you will get that error if your column names don’t match what Google expects. You can’t just use the column names that you see in your spreadsheet. First try changing them to all lowercase. If that doesn’t work, do a GET of the list feed and see what Google expects for column names.

      Hope this helps.
      Karl

      Reply
  3. Anand Chandole

    Hi

    above article is very useful and worked perfectly. Now I want to update / delete particular row from google spreadsheet. can you help me for this?

    Reply
  4. Rohan

    Hi,

    I am trying to connect with my file while using this piece of code but I am getting “200” error on blank page. I have a spreadsheet in the drive which is shared with the service account’s email address. Can you please suggest if I am doing anything wrong with the code or configuration.

    Thanks

    Reply
    1. karl.kranich Post author

      Hello,
      A status code of 200 usually indicates success. Are you trying to read or write? If you’re reading, maybe the response body actually has the data you’re looking for.

      Karl

      Reply
      1. Rohan

        Thanks for your response Karl.

        I am trying to update a sheet with the entry of a new row each time the script is called and when using this code it shows 200 on screen but the sheet is still blank and its not updated at all.

        Do you think there is any error or miss-configuration I did?

        Thanks

        Reply
  5. Anand Chandole

    Hi

    I checked with HTTP example. Now I want to update particular row from google spreadsheet. I used quert string to get that particular row but not rowId and etag. How I can get that rowId and etag to update particular row. Can you please help me.

    Reply
    1. karl.kranich Post author

      Anand,
      I just learned that we need to add a GData-Version header value to the GET query to get the etags. In my PHP, it looks like this:
      $headers = [“Authorization” => “Bearer $accessToken”, “GData-Version” => “3.0”];

      I was already seeing the id values before, and now I also see the etags.

      Karl

      Reply
  6. karl.kranich Post author

    Everyone,
    My example uses list-based feeds for manipulating Google sheets. I just learned that these require that the first row of the spreadsheet is column headers (I just lucked out in that mine were always like that).

    Also, when you create the XML to write data to a sheet, the column headers need to be a lowercase. If you aren’t sure what to use for column headers in the XML, do a GET request for the list feed and find the column headers in the output.

    Karl

    Reply
  7. Anand Chandole

    Hi

    Thanks yes I got etag by passing GData-Version header information but now I am getting one error while executing update request
    “The prefix “gd” for attribute “gd:etag” associated with an element type “entry” is not bound.”

    below is the xml which I passing to execute, can you please check and let me know what is the issue.

    $postBody = ‘
    https://spreadsheets.google.com/feeds/list/key/1OQwhvtDK9vUyPBDgw-BOA9cOzjFA2wYLT7Sa7Da8XLo/private/full/cssly
    2015-07-15T18:04:57.859Z

    Bingley
    email: nilesh.k@edreamz.in, financialstatus: authorized, acceptsmarketing: 1

    Bingley
    20
    4
    ‘;

    Reply
  8. Ayyaz

    Thanks a lot. It worked like charm!

    But I have a question. Your script inserts in only first worksheet. But How to insert in specific worksheet ?

    Reply
  9. stark

    Karl , you have an excellent article , everything works , that’s just my problem – I do not know how to get such a particular file on a particular piece of the value of specific rows … tell me how to do it , and I completely confused. Thanks for the early.

    Reply
    1. karl.kranich Post author

      Stark,
      Tell me more about what you want to do. Do you want to read a row from a spreadsheet? Or edit a row?

      Karl

      Reply
  10. Kamil

    I’m getting this error every second request:

    Fatal error: Uncaught exception 'Google_IO_Exception' with message 'Operation timed out after 100000 milliseconds with 0 bytes received' in /xxx/google-api-php-client/src/Google/IO/Curl.php:115 Stack trace: #0 /xxx/sheets-api-test.php(81): Google_IO_Curl->executeRequest(Object(Google_Http_Request)) #1 {main} thrown in /xxx/google-api-php-client/src/Google/IO/Curl.php on line 115

    Line 81 is: $results = $curl->executeRequest($req);

    Any idea?

    Thanks,
    Kamil

    Reply
    1. karl.kranich Post author

      Sorry, Kamil, I don’t know.

      Since it’s timing out, I wonder if it could be problems with your network connection. But if you’re not having other problems, perhaps it’s some kind of an API limit that your running into. I don’t know what kinds of limits Google has.

      Karl

      Reply
  11. Ronnel Lao

    For those people who had a “400 Error: Blank rows cannot be written; use delete instead.”

    Follow the format on the post body, First you must have column fields on your worksheet not empty.

    Where “gear” is the column name. (if you have spaces on your column name eliminate it on the tag)
    Between tag is the data you want to insert.
    Then Close the tag.

    Example:

    $postBody = '
    Gear Max
    Juan Dela Cruz
    Quezon City, Philippines
    ';

    Hope that helps.

    Reply
  12. Ravindra

    i got spreadsheet data but i won’t to add form data in spreadsheet
    pls give me code to add data in spreadsheet

    Reply
    1. karl.kranich Post author

      Ravindra,
      I have provided example code in the github gist. Section 4 will add a row to a spreadsheet after you’ve figured out the authentication and the file id of the file that you want to edit.

      Karl

      Reply
  13. Pingback: Sheets API access with PHP part 2: Editing Rows - Karl's Place

  14. Spurgeon

    Hi Karl,

    Thanks for this great source. It looks very promising and simple to interact with google.

    I tried using this, but am getting a error –
    Fatal error: Uncaught exception ‘Google_Auth_Exception’ with message ‘Error refreshing the OAuth2 token, message: ‘{ “error” : “invalid_grant” }” in
    /var/www/contents/lib/google-api-php-client/src/Google/Auth/OAuth2.php:363 Stack trace:
    #0 /var/www/contents/lib/google-api-php-client/src/Google/Auth/OAuth2.php(314): Google_Auth_OAuth2->refreshTokenRequest(Array)
    #1 /var/www/contents/forms/signup/workhandler2.php(65): Google_Auth_OAuth2->refreshTokenWithAssertion(Object(Google_Auth_AssertionCredentials))
    #2 {main} thrown in /var/www/contents/lib/google-api-php-client/src/Google/Auth/OAuth2.php on line 363

    Can you pl help in getting past this?

    Thanks!
    warm regards and gratitude,
    Spurgeon

    Reply
    1. karl.kranich Post author

      Spurgeon,
      I don’t know how to troubleshoot oauth issues, but you are the second one to report this error so I’ll try to look into it.

      Karl

      Reply
      1. Spurgeon

        Hi Karl,

        Greetings!

        Thanks a lot for your kind and quick response. Have been searching on this on the net thru out the day and realise it is got to do something with recent changes at google side.

        We are trying to setup up a handler to receive data and store it in google sheets for our ministry campaign.

        Kindly see, if you can help, when things permit you.
        Sincere, warm regards and gratitude,
        Spurgeon

        Reply
  15. Spurgeon

    Hi Karl,

    Praise God! Got past the issue.

    The bug was an extra space ‘ ‘ within the clientid and email literal strings.

    Exploring the wonderful code now.

    Thanks a ton!

    Reply
  16. Yang

    In your Create the Service Account section, the fifth step, OAuth section, do you mean “OAuth consent screen” ? I could not find where to click “Create new Client ID”.
    Any suggestions?

    Thanks

    Reply
    1. karl.kranich Post author

      Thanks for pointing that out. It looks like Google has changed the way the Developer Console is organized since I wrote the instructions. I’m walking through the steps and updating the post right now. Check back in an hour or so.

      Karl

      Reply
  17. Yang

    Thank you so much for your quick reply. The final step of the section Create the Service Account, client id is the same as “certificate fingerprints”?

    Reply
    1. karl.kranich Post author

      No — if you click on the email address on that screen, you’ll get to a detail page that shows the client ID.

      Reply
  18. yang

    Hi karl,

    In section 3, I could get the whole data from a spread sheet. What should I do if I only want data from a particular row ?

    Thanks

    Yang

    Reply
  19. Yang

    Morning Karl,

    Currently I am using the following url to fetch the whole file content,
    $url = “https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full”;
    And it works.

    I would like to know if there is any way to fetch the first cell of the first row in the sheet, I do not know why in my sheet, the cells of the first row will be treated as column name.

    Reply
    1. karl.kranich Post author

      I mention in the second part of this post at http://karl.kranich.org/2015/08/21/sheets-api-access-with-php-part-2-editing-rows/ that this method of accessing Google Spreadsheets assumes that the first row contains column headings. If you don’t want them to be treated as column headings, you have to use the cell-based method of accessing the sheet, not the list-based method. I have never done it, but it’s described here: https://developers.google.com/google-apps/spreadsheets/data

      Karl

      Reply
  20. yang

    In the reference menu, they provide an example of how to fetch specific rows or columns:
    GET https://spreadsheets.google.com/feeds/cells/key/worksheetId/private/full?min-row=2&min-col=4&max-col=4

    Could you please tell me what is the worksheetId? Is it the same as fileId in your script? because I tried to modify the url like this, and it does not workL
    $url = “https://spreadsheets.google.com/feeds/cells/key/$fileId/private/full?min-row=2&min-col=4&max-col=4”

    Thanks

    Reply
  21. yang

    Hi Karl,

    Thank you so much for your great help, I have another question. If multiple sheets share a same fileID, what should I do to specify one of them? They have different gid.

    Such as :
    https://docs.google.com/spreadsheets/d/1jz5Q3W3AcKn39xXAFehjH37Z1jTJTTgSeuZoM15IB-4/edit#gid=0
    https://docs.google.com/spreadsheets/d/1jz5Q3W3AcKn39xXAFehjH37Z1jTJTTgSeuZoM15IB-4/edit#gid=975383788
    https://docs.google.com/spreadsheets/d/1jz5Q3W3AcKn39xXAFehjH37Z1jTJTTgSeuZoM15IB-4/edit#gid=622735183

    Reply
  22. Hardy

    Hi, Have Implemented Code as you Instructed. Have granted Permissions as mentioned here.
    But having Issues with Authentication – getting 401 Unauthorized.
    I Hope you will help me soon.

    Regards,
    Hardy

    Reply
    1. karl.kranich Post author

      Hardy,
      Be sure that you’re connecting to https (secure) URLs. Which section of the code is breaking?

      Karl

      Reply
      1. Hardy

        Thanks for your Quick Reply.
        Have Solved that Issue. Maybe i was gone wrong with the Permissions, checked with another account. Working Well. 🙂
        Have a Great Day ahead.
        And You have Done a Great Job. It Helped me a lot.

        Reply
  23. prajakta

    Hi,
    I am getting following output.
    Service Account Access
    302
    Moved Temporarily
    The document has moved here.

    What’s wrong ?, pls help

    Reply
  24. Sofia

    Just wanted to say thank you, this works perfectly! No more headaches with google api’s confusing websites, woohoo| \o/

    Reply
  25. Andrew

    This is brilliant, thank you for sharing it.

    One tricky step I wanted to point out, in case anyone else has this issue:
    The PHP files were failing on me because of the headers array:
    >>>>> $headers = [“Authorization” => “Bearer $accessToken”];

    Probably because I have an older version of PHP??
    So I switched them to:
    >>>>> $headers = array(“Authorization” => “Bearer $accessToken”);

    Also, I like to GET the table data as JSON, so I switched this:
    >>>>> $url = “https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full”;
    to this:
    >>>>> $url = “https://spreadsheets.google.com/feeds/list/$fileId/od6/private/values?alt=json”;

    And this:
    >>>>> echo “$results[2]\n\n”;
    >>>>> echo “$results[0]\n”;
    to this:
    >>>>> echo $results[0];

    Just sharing for my fellow PHP lightweights…

    Reply
    1. karl.kranich Post author

      Thanks for the feedback and tips, Andrew! I’ve been wanting to look into JSON handling of this stuff.

      Karl

      Reply
  26. Andrew

    No problem. If it helps, I have this JS code that translates the garbly JSON that comes out:

    var tempdata = incoming_data.feed.entry;
    var results_array = []
    for (var i=0; i<tempdata.length; i++) {
    var tempObj = {};
    $.each(tempdata[i], function(key,val) {
    if(key.indexOf("gsx$") != -1){
    tempObj[key.slice(4)] = val["$t"];
    }
    });
    results_array.push(tempObj);
    }

    Reply
  27. Avilio

    Hi Karl, thank you for the useful information.

    I have this problem: /google-api-php-client/src/Google/autoload.php
    IS MISSING

    How can I solve it?

    thank you in advance

    Reply
    1. karl.kranich Post author

      It looks like Google has changed the API client since I wrote this. I will try to figure out how to get my code to work as soon as possible, but I’m not sure when I’ll have time.

      Reply
    2. karl.kranich Post author

      I just updated the instructions for the new installation method and HTTP library. Google made major changes to the PHP API Client!

      Reply
  28. Avilio

    Thank you Sofia, it worked with composer.

    I have another question, Does anyone know how to insert a row just before the header? (not at the bottom)

    Thank you in advance

    Reply
    1. karl.kranich Post author

      The way I understand it, this list-based feed method can only add rows to the end and modify rows below the header. You might be able to do it with the cell-based feed method, but I’m not sure (see https://developers.google.com/google-apps/spreadsheets/data). You could surely do it with a Google apps script that gets called by your script. It’s a new capability that I haven’t explored yet. See http://googleappsdeveloper.blogspot.com/2015/09/run-apps-script-code-from-anywhere.html

      Reply
  29. Ishan Girdhar

    Hi Karl,

    Thanks for your efforts and contributing your code. However, I am having an issue in running this code.

    I could be a lame because I haven’t seen anyone else having this same issue so far.

    I am trying to run this apitest.php from within the XAMPP setup. I have changed the path of autoload.php (which was in /src/Google/autoload.php) and nowhere I have seen where you have given the path for service_account.json file. I have created a similar spreadsheet file, updated the ID, Shared the sheet, service account email id.

    but still, it isn’t working, I am getting the following errors while running apitest.php in browser.

    Warning: include_once(google-api-php-client/src/Google/autoload.php): failed to open stream: No such file or directory in /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/apitest.php on line 20

    Warning: include_once(): Failed opening ‘google-api-php-client/src/Google/autoload.php’ for inclusion (include_path=’.:/Applications/XAMPP/xamppfiles/lib/php’) in /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/apitest.php on line 20

    Warning: include_once(google-api-php-client/examples/templates/base.php): failed to open stream: No such file or directory in /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/apitest.php on line 21

    Warning: include_once(): Failed opening ‘google-api-php-client/examples/templates/base.php’ for inclusion (include_path=’.:/Applications/XAMPP/xamppfiles/lib/php’) in /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/apitest.php on line 21

    Fatal error: Class ‘Google_Client’ not found in /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/apitest.php on line 28

    Can you suggest me what I am doing wrong?

    I’d really appreciate your help.

    Thank you!


    Ishan

    Reply
    1. karl.kranich Post author

      Ishan,
      Have you tried my new instructions and used Composer to install the dependencies and create the autoload file? If not, give that a try. I updated the instructions just a few days ago.

      If you’re using Composer and the new instructions, let me know.
      Karl

      Reply
  30. Ishan Girdhar

    Hi Karl,

    Highly appreciate your immediate response. I have only downloaded and installed it using the curl command. and gave this message after installation:

    Composer successfully installed to: /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/composer.phar

    What I am not able to understand the part where you mentioned, use composer to install dependencies and create the autoload file.

    Only autoload file, I can see is in src/Google/autoload.php directory. Do I have to create a new autoload.php using composer. Can you share the link on where I can find it (or maybe understand why/how it works ?)


    Ishan

    Reply
  31. Ishan Girdhar

    Hi Karl,

    One more thing, ..and yes as per your instructions in blog, I moved to /usr/local/bin/composer too.

    After reading your comments, I run that command again and it create composer.phar in /Applications/XAMPP/xamppfiles/htdocs/google-api-php-client/composer.phar location also. So, now I have it in both location.


    Ishan

    Reply
  32. Ishan Girdhar

    Hi Karl,

    Its sorted. I run, composer install and I have vendor folder with autoload created in it. Another thing I am not able to understand it, you mentioned to update the path of service-account.json, but in your code, I don’t find any line where I need to update the path to service-account.json file, or any instructions on where should I save service-account.json in order for you script to read it .


    Ishan

    Reply
    1. karl.kranich Post author

      Sorry that I didn’t explain it clearly. When you are in the Google Developer Console and request credentials, you download a .json file. You can call it whatever you want, but I named it service-account.json when I downloaded it from the Developer Console, and then I had to tell my php script where I saved it.

      Reply
      1. Ishan Girdhar

        Hi Karl,

        yes, no your explanation was clear, what I want to understand which line in your script actually takes the path of service-account.json file as an input.

        is it line 44?
        line 44: putenv(‘GOOGLE_APPLICATION_CREDENTIALS=/Applications/XAMPP/htdocs/google-api-php-client/service-account.json’);

        or some other line.

        Reply
        1. karl.kranich Post author

          Yes, just line 44. I didn’t have a path because I put service-account.json in the same directory as my apitest.php file.

          Reply
  33. Igor

    Hi, any help about this

    Fatal error: Uncaught exception ‘InvalidArgumentException’ with message ‘json key is missing the type field’ in C:\xampp\htdocs\insur\vendor\google\auth\src\CredentialsLoader.php:125 Stack trace: #0 C:\xampp\htdocs\insur\vendor\google\auth\src\CredentialsLoader.php(79): Google\Auth\CredentialsLoader::makeCredentials(‘https://www.goo…’, Object(GuzzleHttp\Stream\Stream)) #1 C:\xampp\htdocs\insur\vendor\google\auth\src\ApplicationDefaultCredentials.php(94): Google\Auth\CredentialsLoader::fromEnv(‘https://www.goo…’) #2 C:\xampp\htdocs\insur\vendor\google\apiclient\src\Google\Client.php(1074): Google\Auth\ApplicationDefaultCredentials::getCredentials(‘https://www.goo…’) #3 C:\xampp\htdocs\insur\vendor\google\apiclient\src\Google\Client.php(344): Google_Client->createApplicationDefaultCredentials(Object(GuzzleHttp\Client)) #4 C:\xampp\htdocs\insur\vendor\google\apiclient\src\Google\Service\Resource.php(192): Google_Client->authorize(Object(GuzzleHttp\Client)) #5 C:\xampp\htdocs\insur\vendor\google\apiclient\src\Google\Ser in C:\xampp\htdocs\insur\vendor\google\auth\src\CredentialsLoader.php on line 125

    Reply
    1. karl.kranich Post author

      Igor,
      I haven’t seen that one. Maybe you downloaded a P12 file instead of a JSON file from the Google Developer Console? I would download the JSON authentication file again from Google.

      Karl

      Reply
    2. Ishan Girdhar

      Hi Igor,

      I think I got that error when I didn’t updated my service-account.json file path. If you have downloaded json file as Karl mentioned and if you are still getting this error message then you can make sure if you have updated the complete path of service-account.json in your examples/templates/base.php file on line 105.

      Reply
      1. karl.kranich Post author

        Thanks, Ishan. I didn’t have to edit base.php, but I’m glad that got it working for you! Somehow the putenv command in apitest.php is working for me without touching base.php.

        Reply
  34. Mark Hilton

    Hi Karl, variable $accessToken shows up in the code undeclared before. Where do I get this from? The code works for me except the Section 4 to put data into the spreadsheet.

    Reply
    1. karl.kranich Post author

      Hi Mark,
      I only see $accessToken in section 5 (edit an existing row), and it was left over from a previous version of the code and isn’t needed anymore. I just deleted it from the gist. If you just remove that from the headers, everything should work (assuming you’re working with the latest version of the PHP API client and my script).

      Karl

      Reply
  35. Graycie

    Hey Karl,

    You have solved a lot of headache. However I am receiving the following error when i try to execute section 1 getting the file from service using file id…any clues on what might be wrong?:

    Fatal error: Uncaught exception ‘GuzzleHttp\Ring\Exception\RingException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate’ in C:\xampp\htdocs\spreadsheetapi\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlFactory.php:127 Stack trace: #0 C:\xampp\htdocs\spreadsheetapi\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlFactory.php(91): GuzzleHttp\Ring\Client\CurlFactory::createErrorResponse(Array, Array, Array) #1 C:\xampp\htdocs\spreadsheetapi\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlHandler.php(96): GuzzleHttp\Ring\Client\CurlFactory::createResponse(Array, Array, Array, Array, Resource id #94) #2 C:\xampp\htdocs\spreadsheetapi\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlHandler.php(68): GuzzleHttp\Ring\Client\CurlHandler->_invokeAsArray(Array) #3 C:\xampp\htdocs\spreadsheetapi\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\Middleware.php(54): GuzzleHttp\Ring\Client\CurlHandler->__invoke(Array) #4 C: in C:\xampp\htdocs\spreadsheetapi\google-api-php-client\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 51

    Reply
  36. Graycie

    Thanks Karl, I disabled certificate verification in guzzle ringphp curlfactory and it worked perfectly so it might be that Guzzle isn’t able to verify.

    Reply
    1. karl.kranich Post author

      Right. But it works for others, so there must be some certificate-related files missing on the machine you’re developing on. That would be the next thing to check. Perhaps the Guzzle site offers some clues.

      Reply
    2. prajakta

      Hi @Graycie and @karl,
      I am also getting same error while running apitest.php
      Fatal error: Uncaught exception ‘GuzzleHttp\Ring\Exception\RingException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate’ in J:\xampp\htdocs\email_list\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlFactory.php:127 Stack trace: #0 J:\xampp\htdocs\email_list\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlFactory.php(91): GuzzleHttp\Ring\Client\CurlFactory::createErrorResponse(Array, Array, Array) #1 J:\xampp\htdocs\email_list\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlHandler.php(96): GuzzleHttp\Ring\Client\CurlFactory::createResponse(Array, Array, Array, Array, Resource id #94) #2 J:\xampp\htdocs\email_list\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\CurlHandler.php(68): GuzzleHttp\Ring\Client\CurlHandler->_invokeAsArray(Array) #3 J:\xampp\htdocs\email_list\google-api-php-client\vendor\guzzlehttp\ringphp\src\Client\Middleware.php(54): GuzzleHttp\Ring\Client\CurlHandler->__invoke(Array) #4 J:\xampp\htdocs\email_ in J:\xampp\htdocs\email_list\google-api-php-client\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 51

      How and where did you disable certificate verification ? Pls help me

      Reply
      1. karl.kranich Post author

        Prajakta,
        I haven’t tried this, but it looks like anywhere that you see ‘$httpClient = new GuzzleHttp\Client();’ in my code, just put a line after that saying:
        $httpClient->setDefaultOption(‘verify’, false);

        Karl

        Reply
    1. karl.kranich Post author

      I have been exploring the execution API also, and discovered that it unfortunately doesn’t support service accounts right now. See the end of this discussion: http://stackoverflow.com/questions/33306299/can-the-google-apps-script-execution-api-be-called-by-a-service-account

      I also found a post where a Google person was admitting that the diagrams in the execution API documentation show a service account authentication flow, but he said that doesn’t work.

      Reply
  37. Karan

    Hello Karl,

    I was trying to implement your tutorial and as you and others have said, I am also getting the same issue.

    Did you found any other workaround to this problem?

    Thanks

    Reply
    1. karl.kranich Post author

      Karan,
      If you’re talking about the SSL certificate problem, I have not been able to reproduce that error. What OS is on the machine getting the error?

      Karl

      Reply
  38. Bernie

    I am also getting this issue:

    Fatal error: Uncaught exception ‘GuzzleHttp\Ring\Exception\RingException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate

    it gets thrown in Google/Http/REST.php -> function doExecute() -> $client->send($request)

    which, in my case, calls http://www.googleapis.com/upload/drive/v2/files

    I’ve followed all the StackOverflow tips and still don’t have it working on Windows 10 running WAMP.

    I downloaded the cert chain and from Chrome exported the cert from Chrome (went to http://www.googleapis.com and right click on the lock -> Connection tab -> Certificate Information -> Details -> Copy to file -> Base 64 encoded X.509

    I added that to the “curl-ca-bundle.crt” file

    I set this in php.ini:

    curl.cainfo = “C:/Windows/curl-ca-bundle.crt”

    And I still get the error

    Reply
    1. karl.kranich Post author

      Bernie, after the holidays I will try to reproduce your error on a Windows 10 machine. On my Mac, it all just works.

      Reply
  39. Rajiv Sharma

    Hello Karl,
    I am trying to receive feeds from sheets api but the response I get on using Guzzle HTTP CLient is not xml formatted it looks like this for a request for Worksheets feed
    —–
    https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full2015-12-27T18:18:24.557Zrajinsteinrajinstein@gmail.com41https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full/onoo3xd2015-12-27T18:18:24.557ZDec 15262293https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full/o5kg9cn2015-12-27T18:18:24.557ZNov 15251868https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full/od62015-12-27T18:18:24.557ZOct 15261006https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full/oubu2gq2015-12-27T18:18:24.557ZLedger261000
    —–
    while I want it to be like

    https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full2015-12-27T18:18:24.557ZBuyInstagramsFollowers.orgrajinsteinrajinstein@gmail.com41https://spreadsheets.google.com/feeds/worksheets/1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc/private/full/onoo3xd2015-12-27T18:18:24.557ZDec 15Dec 15262293

    My source code is similar to yours and is as follows

    //Spreadsheet key
    $fileId=$key_spreadsheet=”1bNfr_Qz0EWUsY4Rlv12_CO_f4AqTKysWxYu_zBPLHjc”;

    //Obtain access_token
    include_once “google-api-php-client/vendor/autoload.php”;
    include_once “google-api-php-client/examples/templates/base.php”;

    /************************************************
    Make an API request authenticated with a service
    account.
    ************************************************/

    $client = new Google_Client();
    putenv(‘GOOGLE_APPLICATION_CREDENTIALS=Google Sheets Logging-942b19d82ad3.json’);

    if ($credentials_file = checkServiceAccountCredentialsFile()) {
    // set the location manually
    $client->setAuthConfig($credentials_file);
    } elseif (getenv(‘GOOGLE_APPLICATION_CREDENTIALS’)) {
    // use the application default credentials
    $client->useApplicationDefaultCredentials();
    } else {
    echo missingServiceAccountDetailsWarning();
    exit;
    }

    $client->setApplicationName(“Google Sheets Logging”);
    $client->setScopes([‘https://www.googleapis.com/auth/drive’,’https://spreadsheets.google.com/feeds’]);

    // Section 2: GET access token with assertion
    $httpClient = new GuzzleHttp\Client();
    $client->authorize($httpClient);
    //Retrieve worksheets feed for the above spreadsheet
    $url_worksheets_feed = “https://spreadsheets.google.com/feeds/worksheets/”.$key_spreadsheet.”/private/full”;
    $cellFeedURI;
    $row;
    $response = $httpClient->get($url_worksheets_feed);
    echo $response->getBody();

    Please let me know what am I doing wrong..I tried to get the response using $response->xml() as well but it would not display anything…Your help would be appreciated

    Reply
    1. karl.kranich Post author

      Rajiv,
      It’s hard for me to tell what’s going on, but I would suggest that you start by checking for clues in $response->getStatusCode() and $response->getReasonPhrase()

      Karl

      Reply
  40. carlos Ferrandis

    Hello Karl,

    if the spreadsheet I use is shared – anyone with the link can view. I can see it with your script but If I share it following the instructions, I’ve got the following error message:
    Fatal error: Uncaught exception ‘Google_Service_Exception’ with message ‘{ “error”: { “errors”: [ { “domain”: “global”, “reason”: “notFound”, “message”: “File not found: 1YZybJCOivFRFu…”, “locationType”: “other”, “location”: “file” } ], “code”: 404, “message”: “File not found: 1YZybJCOivFRFu…” } } ‘ in /var/www/html/project/google-api-php-client-v2.0.0-RC3/src/Google/Http/REST.php:120 Stack trace: #0 /var/www/html/project/google-api-php-client-v2.0.0-RC3/src/Google/Http/REST.php(80): Google_Http_REST::decodeHttpResponse(Object(GuzzleHttp\Message\Response), Object(GuzzleHttp\Message\Request)) #1 [internal function]: Google_Http_REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Message\Request)) #2 /var/www/html/project/google-api-php-client-v2.0.0-RC3/src/Google/Task/Runner.php(181): call_user_func_array(Array, Array) #3 /var/www/html/project/google-api-php-client-v2.0.0-RC3/s in /var/www/html/project/google-api-php-client-v2.0.0-RC3/src/Google/Http/REST.php on line 120

    Any idea on what I am doing incorrectly? I might get away with sharing to anyone with the link for the current project but it is not a good solution. Please help.

    Reply
    1. karl.kranich Post author

      Sameer,
      You probably don’t need the underscore – I would try without it. If that doesn’t work, try _

      Karl

      Reply
  41. sameer

    Hello karl, I tried with underscore but not write data on google spreadsheet and error comes. Currently i need underscore please however give your solution? Thanks Karl.

    Reply
    1. karl.kranich Post author

      I tried replying to your previous comment with the ampersand XML code for an underscore, but the WordPress editor removed it. Try using this code (with the spaces removed): & # 9 5 ;

      Reply
  42. carlos Ferrandis

    HI Karl, after re-reading your instructions I realized I was not sharing the file with the email generated when I created the Service account key, which is crucial. My bad. Now I need to be able to work on the spreadsheet. thank you

    Reply
  43. Rinsad

    Dear Karl

    I am also struggling to add data to a column with underscore. I could understand your solution about using

    “& # 9 5 ;”

    Can you explain bit more? Do we want to use ?

    Thanks
    Rinsad

    Reply
      1. Rinsad

        Well Karl

        What I am looking to do is, use the underscore as part of the xml tag, not part of the xml value. My excel sheet column name has underscore, which cannot be removed, as it is being used by another application.

        tracking_number and order_id are those column names. But when I use gsx : tracking_number, it doesnt work. Thats where I am stuck.

        Thanks
        Rinsad

        Reply
        1. karl.kranich Post author

          Even in the tag, I think the special code should work. Have you tried it?

          Also, I would look at how it’s represented in the output when you ask Google for your spreadsheet data. You should be able to mimic that.

          Karl

          Reply
  44. carlos

    Hi Karl and everyone following this thread.

    I am really struggling to understand this, for now I wish anyone could answer and point any direction,

    the output of section 3 is an object with my spreadsheet, how can I iterate on that object, I need to read rows and cell, I tried to cast it to an array but it did not work.
    I would appreciate any help.

    Reply
    1. karl.kranich Post author

      Carlos,
      My simple needs have been met by searching the output with regular expressions. You should be able to use XML utilities to parse the output, though.

      Karl

      Reply
    2. karl.kranich Post author

      Carlos,
      I just added a new section to my example code that parses the XML, and you could use that to populate an array. I think you can also tell Google to return the spreadsheet data as json, but I haven’t tried that yet.

      Reply
  45. Rinsad

    I tried it Karl

    But it didnt work. I am getting an exception. Tried using this way without spaces.My excel column name is gear_test

    more gear

    Thanks
    Rinsad

    Reply
    1. karl.kranich Post author

      Rinsad,
      I created a new column in my spreadsheet called col_c. When I use my script to request the XML data, the tag is gsx:colc. Have you tried it all lowercase with no spaces and no underscores?

      Karl

      Reply
  46. TECH

    Karl,

    thank you for this informative article. I had attempted to do something similar before but google was in the process of changing their API around…. I’m having difficulty and im not sure if maybe its due to a recent change to the google-api-php-client, looks like they modified it last to add guzzle 6 into the client and guzzle 6 doesn’t have createRequest method, as I can get everything working up until the commented out areas of your apitest.php those give error:

    Catchable fatal error: Argument 3 passed to GuzzleHttp\Client::request() must be of the type array, string given, called in C:\NEATO\google-api-php-client\vendor\guzzlehttp\guzzle\src\Client.php on line 87 and defined in C:\NEATO\google-api-php-client\vendor\guzzlehttp\guzzle\src\Client.php on line 126

    Reply
    1. karl.kranich Post author

      I’ve updated my example code to work with the updated Google PHP API client and Guzzle 6. Hopefully it’s helpful to you!

      Karl

      Reply
  47. Capitano

    Hi Karl, thank you for your effort!!
    By few days I have trouble with Google Drive api. I think its an error of some libraries to update.
    If I would like update GuzzleHttp to 6.0 version with composer, could you help me, please?
    Thank you very much!

    Reply
    1. karl.kranich Post author

      Hello,
      I’m sorry, but I have not really figured out Composer yet. I just enter the commands that Google tells me to install the dependencies.

      Karl

      Reply
  48. Gopal

    Hi Karl,
    Please guide me how can I update a paricular cell value of a row using status condition.
    i.e. I want to update status field of the sheet ‘complete’ or ‘incomplete’ in the time of autonomous data import in the database.

    Please help me with the example script.

    Thanks,
    Gopal

    Reply
    1. karl.kranich Post author

      Gopal,
      The methods that I have tested all use the list-based feed. This is where you have a header row and then rows of data, and you can only edit the rows of data.
      You probably need to use cell-based feeds. You can read the section called “Change the contents of a cell” here: https://developers.google.com/google-apps/spreadsheets/data

      I will try to put together some example code soon.

      Karl

      Reply
  49. Gopal

    Hi Karl,
    Many many thanks for your response.
    I have gone through the google developer console. And use the below:

    $url = “https://spreadsheets.google.com/feeds/cells/$fileId/0/private/full/cell”;
    $method = ‘PUT’;
    $headers = [“Authorization” => “Bearer $accessToken”, ‘Content-Type’ => ‘application/atom+xml’, ‘GData-Version’ => ‘3.0’];
    $postBody = ‘
    https://spreadsheets.google.com/feeds/cells/$fileId/0/private/full/R2C1

    ‘;
    $httpClient = new GuzzleHttp\Client([‘headers’ => $headers]);
    $resp = $httpClient->request($method, $url, [‘body’ => $postBody]);
    $body = $resp->getBody()->getContents();
    $code = $resp->getStatusCode();
    $reason = $resp->getReasonPhrase();
    echo “$code : $reason\n\n”;
    echo “$body\n”;

    But this gives error:
    “400 Bad Request response: Invalid query parameter value for cell_id”.

    Once Again thanks to give me a bit of your precious time.
    Please help me regarding this.

    Reply
    1. karl.kranich Post author

      Hi Gopal,
      Hopefully I will have some time to try it this week. I’ll let you know if I figure it out.

      Karl

      Reply
  50. Awais

    Hi karl,
    I have installed composer in my system but when i ran ‘composer require google/apiclient:^2.0.0@RC’ as specified to install dependencies. it give me this error that “The package is not available in a stable-enough version”. The full text shown to me is as below.

    Loading composer repositories with package information
    Updating dependencies (including require-dev)
    Your requirements could not be resolved to an installable set of packages.

    Problem 1
    – The requested package google/apiclient could not be found in any version,
    there may be a typo in the package name.

    Potential causes:
    – A typo in the package name
    – The package is not available in a stable-enough version according to your min
    imum-stability setting
    see f
    or more details.

    Read for further commo
    n problems.

    Installation failed, deleting ./composer.json.

    Please help me as it is argent.

    Reply
  51. Catherine

    Hi Karl!

    Thanks for nice instructions. Unfortunately i have som problems with downloading the json key-file when i creating my Service account. Everything seems to work when i creating a project and the service account, and the pop-up-window with the private key also says that it worked and that a file was saved on my computer – but i can’t se any. Do you know what i’m doing wrong?

    Best regards
    Catherine

    Reply
    1. karl.kranich Post author

      Catherine,
      I don’t know what’s going on there — it’s just a normal file that should be in your downloads folder (or wherever your browser saves files).

      Karl

      Reply
  52. Catherine

    Hi again!

    Okey, I don’t know either. Thank you for your answer!
    Is it possible to create your own json key-file? I mean, i know what name it should have and where to store it, and i guess i can find the rest of the information at console.developers?

    If it’s possible, it would be nice to see how the file should look like, is there anyway/anywhere to see that?

    BR Catherine

    Reply
  53. Catherine

    Hi again! This i solved now, so you can ignore the above question. It worked when i switched to Chrome…

    Thanks 🙂

    BR
    Catherine

    Reply
  54. Akash Rathore

    karl.kranich

    $url = “https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full”;
    $method = ‘GET’;
    $headers = [“Authorization” => “Bearer $accessToken”, “GData-Version” => “3.0”];
    $httpClient = new GuzzleHttp\Client([‘headers’ => $headers]);
    $resp = $httpClient->request($method, $url);
    $body = $resp->getBody()->getContents();
    $code = $resp->getStatusCode();
    $reason = $resp->getReasonPhrase();
    echo “$code : $reason\n\n”;
    echo ” $body\n”;

    This codes give me the string response.I want data as an array so, I will filter desired result from the section.and show in our form.

    Reply
  55. Louise Myllylä

    Hi Karl,

    Having a problem with Guzzle.. I get this after following your steps on this line of code:
    $resp = $httpClient->request($method, $url, [‘body’ => $postBody]); request method does not exist, this line is from you apitest.

    Do you know what could be wrong?

    Reply
    1. karl.kranich Post author

      If it worked, your project folder should contain a folder called “vendor” with an autoload.php file that was created by composer and a bunch of folders including “guzzlehttp”. If that’s all there, I don’t know what to suggest.

      Reply
  56. Dennis Blume

    Hey Karl,
    thanks for the work. Keep getting the 401 message, stating that my access to the spreadsheet was denied. At first I couldn’t figure out the error, I tried a different private key, different service account (different mail).. well it was driving me crazy. I was an hour in when I decided to take a look at the actual class and found what has been missing. For some reason the Google API creates an access key, which is not tied to the given service account email, so it appears as “anonymous” when accessing the spreadsheet, therefor the drive denies the access. Long story, short: Setting the “setAuthConfig” fixes the issue.
    $client->setAuthConfig([“type” => “service_account”, “client_email” => “username@appname.iam.gserviceaccount.com”]);
    Maybe you would like to integrate it in your gist to help a few others.
    Dennis

    Reply
    1. karl.kranich Post author

      Thanks for the info, Dennis! I don’t know why that’s happening for you and not for me, but I’ll incorporate that into my gist asap.

      Karl

      Reply
      1. Dennis Blume

        I have 2 ideas what might be the issue.
        First one: I created more than one service account and the API didn’t know which mail to use. I think it’s unlikely since the keys are connected to the accounts.
        Second: We are using Google Apps for Work, maybe the guidelines are different then they are for the regular Google Drive.
        Anyways, thanks for the quick reply. I am looking forward to the update 🙂

        Dennis

        Reply
  57. John Murray

    Wow and Thank you! In 2 hours, I was able to do what I have been working on for several weeks. Thank you.

    The comments about the column headers is very important. I use CamelCase column headers without space in the spreadsheet and all lowercase in for accessing the columns.

    The examples in the apitest.php code walked me through the various CRUD elements (without delete). I don’t know why Google could not provide the same tutorial. I ran their tutorial and was able to access a gcalendar, but not a sheet.

    Thank you!

    Reply
  58. Piter

    Hi. I have done everything as described, but in the end got the error: ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate. Maybe you know that it can be, if the help would be very grateful.(english knowledge – Elementary)

    Reply
    1. karl.kranich Post author

      Maks,
      My spreadsheets (including a new one that I just created) still have od6 as the identifier for the first worksheet. I’ll just leave things the way they are, since I already tell people that they need to look at the XML output of the “list worksheets” section to get the correct ID.

      Karl

      Reply
    1. karl.kranich Post author

      Rebecca,
      You’ll need to either upload the whole folder or run the git and composer commands on your server to build the folder structure.

      Karl

      Reply
  59. rebecca

    Thank you but it still doesn’t work
    I put the code:
    $includePath = Mage::getBaseDir(). ‘/google-api-php-client-2.0.0-RC5/vendor/autoload.php’;
    $includegoogle = Mage::getBaseDir(). ‘/google-api-php-client-2.0.0-RC5/examples/templates/base.php’;

    require_once ($includePath);
    include_once($includegoogle);

    $client = new Google_Client();
    /************************************************
    ATTENTION: Fill in these values, or make sure you
    have set the GOOGLE_APPLICATION_CREDENTIALS
    environment variable. You can get these credentials
    by creating a new Service Account in the
    API console. Be sure to store the key file
    somewhere you can get to it – though in real
    operations you’d want to make sure it wasn’t
    accessible from the webserver!
    ************************************************/
    putenv(“GOOGLE_APPLICATION_CREDENTIALS=service-account-credentials.json”);
    if ($credentials_file = checkServiceAccountCredentialsFile()) {
    // set the location manually
    $client->setAuthConfig($credentials_file);
    } elseif (getenv(‘GOOGLE_APPLICATION_CREDENTIALS’)) {
    // use the application default credentials
    $client->useApplicationDefaultCredentials();
    } else {
    echo missingServiceAccountDetailsWarning();
    exit;
    }
    $client->setApplicationName(“Sheets API Testing”);
    $client->setScopes([‘https://www.googleapis.com/auth/drive’,’https://spreadsheets.google.com/feeds’]);
    // The file ID was copied from a URL while editing the sheet in Chrome
    $fileId = ‘1EC66vuMNB4EUQgEsQyELji1PbUcJskDbVbSTun2kfUI’;
    // Access Token is used for Steps 2 and beyond
    $tokenArray = $client->fetchAccessTokenWithAssertion();
    $accessToken = $tokenArray[“access_token”];
    // Section 1: Uncomment to get file metadata with the drive service
    // This is also the service that would be used to create a new spreadsheet file
    $service = new Google_Service_Drive($client);
    $results = $service->files->get($fileId);
    var_dump($results);
    in my observer.php (i am working with magento) and in the same folder as observer.php the file.json
    the other ones in the root of my magento.
    It doesn’t seem to manage to get to the access tocken.
    What could be thank you!!

    Reply
    1. karl.kranich Post author

      Rebecca,
      I don’t know anything about magento, but I will say that your folder structure is different from mine: my “vendor” folder is not inside my google-api-php-client folder. And my access token is in my main project folder, which is where my vendor folder and google-api-php-client folders are.

      Karl

      Reply
  60. Yury

    Have no problem to GET lines from sheet but after trying to insert new line I get following all the time: Fatal error: Uncaught exception ‘GuzzleHttp\Exception\ClientException’ with message ‘Client error response [url] https://spreadsheets.google.com/feeds/list/1zf4m-C3V2Takoks-zgYtd74VAijs31YYm7vhGVyrXfk/1/private/full [status code] 400 [reason phrase] Bad Request’ in /home/ywamres2/gapi/google-api-php-client/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php:89 Stack trace: #0 /home/ywamres2/gapi/google-api-php-client/vendor/guzzlehttp/guzzle/src/Subscriber/HttpError.php(33): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Message\Request), Object(GuzzleHttp\Message\Response)) #1 /home/ywamres2/gapi/google-api-php-client/vendor/guzzlehttp/guzzle/src/Event/Emitter.php(109): GuzzleHttp\Subscriber\HttpError->onComplete(Object(GuzzleHttp\Event\CompleteEvent), ‘complete’) #2 /home/ywamres2/gapi/google-api-php-client/vendor/guzzlehttp/guzzle/src/RequestFsm.php(91): GuzzleHttp\Event\Emitter->emit(‘complete’, Object(GuzzleHttp\Event\CompleteEvent)) #3 /home/ywamres2/gapi/google-api-php-client/vendor/guzzleh in /home/ywamres2/gapi/google-api-php-client/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php on line 89

    Reply
    1. karl.kranich Post author

      Yury,
      I’m guessing that your spreadsheet doesn’t have headers in row 1, or there is a problem with the XML that you’re sending. Does your spreadsheet have a header row?

      Karl

      Reply
  61. Yury

    Yes Karl! you made my day! I had typo in the column name. Now everything works.

    But I have another request – do you or someone here knows how to insert linebreaks inside of celles? I have large form results inserted in spreadsheet and thought to split it for better reading on 14 columns with 5-10 sections in it (exactly like form itself) but dont know how to separate different information portions inside of cell. E.g. column Personal Information should have Name, Surname, Birthday, Email sections

    Reply
    1. karl.kranich Post author

      Yury,
      Several years ago I did that with a Perl script by putting a line feed (“\n” or ascii 10) in the cell data.

      Karl

      Reply
      1. Yury

        Karl, I have tried \n but it simply converts to text, char(10) would probably work but how to insert it?

        Reply
  62. rebecca

    I just went to https://github.com/google/google-api-php-client/releases and downloaded from there the google-api-php-client-2.0.0-RC5.zip and this is the folder that i uploaded to the root of my magento is it not right? then i get $includePath = Mage::getBaseDir(). ‘/google-api-php-client-2.0.0-RC5/vendor/autoload.php’;
    $includegoogle = Mage::getBaseDir(). ‘/google-api-php-client-2.0.0-RC5/examples/templates/base.php’;? thank you !!

    Reply
    1. karl.kranich Post author

      If that’s how it came from Google, then it should work. I’m sorry, I don’t know what to try next.

      Reply
  63. Greg Turner

    Hi Karl,
    I’ve got a question if you don’t mind. So far I have your code to parse table data with SimpleXML working very nicely. The spreadsheet that I will be ultimately working with is going to have approx. 500 rows and I need to read in a subset of those 500 rows based on a query based on column values. Can that be done using the libraries from your example. I found this reference, but it seems to use a different set of libraries: https://developers.google.com/google-apps/spreadsheets/data. Any guidance would be appreciated. Thanks

    Reply
    1. karl.kranich Post author

      Greg,
      You can definitely use the examples on that page – my work was basically to get the HTTP examples on that page to work with the Google-provided PHP client. Build a URL following the example of the “Send a structured query for rows” section and then send the GET request via the Guzzle client, and you’ll get your subset of the rows.

      Karl

      Reply
  64. rebecca

    Thank you but now i get

    Fatal error: Uncaught exception ‘GuzzleHttp\Exception\ClientException’ with message ‘Client error: `POST https://www.googleapis.com/oauth2/v3/token` resulted in a `400 Bad Request` response: { “error”: “invalid_scope”, “error_description”: “http://www.googleapis.com/auth/drive is not valid scope.” } ‘ in /home/rebecc36/public_html/skysaver/google/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php:107 Stack trace: #0 /home/rebecc36/public_html/skysaver/google/vendor/guzzlehttp/guzzle/src/Middleware.php(65): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response)) #1 /home/rebecc36/public_html/skysaver/google/vendor/guzzlehttp/promises/src/Promise.php(199): GuzzleHttp\Middleware::GuzzleHttp\{closure}(Object(GuzzleHttp\Psr7\Response)) #2 /home/rebecc36/public_html/skysaver/google/vendor/guzzlehttp/promises/src/Promise.php(152): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\Psr7\Response), Array) #3 /home/rebecc36/public_html/skysaver/google/ve in /home/rebecc36/public_html/skysaver/google/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php on line 107
    What is it?

    Reply
    1. karl.kranich Post author

      It looks like I have two versions of my apitest code in github. One has the drive scope, and one just sets the sheets scope: ‘https://spreadsheets.google.com/feeds’

      If you’re not using the Drive api to list sheets, just delete that scope from your code. If you are trying to use it, make sure that you’ve enabled it in the Developer Console also.

      You don’t need the Drive API to read and write to Google Spreadsheets — just to create new spreadsheets or read a list of all of the ones you have.

      Karl

      Reply
      1. Yury

        Karl, to divide lines in cell works the following:
        $test = “some”.”\n”.”text”;
        $postBody = ‘
        First
        second
        this is third
        ‘.$test.’
        ‘;
        \n must be out of text line, for some reason if i place it inside, it will be part of text

        Reply
  65. Greg Turner

    Hi Karl,

    I’ve tried each one of these examples, and none of them return just the rows where ‘county’ equals ‘Sonoma’. All of them return all rows.

    $resp = $httpClient->request($method, $url, [‘query’ => ‘county=Sonoma’]);

    $resp = $httpClient->request($method, $url, [‘query’ => [‘county’ => ‘Sonoma’]]);

    $resp = $httpClient->request($method, $url, [‘query’ => [‘gsx:county’ => ‘Sonoma’]]);

    $resp = $httpClient->request($method, $url, [‘query’ => [‘county’ => new SimpleXMLElement(‘Sonoma’)]]);

    $resp = $httpClient->request($method, $url.’?county=Sonoma’);

    $resp = $httpClient->request($method, $url, [‘query’ => [‘county’ => new SimpleXMLElement(‘Sonoma’)]]);

    The last one also gave me warnings about gsx:county being undefined.

    This is getting frustrating.

    Greg

    Reply
    1. karl.kranich Post author

      Greg,
      Instead of adding the query to the request like you’ve tried, just append this to the end of the URL: ?sq=county=Sonoma

      (sq means “structured query”)

      Karl

      Reply
  66. Rob

    Hey Karl,

    I have tried different cacert.pem but It didn’t work and got cURL 77 every-time. What should I do now?

    Thanks,
    Rob.

    Reply
    1. karl.kranich Post author

      Rob,
      From what I understand, error 77 indicates that php couldn’t find or open the cacert.pem file. Make sure you’re editing the correct php.ini, and if you’re on an OS like linux or Mac OSX, make sure that the permissions are set properly so that the process that runs php can read the file.

      Karl

      Reply
  67. patrick

    karl,

    the parameters updated-min and updated-max are not supported in the list feed. do you know of any other way to query /feed/entry/updated?

    thanks
    patrick

    Reply
    1. karl.kranich Post author

      Patrick,
      No, I don’t. I would grab the whole sheet and filter in my code, but I don’t work with sheets that have more than a few thousand rows.

      Karl

      Reply
  68. Max

    I have some html form. And I’d like to paste some value from hmtl form to google spreadsheet. But I have problem with xml. I can’t paste value from form to $postBody. I can’t paste value between gsx:namecol $value /gsx:namecol
    form method=”POST”
    Value1: input type=”text” name=”value” value=””
    input type=”submit” value=”submit” name=”submit”
    /form

    if($_POST[‘submit’]) {
    $value = $_POST[‘value’];

    $postBody = ‘entry xmlns=”http://www.w3.org/2005/Atom” xmlns:gsx=”http://schemas.google.com/spreadsheets/2006/extended” gsx:colname $value /gsx:colname /entry ‘;
    or
    $postBody = ‘ entry xmlns=”http://www.w3.org/2005/Atom” xmlns:gsx=”http://schemas.google.com/spreadsheets/2006/extended” gsx:colname value $value /value /gsx:colname /entry’;

    Reply
    1. karl.kranich Post author

      Max,
      When you put a variable in a string and want the value of the variable to show up in the string, that’s called variable interpolation. Single quotes turn off variable interpolation, so you’re not getting the value of $value in the string. There are two ways to handle this: 1. Turn on variable interpolation by using double quotes instead of single quotes, but then you’ll have to escape all of the double quotes in the string. 2. Put together several strings like this: $postBody = ‘First part of string’ . $value . ‘second part of string’

      Hope this helps,
      Karl

      Reply
  69. Piter

    Hi, Karl. Sorry for my bad English. Have you encountered with retrieving data from sheets with limited access . For example using OAuth2.0 check the user token , if he has access to this sheet displayed data sheet, else displayed messege “access denide”. I installed without problems google OAuth2 in hosting , but associate it with the document identifer (ID file), i dont know.

    Reply
    1. karl.kranich Post author

      Piter,
      I always use a service account, and I give the service account edit access to the sheet. So I have not seen that problem (if I am understanding you correctly).

      Karl

      Reply
  70. Adrian

    Notice: Undefined index: client_id in C:\wamp\www\google2\google-api-php-client\src\Google\Client.php on line 835

    Reply
    1. karl.kranich Post author

      Adrian,
      I just re-downloaded the Google API PHP client to make sure that they didn’t change anything, and my code still works for me. Looking at Google’s client.php line 835, I’m guessing that either your json file is malformed, missing, or can’t be found.

      Karl

      Reply
  71. Alex

    i get following error message
    Fatal error: Call to undefined function GuzzleHttp\Handler\curl_reset() in C:\Localhost\GAPI\google-api-php-client\vendor\guzzlehttp\guzzle\src\Handler\CurlFactory.php on line 78

    What should i do now? no idea how to solve it.

    Reply
    1. karl.kranich Post author

      Alex,
      When I google that, one thing that comes up is that you need at least PHP 5.5 (try php -v).

      Karl

      Reply
      1. Alex

        Hi Karl,
        I just upgraded to PHP 5.6, and still getting errors

        Fatal error: Uncaught exception ‘GuzzleHttp\Exception\RequestException’ with message ‘cURL error 60: SSL certificate problem: unable to get local issuer certificate (see http://curl.haxx.se/libcurl/c/libcurl-errors.html)’ in C:\Localhost\GAPI\google-api-php-client\vendor\guzzlehttp\guzzle\src\Handler\CurlFactory.php:189 Stack trace: #0 C:\Localhost\GAPI\google-api-php-client\vendor\guzzlehttp\guzzle\src\Handler\CurlFactory.php(152): GuzzleHttp\Handler\CurlFactory::createRejection(Object(GuzzleHttp\Handler\EasyHandle), Array) #1 C:\Localhost\GAPI\google-api-php-client\vendor\guzzlehttp\guzzle\src\Handler\CurlFactory.php(105): GuzzleHttp\Handler\CurlFactory::finishError(Object(GuzzleHttp\Handler\CurlHandler), Object(GuzzleHttp\Handler\EasyHandle), Object(GuzzleHttp\Handler\CurlFactory)) #2 C:\Localhost\GAPI\google-api-php-client\vendor\guzzlehttp\guzzle\src\Handler\CurlHandler.php(43): GuzzleHttp\Handler\CurlFactory::finish(Object(GuzzleHttp\Handler\CurlHandler), Object(GuzzleHttp\Handler\EasyHan in C:\Localhost\GAPI\google-api-php-client\vendor\guzzlehttp\guzzle\src\Handler\CurlFactory.php on line 189

        Reply
          1. Alex

            Hi Karl,
            Thanks for sharing this useful article with me, now Google Sheet API work fine for me.

            Alex

  72. Greg Turner

    Karl,
    I wanted once again to thank you for your help. The project finally went live today. See: http://cleanpowerexchange.org/california-community-choice/. Click on the various California counties. The client manages all that data in a google spread sheet. In the WP dashboard, I defined a custom post type called CA County and each record of which contains the data for a county. On the backend they are able to select individual counties and then do a bulk refresh of those counties, which pulls the data from the spread sheet. Clicking on a county does an ajax call to get the county data and populate the popup.
    Greg

    Reply
  73. Rob

    Hey Karl,

    I have followed all steps given by you and created apitest.php file. When I try to run & debug it, It calls this apitest.php twice.

    We have also tried to exit from this script but It still run this script again after exit().
    We have also tried this section:
    $resp = $httpClient->request($method, $url);

    But $httpClient->request method does not exist in this class. It has createRequest() method. Can you pls guide us to solve this two issues? We are not able to do any operation with this script.

    Thanks,
    Rob

    Reply
    1. karl.kranich Post author

      Rob,
      I haven’t seen those things happen. I’m wondering if there’s a mix of incompatible versions in the dependencies. Did you use Composer to install the dependencies? What OS are you running this on?

      Karl

      Reply
  74. michal

    Hi Karl,

    Many thanks for your script!

    Could you tell me please is there anyway to add a row with google API to new spreadsheet which wil contain dropdown list with one selected value ?

    What should I put in gsx attribute (example below)?

    $postBody = ‘
    name

    ‘;

    Reply
    1. karl.kranich Post author

      Michal,
      You’re talking about data validation, and I’m not sure how to do that with this type of script. This is what I would try: use your web-based script to add a simple row with the value that you want. Then add a Google Apps Script to the spreadsheet (via Tools – Script editor) that watches for changes to the sheet (via a trigger) and adds the validation to new rows. The way I see it, the validation is just there for people who interact with the script directly, since the web-based script can do its own validation so that it never adds a disallowed value.

      Hope this helps,
      Karl

      Reply
  75. Akash

    Hello Karl,I have used following codes as per your suggestion

    function addDataToWorkSheet()
    {
    global $fileId;
    global $accessToken;
    $title = “Test”;
    $row = 50;
    $count = 10;
    //$spreadsheet->addWorksheet(‘New Worksheet’, 50, 20);
    $url = “https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full”;
    $method = ‘POST’;
    $headers = [“Authorization” => “Bearer $accessToken”, ‘Content-Type’ => ‘application/atom+xml’];
    $postBody = ‘
    ‘.$title.’
    ‘.$row.’
    ‘.$count.’
    ‘;
    $httpClient = new GuzzleHttp\Client([‘headers’ => $headers]);
    $resp = $httpClient->request($method, $url, [‘body’ => $postBody]);
    $body = $resp->getBody()->getContents();
    $code = $resp->getStatusCode();
    $reason = $resp->getReasonPhrase();
    return $code;
    }

    It gives the error :

    google-api-php-client\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 107

    for that function

    Reply
    1. karl.kranich Post author

      Akash,
      Your $postBody is going to have to be valid XML. Pattern it off of a dump of the worksheet data. Something like this:

      $postBody = ‘‘ . $title . ‘‘ . $row . ‘‘ . $count . ‘‘;

      Karl

      Reply
    1. karl.kranich Post author

      Hello Atul,
      It might not be possible with the Sheets API, since it tends to work in the populated data area or the following row.

      Have you tried sending a PUT request with R56C1 and cell row=”56″ col=”1″ like in the “Change contents of a cell” section of that page?

      Karl

      Reply
  76. quentin

    Hi Karl,
    I followed your instruction but I still getting an error :

    PHP Fatal error: Class ‘Google\Auth\Credentials\ServiceAccountCredentials’ not found in public_html/vendor/google/auth/src/CredentialsLoader.php on line 124

    Thanks in advance.

    Reply
    1. karl.kranich Post author

      Hello Quentin,
      Make sure that you named the json file “service-account-credentials.json” and put it in the same folder as your script. It sounds like the script can’t find that file.

      Karl

      Reply
  77. quentin

    Yes it was that ! but now I got the error :
    PHP Fatal error: Class ‘Google_Collection’ not found in /public_html/vendor/google/apiclient-services/Google/Service/Drive/DriveFile.php on line 19

    I found a file named Collection that have a class Google_Collection but it’s in the folder /vendor/google/apiclient/src/Google/Collection.php

    So this is the right file? and how make the extends ?

    thank you in advance

    Reply
    1. karl.kranich Post author

      I don’t know what is causing that or what to try. I wonder if Google changed the API client again. I will run some tests, but I can’t do it for a few days.

      Karl

      Reply
  78. quentin

    It might be possible I passed throught lot of errors before being stuck on this one.
    I tried a require to include the file Collection.php but that not worked.

    Let me know if you find something.
    Thanks.

    Reply
    1. karl.kranich Post author

      Quentin,
      Stan just pointed out that these instructions don’t work with PHP versions before 5.5. Maybe that’s your issue?

      Karl

      Reply
  79. Stan Alachniewicz

    This is great! It took me a while to figure out that it doesn’t work with PHP 5.3 or PHP 5.4, so you may want to edit the article to mention that. I had to upgrade to PHP 5.5 to get it working.

    Reply
    1. karl.kranich Post author

      Thank you, Stan! I probably only tried with PHP 5.5 and didn’t even realize that.

      Karl

      Reply
  80. quentin

    Yes but I already upgraded my php version to 5.5 last week to make it work but it didn’t resolve all problems.

    Quentin

    Reply
  81. quentin

    Good news Karl ! I tried the installation on my personal computer on Debian 8 and it worked fine !
    Maybe it’s windows that messed up everything on the install on my other pc.

    Thank you very much !

    Reply
  82. Alejandro

    Hey Karl,

    I just made this “library” based in your work: https://github.com/maparrar/google-spreadsheet-php. Check it if you want. You are free to share and improve :), is MIT License. This example allow get all rows of a spreadsheet or a range of cells. You can pass a range in this format: “A3:F9” . There is a demo in: https://project-spreadsheet-php.herokuapp.com/ that load info from this spreadsheet: https://docs.google.com/spreadsheets/d/154jr-yjqKuBofulfipptUDqx-sTea6sOL5EdOunJMb0/edit#gid=0.

    Thanks for share!

    Reply
    1. karl.kranich Post author

      Alejandro,
      Good idea! I will try it sometime soon and let people know about it.

      Karl

      Reply
  83. Umair Ayub

    I am getting “400 Bad Request` response: Blank rows cannot be written; use delete instead.” even I have my columns named through “DATA” > “NAMES RANGES”

    I tried with empty cells and also with the content as well

    Reply
    1. karl.kranich Post author

      Umair,
      I have seen that error before, but can’t remember what triggered it.

      Are you trying to add a row to a simple spreadsheet that has a single header row?

      Karl

      Reply
  84. Umair Ayub

    I have figured solution for “400 Bad Request` response: Blank rows cannot be written; use delete instead.” error.

    Suppose your POST body looks like

    $postBody = ”
    . ‘s’
    . ’44’
    . ’44’
    . ”;

    Then in your Google Spreadsheet, YOU MUST HAVE name,email,password are the values in FIRST ROW of your spreadsheet.

    Thanks.

    Reply
    1. karl.kranich Post author

      Great! Also, I have always had to send valid XML as my $postBody. I usually dump the spreadsheet with a GET and then model my POST body on the data that I get back from Google with the GET.

      Karl

      Reply
  85. Bhushan

    Fatal error: Uncaught exception ‘Google_Service_Exception’ with message ‘{ “error”: { “errors”: [ { “domain”: “global”, “reason”: “notFound”, “message”: “File not found: 16T3QhII9htHMnZLPy-1myNSPIgQctvg-zbSFoSqRHrc.”, “locationType”: “parameter”, “location”: “fileId” } ], “code”: 404, “message”: “File not found: 16T3QhII9htHMnZLPy-1myNSPIgQctvg-zbSFoSqRHrc.” } } ‘ in C:\xampp\htdocs\spreadsheet\vendor\google\apiclient\src\Google\Http\REST.php:118 Stack trace: #0 C:\xampp\htdocs\spreadsheet\vendor\google\apiclient\src\Google\Http\REST.php(94): Google_Http_REST::decodeHttpResponse(Object(GuzzleHttp\Psr7\Response), Object(GuzzleHttp\Psr7\Request), ‘Google_Service_…’) #1 [internal function]: Google_Http_REST::doExecute(Object(GuzzleHttp\Client), Object(GuzzleHttp\Psr7\Request), ‘Google_Service_…’) #2 C:\xampp\htdocs\spreadsheet\vendor\google\apiclient\src\Google\Task\Runner.php(181): call_user_func_array(Array, Array) #3 C:\xampp\htdocs\spreadsheet\vendor\google\apiclient\src\Google\Htt in C:\xampp\htdocs\spreadsheet\vendor\google\apiclient\src\Google\Http\REST.php on line 118

    I am faacing above error. I am not understanding what I am doing wrong.

    Reply
    1. karl.kranich Post author

      Bhushan,
      I’m not sure what would cause that. Maybe Google updated their PHP API client again and my code needs to be updated. I’ll try it out in a few hours and see if I can recreate the error.

      Karl

      Reply
    2. karl.kranich Post author

      Bhushan,
      I just downloaded the PHP API client, and it worked with my example code. I did notice that the README.MD file had a slightly different composer command to use to download the dependencies, so I changed it in my blog post. The command is composer require google/apiclient:^2.0

      Karl

      Reply
  86. Stuart

    Thanks for the excellent overview and examples. I’ve successfully been able to query one Google sheet, but moving onto a second (a Google Forms response collection sheet) is proving to be a problem. Any idea whether Sheets generated by Forms are not accessible through the methods you’ve described?

    I get Fatal error: Uncaught exception ‘GuzzleHttp\Exception\ClientException’ with message ‘Client error: `GET https://spreadsheets.google.com/feeds/list/1F-jNZQP92PuhdrGA2poCFhEKpjR_fI86A0iAZDq1yjY/od6/private/full` resulted in a `400 Bad Request` response: Invalid query parameter value for grid_id. ‘ in ../google-api-php-client/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php

    Reply
    1. karl.kranich Post author

      Stuart,
      It shouldn’t be a problem. Try referring to the column as gridid. If that doesn’t work, do a complete GET of the spreadsheet and examine the XML response to see what the column is called in the XML. Google usually converts that column name to all lowercase with no underscores.

      Hope that helps!
      Karl

      Reply
  87. Prashant

    I’m getting below error not sure what is wrong. Help appreciated.

    PHP Fatal error: Uncaught exception ‘DomainException’ with message ‘set the JSON service account credentials using Google_Client::setAuthConfig or set the path to your JSON file with the “GOOGLE_APPLICATION_CREDENTIALS” environment variable and call Google_Client::useApplicationDefaultCredentials to refresh a token with assertion.’ in vendor/google/apiclient/src/Google/Client.php:212
    Stack trace:
    #0 apitest.php(45): Google_Client->fetchAccessTokenWithAssertion()
    #1 {main}
    thrown in vendor/google/apiclient/src/Google/Client.php on line 212

    Reply
    1. karl.kranich Post author

      Prashant,
      Did you save the json credentials file as service-account-credentials.json in the same directory as the script? If not, you’ll need to edit the script so that it can find your credentials.

      Karl

      Reply
  88. Stuart

    Karl,
    Thanks again. You’re right — was able to access a sheet created via Google Forms data collection. I had skipped directly into section 6 of your sample code (parsing table data) based on previous success, and should have first checked the different URLs exposed in section 2. Had to manipulate the final URL a little bit, but it’s given me exactly what I needed. Appreciate your help.
    Stuart

    Reply
  89. Roman

    I try insert data in sheets. section 4.

    Fatal error: Uncaught exception ‘GuzzleHttp\Exception\ClientException’ with message ‘Client error: `POST https://spreadsheets.google.com/feeds/list/1ExAgmmySI6-i2WMqd7rtONWFAZTN_lDhLNYZ5aKHExg/od6/private/full` resulted in a `400 Bad Request` response’ in C:\xampp\htdocs\google_work\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php:107 Stack trace: #0 C:\xampp\htdocs\google_work\vendor\guzzlehttp\guzzle\src\Middleware.php(65): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response)) #1 C:\xampp\htdocs\google_work\vendor\guzzlehttp\promises\src\Promise.php(203): GuzzleHttp\Middleware::GuzzleHttp\{closure}(Object(GuzzleHttp\Psr7\Response)) #2 C:\xampp\htdocs\google_work\vendor\guzzlehttp\promises\src\Promise.php(156): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\Psr7\Response), Array) #3 C:\xampp\htdocs\google_work\vendor\guzzlehttp\promises\src\TaskQueue.php(61): GuzzleHttp\Promise\Promise::GuzzleHttp\Promise\{closure}() #4 C:\xampp\htdocs\go in C:\xampp\htdocs\google_work\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 107

    Reply
    1. karl.kranich Post author

      Hi Roman,
      I’m not sure what would cause that. Have you used sections 2 and 3 to read data successfully?

      Karl

      Reply
  90. Roman

    i check script at php version 5.4 and 5.5. Not work. Only section 4 not work.
    Uncaught exception ‘GuzzleHttp\Exception\ClientException’ with message ‘Client error: `POST https://spreadsheets.google.com/feeds/list/11aWXAEk9YE9Lxatxi6mBbXIFnhs35Bn-uf3xDlbjyc4/od6/private/full` resulted in a `400 Bad Request` response’

    problem begin in this string: $resp = $httpClient->request($method, $url, [‘body’ => $postBody]);

    in file RestExeption.php at line 107
    return new $className($message, $request, $response, $previous, $ctx);

    i test this code in 2 different PC with different versions php. I can’t understant what problem.

    Reply
  91. Roman

    I find where i make mistake.
    In SpreadSheet A1 must be “Gear” and B1 must be “Quantity”.
    Thank You Karl for your answers.

    Reply
    1. karl.kranich Post author

      I’m glad you figured it out! I am at a camp where I don’t have a good Internet connection.

      Karl

      Reply
  92. Matt

    Many thanks for your guide Karl, I’ve been struggling with this all day.

    A quick question for you about response times. I’m attempting to develop a website for a local business, and during testing I’m hosting the website on my laptop. I’ve noticed that there is a small lag when I load locally hosted pages that rely on the Google Sheets API for content. In your opinion, will a Google API cause noticeable lag when the website is hosted properly (eg. by a service like GoDaddy)?

    Reply
    1. karl.kranich Post author

      Matt,
      I’m not sure. I’ve only used web sites to post data to a Google Sheet, and haven’t designed a page that displays data that it gets from a sheet.

      Karl

      Reply
  93. Jerome

    Karl,
    When I run the program, I get this error:
    PHP Fatal error: Call to undefined function checkServiceAccountCredentialsFile() in /var/www/apitest.php on line 23
    Do you know what’s going on?

    Reply
    1. karl.kranich Post author

      Hi Jerome,
      I’m not sure. Is your PHP version at least 5.5? And does the path to autoload.php in line 6 make sense?

      Karl

      Reply
  94. Raj

    Hello Karl,
    Thanks for this script. I am able to get metadata, but when I tried to execute section 2 and so on i am getting error as => Call to undefined method GuzzleHttp\Client::request().

    Please can you help me with that..

    Reply
  95. Raj

    I guess the reason behind this is, I am using google client library 2.0 which includes guzzle of version 5.3.1. I am not pretty sure about it

    Reply
  96. Raj

    Hi Karl,
    I have updated the google library and problem has been solved. thanks for your script. It saved my lot of time

    Reply
    1. karl.kranich Post author

      I have not seen that error, but it sounds like the etag that you’re sending with the update doesn’t match what’s in the sheet. Maybe someone else or another script has already edited the row since you read it?

      Karl

      Reply
  97. Ravinder Sharma

    hi,
    I want to access the list of available google drive spreadsheets by using drive api. But while running apitest.php i faced some errors.

    PHP Fatal error: Uncaught InvalidArgumentException: json key is missing the typ
    e field in C:\xampp\htdocs\googleSheets\vendor\google\auth\src\CredentialsLoader
    .php:129

    Reply
    1. karl.kranich Post author

      Hi Ravinder,
      I’m guessing that the problem is either:
      1. The credentials file is a p12 file, not a json file (try re-downloading it)
      2. The php script isn’t finding the json file

      Reply
  98. Ravin

    Thanks.
    The issue was with the path for. json file.

    Hi Karl,,
    How can I get the list of the available spreadsheets inside my google doc in a php array.

    Reply
  99. John

    Hi Karl, Today I’ve got this error :
    “Fatal error: Uncaught exception ‘GuzzleHttp\Exception\RequestException’ with message ‘cURL error 60: SSL certificate problem, verify that the CA cert is OK. Details: error:14090086:SSL routines:SSL3_GET_SERVER_CERTIFICATE:certificate verify failed (see http://curl.haxx.se/libcurl/c/libcurl-errors.html)'”
    I’ve tried this solution but i didn’t figure out.
    —–
    $http = new GuzzleHttp\Client([
    ‘verify’ => ‘/path/to/cert.pem’
    ];
    $google = new Google_Client;
    $google->setHttpClient($http);
    —–

    Any ideas to figure out ?
    Thanks

    Reply
      1. John

        Hi Karl, thank you for your reply. My system is: Linux webm408.media.ha.ovh.net 4.4.67-ovh-vps-grsec-zfs-classid #1 SMP Thu May 11 18:47:35 CEST 2017 x86_64.
        Thank you for your support.
        John

        Reply
  100. kamel

    Hi karl, thank you for this great post but I am having an error now here it is:
    Fatal error: Uncaught Error: Call to undefined function checkServiceAccountCredentialsFile() in /Applications/XAMPP/xamppfiles/htdocs/test/2.php:23 Stack trace: #0 {main} thrown in /Applications/XAMPP/xamppfiles/htdocs/test/2.php on line 23
    all that I did I put my sheet id and credentials path
    any idea?
    thanks

    Reply
    1. karl.kranich Post author

      Hi Kamel,
      I’m sorry that I haven’t gotten back to you. My best guess is that the path to the credentials file might need to be specified differently. Maybe switch from an absolute path (starting with a slash) to a relative path or vice versa?

      Karl

      Reply
  101. said

    aide svp. je veux réaliser un projet concernant un générateur php qui permet d’afficher le contenu de google sheets et de faire l’actualisation

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *