Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
(obsolete due to Google changes) PHP code to use the Google Spreadsheets API with an OAuth Service Account. For more info, see http://karl.kranich.org/google-sheets-api-php
<?php
// Section 5: edit a row
// You'll need to get the etag and row ID, and send a PUT request to the edit URL
$rowid = 'cre1l'; // got this and the etag from the table data output from section 3
$etag = 'NQ8VCRBLVCt7ImA.';
$url = "https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full/$rowid";
$method = 'PUT';
$headers = ["Authorization" => "Bearer $accessToken", 'Content-Type' => 'application/atom+xml', 'GData-Version' => '3.0'];
$postBody = "<entry xmlns=\"http://www.w3.org/2005/Atom\" xmlns:gsx=\"http://schemas.google.com/spreadsheets/2006/extended\" xmlns:gd=\"http://schemas.google.com/g/2005\" gd:etag='&quot;$etag&quot;'><id>https://spreadsheets.google.com/feeds/list/$fileid/od6/$rowid</id><gsx:gear>phones</gsx:gear><gsx:quantity>6</gsx:quantity></entry>";
$req = new Google_Http_Request($url, $method, $headers, $postBody);
$curl = new Google_IO_Curl($client);
$results = $curl->executeRequest($req);
echo "$results[2]\n\n";
echo "$results[0]\n";
<?php
session_start();
include_once "google-api-php-client/examples/templates/base.php";
/************************************************
Make an API request authenticated with a service
account.
************************************************/
require_once realpath(dirname(__FILE__) . '/google-api-php-client/src/Google/autoload.php');
$client_id = 'get your own from google developer console';
$service_account_name = 'get your own from google developer console'; // email address
$key_file_location = 'download your own.p12'; //key.p12
echo pageHeader("Service Account Access");
if (strpos($client_id, "googleusercontent") == false
|| !strlen($service_account_name)
|| !strlen($key_file_location)) {
echo missingServiceAccountDetailsWarning();
exit;
}
$client = new Google_Client();
$client->setApplicationName("Sheets API Testing");
$service = new Google_Service_Drive($client);
/************************************************
If we have an access token, we can carry on.
Otherwise, we'll get one with the help of an
assertion credential. In other examples the list
of scopes was managed by the Client, but here
we have to list them manually. We also supply
the service account
************************************************/
if (isset($_SESSION['service_token'])) {
$client->setAccessToken($_SESSION['service_token']);
}
$key = file_get_contents($key_file_location);
$cred = new Google_Auth_AssertionCredentials(
$service_account_name,
array('https://www.googleapis.com/auth/drive','https://spreadsheets.google.com/feeds'),
$key
);
$client->setAssertionCredentials($cred);
if ($client->getAuth()->isAccessTokenExpired()) {
$client->getAuth()->refreshTokenWithAssertion($cred);
}
$_SESSION['service_token'] = $client->getAccessToken();
// Get access token for spreadsheets API calls
$resultArray = json_decode($_SESSION['service_token']);
$accessToken = $resultArray->access_token;
// The file ID was copied from a URL while editing the sheet in Chrome
$fileId = '15byt2tfdaHmaEpdwd4UYGWs70Eaej8edkQ2dS8x4mIk';
// 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
// $results = $service->files->get($fileId);
// var_dump($results);
// Section 2: Uncomment to get list of worksheets
// $url = "https://spreadsheets.google.com/feeds/worksheets/$fileId/private/full";
// $method = 'GET';
// $headers = ["Authorization" => "Bearer $accessToken"];
// $req = new Google_Http_Request($url, $method, $headers);
// $curl = new Google_IO_Curl($client);
// $results = $curl->executeRequest($req);
// echo "$results[2]\n\n";
// echo "$results[0]\n";
// Section 3: Uncomment to get the table data
// $url = "https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full";
// $method = 'GET';
// $headers = ["Authorization" => "Bearer $accessToken", "GData-Version" => "3.0"];
// $req = new Google_Http_Request($url, $method, $headers);
// $curl = new Google_IO_Curl($client);
// $results = $curl->executeRequest($req);
// echo "$results[2]\n\n";
// echo "$results[0]\n";
// Section 4: Uncomment to add a row to the sheet
$url = "https://spreadsheets.google.com/feeds/list/$fileId/od6/private/full";
$method = 'POST';
$headers = ["Authorization" => "Bearer $accessToken", 'Content-Type' => 'application/atom+xml'];
$postBody = '<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended"><gsx:gear>more gear</gsx:gear><gsx:quantity>99</gsx:quantity></entry>';
$req = new Google_Http_Request($url, $method, $headers, $postBody);
$curl = new Google_IO_Curl($client);
$results = $curl->executeRequest($req);
echo "$results[2]\n\n";
echo "$results[0]\n";
@jamenlang

This comment has been minimized.

Copy link

jamenlang commented Jun 4, 2015

that whole lower case no spaces in the column name really threw me off for a while, thank you for posting this.

@karlkranich

This comment has been minimized.

Copy link
Owner Author

karlkranich commented Jul 18, 2015

I just updated the headers in section 3 so that etag values are returned.

@noneed52

This comment has been minimized.

Copy link

noneed52 commented Apr 16, 2016

Hi, Karl. Thank you for the great tutorial.
I got really far with this, but I can't figure out how to add a row at specific row and column.
Can you please help?....

Thank you!

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.