CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-apigoogle-apis-explorer
Din
Din

Reputation: 535

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

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.

Upvotes: 5

Views: 31493

Answers (2)

Tom Holder
Tom Holder

Reputation: 221

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

Upvotes: 9

Tanaike
Tanaike

Reputation: 201378

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);

Upvotes: 20

Related Questions