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!