求助：通过Google Sheets文件URL获取工作表名称列表的Google Apps Script脚本无法运行

Let's walk through what's going wrong with your current script and adjust it to work properly—plus we'll make it support both direct URL input and cell references (since you mentioned needing both options):

First, the Issues in Your Original Code #

• No support for cell inputs: Your function only accepts a raw URL, but you want to be able to pass a cell reference (like A1) that contains the URL. Right now, if you pass a cell address, it'll treat that string as a URL and throw an error.
• Missing error handling: If the URL is invalid, you don't have access to the target sheet, or the sheet doesn't exist, the script will crash without any helpful feedback.
• Implicit permissions: When using SpreadsheetApp.openByUrl(), you need to authorize access to the target sheet on the first run. If you skip this step, the script will fail silently.

Here's the Revised Script #

/**
 * Fetches all sheet names from a target Google Sheet, supporting direct URL input or cell reference.
 * @param {string} input - Either a Google Sheet URL or a cell reference (e.g., "A1") containing the URL
 * @param {string} [outputRange="A1"] - Optional: Cell to start outputting sheet names (default is A1)
 * @return {void} Writes sheet names to the active sheet starting at outputRange
 */
function getSheetNamesFromOtherGSfile(input, outputRange = "A1") {
  try {
    // Resolve input: if it's a cell reference, get the URL from that cell
    let targetUrl;
    if (input.match(/^[A-Z]+\d+$/)) { // Check if input is a cell address (like A1, B5)
      const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
      targetUrl = activeSheet.getRange(input).getValue().trim();
    } else {
      targetUrl = input.trim();
    }

    // Validate URL format
    if (!targetUrl.includes("docs.google.com/spreadsheets/d/")) {
      throw new Error("Invalid Google Sheet URL. Please check your input.");
    }

    // Open the target spreadsheet and get sheet names
    const targetSpreadsheet = SpreadsheetApp.openByUrl(targetUrl);
    const sheetNames = targetSpreadsheet.getSheets().map(sheet => [sheet.getName()]); // Convert to 2D array for setValues

    // Write results to the specified output range
    const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    const outputStart = activeSheet.getRange(outputRange);
    const outputRangeFull = activeSheet.getRange(
      outputStart.getRow(),
      outputStart.getColumn(),
      sheetNames.length,
      1
    );
    outputRangeFull.setValues(sheetNames);

    SpreadsheetApp.getUi().alert(`Success! Found ${sheetNames.length} sheets.`);
  } catch (error) {
    SpreadsheetApp.getUi().alert(`Error: ${error.message}`);
    console.error("Script failed:", error);
  }
}

Key Improvements Explained #

1. Dual input support: The script checks if your input is a cell reference (using a simple regex for cell addresses) and pulls the URL from that cell if needed. If you pass a direct URL, it uses that instead.
2. Error handling: We added a try-catch block to catch issues like invalid URLs, missing permissions, or non-existent sheets, and show a user-friendly alert.
3. Customizable output: You can optionally specify where to write the sheet names (e.g., pass "B3" as the second argument to start outputting from cell B3).
4. Validation: The script checks that the input URL looks like a Google Sheet URL before trying to open it.

How to Use This #

• Run directly from the script editor: When you run the function, a prompt will ask you to enter the input (either URL or cell reference) and optional output range.
• Use as a custom function in a cell: Type =getSheetNamesFromOtherGSfile("https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID") or =getSheetNamesFromOtherGSfile("A1", "C1") to pull names directly into your sheet.

Important Note on Permissions #

The first time you run this script, you'll need to authorize it to access external Google Sheets. Follow the prompts—you might need to click "Advanced" and then "Go to [Script Name]" to grant access (this is normal for Google Apps Scripts that access external resources).

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