Skip to content

Instantly share code, notes, and snippets.

@karlkranich
Last active June 2, 2020 12:26
Show Gist options
  • Save karlkranich/afa39e3d778455b38c38 to your computer and use it in GitHub Desktop.
Save karlkranich/afa39e3d778455b38c38 to your computer and use it in GitHub Desktop.
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
Copy link

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
Copy link
Author

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

@caspahouzer
Copy link

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