(Perl) Google Sheets - Append Values to an Existing Spreadsheet

Appends values to an existing Google spreadsheet.

Note: This example requires Chilkat v11.0.0 or greater.

Chilkat Perl Downloads Perl Module for Windows, MacOS, Linux, Alpine Linux

use chilkat();

$success = 0;

# 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..
$jsonToken = chilkat::CkJsonObject->new();
$success = $jsonToken->LoadFile("qa_data/tokens/googleSheets.json");
if ($jsonToken->HasMember("access_token") == 0) {
    print "No access token found." . "\r\n";
    exit;
}

$http = chilkat::CkHttp->new();
$http->put_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"
#     ]
#   ]
# }

$json = chilkat::CkJsonObject->new();
$json->UpdateString("range","Sheet1!A1:B5");
$json->UpdateString("majorDimension","ROWS");

$json->put_I(0);
$json->put_J(1);
$json->UpdateString("values[i][j]","Paint");
$json->put_J(1);
$json->UpdateString("values[i][j]","$100");

$json->put_I(1);
$json->put_J(0);
$json->UpdateString("values[i][j]","Brakes");
$json->put_J(1);
$json->UpdateString("values[i][j]","$100");

$json->put_I(2);
$json->put_J(0);
$json->UpdateString("values[i][j]","Totals");
$json->put_J(1);
$json->UpdateString("values[i][j]","$335.50");

$json->put_EmitCompact(0);
print $json->emit() . "\r\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 = chilkat::CkHttpResponse->new();
$success = $http->HttpJson("POST","https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",$json,"application/json",$resp);
if ($success == 0) {
    print $http->lastErrorText() . "\r\n";
    exit;
}

print "response status code = " . $resp->get_StatusCode() . "\r\n";
print "response JSON = " . $resp->bodyStr() . "\r\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:
#