How to mark data validation checkbox as true using Python and Google Sheets API?-CodePudding

I am trying to update some data validation checkboxes using setDataValidation request: However I didn't understand how to set the value to TRUE:

"values": [],

def add_checkboxes_true(self, row_number, column, spreadsheet_id):
        payload = {
            "requests": [
                {
                    "setDataValidation": {
                        "range": {
                            "sheetId": 0,
                            "startRowIndex": row_number - 1,
                            "endRowIndex": row_number,
                            "startColumnIndex": column,
                            "endColumnIndex": column   1
                        },
                        "rule": {
                            "condition": {
                                "type": 'BOOLEAN',
                                "values": [],
                            },
                            "showCustomUi": True,
                            "strict": False
                        }
                    }
                }
            ]
        }

        request = self.service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=payload)
        response = request.execute()

In Google Sheets Documentation I found:

The cell's value must be TRUE/FALSE or in the list of condition values. Supported by data validation. Renders as a cell checkbox. Supports zero, one or two ConditionValues . No values indicates the cell must be TRUE or FALSE, where TRUE renders as checked and FALSE renders as unchecked. One value indicates the cell will render as checked when it contains that value and unchecked when it is blank. Two values indicate that the cell will render as checked when it contains the first value and unchecked when it contains the second value. For example, ["Yes","No"] indicates that the cell will render a checked box when it has the value "Yes" and an unchecked box when it has the value "No".

CodePudding user response：

Based on the documentation that you also provided specifically on this line.

No values indicates the cell must be TRUE or FALSE, where TRUE renders as checked and FALSE renders as unchecked. One value indicates the cell will render as checked when it contains that value and unchecked when it is blank. Two values indicate that the cell will render as checked when it contains the first value and unchecked when it contains the second value.

You can set "values": ["TRUE","FALSE"], which returns checked if the current cell value of the range is set to TRUE and unchecked if set to FALSE.

You can manually set the values of the range on your Google Sheets, or this can be done through Google Sheets API as well using this example documentation:

values = [
    [
        # Cell values ...
    ],
    # Additional rows ...
]
body = {
    'values': values
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheet_id, range=range_name,
    valueInputOption=value_input_option, body=body).execute()
print('{0} cells updated.'.format(result.get('updatedCells')))

CodePudding user response：

In your script, how about the following modification?

Modified script:

def add_checkboxes_true(self, row_number, column, spreadsheet_id):
    gridRange = {
        "sheetId": 0,
        "startRowIndex": row_number - 1,
        "endRowIndex": row_number,
        "startColumnIndex": column,
        "endColumnIndex": column   1
    }
    payload = {
        "requests": [
            {
                "setDataValidation": {
                    "range": gridRange,
                    "rule": {
                        "condition": {
                            "type": 'BOOLEAN',
                            "values": [],
                        },
                        "showCustomUi": True,
                        "strict": False
                    }
                }
            },
            {
                "repeatCell": {
                    "range": gridRange,
                    "cell": {
                        "userEnteredValue": {
                            "boolValue": True
                        }
                    },
                    "fields": "userEnteredValue"
                }
            }
        ]
    }

    request = self.service.spreadsheets().batchUpdate(spreadsheetId=spreadsheet_id, body=payload)
    response = request.execute()

In this case, the inserted checkboxes are checked using repeatCell method. By this, this request can be done by one API call.
By using repeatCell method, if the several checkboxes are put by the same gridRange, those checkboxes are checked.

Reference:

RepeatCellRequest