Hey there! Let's work through the issues in your script that's causing the empty array/"transpose" problem, and get it working smoothly for all your sheets.

First, let's spot the key problems in your current code: #

• Incorrect loop boundary: Your sheet loop uses s <= book.length — since array indexes start at 0, this will try to access book[book.length] which is undefined (out of bounds), leading to errors when the sheet is empty.
• Unsafe array iteration: Using for(i in data) can pick up unintended properties from the array's prototype, which might cause unexpected behavior, especially with empty datasets.
• Missing empty data handling: When a sheet has no content, getDataRange().getValues() returns an empty array. Without checking for this first, your code will try to process non-existent rows, triggering the transpose error.
• Incomplete duplicate-check logic: Your code cuts off mid-way when defining the row check, so we'll fill that in properly.

Here's the corrected, fully functional script: #

function removeDuplicates() {
  // Open the target spreadsheet by ID
  var spreadsheet = SpreadsheetApp.openById("[sheet id]");
  var sheets = spreadsheet.getSheets();

  // Loop through each sheet (fix: use s < sheets.length instead of <=)
  for (var s = 0; s < sheets.length; s++) {
    var sheet = sheets[s];
    var data = sheet.getDataRange().getValues();
    
    // Skip empty sheets entirely to avoid transpose errors
    if (data.length === 0) {
      console.log(`Skipping empty sheet: ${sheet.getName()}`);
      continue;
    }

    var newData = [];
    // Use a Set to track unique rows (convert rows to strings for easy comparison)
    var uniqueRows = new Set();

    // Loop through each row safely with a standard for loop
    for (var i = 0; i < data.length; i++) {
      var row = data[i];
      // Convert the row array to a string to store in the Set
      var rowString = JSON.stringify(row);

      // Only add the row to newData if it hasn't been seen before
      if (!uniqueRows.has(rowString)) {
        uniqueRows.add(rowString);
        newData.push(row);
      }
    }

    // Clear the existing sheet content and write the unique data back
    sheet.clearContents();
    sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
    console.log(`Processed sheet: ${sheet.getName()} — removed ${data.length - newData.length} duplicates`);
  }
}

Key fixes explained: #

1. Fixed sheet loop: s < sheets.length ensures we only access valid sheet indexes (0 to sheets.length - 1).
2. Empty sheet check: The if (data.length === 0) block skips processing entirely for empty sheets, eliminating the transpose error caused by trying to handle an empty dataset.
3. Safe duplicate tracking: Using a Set with stringified rows is a reliable way to detect duplicates — it's faster and cleaner than nested loops for row comparison.
4. Proper data writing: When writing the cleaned data back, we use newData.length and newData[0].length to target the exact range needed, avoiding issues with empty arrays.

Additional notes: #

• Replace [sheet id] with your actual Google Sheet ID (you can find this in the sheet's URL).
• If you need to check duplicates only on specific columns instead of entire rows, modify the rowString to stringify only the columns you care about (e.g., JSON.stringify(row.slice(0, 2)) for the first two columns).

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