Skip to content

Instantly share code, notes, and snippets.

@tanaikech
Last active January 15, 2024 11:25
Show Gist options
  • Save tanaikech/7846ebcafbab8318ff74c9955a99e06b to your computer and use it in GitHub Desktop.
Save tanaikech/7846ebcafbab8318ff74c9955a99e06b to your computer and use it in GitHub Desktop.
Append Values by Inserting Rows using Google Sheets API

Append Values by Inserting Rows using Google Sheets API

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 :

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment