基于模板URL批量复制Google Sheet并自定义命名的App Script需求

需求与现有问题 #

• 核心需求：从外部多Sheet模板URL复制完整Spreadsheet，将输入名称前缀添加到新文件名称，仅创建勾选复选框的文档，存储到指定Drive文件夹并共享给指定邮箱
• 现有脚本问题：只能复制当前文件内的单个Sheet，无法保留多页模板的格式，代码冗余重复，未实现复选框筛选逻辑

修改后的完整脚本 #

const ui = SpreadsheetApp.getUi();
const ss = SpreadsheetApp.getActiveSpreadsheet();
const createSheet = ss.getSheetByName('create');
const rosterSheet = ss.getSheetByName('roster');

// 模板配置：[模板名称, 模板URL, 对应roster表中的复选框列索引(从0开始)]
const TEMPLATES = [
  ['Timelog', '替换为你的Timelog模板URL', 2],
  ['Follow Up Tracker', '替换为你的Follow Up Tracker模板URL', 3],
  ['1:1 Report', '替换为你的1:1 Report模板URL', 4],
  ['Training', '替换为你的Training模板URL', 5]
];

function onOpen() {
  ui.createMenu('自定义菜单')
    .addItem('创建新文档', 'makeNewSheets')
    .addItem('清空历史', 'clearContent')
    .addToUi();
}

function clearContent() {
  const historyRange = ss.getRangeByName('DocHistory');
  if (historyRange) historyRange.clearContent();
}

function makeNewSheets() {
  const folderId = createSheet.getRange('B3').getValue();
  if (!folderId) {
    ui.alert('请先在create表B3单元格填写目标Drive文件夹ID');
    return;
  }
  const targetFolder = DriveApp.getFolderById(folderId);

  // 获取roster表的用户数据（姓名、邮箱、各模板复选框）
  const rosterData = rosterSheet.getRange(2, 1, rosterSheet.getLastRow() - 1, TEMPLATES.length + 1).getValues();

  rosterData.forEach(row => {
    const userName = row[0];
    const userEmail = row[1];
    if (!userName || !userEmail) return; // 跳过空行

    TEMPLATES.forEach((template, idx) => {
      const [templateName, templateUrl, checkboxCol] = template;
      const isChecked = row[checkboxCol];
      if (!isChecked) return; // 未勾选则跳过

      try {
        // 复制完整模板文件
        const templateFile = DriveApp.getFileById(extractFileIdFromUrl(templateUrl));
        const newFileName = `${userName} - ${templateName}`;
        const newFile = templateFile.makeCopy(newFileName, targetFolder);
        
        // 添加编辑权限
        newFile.addEditor(userEmail);

        // 记录到历史表
        const nextRow = createSheet.getLastRow() + 1;
        createSheet.getRange(nextRow, 1).setValue(newFileName);
        createSheet.getRange(nextRow, 2).setValue(newFile.getUrl());

      } catch (e) {
        ui.alert(`创建${templateName}失败：${e.message}`);
      }
    });
  });

  ui.alert('文档创建完成！');
}

// 辅助函数：从Google Sheets URL中提取文件ID
function extractFileIdFromUrl(url) {
  const match = url.match(/[-\w]{25,}/);
  return match ? match[0] : '';
}

关键改进说明 #

1. 模板配置化：通过TEMPLATES数组统一管理所有模板的名称、URL和对应复选框列，后续新增/修改模板只需修改此数组，无需重复编写逻辑
2. 完整复制多Sheet模板：使用DriveApp.File.makeCopy()直接复制整个模板文件，完美保留所有Sheet的格式、公式和数据，解决了单Sheet复制的格式问题
3. 复选框筛选逻辑：读取roster表中对应列的复选框状态，仅创建勾选的模板文档
4. 代码复用与性能优化：将文件移动、权限分配、历史记录等重复逻辑封装到循环中，减少冗余操作，提升执行效率
5. 错误处理：添加try-catch捕获异常并弹窗提示，避免脚本中途崩溃
6. URL转ID辅助函数：自动从模板URL中提取文件ID，无需手动填写ID

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