Cookbook
View as Markdown

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.

Last updated

  • Google Sheets

Endpoints used

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 that appends one row per 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, 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:

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

// 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()}`);
}

Start the receiver with the handler switched on:

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 and send a signed example from GET /v1/webhooks/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