Append Values by Inserting Rows using Google Sheets API

Spreadsheet
Python / spreadsheet / SheetsAPI / batchUpdate

Gists

In the case appending values to cell by inserting rows, when sheets.spreadsheets.values.append is used, the values are appended to the next empty row of the last row. If you want to append values to between cells with values by inserting row, you can achieve it using sheets.spreadsheets.batchUpdate.

When you use this, please use your access token.

Endpoint :

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

Request body :

In this request body, it appends the data of “sample1, sample2, sample3” to “A1:A3” of the sheetId of “1234567890”. Before appends the data, it supposes that there are some values at “A1:A3”.

The flow of request is as follows.

  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 is found that the elements of “requests” which is an array run in the order.

{
 "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,
    }
   }
  }
 ]
}

Python sample :

def main():
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?' 'version=v4')
    service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)

    spreadsheet_id = '### spreadsheet ID ###'
    batch_update_spreadsheet_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
                    }
                }
            }
        ]
    }

    request = service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=batch_update_spreadsheet_request_body)
    response = request.execute()
    print(response)

Reference :

 Share!