How to create a checkbox with value with Python using gspread and gspread_formatting

Question

I'm using google spreadsheets threw python and I want to create a checkbox. The checkbox is been create successfully but unfortunately without any value (When unchecked we expect FALSE). In the meanwhile, I have the following code

from time import localtime, time, sleep
from typing import Iterable, Dict, List, Set, Tuple
from gspread_formatting import DataValidationRule, BooleanCondition, set_data_validation_for_cell_range
import gspread
from oauth2client.service_account import ServiceAccountCredentials

validation_rule = DataValidationRule(
        BooleanCondition('BOOLEAN', ['TRUE', 'FALSE']),  # condition'type' and 'values', defaulting to TRUE/FALSE
        showCustomUi=True)

    
def update_sheet(sheet: 'Google_WorkSheet', org_name: str, error_type: str, num_of_rows: int) -> None:
    """
    This function updates a worksheet by inserting the parameters
    in the last row of the sheet
    :param sheet: the worksheet we modify
    :param org_name: the organization name to put in the row
    :param error_type: the FinOps error type to put in the row
    :param num_of_rows: the number of rows in the sheet
    :return: None
    """
    current_time = localtime(time())  # current_time : time.struct
    current_time = '{0}/{1}/{2}'.format(current_time[2], current_time[1], current_time[0])  # current_time : str

    note = check_error_type(error_type)
    new_row = [current_time, org_name, error_type, note]

sheet.append_row(new_row)
set_data_validation_for_cell_range(sheet, f'G{num_of_rows + 1}', validation_rule)  # inserting checkbox

This code creates the checkbox successfully but with no value in it. I know that I can use the gspread.initialize_spreadsheet().worksheet().update_cell() But I have a limited number of API calls so I don't want to use it. For example:

This checkbox has been created by the script above but we can see that there is no value in it.

Answer 1

I leave you my example based on Tanaike's idea:

    def obtener_api_keys(api_gateway, google_sh):
    try:
        hojaApiKeyBackup = google_sh.worksheet("APIKeysBackup")

        # Obtener todas las claves de API
        api_keys = api_gateway.get_api_keys(limit=5)["items"]

        data = []
        cell_update_requests = []
        sheetId = hojaApiKeyBackup._properties["sheetId"]

        # Obtener los valores actuales de la hoja de cálculo
        values = hojaApiKeyBackup.get_all_values()

        # Borrar los valores a partir de la segunda fila
        if len(values) > 1:
            hojaApiKeyBackup.delete_rows(2, len(values))

        for index, api_key in enumerate(api_keys):
            usage_plans = api_gateway.get_usage_plans(keyId=api_key["id"])["items"]
            usage_plans_csv = ",".join([plan["name"] for plan in usage_plans])

            # Determina el valor de enabled para la casilla de verificación
            bool_value = True if api_key.get("enabled", False) else False
            bool_value_regenerate = False

            
            # Para las columnas D y E (índices 3 y 4 respectivamente)
            columnas = [3]
            solicitudes = crear_solicitudes_casilla_verificacion(sheetId, index + 1, index + 2, columnas, bool_value)
            cell_update_requests.extend(solicitudes)

            columnas = [4]
            solicitudes = crear_solicitudes_casilla_verificacion(sheetId, index + 1, index + 2, columnas, bool_value_regenerate)
            cell_update_requests.extend(solicitudes)

            data.append(
                [
                    api_key.get("id", ""),
                    api_key.get("name", ""),
                    api_key.get("description", ""),
                    bool_value,
                    bool_value_regenerate,
                    f"{usage_plans_csv}",
                ]
            )
        
        # Inserta todas las solicitudes
        hojaApiKeyBackup.insert_rows(data, row=2)
        # Ejecuta todas las solicitudes de actualización de celdas
        res = google_sh.batch_update({"requests": cell_update_requests})

        print("API keys exportadas correctamente")

    except Exception as e:
        logging.warning("Se produjo un error al exportar las API keys:", e)



def crear_solicitudes_casilla_verificacion(sheet_id, start_row_index, end_row_index, columns, bool_value):
    cell_update_requests = []

    for col_index in columns:
        # Solicitud para configurar la validación de datos como BOOLEAN
        cell_update_requests.append(
            {
                "repeatCell": {
                    "cell": {
                        "dataValidation": {
                            "condition": {
                                "type": "BOOLEAN"
                            }
                        }
                    },
                    "range": {
                        "sheetId": sheet_id,
                        "startRowIndex": start_row_index,
                        "endRowIndex": end_row_index,
                        "startColumnIndex": col_index,
                        "endColumnIndex": col_index + 1,  # Incrementa en 1
                    },
                    "fields": "dataValidation",
                }
            }
        )

        # Solicitud para establecer el valor de la casilla de verificación
        cell_update_requests.append(
            {
                "updateCells": {
                    "rows": [
                        {
                            "values": [
                                {"userEnteredValue": {"boolValue": bool_value}}
                            ]
                        }
                    ],
                    "start": {
                        "rowIndex": start_row_index,
                        "columnIndex": col_index,
                        "sheetId": sheet_id,
                    },
                    "fields": "userEnteredValue",
                }
            }
        )

    return cell_update_requests

Answer 2

I used Tanaike's code but it still had an empty value when the checkbox was unchecked, updating the value of the cell to "FALSE" after setting the data validation fixed that.

current_rows = sheet.col_values(2)
number_of_rows = len(current_rows)
row_to_insert_at = number_of_rows + 1

checkbox_request = {
    'requests': [
        {
            'repeatCell': {
                'cell': {'dataValidation': {'condition': {'type': 'BOOLEAN'}}},
                'range': {
                    'sheetId': sheet.id,
                    'startRowIndex': row_to_insert_at - 1,
                    'endRowIndex': row_to_insert_at,
                    'startColumnIndex': 4, 'endColumnIndex': 5
                    },
                'fields': 'dataValidation'
            }
        }
        ]
    }
spreadsheet.batch_update(checkbox_request)
sheet.update('B1', 'FALSE', raw=False)

Answer 3

In this case, the method of batchUpdate in Sheets API is used for putting the checkbox. But the method of set_data_validation_for_cell_ranges uses the batch update request for only creating checkbox. Ref It seems that in this method, several batch requests cannot be included. By this, the initial value cannot be seen. So in order to put the checkbox and give the initial value using one API call, in this answer, I would like to propose to use the method of batch_update in gspread. The sample script is as follows.

Sample script:

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

client = gspread.authorize(credentials)
spreadsheet = client.open_by_key(spreadsheetId)
sheet = spreadsheet.worksheet(sheetName)
sheetId = sheet._properties['sheetId']
requests = {"requests": [
    {
        "repeatCell": {
            "cell": {"dataValidation": {"condition": {"type": "BOOLEAN"}}},
            "range": {"sheetId": sheetId, "startRowIndex": 0, "endRowIndex": 3, "startColumnIndex": 0, "endColumnIndex": 3},
            "fields": "dataValidation"
        }
    },
    {
        "updateCells": {
            "rows": [
                {"values": [{"userEnteredValue": {"boolValue": True}}, {"userEnteredValue": {
                    "boolValue": False}}, {"userEnteredValue": {"boolValue": False}}]},
                {"values": [{"userEnteredValue": {"boolValue": True}}, {"userEnteredValue": {
                    "boolValue": True}}, {"userEnteredValue": {"boolValue": False}}]},
                {"values": [{"userEnteredValue": {"boolValue": True}}, {"userEnteredValue": {
                    "boolValue": True}}, {"userEnteredValue": {"boolValue": True}}]}
            ],
            "start": {"rowIndex": 0, "columnIndex": 0, "sheetId": sheetId},
            "fields": "userEnteredValue"
        }
    }
]}
res = spreadsheet.batch_update(requests)
print(res)

Result:

When above sample script is run, the checkboxes are put to the cells "A1:C3" on "Sheet1" with the initial values like below.

References:

• batch_update(body)
• RepeatCellRequest
• UpdateCellsRequest