# Log prospect replies to Google Sheets

A handler for the webhook receiver that appends one row per prospect reply to a Google Sheet through the Sheets API, using a service account and no library.

Works with: Google Sheets.

Endpoints used:

- [`POST /v1/campaigns/{campaign_id}/webhooks`](https://docs.versionseven.ai/api-reference/campaigns/create-webhook) Create a webhook
- [`GET /v1/webhooks/examples`](https://docs.versionseven.ai/api-reference/reference/list-webhook-examples) List webhook examples

Many teams keep a shared sheet of who replied, what they said and how the Appointment Setter read it. This recipe is a handler for [Receive replies once and fan them out](https://docs.versionseven.ai/cookbook/webhook-receiver) that appends one row per [`prospect_response`](https://docs.versionseven.ai/api-reference/webhooks/prospect-response) delivery to a Google Sheet. It talks to the Sheets API directly, with a service account, so there's nothing to install beyond the receiver itself.

## Before you start

- The receiver from [Receive replies once and fan them out](https://docs.versionseven.ai/cookbook/webhook-receiver), running or ready to run. This handler goes in its `handlers` directory.
- A Google Cloud project with the **Google Sheets API** enabled, and a **service account** with a JSON key downloaded. Save the key file somewhere the receiver can read and put its path in `GOOGLE_SERVICE_ACCOUNT_FILE`.
- A spreadsheet shared with the service account's email address (the `client_email` in the key file) as an editor. Its id, the long string in the sheet's URL, goes in `SHEET_ID`; the tab name in `SHEET_TAB` (default `Replies`).
- Python only: `pip install google-auth`.

Put this header in row 1 of the tab, in this order. The handler appends below it:

```text
Received at, Campaign, Campaign id, Channel, Sentiment, Out of office, First name, Last name, Company, Title, Email, LinkedIn, Goal status, Agent action, Brief, Message, Idempotency key
```

## The handler

**Node.js**

```javascript
// handlers/sheets.mjs
import crypto from "node:crypto";
import { readFile } from "node:fs/promises";

const key = JSON.parse(await readFile(process.env.GOOGLE_SERVICE_ACCOUNT_FILE, "utf8"));
const sheetId = process.env.SHEET_ID;
const tab = process.env.SHEET_TAB ?? "Replies";
const SHEETS_API = "https://sheets.googleapis.com/v4/spreadsheets";
let token = { value: null, expiresAt: 0 };

const base64url = (text) => Buffer.from(text).toString("base64url");

// Google's service-account flow: sign a JWT with the key, exchange it for an access token, keep it until it expires.
async function accessToken() {
  if (token.value && Date.now() < token.expiresAt - 60_000) return token.value;
  const now = Math.floor(Date.now() / 1000);
  const header = base64url(JSON.stringify({ alg: "RS256", typ: "JWT" }));
  const claims = base64url(
    JSON.stringify({
      iss: key.client_email,
      scope: "https://www.googleapis.com/auth/spreadsheets",
      aud: key.token_uri,
      iat: now,
      exp: now + 3600,
    })
  );
  const signature = crypto.sign("RSA-SHA256", Buffer.from(`${header}.${claims}`), key.private_key).toString("base64url");
  const response = await fetch(key.token_uri, {
    method: "POST",
    headers: { "Content-Type": "application/x-www-form-urlencoded" },
    body: new URLSearchParams({
      grant_type: "urn:ietf:params:oauth:grant-type:jwt-bearer",
      assertion: `${header}.${claims}.${signature}`,
    }),
    signal: AbortSignal.timeout(10_000),
  });
  if (!response.ok) throw new Error(`Google token request answered ${response.status}`);
  const data = await response.json();
  token = { value: data.access_token, expiresAt: Date.now() + data.expires_in * 1000 };
  return token.value;
}

// One row, in the header's order. Missing values become empty cells.
export function rowFor({ received_at, campaign_id, event }) {
  const lead = event.lead ?? {};
  const ai = event.ai_response ?? {};
  return [
    received_at,
    event.campaign ?? "",
    campaign_id,
    event.channel ?? "",
    ai.sentiment ?? "",
    ai.out_of_office ? "yes" : "no",
    lead.first_name ?? "",
    lead.last_name ?? "",
    lead.company ?? "",
    lead.title ?? "",
    lead.email ?? "",
    lead.linkedin_profile ?? "",
    ai.goal_status ?? "",
    ai.agent_action ?? "",
    ai.sdr_brief ?? "",
    event.prospect_message ?? "",
    event.idempotency_key,
  ];
}

export default async function handle(delivery) {
  const range = encodeURIComponent(`${tab}!A1`);
  const url = `${SHEETS_API}/${sheetId}/values/${range}:append?valueInputOption=RAW&insertDataOption=INSERT_ROWS`;
  const response = await fetch(url, {
    method: "POST",
    headers: { Authorization: `Bearer ${await accessToken()}`, "Content-Type": "application/json" },
    body: JSON.stringify({ values: [rowFor(delivery)] }),
    signal: AbortSignal.timeout(10_000),
  });
  if (!response.ok) throw new Error(`Sheets API answered ${response.status}: ${await response.text()}`);
}
```

**Python**

```python
# handlers/sheets.py
import os

from google.auth.transport.requests import AuthorizedSession
from google.oauth2 import service_account

SHEETS_API = "https://sheets.googleapis.com/v4/spreadsheets"
SHEET_ID = os.environ["SHEET_ID"]
TAB = os.environ.get("SHEET_TAB", "Replies")

# Google's service-account flow; the session fetches an access token and refreshes it when it expires.
credentials = service_account.Credentials.from_service_account_file(
    os.environ["GOOGLE_SERVICE_ACCOUNT_FILE"],
    scopes=["https://www.googleapis.com/auth/spreadsheets"],
)
session = AuthorizedSession(credentials)


def row_for(delivery: dict) -> list:
    """One row, in the header's order. Missing values become empty cells."""
    event = delivery["event"]
    lead = event.get("lead") or {}
    ai = event.get("ai_response") or {}
    return [
        delivery["received_at"],
        event.get("campaign") or "",
        delivery["campaign_id"],
        event.get("channel") or "",
        ai.get("sentiment") or "",
        "yes" if ai.get("out_of_office") else "no",
        lead.get("first_name") or "",
        lead.get("last_name") or "",
        lead.get("company") or "",
        lead.get("title") or "",
        lead.get("email") or "",
        lead.get("linkedin_profile") or "",
        ai.get("goal_status") or "",
        ai.get("agent_action") or "",
        ai.get("sdr_brief") or "",
        event.get("prospect_message") or "",
        event["idempotency_key"],
    ]


def handle(delivery: dict) -> None:
    response = session.post(
        f"{SHEETS_API}/{SHEET_ID}/values/{TAB}!A1:append",
        params={"valueInputOption": "RAW", "insertDataOption": "INSERT_ROWS"},
        json={"values": [row_for(delivery)]},
        timeout=10,
    )
    if not response.ok:
        raise RuntimeError(f"Sheets API answered {response.status_code}: {response.text}")
```

Start the receiver with the handler switched on:

```bash
HANDLERS=sheets GOOGLE_SERVICE_ACCOUNT_FILE=./service-account.json SHEET_ID=1AbC… node receiver.mjs
HANDLERS=sheets GOOGLE_SERVICE_ACCOUNT_FILE=./service-account.json SHEET_ID=1AbC… flask --app receiver run --port 3000
```

Then register the receiver on the campaign with [`POST /v1/campaigns/{campaign_id}/webhooks`](https://docs.versionseven.ai/api-reference/campaigns/create-webhook) and send a signed example from [`GET /v1/webhooks/examples`](https://docs.versionseven.ai/api-reference/reference/list-webhook-examples), as the receiver recipe shows. A row appears; send the same example again and no row is added, because the receiver drops a repeated `idempotency_key` before any handler runs.

## Choices worth knowing

- `valueInputOption=RAW` stores every cell as text. A prospect's message that starts with `=` or `+` stays a message rather than becoming a formula.
- The last column is the delivery's `idempotency_key`. If you ever replay a delivery, a second row with the same key appears; `COUNTIF` on that column finds them.
- A lead's reply is delivered once per campaign, and again when a later reply is positive after an earlier one wasn't. The sheet gets a second row for that lead then, with the new message.
- Google's quota for the Sheets API is per project and per minute. Replies arrive far below it; if a handler ever gets `429`, the receiver logs it and the delivery can be replayed.
- An Apps Script web app can also receive HTTP posts, but `doPost` can't read request headers, so it can't verify `X-Signature-256`. Keep the receiver in front and let it do the checking.

## Next steps

- [Receive replies once and fan them out](https://docs.versionseven.ai/cookbook/webhook-receiver) for replay and for adding Slack alongside the sheet.
- [Add leads from Google Sheets to a campaign](https://docs.versionseven.ai/cookbook/add-leads-from-google-sheets), the other direction.
- [Replies and sentiment](https://docs.versionseven.ai/help/replies-and-sentiment) for what each sentiment label means.