Google表格脚本遍历工作表报错：无法调用null的getLastRow方法

Hey there! Let's break down why your targetSheet is returning null and throwing that frustrating error—this is a super common pitfall when working with cross-spreadsheet data transfers, so we can sort this out step by step.

Common Causes & Fixes #

• Mismatched Sheet Names
  The most likely culprit is that the name of your source sheet doesn’t exactly match the sheet name in the target spreadsheet. Google Sheets is case-sensitive and picks up on every space, hyphen, or special character. For example, "Q3 Sales" vs "Q3sales" or "Q3 Sales " (with a trailing space) will cause getSheetByName() to return null.
  Fix: Add a Logger.log(sourceSheet.getName()) line right before fetching the target sheet, then double-check that exact name exists in your target spreadsheet.

• Incorrect Target Spreadsheet ID
  If you’re using SpreadsheetApp.openById("TARGET_ID"), make sure you’ve copied the correct ID from the target spreadsheet’s URL. It’s easy to mix up the source and target IDs by accident!
  Fix: Verify the ID in your script matches the string between /d/ and /edit in the target sheet’s web address.

• Missing Permissions
  Your script’s running account (the Google account you’re logged in with when executing the script) might not have access to the target spreadsheet. If the target sheet is private, you need to share it with that account (give edit permissions to avoid read-only issues).
  Fix: Open the target spreadsheet, click "Share", and add your script-running account with edit access.

• Target Sheet Doesn’t Exist
  If the target spreadsheet simply doesn’t have a sheet with the matching name, getSheetByName() will return null. Instead of manually creating all sheets upfront, you can handle this in your script automatically.
  Fix: Add a check to create the sheet if it’s missing:

  const targetSpreadsheet = SpreadsheetApp.openById("YOUR_TARGET_ID");
  const sourceSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); // Or your source sheet fetch logic
  const sourceSheetName = sourceSheet.getName();
  
  let targetSheet = targetSpreadsheet.getSheetByName(sourceSheetName);
  
  // Create sheet if it doesn't exist
  if (!targetSheet) {
    targetSheet = targetSpreadsheet.insertSheet(sourceSheetName);
  }
  
  // Now safely call getLastRow()
  const lastTargetRow = targetSheet.getLastRow();
  

Debugging Tip #

Add a log statement right after fetching targetSheet to confirm its value:

Logger.log(`Target sheet for ${sourceSheetName}: ${targetSheet}`);

If the log shows null, work through the fixes above—one of them will resolve the issue!

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