Guide

Automating Reporting WorkflowsDeep dive

Save Excel Reports to Google Drive with Python

Q: Should I upload an .xlsx or convert it to a Google Sheet?

Upload the .xlsx when readers need the formatting and formulas exactly as generated. Convert to a Google Sheet, by setting the target mimeType, when people will collaborate on it in the browser — conversion drops some Excel-specific formatting.

Publish a generated .xlsx to Google Drive from a scheduled job — service-account auth, folder targeting, resumable uploads, updating in place, and shared drive gotchas.

Google Drive is the natural destination for a recurring report in a Google Workspace organisation: readers already live there, sharing is familiar, and revision history comes free. From Python it is the Drive API v3 with a service account — and three details decide whether it works unattended. The service account must actually be able to see the folder, uploads must update rather than duplicate, and shared drives need an extra parameter on every call. This guide covers all of it. It is the Google Workspace path from Publishing Excel Reports to Cloud Storage.

Prerequisites

Bash

pip install google-api-python-client google-auth pandas xlsxwriter

A service account in a Google Cloud project with the Drive API enabled, and its JSON key downloaded. Then the step people skip: give it somewhere to write.

Preferred: create a shared drive for reports and add the service account's client_email as a Content manager. The shared drive owns the files and supplies the storage.
Workable: share an ordinary folder with the client_email address, giving Editor access. Files remain owned by a person and consume their quota.

The address to share with is inside the key file, and it is not your own email:

Bash

export GOOGLE_APPLICATION_CREDENTIALS=/secrets/reporting-sa.json
export DRIVE_FOLDER_ID=1AbCdEfGhIjKlMnOpQrStUvWxYz

Python

import json, os

with open(os.environ["GOOGLE_APPLICATION_CREDENTIALS"]) as fh:
    print(json.load(fh)["client_email"])
# reports@my-project.iam.gserviceaccount.com  <- share the folder with THIS

The folder ID is the trailing segment of the folder's URL in the browser.

Step 1 — Authenticate

Service-account credentials need no interactive flow, which is exactly what a scheduled job wants:

Python

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

SCOPES = ["https://www.googleapis.com/auth/drive.file"]

def drive_client():
    creds = service_account.Credentials.from_service_account_file(
        os.environ["GOOGLE_APPLICATION_CREDENTIALS"], scopes=SCOPES
    )
    # cache_discovery=False avoids a noisy warning and a stale on-disk cache.
    return build("drive", "v3", credentials=creds, cache_discovery=False)

service = drive_client()

Pick the narrowest scope that works. drive.file grants access only to files the application itself created or that were explicitly shared with it — which covers a reporting job completely. The broader drive scope reaches everything the account can see and is rarely justified.

Step 2 — Upload a report into a folder

Drive uploads take metadata plus a media body. Building the workbook in memory keeps the job filesystem-free:

Python

import io
import pandas as pd
from googleapiclient.http import MediaIoBaseUpload

XLSX = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

def build_workbook(df, sheet_name="Summary"):
    buffer = io.BytesIO()
    with pd.ExcelWriter(buffer, engine="xlsxwriter") as writer:
        df.to_excel(writer, sheet_name=sheet_name, index=False)
    buffer.seek(0)
    return buffer

def upload(service, buffer, name, folder_id):
    """Create a new file in the given folder."""
    metadata = {"name": name, "parents": [folder_id]}
    media = MediaIoBaseUpload(buffer, mimetype=XLSX, resumable=True)

    created = (
        service.files()
        .create(body=metadata, media_body=media,
                fields="id, name, webViewLink",
                supportsAllDrives=True)
        .execute()
    )
    return created

df = pd.DataFrame({"region": ["North", "South"], "revenue": [159.92, 247.50]})
item = upload(service, build_workbook(df),
              "regional-2026-08.xlsx", os.environ["DRIVE_FOLDER_ID"])
print(item["webViewLink"])

Two arguments carry more weight than they look. supportsAllDrives=True must be on every call that touches a shared drive — omit it and the API behaves as though the drive does not exist, returning a confusing 404 for a folder you can see in the browser. And fields= limits the response to what you need; without it the API returns a small default set that often lacks webViewLink, so people conclude the link is unavailable when it simply was not requested.

Step 3 — Update instead of duplicating

Drive permits several files with the same name in one folder. A monthly job that always calls create therefore builds up a pile of identically named reports, and readers cannot tell which link is current.

Search first, then branch:

Python

from googleapiclient.http import MediaIoBaseUpload

def find_file(service, name, folder_id):
    """Return the ID of a non-trashed file with this name in the folder."""
    safe = name.replace("'", r"\'")
    query = (
        f"name = '{safe}' and '{folder_id}' in parents and trashed = false"
    )
    result = (
        service.files()
        .list(q=query, fields="files(id, name)", pageSize=2,
              supportsAllDrives=True, includeItemsFromAllDrives=True)
        .execute()
    )
    files = result.get("files", [])
    return files[0]["id"] if files else None

def publish(service, buffer, name, folder_id):
    """Create the file, or add a revision if it already exists."""
    media = MediaIoBaseUpload(buffer, mimetype=XLSX, resumable=True)
    existing = find_file(service, name, folder_id)

    if existing:
        return (
            service.files()
            .update(fileId=existing, media_body=media,
                    fields="id, name, webViewLink", supportsAllDrives=True)
            .execute()
        )

    return (
        service.files()
        .create(body={"name": name, "parents": [folder_id]},
                media_body=media, fields="id, name, webViewLink",
                supportsAllDrives=True)
        .execute()
    )

files().update adds a revision rather than replacing history, so the ID and every circulated link stay valid while the content moves forward. Note that includeItemsFromAllDrives is needed alongside supportsAllDrives on the list call specifically — the two flags do different things and both are required for search on a shared drive.

Escaping the apostrophe in the query matters: Drive's query language is string-delimited, and a report name containing one otherwise produces a syntax error rather than an empty result.

Step 4 — Publish dated and latest together

Combine the two naming needs from the parent topic in one call:

Python

from datetime import date

def publish_report(service, df, base, folder_id, on=None):
    """Write a dated file for history and update a stable latest file for links."""
    on = on or date.today()

    dated_name = f"{base}-{on:%Y-%m}.xlsx"
    latest_name = f"{base}-latest.xlsx"

    dated = publish(service, build_workbook(df), dated_name, folder_id)
    latest = publish(service, build_workbook(df), latest_name, folder_id)

    return dated["webViewLink"], latest["webViewLink"]

Build the workbook twice rather than reusing one buffer — a MediaIoBaseUpload consumes the stream, so passing the same buffer to a second upload sends zero bytes. Rewinding with seek(0) also works; building twice is simply harder to get wrong.

Step 5 — Convert to a Google Sheet, when that is what people want

If readers will collaborate in the browser rather than download, ask Drive to convert on upload by naming the target MIME type:

Python

SHEET = "application/vnd.google-apps.spreadsheet"

def upload_as_sheet(service, buffer, name, folder_id):
    """Upload an .xlsx and have Drive convert it to a native Google Sheet."""
    media = MediaIoBaseUpload(buffer, mimetype=XLSX, resumable=True)
    return (
        service.files()
        .create(
            body={"name": name, "parents": [folder_id], "mimeType": SHEET},
            media_body=media, fields="id, webViewLink",
            supportsAllDrives=True,
        )
        .execute()
    )

The trade-off is real: conversion keeps values, formulas and basic formatting, but drops Excel-specific features — some conditional formatting rules, certain chart types, and any macros. Where the report's appearance is the deliverable, as with the styled output from writing a formatted Excel report with xlsxwriter, upload the .xlsx unconverted.

Common pitfalls and fixes

Symptom	Cause	Fix
`404` on a folder you can see	Shared drive without `supportsAllDrives`	Pass it on every call.
Search returns nothing on a shared drive	`includeItemsFromAllDrives` missing	Add it to the `list` call.
`storageQuotaExceeded`	Service account has no storage	Use a shared drive, or a user-owned folder.
Duplicate files each month	`create` called unconditionally	Search by name and `update` when found.
`webViewLink` missing from the response	Not requested	Add it to `fields=`.
Second upload writes 0 bytes	Buffer already consumed	Build a fresh buffer, or `seek(0)`.
Service account cannot see the folder	Shared with the wrong address	Share with the key's `client_email`.
Query syntax error on some names	Apostrophe in the file name	Escape it before interpolating.

Performance and scale notes

resumable=True switches the client to a session-based upload that survives a dropped connection. For small files it adds a round trip; above a few megabytes it is clearly worth it, and it lets you report progress:

Python

from googleapiclient.http import MediaIoBaseUpload

def upload_with_progress(service, buffer, name, folder_id, chunk_mb=5):
    media = MediaIoBaseUpload(
        buffer, mimetype=XLSX, resumable=True,
        chunksize=chunk_mb * 1024 * 1024,
    )
    request = service.files().create(
        body={"name": name, "parents": [folder_id]},
        media_body=media, fields="id, webViewLink", supportsAllDrives=True,
    )

    response = None
    while response is None:
        status, response = request.next_chunk()
        if status:
            print(f"{int(status.progress() * 100)}%")
    return response

Drive throttles per project, and the client raises HttpError with status 403 and a rate-limit reason or 429. Back off exponentially with jitter — the same discipline as every other API in this section, and covered in retrying a failed Excel report job:

Python

import random, time
from googleapiclient.errors import HttpError

def with_retry(call, attempts=5):
    for attempt in range(1, attempts + 1):
        try:
            return call()
        except HttpError as exc:
            transient = exc.resp.status in (403, 429, 500, 502, 503, 504)
            if not transient or attempt == attempts:
                raise
            time.sleep(min(2 ** attempt, 30) + random.uniform(0, 1))

Two habits that matter across a fan-out. Reuse the service object — building it resolves credentials and fetches the API discovery document, which is far more expensive than the upload for a small report. And do the existence search once per name, not per attempt: for a job publishing forty regional files monthly, cache the folder listing in one files().list call and look names up in a dict, rather than issuing forty separate searches.

Python

def index_folder(service, folder_id):
    """One listing, then O(1) name lookups instead of a search per file."""
    index, token = {}, None
    while True:
        result = service.files().list(
            q=f"'{folder_id}' in parents and trashed = false",
            fields="nextPageToken, files(id, name)", pageSize=1000,
            pageToken=token, supportsAllDrives=True,
            includeItemsFromAllDrives=True,
        ).execute()
        index.update({f["name"]: f["id"] for f in result.get("files", [])})
        token = result.get("nextPageToken")
        if not token:
            return index

Conclusion

Publishing an Excel report to Google Drive is a service account, a folder it can genuinely see, and a create-or-update decision. Share the target with the key's client_email — ideally a shared drive, so the storage quota problem never arises — and pass supportsAllDrives on every call, plus includeItemsFromAllDrives when searching. Search by name and call files().update so each month adds a revision instead of a duplicate, keeping one stable link. Use resumable=True for anything sizeable, and convert to a native Sheet only when collaboration matters more than exact formatting.

Frequently asked questions

Why can't the service account see the folder I shared with it? Two usual causes. The folder was shared with the wrong address — it must be the service account's own client_email, not your account — or the folder lives on a shared drive and the request is missing supportsAllDrives.

Should I upload an .xlsx or convert it to a Google Sheet? Upload the .xlsx when readers need the formatting and formulas exactly as generated. Convert to a Google Sheet, by setting the target mimeType, when people will collaborate on it in the browser — conversion drops some Excel-specific formatting.

How do I overwrite last month's file instead of creating a duplicate? Drive allows several files with the same name in one folder, so a plain create always adds another. Search for the existing file by name and parent, then call files().update with its ID to add a new revision.

What is the service account storage quota problem? A service account has no Drive storage of its own. Files it creates in My Drive count against a quota it does not have, so uploads fail. Put the target folder on a shared drive, or have the account write into a folder owned by a real user.

Do I need resumable uploads for a report? For anything above a few megabytes, yes. The Drive client switches to a resumable session when you pass resumable=True, which survives a dropped connection instead of restarting the transfer.

Up to the parent: Publishing Excel Reports to Cloud Storage — the naming and verification patterns behind this guide.
Upload an Excel Report to Amazon S3 with boto3 — the object-storage alternative.
Upload an Excel Report to SharePoint with Python — the Microsoft 365 equivalent.
Write a Formatted Excel Report with xlsxwriter — the styled output that conversion would degrade.
Retry a Failed Excel Report Job in Python — backoff for the throttling above.