需Apps Script实现将文件路径转为Google Sheets单元格超链接

问题描述 #

我有一个含650行数据的Google表格：

• A列：共享驱动器上外部文件夹的唯一标识（XX.23.INSP.0001至XX.23.INSP.0650）
• Z列：对应文件夹的Windows格式路径（示例：X:\Shared drives\XXXX XXX FSS County Folders\xxxx\DW\XX Drinking water name\Inspections\XX.23.INSP.0001）
  所有路径的共同父文件夹为X:\Shared drives\XXXX XXX FSS County Folders，对应Google共享驱动器的根目录。

需要用Apps Script遍历工作表单元格，将Z列的Windows路径转换为Google文件夹ID，并添加为A列单元格的超链接。已掌握超链接设置的部分代码，现需解决：如何提取目标文件夹的FileId？是否需要导航到每个文件夹再提取？具体操作步骤？

解决方案 #

不需要手动导航每个文件夹，可通过Google Drive API根据路径层级自动查找文件夹ID，以下是具体实现步骤：

1. 启用Drive API #

在Google Sheets的脚本编辑器中：

• 点击左侧菜单栏的「服务」
• 点击「添加服务」，搜索并添加Google Drive API，点击「添加」

2. 核心思路 #

• 先将Windows格式路径裁剪掉共同前缀，拆分为路径片段数组
• 从共享驱动器的根目录开始，逐层根据路径片段查找对应文件夹，最终获取目标文件夹ID
• 为避免重复API请求，可缓存已查找过的父文件夹ID，提升处理效率

3. 完整代码实现 #

function processHyperlinks() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  // 替换为你的共享驱动器ID（可在Drive界面查看：共享驱动器页面的URL末尾ID）
  const sharedDriveId = "你的共享驱动器ID";
  // Windows路径的共同前缀，需与实际路径完全匹配
  const commonPrefix = "X:\\Shared drives\\XXXX XXX FSS County Folders";
  
  // 缓存已找到的文件夹ID，避免重复查找
  const folderCache = {};

  // 遍历每一行（从第2行开始，假设第1行是表头）
  for (let row = 1; row < data.length; row++) {
    const folderIdentifier = data[row][0]; // A列数据
    const windowsPath = data[row][25]; // Z列是第26列，索引为25
    
    if (!windowsPath || !folderIdentifier) continue; // 跳过空行

    // 裁剪前缀并拆分路径片段
    const relativePath = windowsPath.replace(commonPrefix, "").trim();
    // 处理Windows路径分隔符，转为数组
    const pathSegments = relativePath.split("\\").filter(seg => seg !== "");
    
    // 获取目标文件夹ID
    const targetFolderId = getFolderIdByPath(sharedDriveId, pathSegments, folderCache);
    
    if (targetFolderId) {
      // 设置A列单元格的超链接（使用你提供的代码片段）
      const range = sheet.getRange(`A${row + 1}`);
      const richValue = SpreadsheetApp.newRichTextValue()
        .setText(folderIdentifier)
        .setLinkUrl(`https://drive.google.com/drive/folders/${targetFolderId}`)
        .build();
      range.setRichTextValue(richValue);
    }
  }
}

// 根据路径片段查找文件夹ID
function getFolderIdByPath(driveId, pathSegments, cache) {
  let currentFolderId = driveId;
  
  for (let i = 0; i < pathSegments.length; i++) {
    const segment = pathSegments[i];
    const cacheKey = `${currentFolderId}_${segment}`;
    
    // 先查缓存
    if (cache[cacheKey]) {
      currentFolderId = cache[cacheKey];
      continue;
    }
    
    // 调用Drive API查找子文件夹
    const folders = Drive.Files.list({
      q: `'${currentFolderId}' in parents and mimeType='application/vnd.google-apps.folder' and title='${segment}' and trashed=false`,
      corpora: "drive",
      driveId: driveId,
      includeItemsFromAllDrives: true,
      supportsAllDrives: true,
      fields: "items(id)"
    });
    
    if (folders.items && folders.items.length > 0) {
      currentFolderId = folders.items[0].id;
      cache[cacheKey] = currentFolderId; // 存入缓存
    } else {
      // 未找到对应文件夹，返回null
      console.log(`未找到路径片段：${segment}，父文件夹ID：${currentFolderId}`);
      return null;
    }
  }
  
  return currentFolderId;
}

4. 使用说明 #

1. 替换代码中的你的共享驱动器ID：打开共享驱动器页面，URL末尾的字符串即为ID（格式类似123abcXYZ）
2. 确认commonPrefix与实际路径完全匹配（注意转义反斜杠\\）
3. 若表格第1行是表头，循环从row=1开始；若无表头，改为row=0
4. 运行processHyperlinks函数，脚本会自动遍历处理所有行

关键说明 #

• 无需手动导航文件夹：脚本通过Drive API自动按路径层级查找，全程无需人工干预
• 缓存机制：已查找过的父文件夹ID会被缓存，大幅减少API调用次数，提升650行数据的处理速度
• 权限要求：运行脚本的账号需拥有共享驱动器的访问权限，否则会返回权限错误

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