|
|
(PHP ActiveX) Google Sheets - Append Values to an Existing Spreadsheet
Appends values to an existing Google spreadsheet.
<?php
// This example requires the Chilkat API to have been previously unlocked.
// See Global Unlock Sample for sample code.
// This example uses a previously obtained access token having permission for the
// Google Sheets scope.
// In this example, Get Google Sheets OAuth2 Access Token, the access
// token was saved to a JSON file. This example fetches the access token from the file..
// For versions of Chilkat < 10.0.0, use new COM('Chilkat_9_5_0.Chilkat.JsonObject')
$jsonToken = new COM("Chilkat.JsonObject");
$success = $jsonToken->LoadFile('qa_data/tokens/googleSheets.json');
if ($jsonToken->HasMember('access_token') == 0) {
print 'No access token found.' . "\n";
exit;
}
// For versions of Chilkat < 10.0.0, use new COM('Chilkat_9_5_0.Chilkat.Http')
$http = new COM("Chilkat.Http");
$http->AuthToken = $jsonToken->stringOf('access_token');
// To append values to an existing spreadsheet, our HTTP request body will
// contain JSON in the format of a "ValueRange". For example, the spreadsheet we'll be
// adding to in this example looks like this:
// 
// The JSON ValueRange for the cells in the above spreadsheet is:
// {
// "range": "Sheet1!A1:B5",
// "majorDimension": "ROWS",
// "values": [
// [
// "Item",
// "Cost"
// ],
// [
// "Wheel",
// "$20.50"
// ],
// [
// "Door",
// "$15"
// ],
// [
// "Engine",
// "$100"
// ],
// [
// "Totals",
// "$135.50"
// ]
// ]
// }
// This example will append 6 cells (3 rows / 2 columns).
// We'll be appending the following:
//
// "Paint", "$100"
// "Brakes", "$100"
// "New Total", "$335.50"
//
// The range of cells we'll be appending is A1:B5
// Therefore, the ValueRange JSON we'll be sending in our POST body is:
// {
// "range": "Sheet1!A1:B5",
// "majorDimension": "ROWS",
// "values": [
// [
// "Paint",
// "$100"
// ],
// [
// "Brakes",
// "$100"
// ],
// [
// "New Total",
// "$335.50"
// ]
// ]
// }
// For versions of Chilkat < 10.0.0, use new COM('Chilkat_9_5_0.Chilkat.JsonObject')
$json = new COM("Chilkat.JsonObject");
$json->UpdateString('range','Sheet1!A1:B5');
$json->UpdateString('majorDimension','ROWS');
$json->I = 0;
$json->J = 1;
$json->UpdateString('values[i][j]','Paint');
$json->J = 1;
$json->UpdateString('values[i][j]','$100');
$json->I = 1;
$json->J = 0;
$json->UpdateString('values[i][j]','Brakes');
$json->J = 1;
$json->UpdateString('values[i][j]','$100');
$json->I = 2;
$json->J = 0;
$json->UpdateString('values[i][j]','Totals');
$json->J = 1;
$json->UpdateString('values[i][j]','$335.50');
$json->EmitCompact = 0;
print $json->emit() . "\n";
// Send the POST to:
// https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}:append?valueInputOption=USER_ENTERED
$http->SetUrlVar('spreadsheetId','1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA');
$http->SetUrlVar('range','Sheet1!A1:B5');
// resp is a Chilkat.HttpResponse
$resp = $http->PostJson('https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED',$json->emit());
if ($http->LastMethodSuccess != 1) {
print $http->LastErrorText . "\n";
exit;
}
print 'response status code = ' . $resp->StatusCode . "\n";
print 'response JSON = ' . $resp->BodyStr . "\n";
// Sample output:
//
// response status code = 200
// response JSON = {
// "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
// "tableRange": "Sheet1!A1:B5",
// "updates": {
// "spreadsheetId": "1_SO2L-Y6nCayNpNppJLF0r9yHB2UnaCleGCKeE4O0SA",
// "updatedRange": "Sheet1!A6:B8",
// "updatedRows": 3,
// "updatedColumns": 2,
// "updatedCells": 6
// }
// }
//
// Our Google Spreadsheet now looks like this:
// 
?>
|
