如何在Google Sheets中创建含两列合并内容的下拉菜单？

问题背景 #

我有一个包含两列数据的Google Sheet，数据如下：

我希望基于这两列数据创建下拉菜单，选项显示为类似「Apple|34」的合并形式。请问如何实现让下拉菜单的选项包含两列的值？

我的最终目标是将表格数据导出为CSV文件后，通过脚本去除「Apple|」这类前缀，仅保留ID值；但在Google Sheet中查看时，希望同时显示Fruit和ID，以便知晓ID对应的含义。

实现步骤 #

1. 生成合并后的选项列表 #

在Sheet的空白列（比如C列）生成「Fruit|ID」格式的合并内容：

• 在C2单元格输入公式：=A2&"|"&B2
• 下拉填充公式到所有数据行，C列将自动生成「Apple|34」「Orange|42」这类合并值。

2. 创建含合并内容的下拉菜单 #

• 选中需要添加下拉菜单的目标单元格区域（比如D列）
• 点击菜单栏「数据」→「数据验证」
• 在弹出窗口中配置：
  • 「条件」选择「列表从范围」
  • 输入C列的有效数据范围（例如$C$2:$C$3）
  • 勾选「显示下拉箭头」，按需设置「拒绝输入无效数据」等规则
  • 点击「保存」，下拉菜单即可显示合并后的选项。

3. CSV导出后提取ID的脚本处理 #

导出CSV文件后，可通过脚本拆分字符串提取ID，以Python为例：

import csv

# 读取源CSV并处理后写入新文件
with open('source.csv', 'r', encoding='utf-8') as infile, open('result.csv', 'w', newline='', encoding='utf-8') as outfile:
    reader = csv.reader(infile)
    writer = csv.writer(outfile)
    # 保留表头
    writer.writerow(next(reader))
    # 处理每一行数据
    for row in reader:
        # 假设下拉菜单值在第4列（索引从0开始为3）
        target_col = 3
        if '|' in row[target_col]:
            row[target_col] = row[target_col].split('|')[1]
        writer.writerow(row)

可选优化：隐藏合并列 #

如果不想在Sheet中显示用于生成选项的C列，右键点击C列列标，选择「隐藏列」即可，不会影响下拉菜单的正常使用。

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