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.

CodePudding user response：

you can use valueInputOption='USER_ENTERED' parameter on your sheet update. This will basically input into the spreadsheet call like you input it manually yourself. View more here, https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption

with sheets api:

sheets_service = build('sheets', 'v4', credentials=auth.get_credentials())
response = sheets_service.spreadsheets().values().update(
                spreadsheetId='<ENTER SPREADSHEET ID HERE>', range='<ENTER CELL RANGE HERE>', 
                valueInputOption='USER_ENTERED', body='<YOUR CUSTOM FORMULA HERE>').execute()

with gspread:

sh.values_update(
    'Sheet1!A2',
    params={
        'valueInputOption': 'USER_ENTERED'
    },
    body={
        'values': ...
    }
)

https://docs.gspread.org/en/v3.7.0/api.html#gspread.models.Spreadsheet.values_update

CodePudding user response：

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.