google sheets api v4 Append row after specific row or move row to specific position

Question

Is it possible to append a row after a specific row/range?
For example, I have a spreadsheet with 5 rows where in column A is written for row one "1", for row two "2" but in row three "4" and so on till row five "6".

Now I want to append a row to this table and I currently use this function:

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

The row I want to append has in column A a "3" and I want the row appended after Row two.

But when I set the range to "A2:C2" it will only append it where the first empty row is; after row five.

Is there a way to append rows inbetween rows? If not, is it possible to move the appended row from bottom to the desired position?

PS: I don't want to order the rows by alphabet or number. I just use numbers to show what I mean.

TheMaster TheMaster · Accepted Answer · 2019-08-01T11:04:53

You must insert a row inbetween first and then update the values in that row or append. You can try InsertRangeRequest or InsertDimension

Snippet:

this.sheetsService.spreadsheets.batchUpdate({
  spreadsheetId,
  resource: { 
    requests: [
     "insertRange": {
        "range": {//Sheet1!A3
          "sheetId": 0,
          "startRowIndex": 2,
          "endRowIndex": 3,
          "startColumnIndex": 0,
          "endColumnIndex": 1
         },
        "shiftDimension": "ROWS"
      }
    ]
  }

},(err,res)=>console.log(err ? err : res))

google sheets api v4 Append row after specific row or move row to specific position

1 Answers

Snippet: