Google Apps Script二次运行覆盖原有超链接变为undefined问题咨询

AI 小北

2026-9-25

错误原因

getLinkForFolderName_函数仅在首次创建新文件夹的分支逻辑中返回了文件夹URL，当文件夹已存在、走已有文件夹查询分支时没有定义返回值，因此第二次运行脚本时，已有文件夹对应的返回值为undefined，就会把原有正确超链接覆盖为错误格式。

修复后完整代码

function creatFolder() {
  const parent = DriveApp.getFolderById("insert folder id");
  const sheet = SpreadsheetApp.getActive().getSheetByName('Detailing');
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 2);
  range.getValues().forEach(function (row, index) {
    if(row[0]) {
      var newLink = getLinkForFolderName_(parent, row[0]);
      range.offset(index, 0, 1).setFormula(
        "HYPERLINK(\"" + newLink + "\", \"" + row[0] + "\")"
      );
    }
  });
}

function getLinkForFolderName_(root, name) {
  var folder;
  const search = root.getFoldersByName(name);
  if (search.hasNext()) {
    folder = search.next();
    if (search.hasNext())
      console.warn("Multiple folders named '" + name + "' in root folder '" + root.getName() + "'");
    // 补充已有文件夹场景的URL返回逻辑
    return folder.getUrl();
  }
  else {
    folder = root.createFolder(name);
    ["Quotes", "Construction dwg's", "Window Schedule", "Data File", "Delivery Photos"].forEach(function(e) {
      folder.createFolder(e);
    });
    return folder.getUrl();
  }
}