如何在复制Google Sheets后将新文件URL保存至Reference工作表？

原代码核心问题 #

1. onOpen事件参数误用：onOpen的事件对象e不包含namedValues和range属性，原代码中获取entryRow的逻辑完全错误。
2. 工作表获取语法错误：SpreadsheetApp.getActiveSheet().getSheetName("Reference")写法错误，应通过整个电子表格对象调用getSheetByName指定工作表。
3. 变量未定义/取值错误：fileName未定义、date = Date取的是构造函数而非当前日期，导致写入失败。
4. 逻辑拆分不合理：将创建副本和写入记录拆分到两个函数，且onOpen的上下文无法支持正确写入。

修正方案（自动创建副本+记录） #

以下代码实现：打开模板时自动创建带日期的副本到指定Drive文件夹，同时将副本信息写入Reference工作表末尾，最后弹出打开副本的链接。

function onOpen() {
  makeAcopy();
}

function makeAcopy() {
  const templateId = '1EylqqfqXWaSCJbvVmqrwJHcwGPUvlQYOZ0wSpvT_mMY';
  const destFolderId = '1iCVsXmeSMHOpISQslJhX7r842aSHrqos';
  const baseName = 'My Workbook copy';
  
  // 获取模板文件与目标文件夹
  const templateFile = DriveApp.getFileById(templateId);
  const destFolder = DriveApp.getFolderById(destFolderId);
  
  // 生成带日期的文件名
  const dateStr = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "MM.dd.yyyy");
  const fileName = `${baseName}.${dateStr}`;
  
  // 创建副本并获取URL
  const copiedFile = templateFile.makeCopy(fileName, destFolder);
  const fileUrl = copiedFile.getUrl();
  
  // 写入Reference工作表
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const referenceSheet = ss.getSheetByName("Reference");
  if (!referenceSheet) {
    throw new Error("未找到名为Reference的工作表");
  }
  
  const nextRow = referenceSheet.getLastRow() + 1;
  // A列：创建日期，B列：文件名，C列：副本URL
  referenceSheet.getRange(nextRow, 1).setValue(new Date());
  referenceSheet.getRange(nextRow, 2).setValue(fileName);
  referenceSheet.getRange(nextRow, 3).setValue(fileUrl);
  
  // 弹出打开副本的对话框
  const htmlOutput = HtmlService.createHtmlOutput(
    `<a href="${fileUrl}" target="_blank">${fileName}</a>`
  ).setWidth(350).setHeight(50);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, '点击链接打开新副本');
}

可选方案（点击链接后创建副本） #

若需求是用户点击弹窗链接才创建副本（而非打开模板自动创建），使用以下代码：

function onOpen() {
  const htmlOutput = HtmlService.createHtmlOutput(`
    <a href="javascript:void(0)" onclick="createCopy()">点击创建并打开新副本</a>
    <script>
      function createCopy() {
        google.script.run
          .withSuccessHandler(url => {
            window.open(url, '_blank');
            google.script.host.close();
          })
          .createCopy();
      }
    </script>
  `).setWidth(350).setHeight(50);
  SpreadsheetApp.getUi().showModalDialog(htmlOutput, '创建副本');
}

function createCopy() {
  const templateId = '1EylqqfqXWaSCJbvVmqrwJHcwGPUvlQYOZ0wSpvT_mMY';
  const destFolderId = '1iCVsXmeSMHOpISQslJhX7r842aSHrqos';
  const baseName = 'My Workbook copy';
  
  const templateFile = DriveApp.getFileById(templateId);
  const destFolder = DriveApp.getFolderById(destFolderId);
  
  const dateStr = Utilities.formatDate(new Date(), SpreadsheetApp.getActiveSpreadsheet().getSpreadsheetTimeZone(), "MM.dd.yyyy");
  const fileName = `${baseName}.${dateStr}`;
  
  const copiedFile = templateFile.makeCopy(fileName, destFolder);
  const fileUrl = copiedFile.getUrl();
  
  // 写入Reference工作表
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const referenceSheet = ss.getSheetByName("Reference");
  if (!referenceSheet) {
    throw new Error("未找到名为Reference的工作表");
  }
  
  const nextRow = referenceSheet.getLastRow() + 1;
  referenceSheet.getRange(nextRow, 1).setValue(new Date());
  referenceSheet.getRange(nextRow, 2).setValue(fileName);
  referenceSheet.getRange(nextRow, 3).setValue(fileUrl);
  
  return fileUrl;
}

关键修正点 #

• 使用getLastRow()+1定位Reference表的下一个空白行，确保记录追加到末尾。
• 直接通过copiedFile.getUrl()获取副本链接，避免手动拼接的潜在错误。
• 将创建副本与写入记录的逻辑合并到同一函数，保证上下文一致，避免跨函数的变量传递问题。

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