Google Sheets匹配Drive文件夹名获链接：代码报错及实时运行咨询

问题说明 #

• 需求：将Google Sheets中G3及以下行的文件夹名称匹配到对应Google Drive文件夹，把链接写入O3及以下行
• 现有问题：运行代码仅返回前2个链接，之后触发错误：Cannot retrieve the next object: iterator has reached the end
• 原有代码：

function onOpen() {
  var sheet = SpreadsheetApp.getActive();
  var menu = [{name: "Get Folder Links", functionName: "getFolderLinks"}];
  sheet.addMenu("Custom Menu", menu);
}

function getFolderLinks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;
  var data = sheet.getRange(startRow, 7, sheet.getLastRow() - startRow + 1, 1).getValues();
  
  for (var i = 0; i < data.length; i++) {
    var folderName = data[i][0];
    var folder = DriveApp.getFoldersByName(folderName).next();
    var link = folder.getUrl();
    sheet.getRange(i+startRow, 15).setValue(link);
    logger.log(query);
  }
}

• 额外咨询：能否实现无需点击自定义菜单，实时自动运行且无限制？

错误修复方案 #

错误原因 #

DriveApp.getFoldersByName(folderName)返回的是文件夹迭代器，直接调用.next()会在两种场景下抛出异常：

1. G列中某行的文件夹名称为空
2. 该名称在Drive中没有匹配的文件夹
3. 原有代码逐行写入单元格，频繁调用API容易触发性能问题

修复后代码 #

function onOpen() {
  var sheet = SpreadsheetApp.getActive();
  var menu = [{name: "获取文件夹链接", functionName: "getFolderLinks"}];
  sheet.addMenu("自定义菜单", menu);
}

function getFolderLinks() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;
  var lastRow = sheet.getLastRow();
  // 批量读取G列数据
  var data = sheet.getRange(startRow, 7, lastRow - startRow + 1, 1).getValues();
  // 准备批量写入的结果数组
  var results = [];

  for (var i = 0; i < data.length; i++) {
    var folderName = data[i][0];
    var link = "";
    // 跳过空名称
    if (folderName) {
      var folders = DriveApp.getFoldersByName(folderName);
      // 检查是否有匹配文件夹再获取
      if (folders.hasNext()) {
        link = folders.next().getUrl();
        // 若需处理多个同名文件夹，可在此处循环folders.hasNext()遍历
      } else {
        link = "未找到对应文件夹";
      }
    }
    results.push([link]);
    Logger.log(`处理：${folderName} → ${link}`);
  }

  // 批量写入O列，提升运行效率
  sheet.getRange(startRow, 15, results.length, 1).setValues(results);
}

关键改动 #

• 增加空名称校验，避免无效查询
• 先判断迭代器是否有匹配项再调用.next()，防止抛出异常
• 采用批量读写模式，减少Spreadsheet API调用次数，提升性能
• 修复Logger.log的变量错误，输出有效调试信息

实时自动运行实现 #

可以通过可安装编辑触发器实现G列内容变更时自动获取链接，无需手动点击菜单。

实现代码 #

function handleFolderLinkEdit(e) {
  var sheet = e.source.getActiveSheet();
  var startRow = 3;
  var editedCol = e.range.getColumn();
  var editedRow = e.range.getRow();

  // 仅响应G列（第7列）且行号≥3的编辑操作
  if (editedCol === 7 && editedRow >= startRow) {
    var folderName = e.value;
    var link = "";
    if (folderName) {
      var folders = DriveApp.getFoldersByName(folderName);
      link = folders.hasNext() ? folders.next().getUrl() : "未找到对应文件夹";
    }
    // 写入对应行的O列（第15列）
    sheet.getRange(editedRow, 15).setValue(link);
  }
}

配置步骤 #

1. 打开脚本编辑器，点击左侧「触发器」图标
2. 点击「添加触发器」，配置如下：
   • 选择函数：handleFolderLinkEdit
   • 部署来源：Head
   • 事件类型：「从电子表格」→「编辑时」
3. 保存并完成授权流程

注意事项 #

• 可安装触发器具备访问DriveApp的权限，解决了简单触发器的权限限制
• Google Apps Script有配额限制（如每日触发次数、单次运行时长），但普通办公场景下不会触及上限

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