How do I write values to a google spreadsheets row with the api?

January 26, 2022

I want, in node.js, for my command to, on the second row of my spreadsheet, set the values of A-D to 1-4. The code below seems to be how the documentation wants me to do it, but I keep getting the following error:

"GaxiosError: Invalid JSON payload received. Unknown name "majorDimension": Cannot bind query parameter. Field ‘majorDimension’ could not be found in request message.
Invalid JSON payload received. Unknown name "values": Cannot bind query parameter. Field ‘values’ could not be found in request message."

const {google} = require("googleapis")
const auth = new google.auth.GoogleAuth({
      keyFile: "sheets-credentials.json",
      scopes: "https://www.googleapis.com/auth/spreadsheets",
});
const client = await auth.getClient();
const googleSheets = google.sheets({ version: "v4", auth: client });
const spreadsheetId = "anidthisis";
await googleSheets.spreadsheets.values.append({
      auth,
      spreadsheetId,        
      "range": "Sheet1!A2:D2",
      "majorDimension": "ROWS",
      "values": [
          ["1", "2", "3", "4"],
       ],
});

>Solution :

In your script, how about the following modification?

Modified script:

From:

await googleSheets.spreadsheets.values.append({
      auth,
      spreadsheetId,        
      "range": "Sheet1!A2:D2",
      "majorDimension": "ROWS",
      "values": [
          ["1", "2", "3", "4"],
       ],
});

To:

await googleSheets.spreadsheets.values.append({
  auth,
  spreadsheetId,
  range: "Sheet1!A2:D2", // or "Sheet1!A2"
  valueInputOption: "USER_ENTERED",
  requestBody: { majorDimension: "ROWS", values: [["1", "2", "3", "4"]] },
});

In this case, when the cells "A2:D2" are not empty, the values are appended. So if you want to put the values to the cells "A2:D2" even when the cells "A2:D2" are not empty, how about the following modification?

  await googleSheets.spreadsheets.values.update({
    auth,
    spreadsheetId,
    range: "Sheet1!A2:D2", // or "Sheet1!A2"
    valueInputOption: "USER_ENTERED",
    requestBody: { values: [["1", "2", "3", "4"]] },
  });