如何使用Python复制包含绑定AppScript的Google Sheet完整项目？

Got it, I’ve faced this exact problem before—tools like gspread, pygsheets, or the Google Sheets API only handle spreadsheet data, not the bound Apps Script projects tied to the file. The trick is to use the Google Drive API instead, since the manual "Make a Copy" action you rely on is actually powered by Drive, not the Sheets service.

Here’s a step-by-step implementation to replicate that behavior in Python:

1. Prerequisites #

• Enable the Google Drive API in your Google Cloud Project.
• Create a service account with access to both the original spreadsheet and your target Drive folder.
• Download the service account’s JSON key file for authentication.
• Install the required Python library:

  pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib
  

2. Python Code Implementation #

This script will copy the entire spreadsheet project (including bound Apps Script) just like the manual "Make a Copy" action:

from google.oauth2 import service_account
from googleapiclient.discovery import build

# Configure your settings here
SERVICE_ACCOUNT_KEY_PATH = "path/to/your/service-account-key.json"
ORIGINAL_SPREADSHEET_ID = "your-original-sheet-unique-id"
NEW_SPREADSHEET_NAME = "Copy of Original Sheet"
DESTINATION_FOLDER_ID = "optional-target-drive-folder-id"  # Leave None to copy to root

# Authenticate with Google Drive API
SCOPES = ["https://www.googleapis.com/auth/drive"]
credentials = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_KEY_PATH, scopes=SCOPES
)
drive_service = build("drive", "v3", credentials=credentials)

# Execute the copy request (preserves bound Apps Script)
copy_request = drive_service.files().copy(
    fileId=ORIGINAL_SPREADSHEET_ID,
    body={
        "name": NEW_SPREADSHEET_NAME,
        "parents": [DESTINATION_FOLDER_ID] if DESTINATION_FOLDER_ID else None
    },
    supportsAllDrives=True  # Keep this if working with shared drives
)
response = copy_request.execute()

print(f"Success! Copied spreadsheet. New sheet ID: {response['id']}")

3. Key Notes #

• Permissions Check: Make sure your service account has at least "Viewer" access to the original spreadsheet (or "Editor" if you need to modify the copy later).
• Script Preservation: The Drive API’s files.copy method automatically copies all associated resources—including bound Apps Script projects—mirroring the manual workflow exactly.
• Shared Drives: If your original sheet lives in a shared drive, keep supportsAllDrives=True and confirm the service account has access to that shared drive.
• Ownership: By default, the copied spreadsheet will be owned by the service account. To transfer ownership to a regular user, you’ll need an additional API call to update file permissions.

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