谷歌表格脚本报错：TypeError: cannot read null (reading 'getRange')

我正在编写谷歌表格脚本，实现自动向每人分配款项并保留历史支付记录。脚本逻辑是从「Pay Tracking」表的M2:N区域读取姓名和对应金额，然后将存款记录（类型、金额、日期）写入对应姓名的专属表格中，但运行时始终提示范围引用错误，调整范围后仍未解决。

原脚本代码：

const payDay = () => {

  // get spreadsheet
  const ss = SpreadsheetApp.getActiveSpreadsheet()

  // get Pay Tracking sheet
  const paySheet = ss.getSheetByName('Pay Tracking')

  // get names and amounts from M2
  const namesAmounts = paySheet.getSheetValues(2, 13, 20, 2)

  // add the amount to the corresponding sheet name
  namesAmounts.forEach(data => {

    // get sheet by name
    const sheet = ss.getSheetByName(data[0])

    // get last row in column D (= 4)
    const lRow = sheet.getRange('N2:N').getValues().filter(String).length

    // prepare data
    const newData = [['deposit', data[1], new Date()]]

    // prepare formats
    const formats = [['@', '$0.00','dd/MM/yyyy']]

    // set range
    const range = sheet.getRange(lRow + 2, 4, 1, 3)

    // set formats
    range.setNumberFormats(formats)
    
    // add new data
    range.setValues(newData)
  })
} 

错误原因分析 #

1. 无效工作表引用：原代码读取了固定20行数据，其中可能包含空行或不存在的工作表名称，导致ss.getSheetByName(data[0])返回null，后续调用sheet.getRange直接报错。
2. 最后行计算逻辑错误：基于N列计算最后行，但实际要写入的是D-F列，两者数据行数可能不一致，导致写入位置偏差；若N列全为空，lRow为0，lRow+2会导致范围超出表格边界。

修正后的脚本代码 #

const payDay = () => {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const paySheet = ss.getSheetByName('Pay Tracking');
  
  // 仅读取姓名和金额均非空的有效行
  const namesAmounts = paySheet.getRange('M2:N')
    .getValues()
    .filter(row => row[0] && row[1]);

  namesAmounts.forEach(data => {
    const name = data[0];
    const amount = data[1];
    const sheet = ss.getSheetByName(name);
    
    // 跳过不存在的工作表，避免脚本中断
    if (!sheet) {
      console.log(`未找到工作表：${name}，已跳过`);
      return;
    }

    // 基于目标写入列（D列）计算最后有数据的行
    const targetColumn = 4; // D列的索引
    const lastDataRow = sheet.getRange(`${targetColumn}:${targetColumn}`)
      .getValues()
      .filter(String)
      .length;
    
    // 确定下一个写入行：D列无数据则从第2行开始，否则接在最后一行之后
    const nextWriteRow = lastDataRow === 0 ? 2 : lastDataRow + 1;

    const newData = [['deposit', amount, new Date()]];
    const formats = [['@', '$0.00', 'dd/MM/yyyy']];
    
    // 设置准确的写入范围
    const writeRange = sheet.getRange(nextWriteRow, targetColumn, 1, 3);
    writeRange.setNumberFormats(formats);
    writeRange.setValues(newData);
  });
};

关键修正点 #

• 过滤无效数据：仅处理姓名和金额都不为空的行，避免无效循环。
• 校验工作表存在性：如果找不到对应姓名的工作表，跳过并打印日志，确保脚本不会因单个错误中断。
• 修正行号计算逻辑：基于要写入的D列计算最后行，保证写入位置准确，避免范围越界。
• 兼容空列场景：处理D列无历史数据的情况，默认从第2行开始写入记录。

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