Google Sheets提交表单新数据时如何自动复制上一行的公式？

直接在现有代码的写入数据逻辑后，增加复制上一行公式的逻辑即可，Google Apps Script内置的copyTo方法可以直接实现公式复制，且会自动适配相对引用的公式偏移，效果和手动下拉复制公式完全一致。

修改后的完整代码 #

function submitData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Form");
  var datasheet = ss.getSheetByName("Database");

  // 获取表单输入值
  var values = [[formSS.getRange("d3").getValue(),
                formSS.getRange("d5").getValue(),
                formSS.getRange("d7").getValue(),
                formSS.getRange("d9").getValue(),
                formSS.getRange("d11").getValue(),
                formSS.getRange("d13").getValue()]];
  
  // 计算新数据写入的行号
  var newRow = datasheet.getLastRow() + 1;
  // 写入前6列用户输入数据
  datasheet.getRange(newRow, 1, 1, 6).setValues(values);

  // 复制上一行的第7-11列公式到新行对应列
  if (newRow > 2) { // 排除第一行是表头的情况，若无表头可直接删除该判断
    var lastRowWithFormula = newRow - 1;
    // 选中上一行第7到11列的区域
    datasheet.getRange(lastRowWithFormula, 7, 1, 5)
    // 复制到新行对应列，默认保留公式和格式
    .copyTo(datasheet.getRange(newRow, 7, 1, 5));
  }

  // 清空表单输入项
  formSS.getRange("d3").clearContent();
  formSS.getRange("d5").clearContent();
  formSS.getRange("d7").clearContent();
  formSS.getRange("d9").clearContent();
  formSS.getRange("d11").clearContent();
  formSS.getRange("d13").clearContent();
}

逻辑说明 #

• 提前计算新数据要写入的行号newRow，避免重复调用getLastRow()出现偏差
• copyTo方法默认会复制原区域的公式、格式、数据验证规则，如果你只需要复制公式，可以替换为.copyTo(datasheet.getRange(newRow, 7, 1, 5), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false)
• 新增的newRow > 2判断是为了兼容第一行是表头的场景，如果你Database表的第一行就有数据和公式，直接删除该判断即可
• 全程不会提前在空白行填充内容，不会影响getLastRow()识别最后一行数据的逻辑

内容的提问来源于stack exchange，提问作者Osman P.