Skip to content

Instantly share code, notes, and snippets.

@tanaikech

tanaikech/submit.md

Last active Sep 21, 2020
Embed
What would you like to do?
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
You can’t perform that action at this time.