Look up a row in Google Sheets by email

Receive an email address through a webhook, validate it, search a private Google Sheets tab and return the first matching row. This template uses standard workflow components without an AI model.

Status: production-tested on 9 October 2026 on AI Flowi Workflow 1.9.2 (30 of 30 cases passed)

By Ai Flowi · Published 9 October 2026 · Updated 9 October 2026

Task

Look up one row by its email column. Observed behaviour: HTTP 200 with found:true and the configured row fields, or HTTP 404 with exactly {"found":false}. Matching was case-sensitive, with no trimming or lowercasing.

Who it is for

Teams building internal record lookups from a trusted backend into a small private spreadsheet. Keep personal data behind caller authorization; an API key alone does not grant permission to view every email’s row.

Sample input

{"email":"lookup@example.com"}

Send JSON with Content-Type: application/json and a private x-api-key header. All examples use synthetic values.

Setup

1. Authorize your Google connection and create a private test tab. Set row 1 to email,name,status,note, in that order, with unique lowercase headers. Add synthetic test rows.
2. Add the eight nodes below. On Universal Webhook select POST, Platform API Key and the POST response mode From Flow. Copy its Production Endpoint into your trusted sender.
3. On Search Row select your authorized Google Account, private Spreadsheet ID and Sheet Name. Set Key Column to email, Header Row to 1, and Case Sensitive Key Match to enabled.
4. On each JSON Operations select JQ Expression and keep the query field visible. Paste the queries below. Connect the exact ports in the table.
5. On both Webhook Response nodes set default HTTP Status to 200 OK, Content Type to application/json, and Additional Response Headers to Cache-Control: no-store. The connected Response Definition overrides the default with 200, 404 or 422.
6. Use synthetic fixtures to run the full checklist before handling private records. Compare the entire tab before and after each request.

Node connections and field mappings

The internal port names below match the configuration guide. Repeated JSON Operations and Webhook Response nodes have distinct IDs.

Validation: JSON-validate

def reject: {verified:false, status_code:422, body:{error:"Invalid email", code:"INVALID_EMAIL"}};
.body as $b
| if ($b|type) != "object" then reject
  elif ($b.email|type) != "string" then reject
  elif ($b.email|length) < 1 or ($b.email|length) > 254 then reject
  elif ($b.email|test("^[^@[:space:]]+@[^@[:space:]]+[.][^@[:space:]]+$")) then {verified:true, email:$b.email}
  else reject end

Search key: JSON-key

{email:.email}

Only the validated email reaches Search Row’s Search Data (row_data). Spreadsheet, tab and response fields are fixed configuration; the caller cannot override them.

Response: JSON-response

if .found == true then
  if (.row|type) != "object" then error("Unexpected lookup result")
  else {status_code:200, body:{found:true, row:{email:.row.email, name:.row.name, status:.row.status, note:.row.note}}} end
elif .found == false then {status_code:404, body:{found:false}}
else error("Unexpected lookup result") end

Connect to Response Definition, not Response Body. Numeric status_code chooses the HTTP status; body supplies the JSON. This removes raw sheet IDs, connection values, row numbers and Google metadata from completed lookup responses.

Related: Write a scheduled status row to Google Sheets.

Sanitized request and response examples

Observed responses from synthetic production cases; 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-private-key>

{"email":"lookup@example.com"}

Observed match: HTTP 200.

{"found":true,"row":{"email":"lookup@example.com","name":"Synthetic First","status":"active","note":"first row"}}

Observed miss for absent@example.com: HTTP 404.

{"found":false}

Observed rejection for {}: HTTP 422.

{"error":"Invalid email","code":"INVALID_EMAIL"}

Validation and failure behaviour

The body must be an object. Email must be a string of 1–254 characters, with one @, a domain dot and no whitespace. Empty, non-text and malformed addresses are rejected before any sheet read. Extra fields are ignored. This pragmatic check does not verify deliverability or mailbox ownership.

Missing and invalid API keys returned HTTP 403. Malformed JSON returned HTTP 422 with the documented INVALID_EMAIL object. In-flow responses had JSON content type and Cache-Control: no-store; the platform 403 responses had JSON content type but no no-store header.

Search Row reads the whole tab and chooses the first physical matching row below the header. Existing duplicates remain unchanged. A missing trailing note becomes JSON null.

Google errors are not caught by this graph. An access, header, quota or spreadsheet error can stop execution and return a raw platform error, possibly HTTP 404 or 500. That response is distinct from the controlled 404 {"found":false}. It may include private sheet metadata and bypass the response headers. Verify configuration and handle errors in a trusted backend before exposing results.

Test results

The 30 sequential production requests all passed. All five data rows and their values remained unchanged after every request. Temporary API key deletion was confirmed. The non-object and invalid-email groups each contain five requests.

Download

Download the credential-free configuration guide for the node settings, JQ and ports. It is a setup guide, not an importable workflow file.

Download setup JSON

Importable workflow download: Not available; imported-copy test not performed. A sanitized import file can be added after a live build, credential review and imported-copy test. Until then, reproduce the documented nodes and mappings.

Required configuration

• An AI Flowi Workflow workspace and private Platform API Key for the sender.
• An authorized Google connection with access to the private spreadsheet.
• Your private Spreadsheet ID and configured lookup tab; do not use caller-provided identifiers.
• Unique lowercase email,name,status,note headers in row 1. Adjust the response projection deliberately if you need another schema.
• Synthetic fixture rows for match, miss, case difference, duplicate and blank-cell checks; a trusted backend for caller authorization.

Tested version and date

Production-tested on AI Flowi Workflow 1.9.2 on 9 October 2026 (MYT). Runtime node billing and imported-copy verification were not measured in this evidence.

Usage and cost assumptions

Plans: Go from USD8/month billed yearly (USD10 monthly), Plus from USD16 (USD20 monthly), Pro 100 from USD80 (USD100 monthly), Pro 200 from USD160 (USD200 monthly) and Pro 500 from USD400 (USD500 monthly), with 5,000 to 250,000 Flowi Tokens/month and top-ups; 1 token per node run; third-party API and model usage is not included.

Expected selected paths: 7 node runs for a valid lookup, including a miss; 4 for validation rejection; 0 graph node runs for platform key rejection. These are structural estimates awaiting runtime evidence. A sheet failure reaches four completed nodes and the failed search attempt; failed-node billing is unverified.

For mixed traffic, estimate 7 × valid + 4 × invalid. Each retry repeats its selected path. The test-suite estimate adds 31 one-node read-back runs to 139 expected webhook node runs: 170 expected Flowi Tokens if every request completes without retries. Catalogue fetches, graph saves and event polls are not node runs.

No model component or third-party paid API is required. Third-party API/model usage is not included in the plan. Google account access and API quotas still apply. No token overage price or additional plan entitlement is assumed.

Limitations

• Whole-tab read and linear search; unsuitable as a high-volume database index.
• Exact case-sensitive keys can miss differently cased or space-padded sheet values. The first duplicate wins; concurrent external edits may change the result.
• Headers and returned cell values must match the configured schema. Formula and formatted values follow Google API defaults.
• No per-email authorization, Google error recovery or snapshot isolation is provided by this graph.
• Value comparisons do not verify formulas, formatting, permissions or other tabs. Production HTTP/body/value checks passed; imported-copy verification remains untested.

Next steps

Re-run the checklist on your own private configuration. Review key cleanup and raw-error handling, then sanitize and re-test an imported copy before treating a download as production-tested.

Related: Universal Webhook · Google connection · Webhook to Google Sheets tutorial · Use cases.