Python Google Sheets API: Insert and Update Row

July 16, 2019

google-sheets

This artile focus on insert a new row at the top (row 2).

For append row at the bottom, refer to Python Google Sheets API: Append New Row.

credentials = ...
service = build('sheets', 'v4', credentials=credentials)
sheet = service.spreadsheets()

SPREADSHEET_ID = ...
SHEET_NAME = ...
SHEET_ID = ...

# Insert New Empty Row at A2
body = {
    'requests': [
        {
            'insertDimension': {
                'range': {
                    'sheetId': SHEET_ID,
                    'dimension': 'ROWS',
                    'startIndex': 1,
                    'endIndex': 2
                },
                'inheritFromBefore': True
            }
        }
    ]
}

# Using sheet.values().batchUpdate will trigger: Invalid JSON payload received. Unknown name "requests": Cannot find field.
result = sheet.batchUpdate(spreadsheetId=SPREADSHEET_ID,
                           body=body).execute()

body = {
    'value_input_option': 'USER_ENTERED', # RAW
    'data': [
        {'range': f"'{SHEET_NAME}'!A2:B2", 'values': [["1", "New Row"]]}
    ]
}

result = sheet.values().batchUpdate(spreadsheetId=SPREADSHEET_ID,
                                    body=body).execute()