System design / API integrations / 06_google_workspace_apis.md

Google Workspace APIs: Sheets and Drive

Updated 6 interview angles 4 min read source
On this page6
  1. Which credential
  2. Sheets: the API is not a database
  3. Drive: ids, not paths
  4. Making it reliable
  5. Related
  6. Interview angle

Google Workspace APIs: Sheets and Drive

Sheets and Drive turn up constantly in internal tooling, because a spreadsheet is the interface non-engineers already have. The engineering content of this integration is almost entirely auth and quotas.

Which credential

This is the decision that goes wrong, and it is worth being able to answer directly.

Credential Acts as
OAuth user flow The signed-in human
Service account Itself, a robot identity
Domain-wide delegation Any user in the workspace

Service account is right for a backend job: it has its own identity and needs no interactive consent. The catch that surprises people is that a service account has no Drive of its own that anyone can see - it starts with no access to your files. You grant it access by sharing the sheet or folder with its email address, exactly as you would with a colleague.

OAuth user flow is right when the tool acts on behalf of a person and should see only what they can see.

Domain-wide delegation lets a service account impersonate users in the workspace. It is powerful, requires admin approval, and is a standing credential that can read every user’s files - treat it as the last resort.

python 
SCOPES = [
    "https://www.googleapis.com/auth/spreadsheets"
]
creds = (
    service_account.Credentials
    .from_service_account_file(
        KEY_PATH, scopes=SCOPES
    )
)

Prefer workload identity federation or a secret manager over a key file on disk. A downloaded service-account JSON is a long-lived credential with no expiry, which is the thing that ends up committed to a repository.

Scopes

Ask for the narrowest that works. drive.file grants access only to files the app itself created or the user explicitly opened with it, and is dramatically easier to justify than full drive. Broad scopes also trigger Google’s verification review for external apps.

Sheets: the API is not a database

The most common failure is treating a sheet like a table and issuing a request per row.

python 
# One call, many rows - not one call per row.
sheet.values().append(
    spreadsheetId=SHEET_ID,
    range="Data!A1",
    valueInputOption="USER_ENTERED",
    body={"values": rows},
).execute()

Quotas are per-minute and per-user - on the order of a few hundred read or write requests per minute per project - so a loop over a thousand rows fails quickly. Batch: batchUpdate for structural or formatting changes, values().batchGet and values().batchUpdate for data.

Two behaviours worth knowing:

  • valueInputOption decides parsing. RAW stores what you send; USER_ENTERED parses as if a person typed it, so =SUM(A1:A5) becomes a formula and 1/2 may become a date. For untrusted data, RAW avoids surprising coercion.
  • A1 ranges are the addressing model. There is no WHERE. Reading “the row where id = X” means reading a range and filtering in Python, which is another reason a sheet is a poor primary store.

Sheets is fine as an interface - a place humans read output or supply configuration - and poor as a system of record. When it is genuinely the source of data, sync it into Postgres on a schedule and query that.

Drive: ids, not paths

Drive has no real path hierarchy. A file has an id, and folders are just files that other files list as parents. So resolving “the report in the Finance folder” is a query, not a path lookup:

python 
q = (f"'{folder_id}' in parents and "
     "name = 'report.csv' and trashed = false")
res = drive.files().list(
    q=q, fields="files(id,name)"
).execute()

The other practical points: shared drives need supportsAllDrives=True on most calls or the file appears not to exist; exporting a Google-native document requires files().export with a target MIME type, since a Doc has no bytes to download directly; and uploads over about 5 MB should use resumable upload rather than a single request.

Making it reliable

The same discipline as any third-party integration:

  • Back off on 429 and 5xx, honouring Retry-After. Quota errors are normal at volume, not exceptional.
  • Make the write idempotent. A retried append duplicates rows. Either write by explicit range rather than appending, or carry a key column and reconcile.
  • Do it in a worker, not a request handler - see Web scraping and browser automation for the same argument.

Interview angle 6

  • “Service account or OAuth?” - a service account for backend jobs acting as themselves, OAuth when the tool acts on behalf of a person and should see only their files. Domain-wide delegation impersonates any user, needs admin approval, and is a last resort.
  • “Why can’t the service account see the sheet?” - it has its own identity and no access to your files by default. Share the sheet or folder with the service account’s email address, the same as sharing with a person.
  • “Your Sheets job fails at scale. Why?” - a request per row against per-minute quotas. Batch with batchUpdate and values().batchGet, and treat quota errors as normal, with backoff honouring Retry-After.
  • “What does valueInputOption change?” - whether Google parses the input. USER_ENTERED behaves as if typed, so strings become formulas or dates; RAW stores literally and is safer for untrusted data.
  • “How do you find a file in Drive?” - by query, not path. Drive addresses by id and folders are files listed as parents, so you search with a q filter. Shared drives also need supportsAllDrives, and Google-native docs must be exported with a MIME type rather than downloaded.
  • “Is a spreadsheet an acceptable data store?” - as an interface for humans, yes; as a system of record, no. There is no WHERE, addressing is A1 ranges, and quotas are low. Sync it into Postgres and query that.
Previous
Web scraping and browser automation