How to append rows with google sheet api v4 in nodejs

Question

I really don't get how I can write new rows with the apiv4.

let's say I have a table with the columns ID, name, count, size.

The official nodejs example looks like this:

let values = [
  [
    // Cell values ...
  ],
  // Additional rows ...
];
let resource = {
  values,
};
this.sheetsService.spreadsheets.values.append({
  spreadsheetId,
  range,
  valueInputOption,
  resource,
}, (err, result) => {
  if (err) {
    // Handle error.
    console.log(err);
  } else {
    console.log(`${result.updates.updatedCells} cells appended.`);
  }
});

So for this to work I need the variables values, spreadsheetId, range and valueInputOption.

For value it says that the cell values go in there. Does this mean it must be filled with order from left to right? like with my example:

let values = [
      [
        "h7Bfhi87gBjh7fs9", //first the id
        "Max", //then name
        "20", //count
        "500" //size
      ]
    ];

Would this be correct?

spreadsheetId do I already have.

For the last two variables I don't know what they mean.

First the range variable. For reading, you must give the function a range to read from, that is simple. But Why do I need a range if I want to append rows to the table?

Second, I have no clue what valueInputOption does.

tldr:

What values do I need to declare to the variables values, range and valueInputOptions if I want to append rows to a spreadsheet with google spreadsheet apiv4?

laurent · Accepted Answer · 2022-01-17 23:17:04Z

4

If you just want to append rows at the end, you can specify "Sheet1" (or whatever the name of the sheet your want to append to) for the range (cf https://developers.google.com/sheets/api/guides/values#appending_values).

answered Jan 17, 2022 at 23:17

laurent

7739 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ADW · Accepted Answer · 2019-07-31 09:37:28Z

3

Values is an array of data to go into the cells.

Range is the address of the cells where the data needs to go to.

valueInputOptions could be raw. See here.

Edited:

Try to replace:

this.sheetsService.spreadsheets.values.append({
  spreadsheetId,
  range,
  valueInputOption,
  resource,
}

with

this.sheetsService.spreadsheets.values.append({
  spreadsheetId: 'string_spreadsheet_ID',
  range: 'Sheet1!A1:D1', // Or where you need the data to go 
  valueInputOption: 'RAW',
  resource: resource // takes the array created in the lines earlier
}

See this page for hints on the code structure.

edited Jul 31, 2019 at 9:37

answered Jul 31, 2019 at 9:20

ADW

4,2471 gold badge17 silver badges23 bronze badges

2 Comments

TakeAMinute Over a year ago

So valueinputOption = ValueinputOptions.raw; ? For value, I think its right how I did it in my question but I still don't get what Range needs, I just want to append the rows on the end of the table.

EvilJordan Over a year ago

Thank you for this. The official google docs are absolute dogshit.

Collectives™ on Stack Overflow

How to append rows with google sheet api v4 in nodejs

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related