将MS Excel转换为Google Sheets时如何获取原始跨文件引用公式

实现逻辑 #

Google Sheets导入Excel文件时会自动改写外部跨文件引用公式，导致原始文件名标识丢失，因此需要避开Google Sheets的自动转换逻辑，直接从本地Excel源文件中提取原始公式后再导入。

操作步骤 #

• 本地用Python的openpyxl库批量提取原始公式，参考代码如下：

from openpyxl import load_workbook
import os
import csv

# 批量处理当前目录下所有xlsx文件
for filename in os.listdir("."):
    if not filename.endswith(".xlsx"):
        continue
    # 配置data_only=False确保读取公式而非计算结果
    wb = load_workbook(filename, data_only=False)
    for sheet_name in wb.sheetnames:
        ws = wb[sheet_name]
        output_rows = []
        for row in ws.iter_rows():
            row_data = []
            for cell in row:
                row_data.append(cell.formula if cell.data_type == "f" else cell.value)
            output_rows.append(row_data)
        # 导出为带原始公式的CSV文件
        output_name = f"{os.path.splitext(filename)[0]}_{sheet_name}_原始公式.csv"
        with open(output_name, "w", encoding="utf-8-sig", newline="") as f:
            csv.writer(f).writerows(output_rows)

• 将导出的CSV文件上传导入Google Sheets，此时所有原始公式会以纯文本形式完整保留，包含[P2.xlsx]这类外部文件名标识，不会被自动改写。
• 如需适配Google Sheets的跨文件引用规则，可批量替换公式格式：将=[文件名.xlsx]工作表名!单元格的格式替换为=IMPORTRANGE("对应Google Sheets文件ID", "工作表名!单元格")即可正常使用跨表引用功能。

注意事项 #

• 不要直接将Excel文件上传转换为Google Sheets，转换过程的公式改写是不可逆的，无法从转换后的文件中找回原始引用标识。
• 若无需在Google Sheets中使用引用计算，仅需留存原始公式记录，导出CSV后无需修改公式即可直接存档。

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