Use a Google Sheet as a read-only JSON API
Quick answer: Use an authenticated webhook, Read Range and JSON Operations to return approved Google Sheets columns as a bounded JSON array. Production testing recorded one failed disclosure check.
Status: production-tested on 9 October 2026 on AI Flowi Workflow 1.9.2 (25 of 26 cases passed)
By Ai Flowi · Published . Updated 9 October 2026.
Task
Receive an authenticated POST, read three approved columns from a private Google Sheet, optionally filter rows and return a JSON array containing at most 100 objects. This template uses JSON Operations with JQ to build the response. It has no AI or LLM component and requires no third-party paid API.
The production run exercised Read Range, JSON Operations and Webhook Response. The results below record 25 passing cases and one failed disclosure check. An imported-copy test and download remain unavailable.
Who it is for
Teams serving a small internal catalogue or status list to a backend application. Use a sheet containing only data you intend authenticated callers to read. The complete selected range is read into memory, so this is a small-sheet template.
Sample input
Send an empty JSON object to request the first 100 nonblank rows:
{}Or supply both filter fields. Matching is exact and case-sensitive; whitespace is significant.
{"filter_field":"status","filter_value":"enabled"}Allowed columns are id, label and status. Both filter values must be strings. An empty filter_value matches an empty cell. Other request fields are rejected.
Setup
- Use a private test spreadsheet accessible through your existing Google connection. Give your selected tab exactly three row-one headers:
id,label,status, in that order. Use synthetic rows. - Set Read Range’s Spreadsheet ID and Range to your selected tab’s
A:Ccolumns. The private test configuration supplies its own tab name. The public examples useYOUR_TAB. - Configure Universal Webhook for POST, Platform API Key authentication and From Flow response mode. Keep the sender’s credential on its backend.
- Configure the JSON Operations node that combines inputs as Combine, with a list input. Select JQ Expression on the response projection node and keep its query visible.
- Connect the projection to Webhook Response’s Response Definition input so the array becomes the HTTP body directly.
- Use the exact JQ query below. Set Webhook Response’s default Status Code to 200 OK, Content Type to
application/jsonand Additional Response Headers to an empty list. The connected definition supplies each response’s actual status and body.
Node connections and field mappings
Graph: Universal Webhook and Read Range feed JSON Operations (Combine); a second JSON Operations validates and projects the combined data; Webhook Response sends its dynamic response definition. Read Range is an independent root, so authenticated invalid input can still trigger a sheet read. There are no sheet-writing nodes.
| Source → target | Output → input |
|---|---|
| Universal Webhook → JSON Operations (Combine) | request_data → data |
| Read Range → JSON Operations (Combine) | values → data |
| JSON Operations (Combine) → JSON Operations (JQ) | data_output → data |
| JSON Operations (JQ) → Webhook Response | data_output → response_definition |
The webhook’s body holds the filter. Read Range’s values[0] holds headers; subsequent rows map columns A, B and C to id, label and status. Missing trailing cells become empty strings. Fully blank rows are omitted. Filtering happens before the 100-row cap, preserving sheet order. Responses expose those three fields only.
JQ returns an object containing body, status_code and content_type. Connecting a bare JQ array to Response Body would return the JSON Operations wrapper instead of the required array. The dynamic response definition avoids that wrapper.
JQ validation and array response
def reject($message): {status_code: 422, content_type: "application/json", body: {error: $message}};
.body as $b
| if ($b|type) != "object" then reject("Request body must be a JSON object")
elif (($b|keys) - ["filter_field","filter_value"] | length) > 0 then reject("Unknown request field")
elif ($b|has("filter_field")) != ($b|has("filter_value")) then reject("filter_field and filter_value must be supplied together")
elif ($b|has("filter_field")) and (($b.filter_field|type) != "string" or ($b.filter_value|type) != "string") then reject("Filter fields must be text")
elif ($b|has("filter_field")) and (["id","label","status"]|index($b.filter_field)) == null then reject("Unknown filter_field")
elif (.values|type) != "array" then error("Sheet values unavailable")
elif .values[0] != ["id","label","status"] then {status_code: 502, content_type: "application/json", body: {error: "Invalid sheet headers"}}
else ([.values[1:][] | select(any(.[]; . != "" and . != null))
| {id: (.[0] // "" | tostring), label: (.[1] // "" | tostring), status: (.[2] // "" | tostring)}
| select(if ($b|has("filter_field")) then .[$b.filter_field] == $b.filter_value else true end)] | .[:100]) as $rows
| {status_code: 200, content_type: "application/json", body: $rows}
endSanitized request and response examples
Request shape; replace bracketed values locally. No live endpoint or credential is included.
POST https://workflow.aiflowi.com/api/v1/webhook/<your-flow-id>
Content-Type: application/json
x-api-key: <your-platform-api-key>
{"filter_field":"status","filter_value":"enabled"}Illustrative successful response for one matching synthetic row (not a retained test response):
HTTP 200
Content-Type: application/json
[{"id":"row-001","label":"Item 1","status":"enabled"}]No matches, or a valid header-only sheet: observed HTTP 200 with [].
Supplying {"filter_field":"status"}: observed HTTP 422 with:
{"error":"filter_field and filter_value must be supplied together"}Validation and failure behaviour
- Non-object or malformed JSON: observed 422,
Request body must be a JSON object. - Only one filter field: observed 422. Number, null, boolean, object or array filter values: observed 422,
Filter fields must be text. - Unknown request property: observed 422,
Unknown request field. Unknown or empty column name: observed 422,Unknown filter_field. - Missing or invalid Platform API Key: observed HTTP 403. Graph node billing was not measured.
- Incorrect sheet headers: observed 502,
{"error":"Invalid sheet headers"}. - Known platform limitation: T20 (inaccessible spreadsheet) returned HTTP 404 with a raw platform error containing the private spreadsheet identifier and failed the no-leak check. AI Flowi Workflow 1.9.2 has no in-flow Google exception branch. Do not expose the endpoint’s raw responses to end users; use a trusted backend to return a sanitized error. Successful JQ projection does not sanitize an exception response.
Test results
The production run completed 26 requests: 25 passed and T20 failed the no-leak check. Status, JSON content type and body checks passed for all 26; those checks did not require the T20 error body to be sanitized. The selected sheet values were unchanged after every request, including T20. Temporary API key deletion was confirmed. Returned-object counts below follow the exact expected-body comparisons; raw response bodies were not retained.
| Case | Observed HTTP | Verdict |
|---|---|---|
| T1 valid no filter (100 returned objects checked) | 200 | PASS |
| T2 missing filter_value | 422 | PASS |
| T2b missing filter_field | 422 | PASS |
| T3 wrong filter type | 422 | PASS |
| T3-0 non-text filter_value | 422 | PASS |
| T3-1 non-text filter_value | 422 | PASS |
| T3-2 non-text filter_value | 422 | PASS |
| T3b non-text filter_field | 422 | PASS |
| T4 malformed JSON | 422 | PASS |
| T5 missing API key | 403 | PASS |
| T6 invalid API key | 403 | PASS |
| T7 exact filter (52 returned objects checked) | 200 | PASS |
| T8 no matches (0 returned objects checked) | 200 | PASS |
| T9 case sensitivity (0 returned objects checked) | 200 | PASS |
| T10 unknown column | 422 | PASS |
| T10b empty column | 422 | PASS |
| T11 non-object body | 422 | PASS |
| T12 null filter | 422 | PASS |
| T13 unknown request field | 422 | PASS |
| T14 header-only range (0 returned objects checked) | 200 | PASS |
| T15 filter before cap; ragged row (1 returned objects checked) | 200 | PASS |
| T16 empty text filter (1 returned objects checked) | 200 | PASS |
| T17 whitespace is significant (0 returned objects checked) | 200 | PASS |
| T18 quoted Unicode cell (1 returned objects checked) | 200 | PASS |
| T19 invalid sheet headers | 502 | PASS |
| T20 inaccessible spreadsheet | 404 | FAIL — no-leak check; private spreadsheet identifier in raw platform error |
Download
No import download is available. Sanitize an export, import and re-test it before adding a download link. Resolve raw-error exposure in a trusted backend before serving responses to end users.
Required configuration
An authorized Google connection; your private Spreadsheet ID; the selected tab and A:C range; exact id,label,status headers; Read Range; POST and From Flow webhook settings; and Platform API Key authentication. A read-only graph does not reduce the existing Google connection’s OAuth permissions. The sheet need not be published to the web.
Tested version and date
| Tested platform version | AI Flowi Workflow 1.9.2 |
|---|---|
| Production test date | 9 October 2026 (MYT) |
| Import and re-test | Not performed in supplied evidence |
Usage and cost assumptions
Each node run uses 1 Flowi Token. Plans: Go from USD8/month billed yearly (USD10 monthly), five plans up to Pro 500. Every plan is listed on the pricing page.
The estimated successful and validation-rejection paths each run five nodes: webhook, read, combine, JQ and response. Header validation also uses five. Authentication rejection returned 403; zero graph nodes remains a structural estimate, with billing unmeasured. Failed reads may stop after some nodes, and their actual usage is unknown.
| Requests | Assumed Flowi Tokens / Monthly cost on the cheapest plan (USD, billed monthly) |
|---|---|
| 100 | 500; USD10 on Go |
| 1,000 | 5,000; USD10 on Go |
| 10,000 | 50,000; USD100 on Pro 100 |
Assumes no retries, no additional branches and one Google range read per request. A completed 26-case test run adds 27 separate one-node read-backs; failed-read usage remains unknown. No model usage is included or required. Third-party API/model usage is not included in the plan. Google quotas and any external account costs require separate review; no external price or response-time saving is asserted. Monthly cost = the plan’s monthly price + number of top-ups x its top-up price. A top-up adds another whole month’s allowance of that plan, so round the number of top-ups up. Plans: Go from USD8/month billed yearly (USD10 monthly), five plans up to Pro 500. Every plan is listed on the pricing page.
Limitations
- Caching and rate limits: unknown. No cache, TTL, refresh guarantee or requests-per-minute figure is specified.
- At most 100 matching objects are returned. There is no pagination, total count, sorting control or truncation flag. The entire selected range is still read.
- Only three text fields are exposed. Google-rendered values are converted to strings; this is not a typed database API.
- No write, delete, upsert, public anonymous access, transaction isolation or controlled Google exception response is implemented. T20 demonstrated private-identifier disclosure in a raw HTTP 404 error; do not expose raw endpoint responses to end users.
- Concurrent manual edits can change results. Snapshot comparisons cover only the selected columns at read times; they do not detect intervening changes or edits outside the range.
Next steps
Re-run the cases on your own synthetic sheet. Handle raw platform errors in a trusted backend and verify a sanitized imported copy before deployment. Caching, rate limits and runtime node billing remain unknown. Review Google Sheets integration and the component catalogue for configuration context. For webhook and Google connection setup background, see the Webhook to Google Sheets tutorial.