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.

Appendant

Google's example has range field within the JSON payload – Euphemism

Changed the code to this: { "range": "Sheet1!A1:D1", "majorDimension": "ROWS", "values": [ ["Door", "$15", "2", "3/15/2016"], ["Engine", "$100", "1", "3/20/2016"], ] }; but it gives error: Requested entity was not found. Although all the information for range and sheetId is correct. – Appendant

Looks like it can't find the entity ("USER_ENTERED") in the given range of your spreadsheet. Are you sure you want this method, to begin with? It is to find some value in the sheet and add data under it. – Euphemism

if i remove it, it says: Invalid number of arguments provided. Expected 3-4 only. And also the link you provided also says that ValueInputOption [that has the option "USER_ENTERED"] is required. Really strange why Google does not provide full documentation on Google Sheets API v4. – Appendant

Why don't you change "USER_ENTERED" to some value that is present in the range? And yes, v4 docs are a disaster. – Euphemism

I found the use of a generic range e.g. "Sheet1!A:A" for appending to be unreliable. In many instances existing rows were being overwritten, even with a delay of many minutes between updates. The solution was to fetch the current spreadsheet range, then specify the range to append to explicitly e.g. "Sheet1!A1:F2963". Perhaps there is something else I am missing (ideas?), but it now works. – Kelter

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

Sthilaire

@Appendant Welcome. Thank you, too. – Sthilaire

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

Brine

Thank you so much!! No wonder people use wrappers like gspread. This API is just too much! :/ – Imposture

I think the confusion lies in the fact that Google, in all its infinite wisdom, decided that the V4 of Sheets API should be RESTful service API's, whereas the "previous" version is an internal apps script service. As per Google's own doc: "The Advanced Sheets service lets you access the Sheets API using Apps Script. Much like Apps Script's built-in Google Sheets API service, this API allows scripts to read, etc... In most cases, the built-in service is easier to use, but this advanced service provides a few extra features." – Ananthous