(Node.js) Google Sheets - Append Values to an Existing Spreadsheet

Appends values to an existing Google spreadsheet.

Install Chilkat for Node.js and Electron using npm at Chilkat npm packages for Node.js Chilkat npm packages for Electron on Windows, Linux, MacOSX, and ARM

var os = require('os');
if (os.platform() == 'win32') {  
    if (os.arch() == 'ia32') {
        var chilkat = require('@chilkat/ck-node21-win-ia32');
    } else {
        var chilkat = require('@chilkat/ck-node21-win64'); 
    }
} else if (os.platform() == 'linux') {
    if (os.arch() == 'arm') {
        var chilkat = require('@chilkat/ck-node21-arm');
    } else if (os.arch() == 'x86') {
        var chilkat = require('@chilkat/ck-node21-linux32');
    } else {
        var chilkat = require('@chilkat/ck-node21-linux64');
    }
} else if (os.platform() == 'darwin') {
    if (os.arch() == 'arm64') {
        var chilkat = require('@chilkat/ck-node21-mac-m1');
    } else {
        var chilkat = require('@chilkat/ck-node21-macosx');
    }
}

function chilkatExample() {

    // 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..
    var jsonToken = new chilkat.JsonObject();
    var success = jsonToken.LoadFile("qa_data/tokens/googleSheets.json");
    if (jsonToken.HasMember("access_token") == false) {
        console.log("No access token found.");
        return;
    }

    var http = new 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"
    //     ]
    //   ]
    // }

    var json = new 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 = false;
    console.log(json.Emit());

    // 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: HttpResponse
    var resp = http.PostJson("https://sheets.googleapis.com/v4/spreadsheets/{$spreadsheetId}/values/{$range}:append?valueInputOption=USER_ENTERED",json.Emit());
    if (http.LastMethodSuccess !== true) {
        console.log(http.LastErrorText);
        return;
    }

    console.log("response status code = " + resp.StatusCode);
    console.log("response JSON = " + resp.BodyStr);

    // 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:
    // 

}

chilkatExample();