如何用Python将Google Sheet发票转PDF并存至同个Drive文件夹？

Alright, let's solve this problem where you need to copy a Google Sheet invoice template, fill it with data, then export it as a PDF directly to the same Drive folder (no local file required) using Python, gspread, and PyDrive. Here's a step-by-step solution:

I’ll assume you already have gspread and PyDrive set up with proper authentication (either via service account or OAuth 2.0) to access your Google Drive.

Step 1: Copy the Template Sheet & Populate Invoice Data #

First, we’ll duplicate your blank template and fill in the necessary invoice details using gspread.

import gspread
from pydrive2.auth import GoogleAuth
from pydrive2.drive import GoogleDrive

# Initialize authenticated clients
gc = gspread.service_account()  # Swap to OAuth if you're using that method
gauth = GoogleAuth()
gauth.LocalWebserverAuth()  # Or use service account auth here for server-side use
drive = GoogleDrive(gauth)

# 1. Copy the blank invoice template
TEMPLATE_SHEET_ID = "YOUR_TEMPLATE_SHEET_ID_HERE"
template_spreadsheet = gc.open_by_key(TEMPLATE_SHEET_ID)
# Customize the new sheet title (e.g., include client name/invoice number)
new_invoice_sheet = template_spreadsheet.copy(title="Invoice_AcmeCorp_001")

# 2. Fill the new sheet with invoice data
worksheet = new_invoice_sheet.sheet1
worksheet.update("B2", "Acme Corporation")  # Example: Client name cell
worksheet.update("B3", "456 Oak Avenue")     # Example: Client address cell
worksheet.update("D5", "$1,200.00")         # Example: Total amount cell
# Add more cell updates to match your template's layout

Step 2: Get the New Sheet's Drive File ID & Parent Folder #

To export the sheet as a PDF, we need its Drive file ID and the ID of the folder it lives in (so we can save the PDF there).

# Get the Drive ID of the new invoice sheet
new_sheet_drive_id = new_invoice_sheet.id

# Fetch the parent folder ID (matches the template's folder)
template_drive_file = drive.CreateFile({"id": TEMPLATE_SHEET_ID})
template_drive_file.FetchMetadata(fields="parents")
parent_folder_id = template_drive_file["parents"][0]

Step 3: Export PDF Directly to the Parent Folder #

Instead of downloading the PDF to your local machine, we’ll use the Drive API to export the sheet as PDF bytes, then create a new PDF file directly in the target folder.

# Export the sheet as PDF content (no local file write)
pdf_content = drive.auth.service.files().export_media(
    fileId=new_sheet_drive_id,
    mimeType="application/pdf"
).execute()

# Create a new PDF file in the same parent folder
pdf_file = drive.CreateFile({
    "title": f"{new_invoice_sheet.title}.pdf",
    "parents": [{"id": parent_folder_id}],
    "mimeType": "application/pdf"
})

# Attach the PDF content and upload to Drive
pdf_file.content = pdf_content
pdf_file.Upload()

print(f"Invoice PDF saved to Drive: {pdf_file['alternateLink']}")

Key Notes #

• Authentication Consistency: Make sure gspread and PyDrive use the same authentication method (e.g., service account) to avoid permission errors when accessing Drive files.
• Template Layout: Adjust the cell references (like B2, D5) to match your specific invoice template’s structure.
• Permissions: Ensure your authenticated account has edit access to the template folder and can create new files there.

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