求助：2020年7月更新致无需Spreadsheet ID的Google Sheet导入脚本失效

Hey Adam, let's break down why your script stopped working after Google's 2020 July update and fix it for good.

Why It's Breaking #

1. Uncaught Iterator Crash: Your original code calls .next() directly on getFoldersByName() and getFilesByName() without checking if the iterator actually has results. If the folder/file can't be found (due to permission changes, name typos, or Google's Drive API tweaks), this throws the Cannot retrieve the next object: iterator has reached the end error you're seeing.
2. Global Code Blocks All Scripts: The folder/file lookup was in the global scope, which runs as soon as any script in your spreadsheet loads. When this global code fails, it locks up the entire script project—so even your other unrelated scripts can't run until you fix this one.

Fixed Script #

Here's the updated version with safeguards, error handling, and no more problematic global code:

function importData1() {
  // Configuration - update these values to match your setup
  const FOLDER_NAME = "SOURCE FOLDER NAME";
  const FILE_NAME = "SOURCE FILE NAME";
  const SOURCE_WORKSHEET_NAME = "SOURCE WORKSHEET NAME";
  const TARGET_SPREADSHEET_ID = "TARGET FILE ID";
  const TARGET_WORKSHEET_NAME = "TARGET WORKSHEET NAME";
  const DATA_RANGE = "A:Q"; // Use a named range here if you prefer

  try {
    // Find the source folder safely
    const folderIterator = DriveApp.getFoldersByName(FOLDER_NAME);
    if (!folderIterator.hasNext()) {
      throw new Error(`Folder "${FOLDER_NAME}" couldn't be found.`);
    }
    const folder = folderIterator.next();

    // Find the source file safely
    const fileIterator = folder.getFilesByName(FILE_NAME);
    if (!fileIterator.hasNext()) {
      throw new Error(`File "${FILE_NAME}" not found in folder "${FOLDER_NAME}".`);
    }
    const file = fileIterator.next();
    const sourceSpreadsheetID = file.getId();

    // Access source spreadsheet and worksheet
    const sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetID);
    const sourceWorksheet = sourceSpreadsheet.getSheetByName(SOURCE_WORKSHEET_NAME);
    if (!sourceWorksheet) {
      throw new Error(`Worksheet "${SOURCE_WORKSHEET_NAME}" missing from source spreadsheet.`);
    }

    // Get the data range you want to copy
    const sourceData = sourceSpreadsheet.getRange(DATA_RANGE);
    if (!sourceData) {
      throw new Error(`Range "${DATA_RANGE}" not found in source spreadsheet.`);
    }

    // Access target spreadsheet and worksheet
    const targetSpreadsheet = SpreadsheetApp.openById(TARGET_SPREADSHEET_ID);
    const targetWorksheet = targetSpreadsheet.getSheetByName(TARGET_WORKSHEET_NAME);
    if (!targetWorksheet) {
      throw new Error(`Worksheet "${TARGET_WORKSHEET_NAME}" missing from target spreadsheet.`);
    }

    // Copy data to the target sheet
    const targetRange = targetWorksheet.getRange(1, 1, sourceData.getNumRows(), sourceData.getNumColumns());
    targetRange.setValues(sourceData.getValues());

    console.log("Data imported successfully!");
  } catch (error) {
    console.error(`Import failed: ${error.message}`);
    // Optional: Uncomment below to get email alerts for errors
    // MailApp.sendEmail("your-email@example.com", "Data Import Error", `Details: ${error.stack}`);
  }
}

Key Improvements #

• No More Global Code: All lookup logic lives inside the importData1() function, so it only runs when you call the script—no more crashing the entire project on load.
• Iterator Safety Checks: We always verify hasNext() before calling .next() to avoid the iterator error.
• Clear Error Handling: Every step checks for missing resources (folders, files, worksheets, ranges) and throws specific, actionable errors.
• Logging: Errors are logged to the script console, and you can add email alerts to get detailed failure notifications.

Extra Troubleshooting Tips #

• Re-Authorize the Script: Google tightened permission scopes in 2020, so re-authorize the script to make sure it has access to your Drive and Sheets.
• Check Exact Names: Double-check that folder and file names match exactly (they're case-sensitive, so no extra spaces or typos!).
• Optimize the Range: Using A:Q copies every row in those columns, which can be slow for large sheets. Try sourceWorksheet.getDataRange() instead to only copy used rows.

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