基于Google Sheets脚本实现按单元格指定文件夹上传Drive附件

核心修改思路 #

1. 从ADMIN工作表读取分类名称与对应Folder ID的映射
2. 动态生成带分类名称的自定义菜单选项
3. 将选中分类的Folder ID传递给上传逻辑，替换固定ID实现定向存储

步骤1：修改Google Apps Script代码 #

替换原有的onOpen、openAttachmentDialog、saveFile函数，并新增辅助函数：

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const uploadMenu = ui.createMenu('上传文件');
  
  // 读取ADMIN工作表的分类与Folder ID（A列=Folder ID，B列=分类名称，范围A2:B9）
  const adminSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('ADMIN');
  const categoryData = adminSheet.getRange('A2:B9').getValues();
  
  // 动态生成分类菜单选项
  categoryData.forEach(row => {
    const folderId = row[0].toString().trim();
    const categoryName = row[1].toString().trim();
    // 跳过空行或无效数据
    if (folderId && categoryName) {
      uploadMenu.addItem(`附加文件【${categoryName}】`, () => openAttachmentDialog(folderId));
    }
  });
  
  // 保留原有的通用上传选项（可选）
  uploadMenu.addSeparator();
  uploadMenu.addItem('通用上传（默认文件夹）', 'openAttachmentDialog');
  uploadMenu.addToUi();
}

function openAttachmentDialog(targetFolderId = null) {
  let htmlDialog = HtmlService.createHtmlOutputFromFile('UploadFile');
  
  // 将目标Folder ID传递给对话框
  if (targetFolderId) {
    htmlDialog = htmlDialog.setProperty('targetFolderId', targetFolderId);
  }
  
  SpreadsheetApp.getUi().showModalDialog(htmlDialog, '上传文件');
}

// 供对话框获取目标Folder ID的辅助函数
function getTargetFolderId() {
  const dialogHtml = HtmlService.getHtmlOutputFromFile('UploadFile');
  return dialogHtml.getProperty('targetFolderId');
}

function saveFile(obj) {
  // 优先使用传递的分类Folder ID，无则用默认ID
  const targetFolderId = obj.folderId || "1J5naBr1_PPgTLNpJsgro3r77yDJLmXTr";
  
  const fileBlob = Utilities.newBlob(Utilities.base64Decode(obj.data), obj.mimeType, obj.fileName);
  const targetFolder = DriveApp.getFolderById(targetFolderId);
  const uploadedFile = targetFolder.createFile(fileBlob);
  
  // 生成单元格超链接公式
  const linkFormula = `hyperlink("${uploadedFile.getUrl()}";"${uploadedFile.getName()}")`;
  const activeCell = SpreadsheetApp.getActiveSheet().getSelection().getCurrentCell();
  activeCell.setFormula(linkFormula);
  
  return uploadedFile.getId();
}

步骤2：修改UploadFile.html文件 #

更新HTML中的脚本逻辑，添加初始化函数获取目标Folder ID，并在上传时传递给saveFile：

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
  </head>
  <script>
    let targetFolderId = null;
    
    // 初始化获取目标Folder ID
    function initDialog() {
      google.script.run.withSuccessHandler(folderId => {
        targetFolderId = folderId;
      }).getTargetFolderId();
    }

    function getFiles() {
      document.getElementById("uploadButton").disabled = true;
      const progressText = document.getElementById("progress");
      const fileInput = document.getElementById('files');
      let completedCount = 0;
      const totalFiles = fileInput.files.length;
      
      progressText.innerHTML = `正在上传文件 ${completedCount + 1}/${totalFiles}`;
      
      [...fileInput.files].forEach((file, index) => {
        const reader = new FileReader();
        reader.onload = (e) => {
          const dataParts = e.target.result.split(",");
          const uploadObj = {
            fileName: fileInput.files[index].name,
            mimeType: dataParts[0].match(/:(\w.+);/)[1],
            data: dataParts[1],
            folderId: targetFolderId // 传递分类对应的Folder ID
          };
          
          google.script.run.withSuccessHandler(() => {
            completedCount++;
            if (completedCount >= totalFiles) {
              progressText.innerHTML = "上传完成";
              setTimeout(() => google.script.host.close(), 1000);
            } else {
              progressText.innerHTML = `正在上传文件 ${completedCount + 1}/${totalFiles}`;
            }
          }).saveFile(uploadObj);
        }
        reader.readAsDataURL(file);
      });
    }
  </script>
  <body onload="initDialog()">
    <input type="file" name="upload" id="files"/>
    <input type='button' id="uploadButton" value='上传' onclick='getFiles()' class="action"> 
    <br><br>
    <div id="progress"> </div>
  </body>
</html>

注意事项 #

1. ADMIN工作表结构要求：确保ADMIN表中A2:A9为Folder ID，B2:B9为对应的分类名称（若结构不同，可修改onOpen函数中categoryData.forEach里的row[0]和row[1]顺序）
2. 权限验证：确保脚本拥有Drive文件创建权限，首次运行时需授权
3. 空行处理：脚本会自动跳过ADMIN表中Folder ID或分类名称为空的行

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