时间戳

首页 > 解决方案 > 如何自动从 Google 表格的上一行复制公式？

google-apps-script - 如何自动从 Google 表格的上一行复制公式？

问题描述

我制作了一个简单的用户界面来获取用户数据。它获取一些条目，将其放入指定的数据库并清除表格。这很好用。虽然第 0 到 6 列是用户数据，但第 7 到 11 列是使用 0 到 6 数据的公式。我正在寻找一种解决方案，用户不必输入数据然后手动需要复制上述公式。我不想事先输入公式，因为我认为我会遇到问题，因为脚本会查找最后一行。

简而言之：它需要找到工作表上的最后一行，将数据写入新行并将第 7 到 11 列中的公式从其上方的行复制到新行。完全是菜鸟，这有意义吗？

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()]]
  
  datasheet.getRange(datasheet.getLastRow()+1,1,1,6).setValues(values)

  formSS.getRange("d3").clearContent();
  formSS.getRange("d5").clearContent();
  formSS.getRange("d7").clearContent();
  formSS.getRange("d9").clearContent();
  formSS.getRange("d11").clearContent();
  formSS.getRange("d13").clearContent();
}

标签： google-apps-scriptgoogle-sheets

解决方案

有几个选项可以解决您的任务：

选项1：为要插入的行生成数据数组时，可以从上一行读取公式，并在插入前将它们添加到数组中

...
  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()],
      lastRow = datasheet.getLastRow(),
      formulas = datasheet.getRange(lastRow,7,1,5).getFormulasR1C1();
  
  datasheet.getRange(lastRow+1,1,1,11).setValues([[...values,...formulas[0]]])
...

选项 2。您可以直接在脚本中插入公式，而无需从工作表中读取它们

...
  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(),
                '=FormulaR1C1 for column 7',
                `=IMPORTXML(R1C2&R[0]C[-6]&"/","//div[@class='priceValue ']")`,
                '=FormulaR1C1 for column 9',
                '=FormulaR1C1 for column 10',
                '=FormulaR1C1 for column 11',]]
  
  datasheet.getRange(datasheet.getLastRow()+1,1,1,11).setValues(values)
...

推荐阅读