Google Apps Script删除指定行报错TypeError: Cannot call method "getRange" of null

Hey there! Let's break down why your script is hitting that error and fix it up properly.

首先，错误的核心原因 #

The TypeError: Cannot call method "getRange" of null error means this line:

var s = ss.getSheetByName('delete containing');

is returning null. In plain terms: your Google Sheet doesn't have a worksheet named delete containing (or the name doesn't match exactly).

Double-check the worksheet name carefully—Google Sheets is case-sensitive, so make sure spaces, capitalization, and any special characters are identical to what's in your script. For example, if the sheet is named Delete Containing (with capital D and C), your script will fail because it's looking for lowercase.

其次，修复原代码里的其他潜在问题 #

Even once you fix the sheet name, your original script has a couple of other issues that might cause unexpected behavior:

1. Incorrect array access: v[0,i] is not how you access a 2D array in JavaScript. The getValues() method returns a 2D array where each row is an array, so to get the value in column A of row i, you need v[i][0].
2. Strict equality vs. "contains": Your original code checks if the value is exactly 'Substitution: ', but you mentioned you want to delete rows containing a specific string "X". We'll adjust that to check for partial matches.

修正后的完整代码 #

Here's a polished, error-resistant version of your script:

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // 先检查工作表是否存在，避免报错
  var targetSheet = ss.getSheetByName('delete containing');
  if (!targetSheet) {
    SpreadsheetApp.getUi().alert("哎呀！找不到名为'delete containing'的工作表，请检查名称后重试。");
    return;
  }
  
  // 只获取有数据的范围（比获取整列更高效）
  var dataRange = targetSheet.getDataRange();
  var values = dataRange.getValues();
  
  // 从后往前循环，避免删除行后索引错乱
  for (var i = values.length - 1; i >= 0; i--) {
    // 跳过空单元格，避免错误
    if (values[i][0]) {
      // 检查单元格是否包含目标字符串（把"X"换成你实际要匹配的内容）
      if (values[i][0].indexOf("X") !== -1) {
        // 数组索引从0开始，行号从1开始，所以要+1
        targetSheet.deleteRow(i + 1);
      }
    }
  }
}

关键改进点说明 #

• 工作表存在性检查: 添加了弹窗提示，明确告诉你找不到工作表的问题，而不是让脚本默默崩溃。
• 高效数据获取: 用getDataRange()代替getRange('A:A')，只加载有数据的单元格，大表格下能显著提升脚本速度。
• 正确的数组访问: 把v[0,i]修正为values[i][0]，才能正确读取每行A列的值。
• 包含匹配逻辑: 用indexOf()检查单元格是否包含目标字符串"X"，而不是要求完全相等。如果你确实需要完全匹配（比如原代码的'Substitution: '），可以把条件改成values[i][0] === "Substitution: "。
• 从后往前循环: 从底部开始删除行，避免删除后后续行前移导致索引错乱、漏掉部分行（这是删除行时的常见坑）。

最后小提示 #

• 先在表格副本上测试脚本，确保符合预期后再在原表格运行！
• 确认目标字符串"X"的大小写、空格都完全匹配你要找的内容。

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