(gspread) How can I put a custom formula into a cell using gspread?

Question

I want to put a custom formula into a sheet using gspread. This can be done in Google Sheets and it looks like this.

I want to automate the process of inputting this using gspread, similar to how you can use batch_update to format multiple cells, I would like to use it to add a custom formula to multiple cells.

Answer 1

I believe your goal is as follows.

• You want to create the conditional formatting rule of =indirect("Sheet1!B2")<>"hello" and when this is true, you want to set the background color to white.
• You want to create the conditional formatting rule for all cells in a sheet of Google Spreadsheet.
• You want to achieve this using gspread for python.

In this case, how about the following sample script?

Sample script:

spreadsheetId = "###" # Please set your Spreadsheet ID.
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet("Sheet2") # Please set your sheet name.

formula = '=indirect("Sheet1!B2")<>"hello"'
body = {
    "requests": [
        {
            "addConditionalFormatRule": {
                "index": 0,
                "rule": {
                    "ranges": [{"sheetId": sheet.id}],
                    "booleanRule": {
                        "condition": {
                            "type": "CUSTOM_FORMULA",
                            "values": [{"userEnteredValue": formula}],
                        },
                        "format": {
                            "backgroundColorStyle": {
                                "rgbColor": {"red": 1, "green": 1, "blue": 1}
                            }
                        },
                    },
                },
            }
        }
    ]
}
spreadsheet.batch_update(body)

• When this script is run, the above addConditionalFormatRule is created for all cells in "Sheet2". When Sheet1!B2 is not hello, the background color of all cells of "Sheet2" is white.

References:

• batch_update(body)
• Method: spreadsheets.batchUpdate
• AddConditionalFormatRuleRequest

Added:

About your following reply,

I've modified my script based off of yours and it works perfect. I just want to know if its possible to have a list of conditional format rules that can be created for separate cells. Is this possible?

Your expected goal is the following sample script?

Sample script:

spreadsheetId = "###" # Please set your Spreadsheet ID.
sheetName = "Sheet2" # Please set your sheet name.
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
sheetId = sheet.id
service = build("sheets", "v4", credentials=client.auth)
res = service.spreadsheets().get(spreadsheetId=spreadsheetId, ranges=sheetName, fields="sheets(properties(gridProperties(rowCount,columnCount)))").execute()

row = res["sheets"][0]["properties"]["gridProperties"]["rowCount"]
column = res["sheets"][0]["properties"]["gridProperties"]["columnCount"]

formula = '=indirect("Sheet1!B2")<>"hello"'
requests = []
for i in range(row):
    for j in range(column):
        requests.append(
            {
                "addConditionalFormatRule": {
                    "index": 0,
                    "rule": {
                        "ranges": [
                            {
                                "sheetId": sheetId,
                                "startRowIndex": i,
                                "endRowIndex": i + 1,
                                "startColumnIndex": j,
                                "endColumnIndex": j + 1,
                            }
                        ],
                        "booleanRule": {
                            "condition": {
                                "type": "CUSTOM_FORMULA",
                                "values": [{"userEnteredValue": formula}],
                            },
                            "format": {
                                "backgroundColorStyle": {
                                    "rgbColor": {"red": 1, "green": 1, "blue": 1}
                                }
                            },
                        },
                    },
                }
            }
        )
spreadsheet.batch_update({"requests": requests})

• In this case, in order to retrieve the values of row and column of the sheet, googleapis for python is used. So, please add from googleapiclient.discovery import build.
• When this script is run, addConditionalFormatRule is create in all cells of "Sheet2". But, I thought that this is the same result with the above script.
• PLESE BE CAREFUL
  • When this script is run, addConditionalFormatRule is create in all cells of "Sheet2". So, for example, when the default sheet is used, a lot of conditional formatting rules are created in all cells of 1000 rows x 26 columns. Please be careful about this.
  • If you want to limit the cells, please manually set row and column.