Google Sheets脚本实现公式复制粘贴后转值功能求助

需求明确 #

• A1单元格为目标区域最后一行的参考值
• C2:D2包含公式，C3:D6（直至A1指定行）无公式
• 需自动执行两步操作：
  1. 将C2:D2的公式复制粘贴到C3:D[指定行]
  2. 将C3:D[指定行]的内容转为纯值

原脚本问题分析 #

你提供的脚本跳过了「复制公式」的核心步骤，直接对空区域执行setValues(range.getValues())自然不会生效——空区域获取的值都是空数组，写入后仍保持为空。

修正后的脚本 #

function copyFormulaAndConvertToValues() {
  const sheetName = "Sheet21";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName);
  const lastRow = sheet.getRange("A1").getValue();
  
  // 第一步：复制C2:D2的公式到目标区域
  const sourceFormulaRange = sheet.getRange("C2:D2");
  // 计算目标区域：从第3行开始，到lastRow行，共lastRow-2行，2列（C、D列）
  const targetRange = sheet.getRange(3, 3, lastRow - 2, 2);
  sourceFormulaRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_FORMULA, false);
  
  // 第二步：将目标区域转为纯值
  const calculatedValues = targetRange.getValues();
  targetRange.setValues(calculatedValues);
}

关键说明 #

1. 区域定位更准确：用getRange(row, column, numRows, numColumns)替代字符串拼接，避免因A1值格式问题导致的区域错误
2. 分两步执行：先通过copyTo并指定PASTE_FORMULA参数完成公式复制，再通过「获取计算值→写入值」的操作把公式结果转为纯值
3. 逻辑清晰：每一步对应需求中的操作，便于后续修改调试

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