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

Appends values to an existing Google spreadsheet.

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

use chilkat();

#  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 is a HttpResponse
$resp = $http->PostJson("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",$json->emit());
if ($http->get_LastMethodSuccess() != 1) {
    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:
#