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
- Browse to https://console.developers.google.com
- Click “Create Projectâ€, make up a name, and click “Create”
- 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.
- Click “Credentials†in the left column.
- Click “Add credentials -> Service account
- Leave JSON selected and click “Create”
- Save the generated key file in your project directory as service-account-credentials.json
- Note the email address of the service account
Create a Spreadsheet and share with the Service Account
- Create a Google Spreadsheet to play with
- Share the Google sheet with the email address from above (give edit permissions).
Get the PHP API Client
- Create a directory on your machine for your project
- 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
- Get the script from https://gist.github.com/karlkranich/de225928665dc6b83667
- Fill in the File ID of your spreadsheet from the URL in Chrome (see image below)
- 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!
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
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
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,
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
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?
Anand,
Take a look at the “Update a List Row” and “Delete a List Row” sections of this page: https://developers.google.com/google-apps/spreadsheets/data
I think you can take the HTTP examples and adapt them to PHP.
Karl
Thank you very much, I will check it.
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
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
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
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.
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
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
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
‘;
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 ?
The code in section 3 of my gist with the URL https://spreadsheets.google.com/feeds/worksheets/$fileId/private/full, you should get back a list of URLs to edit each of the worksheets. You need to parse that and find the link for the worksheet you need.
Karl
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.
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
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
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
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.
i got spreadsheet data but i won’t to add form data in spreadsheet
pls give me code to add data in spreadsheet
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
Pingback: Sheets API access with PHP part 2: Editing Rows - Karl's Place
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
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
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
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!
Good job, Spurgeon!
Mismatch: etags = [“A0cGSXs_fyt7ImA9XRJTFkQ.”], version = [59did4bk9j]
i get this when update a row
thanks,
i get ans from
http://karl.kranich.org/2015/08/21/sheets-api-access-with-php-part-2-editing-rows/
thanks again
You’re welcome!
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
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
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”?
No — if you click on the email address on that screen, you’ll get to a detail page that shows the client ID.
Thank you very much for sharing your knowledge. This tutorial saved me a lot of time.
You’re welcome! Check out part 2 also.
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
I just loop through the results. But you can also send a search query for specific rows. See the section called “Send a structured query for rows” on this page: https://developers.google.com/google-apps/spreadsheets/data
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.
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
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
Yang,
You need to run section 2 of my example code and then look for a URL of that format in the output. When I did this, I found that “key” corresponds to what I’m calling fileID, and “worksheetId” is “od6” for my first worksheet. So, the cell-based feed for my first worksheet is https://spreadsheets.google.com/feeds/cells/15byt2tfdaHmaEpdwd4UYGWs70Eaej8edkQ2dS8x4mIk/od6/private/full
Karl
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
Yang,
You need to look further down in the output of section 2. For example, I have Sheet1 and Sheet2. My list feed URL for Sheet2 is https://spreadsheets.google.com/feeds/list/15byt2tfdaHmaEpdwd4UYGWs70Eaej8edkQ2dS8x4mIk/od6/private/full and my list feed URL for Sheet2 is https://spreadsheets.google.com/feeds/list/15byt2tfdaHmaEpdwd4UYGWs70Eaej8edkQ2dS8x4mIk/of5m3rz/private/full
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
Hardy,
Be sure that you’re connecting to https (secure) URLs. Which section of the code is breaking?
Karl
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.
Hi,
I am getting following output.
Service Account Access
302
Moved Temporarily
The document has moved here.
What’s wrong ?, pls help
Prajakta,
I’m sorry, I have not seen that error from the API. If there is any more information in the response, you could email me at karl.kranich@gmail.com
Karl
Just wanted to say thank you, this works perfectly! No more headaches with google api’s confusing websites, woohoo| \o/
Cool — glad I could help!
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…
Thanks for the feedback and tips, Andrew! I’ve been wanting to look into JSON handling of this stuff.
Karl
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);
}
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
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.
You can always install it through composer following the instructions here: https://developers.google.com/api-client-library/php/start/installation
That’s how I did it last week and it worked just fine.
Thank you, Sofia!
I just updated the instructions for the new installation method and HTTP library. Google made major changes to the PHP API Client!
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
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
Just wanted to drop this here also: https://developers.google.com/google-apps/spreadsheets/?hl=en#adding_a_list_row It does mention an empty row, but google apps script would probably be your best bet, as karl.kranich mentioned.
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
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
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
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
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
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.
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.
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.
Found it, it’s in examples/templates/base.php. Stupid of me.
Thank you.
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
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
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.
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.
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.
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
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
Graycie,
I’m guessing that Guzzle isn’t able to verify Google’s certificate. Check out this page: http://stackoverflow.com/questions/24923604/guzzle-curl-error-60-ssl-unable-to-get-local-issuer.
I would temporarily disable certificate verification just to see if that fixes it. If it does, I’d then try to figure out how to tell Guzzle about Google’s certificate chain.
Karl
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.
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.
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
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
Thanks Karl,
I am looking into it because I recieved the same error when trying to build on executable API
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.
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
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
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
Bernie, after the holidays I will try to reproduce your error on a Windows 10 machine. On my Mac, it all just works.
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
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
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.
Not sure what is going on. I would try recreating your service account in the developer console.
hi,
i want to put underscore to <gsx: header like oreder_id. i tried but error come. can you please any idea ?
Sameer,
You probably don’t need the underscore – I would try without it. If that doesn’t work, try _
Karl
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.
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 ;
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
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
Do we want to use ?
There are codes that you can use in XML to represent special characters like underscores. The codes start with & and end with ;
Please see http://www.dvteclipse.com/documentation/svlinter/How_to_use_special_characters_in_XML.3F.html and other pages like it.
Karl
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
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
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.
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
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.
Karl,
Do you have any update for my problem regarding underscore?
Thanks
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
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
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
I will look into this as soon as I can! (Hopefully today, possibly tomorrow)
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
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!
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
Dear Karl,
Now i want to delete all records in googlespreadsheet. please can you explain?
Thanks.
Sameer,
Once you retrieve the table data, you can loop through it and issue delete commands as described here: https://developers.google.com/google-apps/spreadsheets/data#delete_a_list_row
You could also use the Drive API to delete the whole file and create a new, empty one.
Karl
Thanks Karl! Great work!
Thanks Karl for your Quick Reply.
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
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
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.
Hi Gopal,
Hopefully I will have some time to try it this week. I’ll let you know if I figure it out.
Karl
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.
I haven’t seen that, but you can install without composer. See the section that starts with “If you abhor using composer” on https://github.com/google/google-api-php-client
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
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
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
Hi again! This i solved now, so you can ignore the above question. It worked when i switched to Chrome…
Thanks 🙂
BR
Catherine
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.
Akash,
You can parse the data with regular expressions, or use an XML library like I illustrate here: http://karl.kranich.org/2016/01/10/parsing-google-sheets-data-with-phps-simplexml/
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?
Hi Louise,
Were you able to install the dependencies with composer?
Karl
Yes I think so! Is there a way to check if I did?
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.
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
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
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
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!
Thanks for the compliments! And a good reminder that I should probably mention delete…
Karl
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)
Piter,
I just figured out how to get past the SSL certificate problem on Windows. Please see my latest blog post at http://karl.kranich.org/2016/02/21/sheets-api-access-from-windows-curl-error-60/
Karl
Hey Karl! You helps me a lot. Thank you.
You should change some code
https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full into https://spreadsheets.google.com/feeds/list/$fileId/1/private/full
(1 for the first/default worksheet, 2 for the second worksheet)
od6 doesn’t work anymore
Thank you, Maks! I will do that asap.
Karl
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
Hi Karl,
I have followed all Steps provided by you. When I run a script and debug it, I got an Error from Library Class GuzzleHttp/Exception/RequestException with message “cURL 60: see http://curl.haxx.se/libcurl/libcurl-errors.html”
Can you please help me to move ahead?
Thanks.
Hi Karl,
Sorry, I have posted a wrong URL. Please look @ https://curl.haxx.se/libcurl/c/libcurl-errors.html
Thanks,
Rob
Rob,
Please check out http://karl.kranich.org/2016/02/21/sheets-api-access-from-windows-curl-error-60/
Karl
git clone https://github.com/google/google-api-php-client.git google-api-php-client
i am not sure about what to do I have to upload to my website the whole folder or only the file autoload.php
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
This page is a godsend! Thank you so much.
You’re welcome, Greg!
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!!
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
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
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
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
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
Karl, I have tried \n but it simply converts to text, char(10) would probably work but how to insert it?
Sorry, I don’t know. Let me know if you figure it out!
Ok, thank you for your help
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 !!
If that’s how it came from Google, then it should work. I’m sorry, I don’t know what to try next.
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
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
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?
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
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
Excellent! Thanks for letting me know what worked.
Karl
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
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
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.
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
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
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
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’;
…
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
Thank you a lot, Karl.
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.
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
Notice: Undefined index: client_id in C:\wamp\www\google2\google-api-php-client\src\Google\Client.php on line 835
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
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.
Alex,
When I google that, one thing that comes up is that you need at least PHP 5.5 (try php -v).
Karl
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
Alex,
Well, that is at least more information. I have written about getting around cURL error 60 here: http://karl.kranich.org/2016/02/21/sheets-api-access-from-windows-curl-error-60/
Karl
Hi Karl,
Thanks for sharing this useful article with me, now Google Sheet API work fine for me.
Alex
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
Very nice, Greg! Thanks for sharing the final product.
Karl
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
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
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
‘;
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
Hello,
Karl can i add new worksheet by the if is it possible please give me some suggestion
Hi Akash,
I have not done that, but it looks like the API supports it. Please look at this page:https://developers.google.com/google-apps/spreadsheets/worksheets#add_a_worksheet and look at the HTTP example. You should be able to adapt my example code to make that work.
Karl
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
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
Hello
How do i insert a new row after specific number of row for example if my spreadsheet is full till 45 row and i want to insert a new row on row number 56 . How do i do that i have followed this https://developers.google.com/google-apps/spreadsheets/data#fetch_specific_rows_or_columns but its not giving me any result . It is just adds a new row wherever it finds first empty row .
Thanks in advance
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
Hey man,
Thanks a lot. It works like a charm!
Glad it helped!
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.
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
God Bless you abundantly Brother Karl!
This is a great blessing to us!
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
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
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.
Quentin,
Stan just pointed out that these instructions don’t work with PHP versions before 5.5. Maybe that’s your issue?
Karl
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.
Thank you, Stan! I probably only tried with PHP 5.5 and didn’t even realize that.
Karl
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
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 !
Excellent! good work.
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!
Alejandro,
Good idea! I will try it sometime soon and let people know about it.
Karl
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
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
This is the spreadsheet
https://docs.google.com/spreadsheets/d/1iglsiI4Xv8QZ-FDTMSAIP0H5DGWdFlIYToL6bmqxmVQ/edit?usp=sharing
And this is POST body
$postBody = ”
. ‘s44’
. ”;
Can you communicate via Skype? mani619cash is my skype. Your help will be really appreciated because I really need this done.
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.
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
Hi
Can we add multiple rows using single POST request?
Hi,
can we add multiple rows using single POST request?
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.
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
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
HI,
I have updated my dependacies. But still I am facing a same issue.
I’m sorry, I don’t know what would cause that error.
Karl
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
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
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
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
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
Excellent! Glad to be of help.
Karl
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
Hi Roman,
I’m not sure what would cause that. Have you used sections 2 and 3 to read data successfully?
Karl
yes. i checked sections 2 and 3. Work ideally. Only 4 section errors.
i use xampp. after 3 hours i upload files to web server. Errors is not gone
Do you mean that the errors are gone? Maybe it was the version of PHP in XAMPP.
still does not work. What version php i must use?
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.
I find where i make mistake.
In SpreadSheet A1 must be “Gear” and B1 must be “Quantity”.
Thank You Karl for your answers.
I’m glad you figured it out! I am at a camp where I don’t have a good Internet connection.
Karl
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)?
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
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?
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
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..
Hello Raj,
Is your PHP version at least 5.5?
Its 5.6
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
Hi Karl,
I have updated the google library and problem has been solved. thanks for your script. It saved my lot of time
Raj,
That’s great to hear — good work!
Karl
[Hi ] karl.kranich
Client error: `PUT https://spreadsheets.google.com/feeds/list/1Lf7oXPoyjmRCY7FVJ6AeSvgEfY7HPeesnOWbfh4Tubc/od6/private/full/cre1l` resulted in a `412 Precondition Failed` response: Mismatch: etags = ["NQ8SVE8fDSt7ImA."], version = [hjc3hlg]
please help me!
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
Very nice work,
Working perfectly.
Thanks a ton.
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
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
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.
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
Hi John,
Sorry, I’ve been away. What OS are you using?
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
Thank you for the update about v4.
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
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
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
Hello said,
Have you looked at the Google documentation at https://developers.google.com/sheets/api/quickstart/php ? They provide some PHP example code now.
Karl
I have hyperlinks in my Spreadsheet cells; how can I access those URLs?
Hi Lance,
You should be able to just read the values of the cells that contain the URLs. What happens when you do that?
https://developers.google.com/sheets/api/guides/values#php
Karl