编写跨工作簿删重复行脚本时遇getDataRange函数报错求助

Hey there, let's break down what's going wrong here and get your script working properly.

Why You're Seeing This Error #

That error pops up because the object you're calling getDataRange() on isn't a valid Google Sheets Sheet instance. Looking at your truncated code (getShe...), it's almost certainly one of these issues:

• You used getSheets() (which returns an array of all sheets in the workbook) instead of targeting a single sheet
• You tried to get a sheet by name with a typo, so it returned undefined
• Your code got cut off before fully specifying which sheet to work with

Full Working Script to Delete Duplicate Rows #

Here's a complete, tested script that will find duplicates between Workbook A and Workbook B, then delete matching rows from Workbook A:

function deleteDuplicateRows() {
  // Replace these with your actual workbook IDs and sheet names
  const workbookAId = "1pxWr3jOYZbcwlFR7igpSWa9BKCa2tTeliE8FwFCRTcQ";
  const workbookBId = "YOUR_WORKBOOK_B_ID_HERE";
  const sheetName = "Sheet1"; // Change to your sheet name, e.g., "Data"

  // Get the specific sheets we need (critical to avoid the TypeError!)
  const sheetA = SpreadsheetApp.openById(workbookAId).getSheetByName(sheetName);
  const sheetB = SpreadsheetApp.openById(workbookBId).getSheetByName(sheetName);

  // Quick check to make sure we found both sheets
  if (!sheetA || !sheetB) {
    throw new Error("Couldn't find one or both sheets! Double-check the sheet names and workbook IDs.");
  }

  // Pull all data from both sheets
  const dataA = sheetA.getDataRange().getValues();
  const dataB = sheetB.getDataRange().getValues();

  // Create a set of values from Workbook B for fast lookup (we're checking column A here)
  const valuesInB = new Set(dataB.map(row => row[0].toString()));

  // Loop backwards through Workbook A to avoid index shifting when deleting rows
  for (let i = dataA.length - 1; i >= 0; i--) {
    const currentValue = dataA[i][0].toString();
    // If the value exists in B, delete the row in A
    if (valuesInB.has(currentValue)) {
      sheetA.deleteRow(i + 1); // Sheets uses 1-based indexing, our array is 0-based
    }
  }

  SpreadsheetApp.getUi().alert("Duplicate rows deleted successfully!");
}

Key Tips to Avoid This Error (and Other Headaches) #

• Always target a specific sheet: Use getSheetByName("YourSheetName") instead of getSheets() unless you need to loop through all sheets. This ensures you're working with a valid Sheet object, not an array.
• Loop backwards when deleting rows: If you loop forward and delete a row, all subsequent rows shift up, which means you'll skip rows or delete the wrong ones. Backwards looping avoids this.
• Normalize data types: Converting values to strings with toString() ensures you don't miss duplicates because of type mismatches (e.g., a number in A and text in B).
• Add debug logs: If you're unsure if you're getting a valid sheet, add a log to check:

  console.log("Sheet A:", sheetA); // If this logs "undefined", your sheet name/ID is wrong
  

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