How to insert row and change cell styles using batch_update in gspread?

Question

I am using gspread and gspread_formatting to update my sheets in Google. Some of my codebase has been reworked to use batch_update, because I found some code samples in another answer that I could use as reference. However, I can't seem to convert two other operations. Here is my code:

import gspread

from gspread_formatting import *
from oauth2client.service_account import ServiceAccountCredentials

def operate():
    scope = [
        'https://spreadsheets.google.com/feeds',
        'https://www.googleapis.com/auth/drive'
    ]
    credentials = ServiceAccountCredentials.from_json_keyfile_name(
        'creds.json',
        scope
    )
    gc = gspread.authorize(credentials)

    sh = gc.open('My spreadsheet')
    ws = sh.sheet1
    sheet_id = ws._properties['sheetId']

    # Setting the column sizes
    body = {
        'requests': [
            {
                'updateDimensionProperties': {
                    'range': {
                        'sheetId': sheet_id,
                        'dimension': 'COLUMNS',
                        'startIndex': 0,
                        'endIndex': 10
                    },
                    'properties': {
                        'pixelSize': 150
                    },
                    'fields': 'pixelSize'
                }
            },
            {
                'updateDimensionProperties': {
                    'range': {
                        'sheetId': sheet_id,
                        'dimension': 'COLUMNS',
                        'startIndex': 4,
                        'endIndex': 6
                    },
                    'properties': {
                        'pixelSize': 250
                    },
                    'fields': 'pixelSize'
                }
            }
        ]
    }
    res = sh.batch_update(body)

    # Request 1
    ws.insert_row(['One', 'Two', 'Three'], 1)

    # Request 2
    format_cell_range(
        ws, 'A1:Z7',
        cellFormat(
            wrapStrategy='WRAP',
            verticalAlignment='MIDDLE',
            backgroundColor=color(0.886, 0.945, 0.988),
            textFormat=textFormat(
                foregroundColor=color(0, 0.129, 0.443),
                fontFamily='Roboto',
                bold=True
            )
        )
    )

So, what I want to do is somehow add request 1 and request 2 into the bulk_update method. Is it possible to insert and a row and change formatting using bulk_update? If yes, how can I do this? Thanks for any help.

score 4 · Accepted Answer · answered Jan 20 '20 at 22:20

You want to insert new row to the 1st row.
You want to put the values of ['One', 'Two', 'Three'] to the inserted row.

You want to set the following cell format to the range of "A1:Z7" using the method of batch_update() of gspread.

wrapStrategy='WRAP',
verticalAlignment='MIDDLE',
backgroundColor=gsf.color(0.886, 0.945, 0.988),
textFormat=gsf.textFormat(
    foregroundColor=gsf.color(0, 0.129, 0.443),
    fontFamily='Roboto',
    bold=True
)

You want to achieve this using gspread with python.

Your goal is to convert the following script to batch_update()

# Request 1
ws.insert_row(['One', 'Two', 'Three'], 1)

# Request 2
format_cell_range(
    ws, 'A1:Z7',
    cellFormat(
        wrapStrategy='WRAP',
        verticalAlignment='MIDDLE',
        backgroundColor=color(0.886, 0.945, 0.988),
        textFormat=textFormat(
            foregroundColor=color(0, 0.129, 0.443),
            fontFamily='Roboto',
            bold=True
        )
    )
)

You have already been able to get and put values for Spreadsheet using Sheets API.

If my understanding is correct, how about this answer? Please think of this as just one of several possible answers.

Flow:

In this case, the flow of the request body of batch_update() is as follows.

Insert new row to the 1st row by insertDimension.
Put the values of ['One', 'Two', 'Three'] to the inserted row by updateCells.
Set the cell format to the range of "A1:Z7" by repeatCell.

Modified script:

spreadsheetId = "###"  # Please set the Spreadsheet ID.
sheetName = "###"  # Please set the sheet name.

sh = gc.open_by_key(spreadsheetId)
ws = sh.worksheet(sheetName)
sheetId = ws._properties['sheetId']
requests = {
    "requests": [
        {
            "insertDimension": {
                "range": {
                    "sheetId": sheetId,
                    "startIndex": 0,
                    "dimension": "ROWS",
                    "endIndex": 1
                }
            }
        },
        {
            "updateCells": {
                "range": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 1
                },
                "rows": [
                    {
                        "values": [
                            {
                                "userEnteredValue": {
                                    "stringValue": "One"
                                }
                            },
                            {
                                "userEnteredValue": {
                                    "stringValue": "Two"
                                }
                            },
                            {
                                "userEnteredValue": {
                                    "stringValue": "Three"
                                }
                            }
                        ]
                    }
                ],
                "fields": "userEnteredValue"
            }
        },
        {
            "repeatCell": {
                "range": {
                    "sheetId": sheetId,
                    "startRowIndex": 0,
                    "endRowIndex": 7,
                    "startColumnIndex": 0,
                    "endColumnIndex": 26
                },
                "cell": {
                    "userEnteredFormat": {
                        "wrapStrategy": "WRAP",
                        "verticalAlignment": "MIDDLE",
                        "backgroundColor": {
                            "red": 0.886,
                            "green": 0.945,
                            "blue": 0.988
                        },
                        "textFormat": {
                            "foregroundColor": {
                                "red": 0,
                                "green": 0.129,
                                "blue": 0.443
                            },
                            "fontFamily": "Roboto",
                            "bold": True
                        }
                    }
                },
                "fields": "userEnteredFormat"
            }
        }
    ]
}
res = sh.batch_update(requests)

Note:

I understood that bulk_update is batch_update.

References:

If I misunderstood your question and this was not the result you want, I apologize.

@darkhorse Thank you for replying. I'm glad your issue was resolved. Thank you, too. — Tanaike, Jan 20 '20 at 23:36

How to insert row and change cell styles using batch_update in gspread?

1 Answers1

Flow:

Modified script:

Note:

References: