Google Sheets API v4 - Method: spreadsheets.values.append

Question

What is the correct syntax for Google Sheets API v4 method spreadsheets.values.append for Google Apps Script?

Tried the following code but it is giving an error: Invalid JSON payload received.

function appendRow() {
  Sheets.Spreadsheets.Values.append("SpreadsheetID", "Sheet1!A:A", "USER_ENTERED", { "values": [[new Date()]] } );
}

Thank you.

Answer 1

How about this sample? Sheets.Spreadsheets.Values.append() of Advanced Google services is used like Sheets.Spreadsheets.Values.append(resource, spreadsheetId, range, optionalArgs). So the sample used your parameters is as follows.

Sample :

var resource = {
  "majorDimension": "ROWS",
  "values": [[new Date()]]
}
var spreadsheetId = "### SpreadsheetID ###";
var range = "Sheet1!A:A";
var optionalArgs = {valueInputOption: "USER_ENTERED"};
Sheets.Spreadsheets.Values.append(resource, spreadsheetId, range, optionalArgs);

Answer 2

The top answer here didn't work for me. With v4 of API, after some trial and error, the following worked. The Google Sheets API docs are all over the place, this will no doubt be out of date for the next release! Good luck anyone using the Sheets API!

const request = {
    spreadsheetId: 'SHEET_ID',
    range: 'Sheet1!A:B',
    valueInputOption: 'USER_ENTERED',
    insertDataOption: 'INSERT_ROWS',
    resource: {
        "majorDimension": "ROWS",
        "values": [["Row 1 Col 1","Row 1 Col 2"], ["Row 2 Col 1","Row 2 Col 2"]]
    },
    auth: oAuth2Client,
};

try {
    const response = (await sheets.spreadsheets.values.append(request)).data;
    console.log(JSON.stringify(response, null, 2));
} catch (err) {
    console.error(err);
}