如何获取Google Sheets中带数据验证的单元格值？

使用场景 #

我正在遍历Google Sheets中的行，并根据不同字段的值执行相应操作。

当前代码 #

client = authorize()
worksheets_data = get_worksheet(client, CAREEM_SHEET, "UAE")
sheet_data = SheetData(worksheets_data)
rows = sheet_data.get_all_values()

for row in rows[2:]:  # Skip header and template row
    channel_link_id = row[sheet_data.get_column_index(ActivationSheets.CAREEM_CHANNEL_LINK_ID.value)]
    careem_mapped = row[sheet_data.get_column_index(ActivationSheets.CAREEM_MAPPED.value)]
    menu_pushed = row[sheet_data.get_column_index(ActivationSheets.CAREEM_MENU_PUSHED.value)]
    created = row[sheet_data.get_column_index(ActivationSheets.CAREEM_DRECT_CREATED.value)]

    if (careem_mapped == "TRUE" and menu_pushed != "Yes" and created == "TRUE"):
        print(row)

问题描述 #

menu_pushed是工作表中使用数据验证的下拉列，但使用get_all_values()函数似乎无法获取其值。我该如何访问单元格的数据验证？目前在文档中仅能找到set_data_validation()方法。

示例输出 #

['6093be05481dce29eaac6bfe', 'TRUE', 'FALSE', '1/23/2022 13:45:47', '']

我的SheetData类代码 #

class SheetData:
    def __init__(self, worksheet: pygsheets.Worksheet):
        self._worksheet = worksheet
        self._header_indexes = {}
        self._all_values = None
        self._populate_fields()

    def _populate_fields(self):
        self._all_values = self._worksheet.get_all_values()
        headers = self._all_values[0]
        self._header_indexes = {header: index for index, header in enumerate(headers)}

    @property
    def header_indexes(self):
        return self._header_indexes

    def get_all_values(self):
        """
        :return: all cell values from within the supplied worksheet.
        """
        return self._worksheet.get_all_values()

def authorize() -> pygsheets.client.Client:
    """
    Authorizes the pygsheets client using a service account credentials file.
    :param credentials_file: str location of Google Service Account credentials file.
    :returns: A pygsheets.Client object.
    :raises pygsheets.exceptions.PyGsheetsException: If the authorization process fails.
    """
    return pygsheets.authorize(service_account_json=SPREADSHEET_SERVICE_ACCOUNT)


def get_worksheet(client: pygsheets.client.Client, spreadsheet_url: str, worksheet_name: str) -> pygsheets.Worksheet:
    """
    Fetches a worksheet from a Google Sheets spreadsheet.
    :param client: pygsheets.Client object.
    :param spreadsheet_url: str URL of the spreadsheet.
    :param worksheet_name: str name of the worksheet to retrieve.
    :returns: A pygsheets.Worksheet object representing the specified worksheet.
    :raises pygsheets.exceptions.PyGsheetsException: If the worksheet retrieval fails.
    """
    spreadsheet = client.open_by_url(spreadsheet_url)
    return spreadsheet.worksheet_by_title(worksheet_name)

解决方案 #

1. 先确认单元格实际值 #

get_all_values()获取的是单元格的显示值，如果下拉列的单元格未选择任何选项，会返回空字符串。先检查目标单元格是否确实选中了下拉选项，比如你的示例输出最后一个空值，可能对应menu_pushed列未选择值。

2. 直接访问单元格对象获取数据验证 #

pygsheets没有批量获取数据验证的高层API，但可以通过单个Cell对象的data_validation属性访问规则。需要修改遍历逻辑，改为操作单元格对象：

client = authorize()
worksheets_data = get_worksheet(client, CAREEM_SHEET, "UAE")
sheet_data = SheetData(worksheets_data)

# 获取各列索引
channel_link_col = sheet_data.get_column_index(ActivationSheets.CAREEM_CHANNEL_LINK_ID.value)
careem_mapped_col = sheet_data.get_column_index(ActivationSheets.CAREEM_MAPPED.value)
menu_pushed_col = sheet_data.get_column_index(ActivationSheets.CAREEM_MENU_PUSHED.value)
created_col = sheet_data.get_column_index(ActivationSheets.CAREEM_DRECT_CREATED.value)

# 从第3行开始遍历（对应原代码的rows[2:]）
for row_num in range(3, worksheets_data.rows + 1):
    # 获取整行的单元格对象
    row_cells = worksheets_data.get_row(row_num, returnas='cell')
    
    # 提取各字段值
    channel_link_id = row_cells[channel_link_col].value
    careem_mapped = row_cells[careem_mapped_col].value
    menu_pushed = row_cells[menu_pushed_col].value
    created = row_cells[created_col].value
    
    # 获取menu_pushed单元格的数据验证规则
    dv_rule = row_cells[menu_pushed_col].data_validation
    if dv_rule:
        print(f"第{row_num}行的下拉规则: {dv_rule}")
    
    # 原判断逻辑
    if careem_mapped == "TRUE" and menu_pushed != "Yes" and created == "TRUE":
        print([cell.value for cell in row_cells])

3. 优化SheetData类，添加数据验证获取方法 #

可以给SheetData类扩展方法，方便快速获取指定单元格的数据验证：

class SheetData:
    # 原有代码...
    
    def get_cell_data_validation(self, row_num: int, col_num: int):
        """获取指定单元格的数据验证规则（行号、列号从1开始）"""
        cell = self._worksheet.cell((row_num, col_num))
        return cell.data_validation
    
    def get_column_data_validation(self, header: str, row_num: int):
        """根据表头获取指定行单元格的数据验证规则"""
        col_idx = self._header_indexes.get(header)
        if not col_idx:
            return None
        # 注意：pygsheets列索引从1开始，所以要+1
        return self.get_cell_data_validation(row_num, col_idx + 1)

使用示例：

# 获取第3行menu_pushed列的数据验证规则
dv_rule = sheet_data.get_column_data_validation(ActivationSheets.CAREEM_MENU_PUSHED.value, 3)
if dv_rule:
    print(dv_rule)

注意事项 #

• 数据验证是单元格级别的设置，即使整列套用相同规则，也需要访问单个单元格获取规则。
• 如果单元格没有设置数据验证，data_validation属性会返回None。
• get_all_values()仅批量返回单元格值，不包含格式、验证规则等元数据，必须操作Cell对象才能获取这些信息。

内容的提问来源于stack exchange，提问作者lammyalex