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

user_5824677 0
Viewed 20475

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.

google-apis-explorer google-apps-script google-sheets-api google-sheets
2 Answers

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);
}
user_10311110
Related