Hey there, let's fix this issue you're having with Google Apps Script and duplicate sheet errors. You want to safely create a sheet named myname without hitting that annoying "sheet already exists" error, and your current approach isn't cutting it. Let's break down the most reliable methods to handle this.

The Simplest & Most Efficient Method: getSheetByName() #

The Google Apps Script API has a built-in method that does exactly what we need: getSheetByName(). If the sheet exists, it returns the sheet object; if not, it returns null. This is the cleanest way to check before creating a new sheet.

Here's a ready-to-use script:

function createSheetSafely() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheetName = "myname";
  
  // Check if the sheet exists
  const existingSheet = activeSpreadsheet.getSheetByName(targetSheetName);
  
  if (!existingSheet) {
    // No sheet found—create it
    activeSpreadsheet.insertSheet(targetSheetName);
    Logger.log(`Successfully created sheet: "${targetSheetName}"`);
  } else {
    // Sheet already exists, skip creation
    Logger.log(`Sheet "${targetSheetName}" already exists. No changes made.`);
  }
}

Why this works: #

getSheetByName() is optimized for this exact use case—no need to loop through all sheets unless you need extra logic. If your previous method failed, it might have been checking for undefined instead of null, or had a typo in the sheet name (remember, sheet names are case-sensitive!).

Alternative: Loop Through All Sheets (For Custom Checks) #

If you need more control—like a case-insensitive check or matching partial sheet names—you can loop through all sheets in the spreadsheet. Here's how:

Basic Case-Sensitive Check #

function checkSheetWithLoop() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheetName = "myname";
  const allSheets = activeSpreadsheet.getSheets();
  
  let sheetExists = false;
  for (const sheet of allSheets) {
    if (sheet.getName() === targetSheetName) {
      sheetExists = true;
      break; // Exit loop early once we find a match
    }
  }
  
  if (!sheetExists) {
    activeSpreadsheet.insertSheet(targetSheetName);
    Logger.log(`Sheet "${targetSheetName}" created.`);
  } else {
    Logger.log(`Sheet "${targetSheetName}" is already present.`);
  }
}

Case-Insensitive Check #

If you want to ignore capitalization (e.g., treat "MyName" and "myname" as the same sheet), modify the loop like this:

function checkSheetCaseInsensitive() {
  const activeSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const targetSheetName = "myname";
  const allSheets = activeSpreadsheet.getSheets();
  
  let sheetExists = false;
  for (const sheet of allSheets) {
    if (sheet.getName().toLowerCase() === targetSheetName.toLowerCase()) {
      sheetExists = true;
      break;
    }
  }
  
  if (!sheetExists) {
    activeSpreadsheet.insertSheet(targetSheetName);
    Logger.log(`Case-insensitive check: Created sheet "${targetSheetName}"`);
  } else {
    Logger.log(`Case-insensitive check: A sheet matching "${targetSheetName}" (case ignored) already exists.`);
  }
}

Key Notes to Avoid Future Errors #

• Case Sensitivity: getSheetByName() treats "myname" and "MyName" as different sheets. Use the case-insensitive loop method if you want to avoid duplicates regardless of capitalization.
• Typos: Double-check that the sheet name in your script matches exactly what you're looking for—even a single space or typo will make getSheetByName() return null.
• Hidden Sheets: Both methods will detect hidden sheets too, so you won't accidentally create a duplicate of a sheet that's just hidden from view.

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