Google Sheets
Add each Encatch survey response as a row in a Google Sheets spreadsheet - how to set up the service account and share the spreadsheet
Overview
The Google Sheets connector adds each survey response as one row in a spreadsheet, with one column per question. A quick way to share live responses with people who don't use the Encatch dashboard.
Encatch writes with a service account that you create in your own Google Cloud project. It can reach only the spreadsheets you share with it.
| What it gets | One row per response in a spreadsheet tab |
| Events | Survey submitted, and Survey partially submitted when ticked |
| Anonymous respondents | Not sent: identified users only |
| Test connection | Checks the key only: nothing is written in the sheet |
| Status | Available |
What You'll Need
Before you start, make sure you have:
- A Google Cloud project — any project; no billing is needed for the Sheets API
- Permission to create a service account and its key
- A Google Sheets spreadsheet — that you can share
Step 1: Turn on the Google Sheets API
- In the Google Cloud console, choose a project.
- Open APIs and services → Library, search for Google Sheets API and click Enable.
Step 2: Create a service account and its key
- In the Google Cloud console, open IAM and admin → Service accounts and click Create service account.
- Give it a name you'll recognise, for example
encatch-data-sync, and create it. It needs no project role. - Open the service account, go to Keys → Add key → Create new key, choose JSON and create it. Your browser downloads the key file.
Google Cloud organizations created since May 2024 block service account keys by default. If Create new key is refused, ask your Cloud admin to allow key creation for the project (the iam.managed.disableServiceAccountKeyCreation policy).
Step 3: Add the connection in Encatch
- In Encatch, open Data sync → Survey events and click Add connection.
- Choose Google Sheets and enter a Name.
- Fill in the settings:
| Field | What to enter |
|---|---|
| Service account key | Open the JSON key file and paste its whole content. |
| Spreadsheet link | The spreadsheet's address from your browser, https://docs.google.com/spreadsheets/d/…. |
| Sheet | The tab Encatch writes to. The default is Encatch responses. It is added if the spreadsheet does not have it. |
Once the key is pasted, the form shows Share the spreadsheet with: the service account's email.
Step 4: Share the spreadsheet with the service account
- Copy the service account's email from the Encatch form.
- Open the spreadsheet in Google Sheets and click Share.
- Paste the email, set it to Editor and share.
If your Google Workspace blocks sharing outside your company, sharing the spreadsheet with a service account is blocked too. Ask your Workspace admin.
Step 5: Test, save and start sending
- Back in Encatch, press Test connection. It checks the service account key by reading the spreadsheet and its tab: nothing is written in the sheet. Edit access shows with the first response.
- Pick the surveys, click Save, then press Start sending.
Other options (partial responses, rate limit, Admin API responses) are explained in Connection settings.
Check it in Google Sheets
After the first response, open the spreadsheet. Each response adds one row: the submitted time, the response ID, the survey and the user, then one column per question and one per user trait sent with responses (the traits marked Feedback Response in User Traits, headed by the trait's key).
Good to know
- Your layout is safe. Encatch marks its own columns, so you can rename their headers and move or insert columns. It adds a column when a survey gains a question, and never reorders or deletes columns.
- Partial responses. With Survey partially submitted ticked, a response left unfinished adds a row too, with the answers given so far. The sheet has no status column, so it looks like a submitted one.
- Edited responses. On a form that lets people edit their response, each edit they submit adds a row under the same response ID.
- Size. Google Sheets caps the cells in one spreadsheet. Point the connection at a new spreadsheet before it fills up.
- Rate limit. A new connection starts at 30 a minute. Google Sheets takes 60 writes and 60 reads a minute per service account, and a response is one read and one write.
Troubleshooting
| What you see | What it means | What to do |
|---|---|---|
| This is not a service account key | The pasted text is not the whole JSON key file | Open the downloaded file and paste all of it |
| Test connection cannot read the spreadsheet | The spreadsheet is not shared with the service account, or the Sheets API is off | Share it as Editor (Step 4); enable the Google Sheets API (Step 1) |
| Test passes, first response fails | The service account is a Viewer | Change it to Editor in Share |
| Deliveries wait, then arrive late | Many responses a minute against Google's 60 writes a minute | Keep the rate limit at 30, or split surveys across spreadsheets with separate service accounts |
Was this page helpful?
