Google Apps Script调用多工作表时报Cannot read property 'getRange' of null错误

报错Cannot read property 'getRange' of null的核心原因是getSheetByName()方法未找到对应名称的工作表，返回了null值，后续调用null对象的getRange方法触发错误。

• 核对工作表名称
  逐一核对当前Google表格底部的工作表标签名称，需和代码中的四个名称100%匹配：Getting Started Checklist、Mid-Point Checklist、Pre-Go Live Checklist、Video Course Checklist，大小写、前后空格、特殊符号任何一处不符都会导致获取失败。
• 添加空值校验逻辑
  优化代码，先校验工作表是否获取成功再执行后续操作，同时复用活动表格对象减少重复调用，参考优化后代码：

function onTrigger() {
  SpreadsheetApp.getUi()
      .createMenu('Routed Options')
      .addItem('Add Routed Options', 'AddRoutedOptions')
      .addItem('Remove Routed Options', 'RemoveRoutedOptions')
      .addToUi();
}

function AddRoutedOptions() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.alert(
      'Add all Routed tasks to the Project Tracker?',
      'Are you sure you want to add all Routed tasks to the Project Tracker?',
      ui.ButtonSet.YES_NO);

  if (result == ui.Button.YES) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    // 获取工作表并校验
    const gs1 = ss.getSheetByName('Getting Started Checklist');
    const mp1 = ss.getSheetByName('Mid-Point Checklist');
    const pg1 = ss.getSheetByName('Pre-Go Live Checklist');
    const vc1 = ss.getSheetByName('Video Course Checklist');
    if (!gs1 || !mp1 || !pg1 || !vc1) {
      ui.alert('错误：未找到匹配名称的工作表，请核对工作表名称后重试');
      return;
    }
    // 执行复制操作
    gs1.getRange('B29:B30').copyTo(gs1.getRange('E20:E21'), {contentsOnly:true});
    gs1.getRange('B31').copyTo(gs1.getRange('H18'), {contentsOnly:true});
    mp1.getRange('B28:B31').copyTo(mp1.getRange('B21:B24'), {contentsOnly:true});
    mp1.getRange('B32:B33').copyTo(mp1.getRange('E12:E13'), {contentsOnly:true});
    mp1.getRange('B34').copyTo(mp1.getRange('H18'), {contentsOnly:true});
    pg1.getRange('B32:B35').copyTo(pg1.getRange('E25:E28'), {contentsOnly:true});
    vc1.getRange('B32:C37').copyTo(vc1.getRange('G10:H15'), {contentsOnly:true});
  } else {
    ui.alert('No tasks were added.');
  }
}

function RemoveRoutedOptions() {
  const ui = SpreadsheetApp.getUi();
  const result = ui.alert(
      'Remove all Routed tasks from the Project Tracker?',
      'Are you sure you want to remove all Routed tasks from the Project Tracker?',
      ui.ButtonSet.YES_NO);

  if (result == ui.Button.YES) {
    const ss = SpreadsheetApp.getActiveSpreadsheet();
    // 获取工作表并校验
    const gs1 = ss.getSheetByName('Getting Started Checklist');
    const mp1 = ss.getSheetByName('Mid-Point Checklist');
    const pg1 = ss.getSheetByName('Pre-Go Live Checklist');
    const vc1 = ss.getSheetByName('Video Course Checklist');
    if (!gs1 || !mp1 || !pg1 || !vc1) {
      ui.alert('错误：未找到匹配名称的工作表，请核对工作表名称后重试');
      return;
    }
    // 执行清空操作
    gs1.getRange('E20:E21').clearContent();
    mp1.getRange('B21:B24').clearContent();
    mp1.getRange('E12:E13').clearContent();
    mp1.getRange('H18').clearContent();
    pg1.getRange('E25:E28').clearContent();
    vc1.getRange('G10:H15').clearContent();
  } else {
    ui.alert('No tasks were removed.');
  }
}

• 补充优化说明
  原代码onTrigger函数中注册的showAlert方法未定义，已替换为实际可用的两个功能菜单入口，避免点击菜单触发额外报错。

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