Google Sheets自定义宏复制公式失效，录制宏正常的原因排查

问题背景 #

我有一个关联Google表单的Google表格，表单响应会自动记录到表格中。需要为响应对应的后续单元格添加数据处理逻辑（比如将时间戳转换为周数、年份，计算提交后的天数），已经在第2行（示例行）设置好公式，尝试通过自定义宏将这些公式复制到有数据的最后一行。但测试发现：自定义宏只能成功复制数据验证，公式复制后单元格仍为空白；而录制的宏却能正常完成公式复制操作，两者逻辑看似一致，求问题原因。

涉及代码 #

自定义宏（无法复制公式） #

function Copypaste() 
{
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
  var lastrow = sheet1.getLastRow();
  sheet1.getRange(lastrow, 12).activate();
  sheet1.getRange(2, 12, 1, 15).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  sheet1.getRange(2, 12, 1, 15).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
};

录制的宏（可正常运行） #

function CopyFormula() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('L12').activate();
  spreadsheet.getRange('L2:Q2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getRange('L2:Q2').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false);
};

问题原因 #

核心问题是自定义宏中未定义spreadsheet变量，却调用了spreadsheet.getActiveRange()：

• 录制的宏开头明确定义了var spreadsheet = SpreadsheetApp.getActive();，后续调用spreadsheet.getActiveRange()是合法的；
• 自定义宏仅定义了sheet1，未声明spreadsheet变量，执行到spreadsheet.getActiveRange()时会抛出变量未定义的错误，导致公式复制步骤直接终止（你看到的"成功复制数据验证"可能是之前测试的残留结果，实际脚本执行已失败）。

修复方案 #

方案1：补全spreadsheet变量定义 #

在自定义宏开头添加spreadsheet变量的定义，保持原有逻辑：

function Copypaste() 
{
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = spreadsheet.getSheetByName('Form Responses');
  var lastrow = sheet1.getLastRow();
  sheet1.getRange(lastrow, 12).activate();
  sheet1.getRange(2, 12, 1, 15).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  sheet1.getRange(2, 12, 1, 15).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
};

方案2：移除冗余的单元格激活操作（更高效） #

激活单元格是录制宏的冗余操作，直接指定源范围和目标范围的复制逻辑更高效，也避免了依赖激活状态的问题：

function Copypaste() 
{
  var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses');
  var lastrow = sheet1.getLastRow();
  // 定义源范围（第2行，第12列开始的15个单元格）和目标范围（最后一行对应位置）
  var sourceRange = sheet1.getRange(2, 12, 1, 15);
  var targetRange = sheet1.getRange(lastrow, 12, 1, 15);
  // 复制公式
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  // 复制数据验证
  sourceRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_DATA_VALIDATION, false);
};

排查技巧 #

脚本执行失败时，可通过**脚本编辑器右上角的"执行日志"**查看具体报错信息，比如变量未定义、范围越界等错误都会明确显示，帮助快速定位问题。

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