本文介绍如何使用 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 这一典型错误。
✅ 核心改进点
- 使用 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 列（邮箱）即自动填充，大幅提升跨文档审计与责任追溯效率。
