基于Google API检索Drive文件夹及子文件夹中Google Sheet的URL

核心实现思路 #

用Google Apps Script递归遍历目标主文件夹及其子文件夹，筛选出名称匹配的Google Sheet文件，再将结果输出到Google Sheet或Google Doc中，方便直接用于importrange()调用。

完整脚本代码 #

1. 核心搜索函数 #

这个函数负责遍历文件夹并筛选目标Sheet：

function findRosterSheets(mainFolderName) {
  // 定位主文件夹（取第一个匹配的文件夹，建议名称唯一）
  var mainFolder = DriveApp.getFoldersByName(mainFolderName).next();
  var results = [];

  // 递归遍历文件夹的内部函数
  function traverseFolder(folder) {
    // 筛选当前文件夹内的Google Sheet文件
    var files = folder.getFilesByType(MimeType.GOOGLE_SHEETS);
    while (files.hasNext()) {
      var file = files.next();
      // 匹配文件名包含"roster"（不区分大小写，可修改为精确匹配）
      if (file.getName().toLowerCase().includes("roster")) {
        results.push({
          name: file.getName(),
          url: file.getUrl(),
          parentFolder: folder.getName()
        });
      }
    }

    // 递归处理子文件夹
    var subFolders = folder.getFolders();
    while (subFolders.hasNext()) {
      traverseFolder(subFolders.next());
    }
  }

  traverseFolder(mainFolder);
  return results;
}

2. 输出结果到Google Sheet #

运行这个函数，会把找到的Sheet信息写入当前Sheet的单元格，URL自动设为可点击链接：

function exportToSheet() {
  var mainFolderName = "2024 season"; // 替换为你的主文件夹名称
  var results = findRosterSheets(mainFolderName);
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  // 写入表头
  sheet.getRange(1, 1, 1, 3).setValues([["文件名", "Sheet URL", "所在文件夹"]]);
  
  // 写入匹配结果
  for (var i = 0; i < results.length; i++) {
    var row = i + 2;
    sheet.getRange(row, 1).setValue(results[i].name);
    // 设置可点击的URL链接
    sheet.getRange(row, 2).setFormula(`=HYPERLINK("${results[i].url}", "${results[i].url}")`);
    sheet.getRange(row, 3).setValue(results[i].parentFolder);
  }

  SpreadsheetApp.getUi().alert(`已找到${results.length}个roster表格，结果已写入当前Sheet！`);
}

3. 输出结果到Google Doc #

运行这个函数，会把结果添加到当前Google Doc中：

function exportToDoc() {
  var mainFolderName = "2024 season"; // 替换为你的主文件夹名称
  var results = findRosterSheets(mainFolderName);
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();

  // 添加标题
  body.appendParagraph("Roster表格搜索结果").setHeading(DocumentApp.ParagraphHeading.HEADING1);

  if (results.length === 0) {
    body.appendParagraph("未找到匹配的roster表格");
  } else {
    results.forEach(result => {
      var itemText = `${result.name}（所在文件夹：${result.parentFolder}）`;
      var listItem = body.appendListItem(itemText);
      // 给整行文本添加URL链接
      listItem.editAsText().setLinkUrl(0, itemText.length, result.url);
    });
  }

  DocumentApp.getUi().alert(`已找到${results.length}个roster表格，结果已写入当前Doc！`);
}

使用步骤 #

1. 打开一个新的Google Sheet或Google Doc
2. 点击菜单栏「扩展程序」→「Apps Script」，进入脚本编辑器
3. 将上述代码粘贴进去，把mainFolderName替换为你的实际主文件夹名称（比如"2024 season"）
4. 保存项目，然后运行对应的函数（exportToSheet或exportToDoc）
5. 首次运行会触发权限授权，按照提示完成授权即可

注意事项 #

• 确保你的Google账号拥有目标文件夹及其子文件夹的访问权限
• 文件名匹配逻辑可调整：如果需要精确匹配，把toLowerCase().includes("roster")改为getName() === "roster"
• 若存在同名主文件夹，脚本会取第一个匹配的文件夹，建议保证主文件夹名称唯一
• 获取到的URL可直接用于importrange()，格式示例：=IMPORTRANGE("https://docs.google.com/spreadsheets/d/xxx...", "Sheet1!A1:C10")

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