如何在Google Sheets中基于列行数设置单元格范围并自动填充公式？

我的宏需要实现两个功能：向单元格插入公式，再将公式向下复制指定行数。行数根据表格某列的内容而定，而且不能复制到表格底部（因为主表格下方还有其他内容）。请问怎么统计指定列的内容行数，并把这个数值用到公式向下复制的范围里？

能正确返回行数的代码 #

我已经写出了能正确返回目标行数的代码：

function AutoNotes3() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;

  console.log(lastRow);

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AW8').activate();
  spreadsheet.getCurrentCell().setFormula([lastRow]-2); // 减2是为了排除我不需要的范围下方的额外内容
};

尝试但无法生效的代码 #

我尝试把这个行数应用到公式复制范围里，但代码运行后无法正确应用范围：

function AutoNotes4() {
  const sheet = SpreadsheetApp.getActiveSheet();

  const data = sheet.getDataRange().getValues();
  const lastRow = data.length;

  console.log(lastRow);

  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('AW8').activate();
  spreadsheet.getCurrentCell().setFormula('=VLOOKUP(B8,INDIRECT(Settings!$A$3&"!$B$8:$AW"), 48, FALSE)');
  spreadsheet.getActiveRange().autoFill(spreadsheet.getRange('AW8:AW&[lastRow]-2'), SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

解决方案 #

问题出在范围字符串的拼接方式上，你不能直接在字符串里用'AW8:AW&[lastRow]-2'这种写法，JavaScript需要用模板字符串（反引号`）或者+运算符来拼接变量和字符串。另外可以简化代码，去掉不必要的activate操作，提升执行效率：

修正后的完整代码

function AutoNotes4() {
  const sheet = SpreadsheetApp.getActiveSheet();
  // 获取整个数据范围的行数
  const lastRow = sheet.getDataRange().getValues().length;
  // 计算最终要填充到的行号（减2排除下方额外内容）
  const targetRow = lastRow - 2;
  
  // 给AW8单元格设置公式
  const startCell = sheet.getRange('AW8');
  startCell.setFormula('=VLOOKUP(B8,INDIRECT(Settings!$A$3&"!$B$8:$AW"), 48, FALSE)');
  
  // 定义自动填充的完整范围
  const fillRange = sheet.getRange(`AW8:AW${targetRow}`);
  // 执行自动填充
  startCell.autoFill(fillRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

针对“统计指定列行数”的优化

如果你的行数需要基于某一列的内容（而非整个数据范围），比如统计B列从第8行开始的非空行数，可以替换行数计算逻辑：

function AutoNotes4() {
  const sheet = SpreadsheetApp.getActiveSheet();
  // 选择要统计的列（比如B列从第8行开始）
  const targetColumnRange = sheet.getRange('B8:B');
  const columnValues = targetColumnRange.getValues();
  
  // 找到该列最后一个非空单元格的行号
  const lastDataRowIndex = columnValues.findLastIndex(row => row[0] !== '');
  const lastDataRow = lastDataRowIndex + 8; // 加8是因为从第8行开始统计
  
  // 这里不需要再减2，因为已经精准定位到该列最后一个有效行
  const targetRow = lastDataRow;
  
  // 后续设置公式和自动填充逻辑和上面一致
  const startCell = sheet.getRange('AW8');
  startCell.setFormula('=VLOOKUP(B8,INDIRECT(Settings!$A$3&"!$B$8:$AW"), 48, FALSE)');
  
  const fillRange = sheet.getRange(`AW8:AW${targetRow}`);
  startCell.autoFill(fillRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
};

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