Last active
June 2, 2020 12:26
-
-
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/
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?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" => "[email protected]"]); | |
| // 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='"$etag"'><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"; |
Author
azzynugraha,
I recently published a blog post at karl.kranich.org that shows how to get past that cURL error 60.
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
Hi Karl
I have tried your code above but there is errors with guzzle like this, I just replaced old ID with my ID :
What should I do? I'm new with this.
Thank you