Google sheets API append method (last on top)

Question

When using google sheets api append method (in any language), the values to be appended to the sheet are added after the last non null row. So new values appear at the bottom of the sheet, as explained here: https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/append#InsertDataOption

How can I append values in a way that the new values appear at the top of the sheet?

Answer 1

You want to append values by inserting new rows. If my understanding is correct, how about this method? It seems that sheets.spreadsheets.values.append appends values to the last row. So I would like to propose to usesheets.spreadsheets.batchUpdate. The endpoint and request body are as follows. When you use this, please modify ### spreadsheet ID ###, "sheetId": 1234567890 and the parameters for range and values.

Endpoint :

POST https://sheets.googleapis.com/v4/spreadsheets/### spreadsheet ID ###:batchUpdate

Request body :

{
 "requests": [
  {
   "insertRange": {
    "range": {
     "sheetId": 1234567890,
     "startRowIndex": 0,
     "endRowIndex": 1
    },
    "shiftDimension": "ROWS"
   }
  },
  {
   "pasteData": {
    "data": "sample1, sample2, sample3",
    "type": "PASTE_NORMAL",
    "delimiter": ",",
    "coordinate": {
     "sheetId": 1234567890,
     "rowIndex": 0,
    }
   }
  }
 ]
}

Flow of this request :

1. Insert new row to row 1 using "insertRange".
2. Import values of "sample1, sample2, sample3" using "pasteData".

When the order of "insertRange" and "pasteData" is changed, at first, the value of "A1:A3" is overwritten. After this, the new row is inserted to the row 1. So it seems that the elements of "requests" which is an array run in the order.

Reference :

• sheets.spreadsheets.batchUpdate

If I misunderstand your question, I'm sorry.