Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Manipulate Google Sheets with the PHP API client. See http://karl.kranich.org/2016/01/16/sheets-api-access-with-php-part-4-editing-cells/
<?php
require_once realpath(dirname(__FILE__) . '/vendor/autoload.php');
include_once "google-api-php-client/examples/templates/base.php";
$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://spreadsheets.google.com/feeds']);
// Some people have reported needing to use the following setAuthConfig command
// which requires the email address of your service account (you can get that from the json file)
// $client->setAuthConfig(["type" => "service_account", "client_email" => "my-service-account@developer.gserviceaccount.com"]);
// The file ID was copied from a URL while editing the sheet in Chrome
// Google calls this "key" in the Sheets API HTTP protocol documentation
$fileId = '15byt2tfdaHmaEpdwd4UYGWs70Eaej8edkQ2dS8x4mIk';
$tokenArray = $client->fetchAccessTokenWithAssertion();
$accessToken = $tokenArray["access_token"];
// Worksheet list section: Uncomment to get list of worksheets
$url = "https://spreadsheets.google.com/feeds/worksheets/$fileId/private/full";
$method = 'GET';
$headers = ["Authorization" => "Bearer $accessToken"];
$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";
$worksheetId = 'od6'; // First worksheet in Karl's tests. Would have gotten this from the output of the previous section
// Cell list section: Uncomment to get the table data as a cell-based feed
// $url = "https://spreadsheets.google.com/feeds/cells/$fileId/$worksheetId/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";
// Cell data XML section: Uncomment to parse table data with SimpleXML
// $url = "https://spreadsheets.google.com/feeds/cells/$fileId/$worksheetId/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();
// $tableXML = simplexml_load_string($body);
// foreach ($tableXML->entry as $entry) {
// $location = $entry->title;
// $etag = $entry->attributes('gd', TRUE);
// $data = $entry->content;
// echo "Cell $location -> $data (etag = $etag)\n";
// }
// Cell subset section: Uncomment to get a specific set of cells
// $url = "https://spreadsheets.google.com/feeds/cells/$fileId/$worksheetId/private/full?min-row=2&max-row=3&min-col=1&max-col=2";
// $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();
// $tableXML = simplexml_load_string($body);
// foreach ($tableXML->entry as $entry) {
// $location = $entry->title;
// $etag = $entry->attributes('gd', TRUE);
// $data = $entry->content;
// echo "Cell $location -> $data (etag = $etag)\n";
// }
// Modify cell section: Uncomment to edit a cell
// You'll need to send a PUT request to the edit URL, using the etag found with a GET request
// $row = '4'; // R1C1 notation for cell B4
// $col = '2';
// $cell = 'R' . $row . 'C' . $col;
// $newValue = '8';
// $etag = 'ImBtWlJXUCt7';
// $url = "https://spreadsheets.google.com/feeds/cells/$fileId/$worksheetId/private/full/$cell";
// $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:gs='http://schemas.google.com/spreadsheets/2006' xmlns:gd='http://schemas.google.com/g/2005' " .
// "gd:etag='&quot;$etag&quot;'><id>https://spreadsheets.google.com/feeds/cells/$fileId/$worksheetId/private/full/$cell</id>" .
// "<link rel='edit' type='application/atom+xml' href='https://spreadsheets.google.com/feeds/cells/$fileId/$worksheetId/private/full/$cell'/>" .
// "<gs:cell row='$row' col='$col' inputValue='$newValue'/></entry>";
// $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";
@azzynugraha

This comment has been minimized.

Copy link

azzynugraha commented Jan 23, 2016

Hi Karl
I have tried your code above but there is errors with guzzle like this, I just replaced old ID with my ID :
error

What should I do? I'm new with this.
Thank you

@karlkranich

This comment has been minimized.

Copy link
Owner Author

karlkranich commented Mar 1, 2016

azzynugraha,
I recently published a blog post at karl.kranich.org that shows how to get past that cURL error 60.

@caspahouzer

This comment has been minimized.

Copy link

caspahouzer commented Sep 1, 2016

I just uncommented cell list section and got this error

Fatal error: Uncaught exception 'GuzzleHttp\Exception\ClientException' with message 'Client error:GET https://spreadsheets.google.com/feeds/cells/1zkACyg-RE9k6BgQeF7IyLHshb3xyYk2OXBjGih-4TQ4/od6/private/full` resulted in a 400 Bad Request response: Ungültiges Abfrageargument für grid_id. ' in /Applications/MAMP/htdocs/egis-google-docs/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php:107 Stack trace: #0 /Applications/MAMP/htdocs/egis-google-docs/vendor/guzzlehttp/guzzle/src/Middleware.php(65): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response)) #1 /Applications/MAMP/htdocs/egis-google-docs/vendor/guzzlehttp/promises/src/Promise.php(203): GuzzleHttp\Middleware::GuzzleHttp{closure}(Object(GuzzleHttp\Psr7\Response)) #2 /Applications/MAMP/htdocs/egis-google-docs/vendor/guzzlehttp/promises/src/Promise.php(156): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\Psr7\Response), Array) #3 /Applications/MAMP/htdocs/egis-google-docs/vendor/guzz in /Applications/MAMP/htdocs/egis-google-docs/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php on line 107`

Any hint what is wrong there?

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.