本文介绍如何使用 google apps script 结合 drive api，从 google sheets 中的超链接批量提取对应文件的最后修改时间及修改者邮箱，并安全写入相邻列，同时解决常见维度不匹配报错问题。
本文介绍如何使用 google apps script 结合 drive api，从 google sheets 中的超链接批量提取对应文件的最后修改时间及修改者邮箱，并安全写入相邻列，同时解决常见维度不匹配报错问题。
在日常协作中，我们常需追踪共享表格（如项目看板、数据采集表）的更新责任人。虽然 Google Sheets 原生不支持“根据 URL 反查修改者”，但借助 Apps Script 调用 Drive API，可高效实现该功能——尤其适用于将多个 Sheet 链接集中管理在一张主表中，并自动同步其元数据。
以下是一个完整、健壮且已验证的解决方案。它扩展了原始脚本，不仅保留原有的「最后修改时间」功能，还新增「修改者邮箱」字段，并彻底规避 Exception: The number of columns in the data does not match the number of columns in the range 这一典型错误。
Access Gmail, Calendar, Drive, Docs, and Sheets via the gws CLI or Python.
✅ 核心改进点
- 使用 Drive.Files.get()（v2 API）精准获取 lastModifyingUser.emailAddress 和 modifiedDate；
- 严格对齐输出数组维度，确保 setValues() 写入列数与目标区域一致；
- 增加空链接/非法 ID 的防御性处理，避免运行中断；
- 支持灵活切换返回 emailAddress 或 displayName。
? 完整脚本（推荐使用）
function myFunction() {
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
const lastRow = sheet.getLastRow();
if (lastRow < 2) return; // 无数据时提前退出
const range = sheet.getRange("H2:H" + lastRow);
const values = range.getRichTextValues();
const results = values.map(([cell]) => {
const link = cell.getLinkUrl();
if (!link || typeof link !== 'string') {
return [null, null]; // 空链接或非字符串 → 双空值
}
// 从标准 Google Docs/Sheets URL 提取文件 ID（兼容 docs.google.com/drawings/spreadsheets）
const idMatch = link.match(/\/d\/([-\w]+)/);
const fileId = idMatch ? idMatch[1] : null;
if (!fileId) {
console.warn(`Invalid URL format, skipped: ${link}`);
return [null, null];
}
try {
// 调用 Drive API 获取元数据（需提前启用）
const file = Drive.Files.get(fileId, {
fields: "lastModifyingUser(emailAddress,displayName),modifiedDate"
});
const { emailAddress, displayName } = file.lastModifyingUser || {};
const modifiedDate = file.modifiedDate ? new Date(file.modifiedDate) : null;
// ✅ 返回双列：[修改时间, 邮箱] —— 严格 2 列，匹配 I & J 列
return [modifiedDate, emailAddress || displayName || null];
} catch (e) {
console.error(`Failed to fetch metadata for ${fileId}:`, e.message);
return [null, null];
}
});
// ✅ 关键修复：明确指定 offset 区域的行列尺寸，避免维度错误
// 将结果写入 I2:J{n}（即 H2:Hn 右侧两列）
range.offset(0, 1, results.length, 2).setValues(results);
}⚙️ 必备前提配置
启用 Drive API：
在脚本编辑器中，依次点击 Resources → Advanced Google services → 开启 Drive API；
同时点击右下角 Google Cloud Platform (GCP) Project → Manage → APIs & Services → Enable APIs and Services → 搜索并启用 “Drive API”。权限授权：
首次运行脚本时，系统会提示授权 https://www.googleapis.com/auth/drive.metadata.readonly —— 请确认允许。-
数据格式要求：
- Column H（H2 起）必须为 富文本超链接（即用户手动插入的链接，非纯文本 URL）；
- 支持常见 Google 文档类型：Sheets、Docs、Slides、Drawings（URL 结构含 /d/{fileId}/）。
⚠️ 注意事项与最佳实践
- 性能提示：若 H 列含上百个链接，建议分批处理（例如每次 50 行），避免 6 分钟执行超时。可添加 Utilities.sleep(100) 降低 QPS。
- 权限边界：脚本仅能获取当前用户有访问权限的文件的修改者信息；若链接指向受限文档，将抛出 403 Forbidden 错误（日志可见）。
- 显示名 vs 邮箱：如需展示姓名而非邮箱，将 return [modifiedDate, emailAddress || displayName || null]; 改为 return [modifiedDate, displayName || null];。
- 错误排查：开启 View → Logs 查看 console.warn/error 输出，快速定位无效链接或权限问题。
✅ 总结
该脚本将「链接→元数据」自动化提升至生产级可用水平：结构清晰、容错性强、符合 Apps Script 最佳实践。部署后，只需在 H 列粘贴文档链接，运行一次 myFunction，I 列（时间）与 J 列（邮箱）即自动填充，大幅提升跨文档审计与责任追溯效率。
