TextQL can read from and write to Google Sheets using a GCP service account. The service account acts as a bot identity — it authenticates via a JSON key file rather than a human login. The same process applies whether you’re connecting Google Sheets, Google Docs, or an entire Drive folder.
Setup has three parts:
| Part | What You Do | Where |
|---|
| Part 1 | Create a service account, enable APIs, download the JSON key | Google Cloud Console |
| Part 2 | Share your Sheets and Drive folders with the service account | Google Drive / Sheets |
| Part 3 | Store the credentials in TextQL (two options) | TextQL Platform |
| Option A (Preferred) | Option B |
|---|
| Method | API Access — a managed integration where TextQL handles authentication at the platform level. Ana never sees the raw credentials. | Environment Variable — store the JSON key as an env var that Ana references directly in Python code. |
| Best For | Security-conscious setups. Credentials stay encrypted and are never exposed in code. | Maximum flexibility. Useful when you need fine-grained control over the authentication flow. |
This part is done entirely in the Google Cloud Console. These steps are the same regardless of which TextQL option you choose in Part 3.
Step 1.1 — Create a GCP Project (or use an existing one)
- Go to console.cloud.google.com and sign in.
- Click the project dropdown in the top navigation bar.
- Click New Project. Give it a descriptive name (e.g., “TechSuite Integrations”).
- Click Create. Make sure this project is selected for the remaining steps.
If your organization already has a GCP project for integrations, you can reuse it. You just need permission to create service accounts and enable APIs in that project.
Step 1.2 — Enable the Required APIs
Two APIs must be enabled in your GCP project:
| API | Purpose | Why It’s Needed |
|---|
| Google Sheets API | Read/write cells, create tabs, format data | Core functionality — without this, no Sheets operations work |
| Google Drive API | List files, manage sharing, open spreadsheets by ID | Required for file-level access and discovering sheets |
- In the GCP Console, go to APIs & Services > Library.
- Search for “Google Sheets API” and click on it.
- Click Enable.
- Repeat for “Google Drive API”.
Both APIs must be enabled in the same project where you create the service account. If either is missing, you will get 403 Forbidden errors at runtime.
Step 1.3 — Create a Service Account
- Go to IAM & Admin > Service Accounts.
- Click + Create Service Account.
- Enter a name (e.g., “textql-sheets-bot”) and an optional description.
- Click Create and Continue.
- For the “Grant this service account access to project” step, you can skip it — no project-level IAM roles are needed for Sheets/Drive access.
- Click Done.
After creation, note the service account email address. It will look something like:
textql-sheets-bot@your-project.iam.gserviceaccount.com
Step 1.4 — Create and Download the JSON Key
- On the Service Accounts page, click on the service account you just created.
- Go to the Keys tab.
- Click Add Key > Create new key.
- Select JSON format and click Create.
- A JSON file will download automatically. Keep this file safe — it is the credential.
This JSON key grants full access as the service account. Treat it like a password. Do not commit it to version control, share it in chat, or store it in plain text. It goes directly into TextQL in Part 3.
Part 2: Share Your Sheets with the Service Account
A service account is like a separate Google user — it cannot see any files unless you explicitly share them. This is the most commonly missed step. These steps are the same regardless of which TextQL option you choose in Part 3.
Option A: Share Individual Sheets
Use this when you only need access to a few specific spreadsheets.
- Open the Google Sheet you want TextQL to access.
- Click the Share button (top right).
- Paste the service account email from Step 1.3.
- Set the permission to Editor. Ana needs read-write access to create tabs, update cells, and apply formatting.
- Uncheck “Notify people” (the service account has no inbox).
- Click Share.
Always use Editor (read-write) permissions. Most TextQL workflows write back to Google Sheets — creating new tabs, updating cell values, and applying formatting. Viewer access will cause permission errors on any write operation.
Option B: Share an Entire Drive Folder
Use this when you have many sheets that Ana should access. Any sheet inside the shared folder (including sheets added later) will automatically be accessible.
- In Google Drive, right-click the folder you want to share.
- Click Share > Share with people and groups.
- Paste the service account email.
- Set to Editor (read-write).
- Click Share.
Folder sharing is inherited. If you share a top-level folder, all subfolders and files within it become accessible to the service account. This is the easiest approach for ongoing use.
Verifying Access
After sharing, verify by checking the sheet’s sharing settings. The service account email should appear in the list of people with Editor access. If it does not appear, the share did not go through — double-check the email address for typos.
Part 3: Store the Credentials in TextQL
Now that you have a service account with a JSON key (Part 1) and your sheets are shared (Part 2), you need to tell TextQL how to authenticate. There are two options:
| Option A: API Access | Option B: Environment Variable |
|---|
| Security | Credentials are encrypted and managed by the platform. Ana never sees the raw key. | The JSON key is available to Ana in code at runtime. Still encrypted at rest, but visible during execution. |
| Simplicity | Fewer fields to fill in. No code-level setup needed. | Requires Ana to parse the JSON and authenticate in Python code. |
| Flexibility | Works for any Google API endpoint automatically. | Full control over the authentication flow. Can use libraries like gspread directly. |
| Recommended for | Most users. Preferred default. | Advanced use cases requiring custom auth logic. |
Option A: API Access (Preferred)
The API Access method stores your service account credentials as a managed integration. TextQL encrypts the key and handles authentication at the platform level — Ana makes authenticated requests to Google APIs without ever seeing the raw credentials in code.
Step A.1 — Navigate to API Access
- In TextQL, click Connectors in the left sidebar.
- Click the APIs tab at the top of the page.
- Click the + New API Access button in the top right corner.
- From the list of providers, select Google Cloud.
Step A.2 — Fill Out the Google Cloud Form
| Field | What to Enter | Required? |
|---|
| Service Account JSON | Open the JSON key file from Step 1.4 in a text editor, copy the entire contents, and paste them here. | Yes |
| Project ID | Your GCP project ID (e.g., “techsuite-integrations”). Found at the top of the GCP Console. | No |
| Description | A short note for your team (e.g., “Google Sheets read-write access for pipeline reports”). | No |
- Click Add to save.
Step A.3 — Privacy Settings
The form includes a Private checkbox. When checked, only you and people you explicitly share with can use this API access. Uncheck it if you want all members of your TextQL organization to be able to use it.
When Ana makes an HTTP request to any *.googleapis.com endpoint, TextQL automatically authenticates the request using the stored service account. The raw JSON key is never exposed in Ana’s code or logs.
Option B: Environment Variable (More Flexible)
The environment variable method stores the JSON key as a named variable that Ana can reference directly in Python code. This gives you full control over the authentication flow and lets you use libraries like gspread for a higher-level interface. The credential is encrypted at rest but is available to Ana during code execution.
Step B.1 — Navigate to Environment Variables
- In TextQL, click Connectors in the left sidebar.
- Click the Env Vars tab at the top of the page.
- Click the + New Env Var button in the top right corner.
Step B.2 — Fill Out the Environment Variable Form
| Field | What to Enter | Required? |
|---|
| Name | A descriptive key name using uppercase with underscores (e.g., GOOGLE_SERVICES_JSON). This is how Ana references the variable in code. | Yes |
| Value | Open the JSON key file from Step 1.4 in a text editor, copy the entire contents, and paste them here. This field is write-only — the value is never displayed after saving. | Yes |
| Description | A short note for your team (e.g., “Service account JSON for Google Sheets read-write access”). | No |
| Documentation Link | An optional URL pointing to relevant docs. Useful for onboarding new team members. | No |
- Click Add Variable to save.
The Name you choose here is exactly how Ana will reference the credential in code. For example, if you name it GOOGLE_SERVICES_JSON, Ana accesses it as SECRETS["GOOGLE_SERVICES_JSON"].
| Visibility | Who Can Use It |
|---|
| Private | Only you (the creator) can use this variable in chats and playbooks |
| Shared | All members of your TextQL organization can use this variable |
import gspread, json
sa = json.loads(SECRETS["GOOGLE_SERVICES_JSON"])
gc = gspread.service_account_from_dict(sa)
sheet = gc.open_by_key("your-sheet-id-here")
gspread yourself — Ana handles library installation automatically when running playbooks.
Quick Reference: What Goes Where
| Item | Where It Lives | Who Sets It Up |
|---|
| GCP Project + APIs | Google Cloud Console | Your GCP admin (one-time) |
| Service Account | GCP IAM & Admin | Your GCP admin (one-time) |
| JSON Key File | Downloaded locally, then stored in TextQL | GCP admin + TextQL admin |
| Sheet/Folder Sharing | Google Drive sharing settings (Editor access) | Sheet owner or Drive admin |
| API Access (Option A) | TextQL > Connectors > APIs | TextQL admin |
| Environment Variable (Option B) | TextQL > Connectors > Env Vars | TextQL admin |
| Google Sheet ID | Playbook prompt (as a variable) | Playbook author |
Troubleshooting
| Symptom | Likely Cause | Fix |
|---|
| 403 Forbidden when opening a sheet | Google Sheets API or Google Drive API not enabled in GCP | Go to GCP Console > APIs & Services > Library and enable both APIs |
| 404 Spreadsheet not found | Sheet not shared with the service account email | Share the sheet or its parent folder with the service account email as Editor |
| Invalid grant / token error | JSON key is malformed or was deleted in GCP | Re-download the JSON key from GCP and update the credential in TextQL |
| Permission denied writing to sheet | Service account was shared as Viewer, not Editor | Update the sharing permission to Editor on the sheet or folder |
| Credential not found at runtime | API access or env var not configured, or set to Private when another user runs it | Check Connectors > APIs or Env Vars; adjust privacy settings if needed |
Setup Checklist
GCP project created (or existing project selected)
Google Sheets API enabled in the project
Google Drive API enabled in the project
Service account created in IAM & Admin
JSON key downloaded for the service account
Target Google Sheet (or Drive folder) shared with the service account email as Editor
Credentials stored in TextQL via Option A (Connectors > APIs) or Option B (Connectors > Env Vars)
Playbook or chat references the correct Google Sheet ID
The service account email (from Step 1.3) is what you share sheets with. The JSON key (from Step 1.4) is what you paste into TextQL — either via the API Access form (Option A) or as an environment variable value (Option B). Always grant Editor (read-write) permissions when sharing sheets.
