google-apps-script - 复制公式并粘贴到特定范围，然后再次粘贴为值

Question

一个完全的新手，但渴望学习。

我们有一个 Google 表格文件，其中包含我们要在特定单元格中复制的公式。我们复制的公式有 3 列或更多列（这意味着脚本应该是动态的）。

请参阅测试文件 - https://docs.google.com/spreadsheets/d/1ayMoMFPllDk5XbIc8cYrnj9K0eFz461JrvdtzW3F5BI/edit?usp=sharing

在 R 到 W 列中（有些表格只有 3 到 10 列有这个公式），我们需要将公式复制到今天的日期 + 1（所以今天是 3 月 31 日，我们需要复制到 4 月 1 日之前的公式）。

复制公式后，我们希望将它们粘贴为值，但​​不包括 4 月 1 日，因此一旦我们再次运行脚本，我们将拥有公式 - 我们想要这样做的原因是当我们使用公式时，表格的响应速度非常慢使用的是计算结果（公式是数组索引/匹配）。

我们将逻辑想象为今天的日期与 B 列中的相同，然后将公式插入该行但只有列 RV （但这部分需要通过动态来容纳其他列数）

录制的宏我们尝试过，但并不接近我们想要的。

function CopyandPasteasValues() {

  var spreadsheet = SpreadsheetApp.getActive();

  spreadsheet.getCurrentCell().offset(1, 0, 7, 5).activate();
  spreadsheet.getCurrentCell().offset(-1, 0, 1, 5).copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  spreadsheet.getCurrentCell().offset(0, 0, 6, 5).activate();
  spreadsheet.getActiveRange().copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
};

标签： google-apps-scriptgoogle-sheets

Answer 1

试试这个，有点麻烦，但缺少调整大小/偏移使事情变得更难。

function fillFormulasDown() {
  const ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  const lastCol = ws.getLastColumn();
  const lastRow = ws.getLastRow();
  const lastRowInR = ws.getRange("R1:R").getValues().filter(String).length;
  const rngFormulas = ws.getRange(lastRowInR,18, 1, lastCol-18+1);
  const rngDst = ws.getRange(lastRowInR,18, lastRow-lastRowInR+1, lastCol-18+1);
  const rngValues = ws.getRange(lastRowInR,18, lastRow-lastRowInR, lastCol-18+1);

  rngFormulas.copyTo(rngDst);

  rngValues.setValues(rngValues.getValues());

}