How can I list all worksheet names from a Google Spreadsheet into another spreadsheet?

Below is one approach using Google Apps Script to loop through all worksheets (sheets) in your source spreadsheet and then output their names to a specified sheet in your target spreadsheet.

Steps

1. In your source or target spreadsheet, open the Script Editor (Extensions → Apps Script).
2. Replace the default code with the script below.
3. Replace "SOURCE_SPREADSHEET_ID" and "TARGET_SPREADSHEET_ID" with the respective spreadsheet IDs (found in the URL).
4. Optionally, adjust the target sheet name or create a new sheet.
5. Save and run the script.

Script

JAVASCRIPTCopied
function listSheetNames() {
  // Open the source spreadsheet by its ID
  var sourceSpreadsheet = SpreadsheetApp.openById("SOURCE_SPREADSHEET_ID");
  var sourceSheets = sourceSpreadsheet.getSheets();
  
  // Collect the sheet names
  var sheetNames = sourceSheets.map(function(sheet) {
    return sheet.getName();
  });
  
  // Open the target spreadsheet by its ID
  var targetSpreadsheet = SpreadsheetApp.openById("TARGET_SPREADSHEET_ID");
  // Get or create a sheet to output the list
  var targetSheetName = "Sheet1"; // Change as needed
  var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName);
  if (!targetSheet) {
    targetSheet = targetSpreadsheet.insertSheet(targetSheetName);
  }
  
  // Clear previous contents
  targetSheet.clearContents();
  
  // Output the list of names (one per row)
  for (var i = 0; i < sheetNames.length; i++) {
    targetSheet.getRange(i + 1, 1).setValue(sheetNames[i]); // Writes in column A
  }
}

Explanation

• The script uses SpreadsheetApp.openById() to open both the source and target spreadsheets.
• It retrieves all worksheets from the source file and extracts their names.
• It then writes each name into column A of the target sheet (one per cell starting from row 1).

Running this script will produce an output similar to:

PYTHONCopied
Worksheet_A1
Worksheet_A2
…
Worksheet_A120

This approach leverages Google Apps Script’s built-in services and is an effective way to combine data from multiple worksheets into a single list in another spreadsheet.

Below is one approach that uses Google Apps Script to open your source spreadsheet, iterate through its worksheets, and then write the list (formatted as a Python code block) to a destination spreadsheet.

Steps

1. Open the Script Editor:
   In your Google Drive, open any spreadsheet, then go to Extensions > Apps Script. You can also use the Google Apps Script dashboard directly.

2. Replace Placeholder IDs:
   Replace the placeholder IDs for the source and destination spreadsheets with your actual spreadsheet IDs. The source spreadsheet is your "Example_Spreadsheet" while the destination is the one where you want to output the list.

3. Paste and Save the Script:
   Copy the following code into the script editor, save the project, and then run the listSheetNames function.

Script Code

JAVASCRIPTCopied
function listSheetNames() {
  // Replace with your source spreadsheet ID (Example_Spreadsheet)
  var sourceSpreadsheetId = "YOUR_SOURCE_SPREADSHEET_ID";  
  // Replace with your destination spreadsheet ID
  var destSpreadsheetId = "YOUR_DEST_SPREADSHEET_ID";  
  
  // Open the source spreadsheet and get all sheets
  var sourceSpreadsheet = SpreadsheetApp.openById(sourceSpreadsheetId);
  var sheets = sourceSpreadsheet.getSheets();
  
  // Create an array of worksheet names
  var names = sheets.map(function(sheet) {
    return sheet.getName();
  });
  
  // Format the list as a Python code block string
  var formattedOutput = "```python\n" + names.join("\n") + "\n```";
  
  // Open the destination spreadsheet
  var destSpreadsheet = SpreadsheetApp.openById(destSpreadsheetId);
  
  // Use a sheet named "WorksheetNames"; create if it doesn't exist
  var destSheet = destSpreadsheet.getSheetByName("WorksheetNames");
  if (!destSheet) {
    destSheet = destSpreadsheet.insertSheet("WorksheetNames");
  }
  
  // Clear previous content
  destSheet.clear();
  
  // Output the formatted list to cell A1
  destSheet.getRange(1, 1).setValue(formattedOutput);
}

How It Works

• Accessing the Spreadsheets:
  The script uses SpreadsheetApp.openById() to access both the source and destination spreadsheets using their IDs.

• Extracting Worksheet Names:
  It obtains all sheets using .getSheets() and maps over them to get each sheet's name.

• Formatting the List:
  The names are concatenated with newline characters and enclosed within the Python code block markers (```python and ```).

• Outputting to the Destination Spreadsheet:
  The script writes the final formatted text to cell A1 of a destination sheet (named "WorksheetNames"). If the sheet doesn't exist, it creates one.

Run the script after replacing the IDs and it will generate the list in your destination spreadsheet in the expected format.