谷歌应用脚本：仅复制和粘贴公式

Question

我需要使用Google Apps Script仅复制和粘贴电子表格中的公式。

var range = activeSheet.getRange(targetRow-1, 1, 1, activeSheet.getLastColumn());
range.copyTo(activeSheet.getRange(targetRow, 1, 1, activeSheet.getLastColumn()), {contentsOnly:false});

这给了我完整的信息，包括公式和数据。然而，我希望只复制公式（不包括数据）。

CopyFormulasToRange 不幸地不存在。

- DomDom

Answer 1

使用getFormulasR1C1和setFormulasR1C1复制公式并保留相对引用。例如：

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

  var currentRow = sheet.getLastRow();

  var sourceRange = sheet.getRange(currentRow, 3, 1, 4);
  var sourceFormulas = sourceRange.getFormulasR1C1();

  currentRow++;

  var targetRange = sheet.getRange(currentRow, 3, 1, 4);
  targetRange.setFormulasR1C1(sourceFormulas);

getFormulaR1C1() 方法可用于 Google Sheets 中的范围对象，它返回指定范围的公式（使用 R1C1 参照样式）。

setFormulasR1C1(formulas) 方法可用于将一组公式（使用 R1C1 参照样式）应用于指定范围中的每个单元格。此方法不仅可以设置现有的单元格公式，还可以覆盖所有其他单元格格式设置。

- Mark Bird

---

这个解决方案对我没用，因为它会复制每一列，即使其中没有包含公式的列也会被复制。我不太明白为什么这是个问题，但在我的情况下它造成了很大的混乱。我想这可能与我有些公式写入到除了它们所在的列之外的其他列有关。我不得不编写一个更加选择性的函数，只复制公式。我已经将其作为另一个答案添加了进去。虽然如此，我还是感谢这个答案，因为它给了我一个起点。 - CryptoFool

Answer 2

以下函数仅会复制公式。它具有用户设置，用于指定从哪一行和列开始获取公式。

function copyFormulas() {
  var activeSheet,numberOfSourceColumnsToGet,sourceColumnStart,sourceFormulas,sourceRange,
      sourceRowStart,targetColumn,targetRange,targetRowStart;

  //USER INPUT

  sourceRowStart = 1; //Row to start getting formulas from
  sourceColumnStart = 2; //Column to start getting formulas from
  numberOfSourceColumnsToGet = 1; //Number of columns to get formulas from

  targetRowStart = 1; //Row to start copying formulas to
  targetColumn = 3; //Column to start copying formulas to

  //END OF USER INPUT

  activeSheet = SpreadsheetApp.getActiveSheet();
  sourceRange = activeSheet.getRange(sourceRowStart, sourceColumnStart, activeSheet.getLastRow(), numberOfSourceColumnsToGet);

  sourceFormulas = sourceRange.getFormulas();//Get only formulas from the source range

  targetRange = activeSheet.getRange(targetRowStart,targetColumn,sourceFormulas.length,sourceFormulas[0].length);

  targetRange.setFormulas(sourceFormulas);//Copy the formulas to the target range
}

- Alan Wells

---

似乎公式没有扩展；就像 Excel 的“下拉公式”一样。 - Alexis Paques

亲爱的@Alan Wells，当表单提交时如何使用自动扩展功能复制粘贴公式？ - Mohamed H

要学习如何在 Google 表单提交时运行函数，请阅读以下链接中的文档：https://developers.google.com/apps-script/guides/triggers/events - Alan Wells

这个函数不会更新复制的公式以引用新行中的单元格值。所有引用保持不变，仍然引用从中复制公式的行中的单元格。也许有些情况下您希望如此，但在我看来，更常见的情况是希望新公式引用新行中单元格的值。我添加了一个答案，在仅复制包含公式的单元格的同时实现了这一点。 - CryptoFool

Answer 3

我在尝试弄清楚如何仅复制一行中的公式并将其粘贴到另一行时发现了这个问题。这是电子表格中非常常见的模式，每个数据集都是单行，该行的某些列是计算值。您希望所有复制的公式都引用新行中的值，而不是从中复制公式的行。

@AllenWell的答案复制了公式，但至少对我来说并没有更新单元格引用以引用新行中的单元格。使用他的代码，复制的公式仍然参考从中复制公式的行。我后来了解到这里的秘密是要使用get/setFormulasR1C1而不是get/setFormulas，因为后者使用相对引用，使得公式引用它们所在的任何行中的值。

@MarkBird的答案使用get/setFormulasR1C1但对我不起作用。它似乎会在新行的每个单元格中放置一些内容，因为即使对于不包含公式的列，它也会进行复制。这破坏了我的逻辑，其中我有多个列中的公式只在它们为空白时才提供结果。他的代码在更简单的情况下可能很好用，但如果它只复制公式那将更好。这就是我的解决方案做的事情。

我编写了一个更复杂的函数，以这样的方式仅复制公式，使得公式引用新行中的值。我获取所有公式，然后有选择地将它们复制到新行中，跳过公式列表中的空位。这给我想要的行为，我认为大多数人在创建电子表格中的新行时都想要这种行为。这是执行此操作的函数，它需要Sheet引用和源行和目标行的索引：

function copyFormulas(sheet, sourceRow, targetRow) {
    var formulas = sheet.getRange(sourceRow, 1, 1, sheet.getLastColumn()).getFormulasR1C1()[0];
    for (var i = 0; i < formulas.length; i++) {
      if (formulas[i] !== '') {
        sheet.getRange(targetRow, i + 1).setFormulaR1C1(formulas[i]);
      }
    }
}

注意测试公式是否为空。我认为这是@MarkBird更简单的解决方案所缺少的。

- CryptoFool