How to Insert data in Google Spreadsheet using PHP?

Source: http://karl.kranich.org/2015/04/16/google-sheets-api-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.

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/cbbfbdf5825217b2976f

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 “API” 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. In the OAuth section, click “Create new Client ID”
6. Choose “Service Account” and click “Create Client ID”
7. Save the generated json file
8. Click “Generate new P12 key”, save the file, and note the password on the screen.
9. Note the Client ID and Email address – we’ll use them later

Create a Spreadsheet and share with the Service Account

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

Get the PHP Drive API Client
git clone https://github.com/google/google-api-php-client.git google-api-php-client

1. Modify and run sheets-api-test.php
2. Get the script from https://gist.github.com/karlkranich/cbbfbdf5825217b2976f
3. Fill in the Client ID and Email address from the Google Developer Console
4. Fill in the File ID of your spreadsheet from the URL in Chrome (see image below)
5. Uncomment various sections of the script to see how they work
6. Let me know if any of this doesn’t work or doesn’t make sense!

1Loading...

Source: http://karl.kranich.org/2015/04/16/google-sheets-api-php/