Google Sheets宏开发：如何将公式填充至A列最后一行数据行（替代VBA逻辑）

一、先解决「不可达代码」的问题 #

你代码里的return语句直接写在了全局代码中，这会导致脚本执行到这里就直接终止，后面的代码自然永远不会被执行（也就是显示灰色的不可达状态）。我们需要把获取最后一行的逻辑封装成一个独立的函数，而不是在全局代码里直接用return。

二、精准获取A列有数据的最后一行 #

sheet.getLastRow()返回的是整个工作表的最后一行（哪怕其他列有数据、A列是空的也会算），所以我们需要专门针对A列查找最后一个非空单元格的行号。

修正后的完整代码 #

function fillFormulasToLastRowInColumnA() {
  var sheetName = "Week 1";
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName(sheetName);
  
  // 定义工具函数：获取指定列的最后一个有数据的行号
  function getLastDataRow(sheet, columnLetter) {
    var columnRange = sheet.getRange(columnLetter + ":" + columnLetter);
    var columnValues = columnRange.getValues();
    // 从下往上遍历，找到第一个非空单元格
    for (var i = columnValues.length - 1; i >= 0; i--) {
      if (columnValues[i][0] !== "") {
        return i + 1; // 数组索引从0开始，行号从1开始，所以要+1
      }
    }
    return 1; // 如果整列都为空，返回第一行作为默认值
  }
  
  // 获取A列最后一行有数据的行号
  var lastRowInA = getLastDataRow(sheet, "A");
  
  // 设置E7的公式（无需激活单元格，直接操作range对象即可）
  var formulaCell = sheet.getRange('E7');
  formulaCell.setFormula('=iferror(if(match($B7,Names,0),VLOOKUP($B7,Special,3,false),0),0)');
  
  // 计算需要填充的范围：从E7到E列的lastRowInA行
  // getRange参数：起始行, 列号, 行数；E列对应数字5，行数=最后一行号 - 起始行号 + 1
  var fillDownRange = sheet.getRange(7, 5, lastRowInA - 6); 
  
  // 批量填充公式
  formulaCell.copyTo(fillDownRange);
}

关键修改点说明 #

1. 封装工具函数：把获取最后一行的逻辑放到getLastDataRow函数中，避免全局作用域的return中断代码执行。
2. 精准定位A列最后一行：通过从下往上遍历A列所有值，确保找到的是A列真正有数据的最后一行，而非整个工作表的最后一行。
3. 简化单元格操作：移除不必要的activate()操作，Google Apps Script中直接操作range对象更高效简洁。
4. 修正填充范围计算：lastRowInA - 6是因为从第7行开始填充，到lastRowInA行总共的行数是lastRowInA - 7 + 1 = lastRowInA -6。

额外优化小技巧 #

如果你的A列从第7行开始没有空行（数据是连续的），可以用更简洁的写法获取最后一行：

var lastRowInA = sheet.getRange("A7").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();

不过这种方法在A列存在空行时会出错，所以还是遍历的方法兼容性更强。

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