Google Sheets脚本问题：按列值将公式转值功能失效

需求：当A列单元格值为"Removed"时，将该行右侧全部或部分单元格的公式替换为值。原脚本错误地将B列内容覆盖到A列，且未遍历所有目标行。

原代码问题分析 #

• sheet.getRange(1, 1, sheet.getMaxRows(), 1)获取的是整个A列（包含大量空行），range.offset(0,0)仍为整个A列范围，getValue()仅返回A1单元格的值，无法实现逐行判断
• nextCell1.copyTo(nextCell0,{contentsOnly: true})是将整个B列复制到A列，这是A列被覆盖的直接原因

修正后的代码（两种场景） #

场景1：处理该行右侧全部单元格（从B列到最后一列） #

function convertFormulasToValues() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  const lastRow = sheet.getLastRow();
  const lastCol = sheet.getLastColumn();
  
  // 逐行遍历数据行
  for (let row = 1; row <= lastRow; row++) {
    const aCellValue = sheet.getRange(row, 1).getValue();
    if (aCellValue === 'Removed') {
      // 获取该行从B列到最后一列的范围
      const targetRange = sheet.getRange(row, 2, 1, lastCol - 1);
      // 将公式转为值（复制自身，仅保留内容）
      targetRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }
  }
}

场景2：处理该行指定部分单元格（示例：B到D列） #

function convertSpecificColumnsToValues() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  const lastRow = sheet.getLastRow();
  const startCol = 2; // B列对应列索引
  const endCol = 4; // D列对应列索引
  
  for (let row = 1; row <= lastRow; row++) {
    const aCellValue = sheet.getRange(row, 1).getValue();
    if (aCellValue === 'Removed') {
      const targetRange = sheet.getRange(row, startCol, 1, endCol - startCol + 1);
      targetRange.copyTo(targetRange, SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    }
  }
}

代码关键说明 #

• 用getLastRow()和getLastColumn()获取实际数据的行/列数，避免无效遍历空行空列，提升执行效率
• 逐行判断A列单元格值，精准定位需要处理的行
• 通过copyTo方法结合PASTE_VALUES参数，实现将目标单元格的公式直接转为计算后的值

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