如何解决Google Sheets条件跨表拷贝脚本超出范围报错问题

核心错误原因 #

• TARGET表非A列存在公式，原生getLastRow()会统计所有包含内容（含公式）的行，无法正确获取A列的最后填充行，导致写入行号偏移、甚至超出表格范围抛出坐标超出边界的报错。
• 你新增的获取A列最后填充行的逻辑是正确的，但调用getRange时传参错误：你嵌套了两层getRange，给外层getRange传入了内层返回的Range对象，而非要求的数字行号，因此触发类型转换报错。同时你获取到的lastFilledRow是字符串格式，需要转换为数字后+1，才是A列的首个空白行号。
• 原脚本的条件判断存在逻辑隐患：H列数值比较时用了字符串"45"，若H列为数值格式会导致判断失效；同时逻辑运算符用了单个&，应改用&&做逻辑与判断。

修正后完整代码 #

function CopyTo(){
  var sheet = SpreadsheetApp.getActive();
  var sourceSheet = sheet.getSheetByName("SOURCE");
  var destination = sheet.getSheetByName("TARGET");
  var lastRow = sourceSheet.getDataRange().getLastRow();

  for(var row=3; row<=lastRow; row++){
    // 修正条件判断：数值比较用数字、逻辑与用&&
    if(sourceSheet.getRange(row,1).getValue() == "" && sourceSheet.getRange(row,8).getValue() >= 45){
      Logger.log("CELL H"+row+" has \">=45\""+"\n=================\n"+"CELL A"+row+" is \"empty\""+"\n=================\n"+"RANGE TO COPY:\n"+ "\"G"+row+":G"+row+"\"");
      var rangeToCopy = "G"+row+":G"+row;
      // 正确获取A列首个空白行
      var column = destination.getRange('A' + destination.getMaxRows());
      var lastFilledRow = Number(column.getNextDataCell(SpreadsheetApp.Direction.UP).getA1Notation().slice(1));
      var targetFirstEmptyRow = lastFilledRow + 1;
      // 修正copyTo传参
      sourceSheet.getRange(rangeToCopy).copyTo(destination.getRange(targetFirstEmptyRow, 1), {contentsOnly:true});
      // 写入其他列内容
      destination.getRange(targetFirstEmptyRow, 9).setValue("Added Text Column 9");
      destination.getRange(targetFirstEmptyRow, 13).setValue("Added Text Column 13");
      destination.getRange(targetFirstEmptyRow, 14).setValue(new Date());
    }
  }
}

可选优化建议 #

如果SOURCE表数据量较大，可一次性读取所有行的A、G、H列值做批量判断，减少getRange、getValue的调用次数，提升脚本运行效率。

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