如何通过API获取Google Sheets的Sheet ID以实现批量表格行列清理？

Got it, let's tackle this problem head-on. You're right that the Drive API doesn't expose Sheet IDs—the Google Sheets API is the exact tool you need here, and it’s perfect for batch-processing your 570 files without hitting Apps Script’s time limits.

Step 1: Enable the Google Sheets API & Authenticate #

First, make sure you’ve enabled the Sheets API in your Google Cloud Project, and set up authentication (either a service account key for server-side processing, or OAuth for user-specific access). This grants you permission to read sheet metadata and modify spreadsheets at scale.

Step 2: Fetch Sheet IDs for Any Spreadsheet #

Use the spreadsheets.get endpoint to pull all sheet details for a given Spreadsheet ID. This endpoint returns a JSON object that includes every sheet’s unique sheetId (along with its name, dimensions, and other properties).

Example Code (Python) #

Here’s a snippet that loops through your list of Spreadsheet IDs and extracts all Sheet IDs for each file:

import google.auth
from googleapiclient.discovery import build

# Authenticate with Sheets API scopes
creds, _ = google.auth.default(scopes=["https://www.googleapis.com/auth/spreadsheets"])
sheets_service = build("sheets", "v4", credentials=creds)

# Your list of 570 Spreadsheet IDs
spreadsheet_ids = ["SPREADSHEET_ID_1", "SPREADSHEET_ID_2", ...]

for spreadsheet_id in spreadsheet_ids:
    # Fetch full spreadsheet metadata
    spreadsheet = sheets_service.spreadsheets().get(spreadsheetId=spreadsheet_id).execute()
    
    # Extract Sheet IDs and names
    sheets = spreadsheet.get("sheets", [])
    for sheet in sheets:
        sheet_id = sheet["properties"]["sheetId"]
        sheet_name = sheet["properties"]["title"]
        print(f"Spreadsheet {spreadsheet_id} | Sheet '{sheet_name}' has ID: {sheet_id}")

Step 3: Batch Delete Rows/Columns Using Sheet IDs #

Once you have the Sheet ID, use the spreadsheets.batchUpdate endpoint to send a deleteDimension request. This lets you remove multiple rows/columns in a single API call—way more efficient than looping through each file in Apps Script.

Example Batch Update Request #

Here’s how to delete a specific range of rows and columns in a target sheet:

def delete_excess_dimensions(spreadsheet_id, sheet_id):
    requests = [
        # Delete rows (adjust start/end indices as needed—note they're 0-indexed)
        {
            "deleteDimension": {
                "range": {
                    "sheetId": sheet_id,
                    "dimension": "ROWS",
                    "startIndex": 9,  # Deletes row 10 (index 9)
                    "endIndex": 20    # Up to but not including row 20 (so rows 10-19)
                }
            }
        },
        # Delete columns
        {
            "deleteDimension": {
                "range": {
                    "sheetId": sheet_id,
                    "dimension": "COLUMNS",
                    "startIndex": 3,  # Column D (index 3)
                    "endIndex": 6     # Columns D-F (indices 3-5)
                }
            }
        }
    ]
    
    # Send the batch request
    response = sheets_service.spreadsheets().batchUpdate(
        spreadsheetId=spreadsheet_id,
        body={"requests": requests}
    ).execute()
    print(f"Updated {spreadsheet_id}: {response}")

Why This Beats Apps Script #

• No runtime limits: Running this locally or on a cloud server (like Google Cloud Functions, AWS Lambda) means you aren’t restricted to Apps Script’s 5-minute cap. You can even add parallel processing to handle multiple spreadsheets at once and cut down total runtime.
• Faster operations: API batch calls reduce redundant HTTP requests and leverage Google’s server-side processing, making the workflow far more efficient than looping through files in Apps Script.

Key Tips #

• If all your spreadsheets have a consistent structure (e.g., only one sheet, or sheets with identical names), filter Sheet IDs by name to target only the sheets you need.
• Test with a small subset of spreadsheets first to double-check your delete ranges are correct!

内容的提问来源于stack exchange，提问作者Mohamad El Baba