Survey responses to Google Sheets

A Google Sheet is often the fastest way to let a client watch fieldwork happen without giving them a platform account. This connector keeps one in step with a project table, declared in siamang.yaml rather than maintained by hand.

It is the lowest-friction target in the catalogue and the one most likely to trip you up in exactly one place — sharing. A service account is a separate identity, and the sheet has to be shared with it.

What this gives you

A client-facing view with no account. They open a sheet; you keep the repository.
The export is committed. The task block lives beside the questionnaire, so it is reviewed and versioned rather than living in someone’s browser.
Available on Plus. Sheets, Excel 365 and Supabase are the everyday destinations; warehouses, object storage, SFTP and REDCap need Pro.

How to set it up

Create the service account. In Google Cloud, create a service account and download a JSON key. It needs no project-level roles — access comes from sharing the sheet.
Store the key as a project secret under the name GSHEETS_SA. Secrets are encrypted at rest and write-only.
Share the sheet with the service account. Copy client_email out of the JSON key and share the spreadsheet with that address, giving it edit access. This is the step that is usually missing when an export runs cleanly and the sheet stays empty.
Declare the task with Connectors → Add connector, review the staged diff, and commit it.
Run it and check the history, which records the status, the log and the commit the run used.

When to use something else

If the point is analysis rather than visibility, a spreadsheet loses the things that make survey data survey data: variable labels, value labels and typed missing values. Those survive in an SPSS export. If the point is joining responses to other company data, send them to BigQuery instead.

The siamang.yaml task

The Add-connector wizard writes this block and tells you which secret to create. It is committed to your repository like any other change, so a change to where responses go arrives as a reviewable diff.

tasks:
  responses_to_sheet:
    type: connector
    target: sheets
    direction: out
    table: clean_responses
    secret: GSHEETS_SA
    config: { spreadsheet_id: "1AbC…", range: A1 }

Limits and caveats

50,000 rows per run for this target — lower than the 100,000-row cap the other connectors use.
Full replace from the anchor cell. The written block overwrites the range starting at config.range, but rows below the block are not cleared, so a shorter export leaves older rows in place.
Values are written raw. Nothing is coerced into Sheets number or date types, and nothing is interpreted as a formula.
Manual trigger only. Schedules accept run_script and run_all, not connectors.
No automatic retry, and a five-minute job timeout; a failed run is recorded with its error for you to re-run.

Questions

Why a service account instead of signing in with Google?

There is no OAuth flow anywhere in the platform — credentials are config, stored as encrypted project secrets. That keeps an export working when the person who set it up leaves, and keeps the whole pipeline declarable in the repository.

The connector runs but the sheet is empty. What did I miss?

Almost always sharing. A service account is its own identity, so the spreadsheet has to be shared with the client_email from the JSON key, exactly as you would share it with a colleague.

Where do I find the spreadsheet ID?

It is the long token in the sheet's URL between /d/ and /edit. Pass it as config.spreadsheet_id; config.range defaults to A1 on the first sheet.

Old rows are still there after an export. Why?

The write replaces the block it needs and stops. If a later export is shorter than an earlier one, the leftover rows below remain — clear the sheet first, or write to a fresh tab.

Is Google Sheets enough for a real analysis?

For monitoring fieldwork and sharing a snapshot with a client, usually yes. For labelled analysis you want SPSS or Stata output, and for anything that joins to other data a warehouse — Sheets has neither variable labels nor types.