How to use spreadsheets.values.batchUpdate with Google Cloud Functions and NodeJS and 'googleapis' module

Question

I'm trying to use the Google Sheets API spreadsheets.values.batchUpdate function in the same way as described here. This example uses Google Apps Script. I would like to do it using Google Cloud Functions instead.

The spreadsheets.values.batchUpdate API documentation is available. Note that it is different from the spreadsheets.batchUpdate , which has a different syntax. I'm interested in using the former.

I would like to do the same using the environment: Google Cloud Functions + nodejs8 + googleapis module.

This is the example code for Google Apps Script from the link above:

function updateGoogleSheet(spreadsheetId) {

  /* Written by Amit Agarwal */
  /* Web: ctrlq.org  Email: amit@labnol.org */
  
  var data = [
    { 
      range: "Sheet1!A1",   // Update single cell
      values: [
        ["A1"]
      ]
    },
    {
      range: "Sheet1!B1:B3", // Update a column
      values: [
        ["B1"],["B2"],["B3"]
      ]
    },
    {
      range: "Sheet1!C1:E1", // Update a row
      values: [
        ["C1","D1","E1"]
      ]
    },
    {
      range: "Sheet1!F1:H2", // Update a 2d range
      values: [
        ["F1", "F2"],
        ["H1", "H2"]
      ]
    }];
  
  var resource = {
    valueInputOption: "USER_ENTERED",
    data: data
  };
  
  Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
  
}

.

This is the above code in the new environment:

const {google} = require('googleapis');
const sheets = google.sheets({version: 'v4'});
const auth = new google.auth.JWT (credentials.client_email, null, credentials.private_key, ['https://www.googleapis.com/auth/spreadsheets']);

function updateGoogleSheet (spreadsheetId) {

  /* Written by Amit Agarwal */
  /* Web: ctrlq.org  Email: amit@labnol.org */

  var data = [
    { 
      range: "Sheet1!A1",   // Update single cell
      values: [
    ["A1"]
      ]
    },
    {
      range: "Sheet1!B1:B3", // Update a column
      values: [
    ["B1"],["B2"],["B3"]
      ]
    },
    {
      range: "Sheet1!C1:E1", // Update a row
      values: [
    ["C1","D1","E1"]
      ]
    },
    {
      range: "Sheet1!F1:H2", // Update a 2d range
      values: [
    ["F1", "F2"],
    ["H1", "H2"]
      ]
    }];

  var resource = {
    spreadsheetId: spreadsheetId,
    auth: auth,
    valueInputOption: "USER_ENTERED",
    data: data
  };

  sheets.spreadsheets.values.batchUpdate (resource);

}

However this gives the following error:

Error: Invalid JSON payload received. Unknown name "data[range]": Cannot bind query parameter. Field 'data[range]' could not be found in request message. Invalid JSON payload received. Unknown name "data[values]": Cannot bind query parameter. Field 'data[values]' could not be found in request message. at Gaxios.request (/srv/node_modules/gaxios/build/src/gaxios.js:70:23) at <anonymous> at process._tickDomainCallback (internal/process/next_tick.js:229:7) response: { config: [Object], data: [Object], headers: [Object], status: 400, statusText: 'Bad Request' }, config: { url: 'https://sheets.googleapis.com/v4/spreadsheets/......spreadsheetId....../values:batchUpdate?valueInputOption=USER_ENTERED&data%5Brange%5D=Sheet1%21A1&data%5Bvalues%5D=A1&data%5Brange%5D=Sheet1%21B1%3AB3&data%5Bvalues%5D=B1&data%5Bvalues%5D=B2&data%5Bvalues%5D=B3&data%5Brange%5D=Sheet1%21C1%3AE1&data%5Bvalues%5D=C1&data%5Bvalues%5D=D1&data%5Bvalues%5D=E1&data%5Brange%5D=Sheet1%21F1%3AH2&data%5Bvalues%5D=F1&data%5Bvalues%5D=F2&data%5Bvalues%5D=H1&data%5Bvalues%5D=H2', method: 'POST', paramsSerializer: [Function], headers: [Object], params: [Object], validateStatus: [Function], retry: true, responseType: 'json', retryConfig: [Object] }, code: 400, errors: [ [Object] ]

My question is, how to format the resource parameter to spreadsheets.values.batchUpdate () to make the request succeed?

I couldn't find any documentation on this, but it seems possible from the original code example above.

Answer 1

• You want to put the values using the method of spreadsheets.values.batchUpdate of Sheets API.
• You want to achieve this using googleapis with Node.js.
• You have already been able to put and get values for the Spreadsheet using Sheets API.

If my understanding is correct, how about this modification?

From:

var resource = {
  spreadsheetId: spreadsheetId,
  auth: auth,
  valueInputOption: "USER_ENTERED",
  data: data
};

To:

var resource = {
  spreadsheetId: spreadsheetId,
  auth: auth,
  resource: { data: data, valueInputOption: "USER_ENTERED" }
};

Note:

• If an error occurs, please confirm the following points.
  • The Spreadsheet is shared with the Service account.
  • Sheets API is enabled.
• I could confirm that the script of above modification worked with googleapis of v42.0.0(latest) at Node.js v8.0.0 and v12.9.0(latest).

Reference:

• spreadsheets.values.batchUpdate

If this didn't resolve your issue, I apologize.

Answer 2

UPDATE

As per the comments, neither the params or requestBody properties appear viable (even though both are documented on the home page of the github repo). From the samples I've seen, the requestBody parameter is always paired with a service that is initialized with oauth credentials as follows:

const sheets = google.sheets({"version":"v4", "auth":auth});

Where the request no longer has the auth property:

var resource = {
    "spreadsheetId": spreadsheetId,
    "requestBody": {
        "validInputOption":"USER_ENTERED",
        "data":data
    }
}

sheets.spreadsheets.values.batchUpdate (resource);

The above should work. If not, users will at least be aware of the issue.

---

̶T̶h̶e̶ ̶[̶g̶o̶o̶g̶l̶e̶ ̶a̶p̶i̶ ̶n̶o̶d̶e̶j̶s̶ ̶c̶l̶i̶e̶n̶t̶]̶[̶1̶]̶ ̶w̶o̶r̶k̶s̶ ̶a̶ ̶b̶i̶t̶ ̶d̶i̶f̶f̶e̶r̶e̶n̶t̶l̶y̶.̶ ̶[̶A̶c̶c̶o̶r̶d̶i̶n̶g̶ ̶t̶o̶ ̶t̶h̶e̶ ̶d̶o̶c̶u̶m̶e̶n̶t̶a̶t̶i̶o̶n̶]̶[̶2̶]̶ ̶y̶o̶u̶ ̶h̶a̶v̶e̶ ̶t̶o̶ ̶s̶p̶e̶c̶i̶f̶y̶ ̶a̶ ̶̶r̶e̶q̶u̶e̶s̶t̶B̶o̶d̶y̶̶ ̶p̶a̶r̶a̶m̶e̶t̶e̶r̶ ̶a̶s̶ ̶f̶o̶l̶l̶o̶w̶s̶:̶

̶̶ ̶ ̶ ̶ ̶v̶a̶r̶ ̶r̶e̶s̶o̶u̶r̶c̶e̶ ̶=̶ ̶{̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶s̶p̶r̶e̶a̶d̶S̶h̶e̶e̶t̶I̶d̶:̶ ̶s̶p̶r̶e̶a̶d̶S̶h̶e̶e̶t̶I̶d̶,̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶a̶u̶t̶h̶:̶ ̶a̶u̶t̶h̶,̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶r̶e̶q̶u̶e̶s̶t̶B̶o̶d̶y̶:̶{̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶v̶a̶l̶i̶d̶I̶n̶p̶u̶t̶O̶p̶t̶i̶o̶n̶:̶"̶U̶S̶E̶R̶_̶E̶N̶T̶E̶R̶E̶D̶"̶,̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶d̶a̶t̶a̶:̶d̶a̶t̶a̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶}̶
̶ ̶ ̶ ̶ ̶}̶;̶

̶I̶f̶ ̶y̶o̶u̶ ̶w̶a̶n̶t̶ ̶t̶o̶ ̶m̶a̶k̶e̶ ̶t̶h̶e̶ ̶d̶i̶s̶t̶i̶n̶c̶t̶i̶o̶n̶ ̶b̶e̶t̶w̶e̶e̶n̶ ̶U̶R̶L̶ ̶p̶a̶r̶a̶m̶e̶t̶e̶r̶s̶ ̶a̶n̶d̶ ̶P̶O̶S̶T̶ ̶b̶o̶d̶i̶e̶s̶ ̶m̶o̶r̶e̶ ̶e̶x̶p̶l̶i̶c̶i̶t̶,̶ ̶y̶o̶u̶ ̶c̶a̶n̶ ̶a̶l̶s̶o̶ ̶w̶r̶i̶t̶e̶ ̶y̶o̶u̶r̶ ̶r̶e̶s̶o̶u̶r̶c̶e̶ ̶o̶b̶j̶e̶c̶t̶ ̶a̶s̶ ̶f̶o̶l̶l̶o̶w̶s̶:̶

̶
̶ ̶ ̶ ̶ ̶v̶a̶r̶ ̶r̶e̶s̶o̶u̶r̶c̶e̶ ̶=̶ ̶{̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶a̶u̶t̶h̶:̶a̶u̶t̶h̶,̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶p̶a̶r̶a̶m̶s̶:̶{̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶s̶p̶r̶e̶a̶d̶S̶h̶e̶e̶t̶I̶d̶:̶s̶p̶r̶e̶a̶d̶S̶h̶e̶e̶t̶I̶d̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶}̶,̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶r̶e̶q̶u̶e̶s̶t̶B̶o̶d̶y̶:̶{̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶v̶a̶l̶i̶d̶I̶n̶p̶u̶t̶O̶p̶t̶i̶o̶n̶:̶"̶U̶S̶E̶R̶_̶E̶N̶T̶E̶R̶E̶D̶"̶,̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶d̶a̶t̶a̶:̶d̶a̶t̶a̶
̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶ ̶}̶
̶ ̶ ̶ ̶ ̶}̶;̶

---

Addendum

Tanaike's solution is also viable.