DEV Community: Hayrullah Kar

Build a WhatsApp Storefront in Google Sheets with OpenAI Function Calling (No Website)

Hayrullah Kar — Mon, 20 Jul 2026 14:17:19 +0000

A café owner asked me for an online store. She didn't want Shopify, a domain, a theme, or a $90/month bill — she wanted customers to message her on WhatsApp, ask what's available, and place an order. That's not a website. That's three functions and a spreadsheet.

Here's the whole thing: an AI that browses your catalog and logs orders over chat, backed by two Google Sheets tabs, for under $20/month. It's about 200 lines of Apps Script, and the three tools that do the work are below — verbatim.

Why function calling is the right tool here

A keyword bot ("reply 1 for menu") falls apart the moment a customer types "what pastries do you have under 50?". A language model handles that phrasing easily — but on its own it can only talk. To actually read your catalog and write an order, it needs tools: functions it's allowed to call.

OpenAI's function calling is exactly that. You hand GPT a list of tool schemas; when it decides it needs data, it returns a tool_call naming the tool and its arguments; your Apps Script runs the matching function and hands the result back. Three tools cover a storefront: list products, look up one product, log an order.

Tool 1: read the catalog

The model calls this when a customer wants to browse. It skips anything inactive or out of stock, and treats a blank stock cell as "unlimited" (handy for services and made-to-order items).

function toolGetProducts(phone, args, lead) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Products');
  if (!sheet || sheet.getLastRow() < 2) {
    return { products: [], note: 'No products configured yet.' };
  }

  const data = sheet.getDataRange().getValues();
  const products = [];
  for (let i = 1; i < data.length; i++) {
    const [sku, name, desc, price, currency, stock, active] = data[i];
    if (active !== true) continue;
    if (stock !== '' && Number(stock) <= 0) continue;
    products.push({
      sku: String(sku),
      name: String(name),
      description: String(desc || ''),
      price: Number(price),
      currency: String(currency || ''),
      in_stock: stock === '' ? null : Number(stock)
    });
  }
  return { count: products.length, products };
}

Tools 2 & 3: look up a product, then log the order

toolGetProduct is a case-insensitive SKU lookup. toolLogOrder is the one that touches money, so it validates first, re-reads the product for the authoritative price (never trusting a price the model might invent), computes the total itself, and stamps a unique order id.

function toolGetProduct(phone, args, lead) {
  const sku = args && args.sku ? String(args.sku).trim() : '';
  if (!sku) return { error: 'sku is required' };

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Products');
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    if (String(row[0]).toLowerCase() === sku.toLowerCase()) {
      return {
        sku: String(row[0]),
        name: String(row[1]),
        description: String(row[2] || ''),
        price: Number(row[3]),
        currency: String(row[4] || ''),
        in_stock: row[5] === '' ? null : Number(row[5]),
        active: row[6] === true
      };
    }
  }
  return { error: 'Product ' + sku + ' not found' };
}

function toolLogOrder(phone, args, lead) {
  const sku = args && args.sku ? String(args.sku).trim() : '';
  const qty = args && args.quantity ? Number(args.quantity) : 0;
  const notes = (args && args.notes) ? String(args.notes) : '';

  if (!sku || !qty || qty < 1) return { error: 'sku and quantity required' };

  const product = toolGetProduct(phone, { sku }, lead);
  if (product.error) return product;

  const total = product.price * qty;                       // server computes it, not the model
  const orderId = 'ORD-' + Date.now().toString(36).toUpperCase();

  SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Orders').appendRow([
    orderId, phone, product.sku, product.name, qty,
    product.price, total, product.currency, notes,
    'pending', new Date().toISOString()
  ]);

  return {
    success: true, order_id: orderId, sku: product.sku,
    product_name: product.name, quantity: qty, total: total,
    currency: product.currency, status: 'pending'
  };
}

The glue: expose the tools and dispatch the model's call

This is what turns three functions into a storefront. You register each tool with the schema the model sees, and route whatever it calls to the matching handler. The description on each schema is doing real work — it's how GPT knows not to call log_order until the customer has actually confirmed.

const TOOLS = {
  get_products: {
    handler: toolGetProducts,
    schema: {
      name: 'get_products',
      description: 'List the active, in-stock products this shop sells.',
      parameters: { type: 'object', properties: {} }
    }
  },
  get_product: {
    handler: toolGetProduct,
    schema: {
      name: 'get_product',
      description: 'Look up one product by its SKU.',
      parameters: {
        type: 'object',
        properties: { sku: { type: 'string' } },
        required: ['sku']
      }
    }
  },
  log_order: {
    handler: toolLogOrder,
    schema: {
      name: 'log_order',
      description: 'Log a confirmed order. Only call AFTER the customer confirms the item and quantity.',
      parameters: {
        type: 'object',
        properties: {
          sku: { type: 'string' },
          quantity: { type: 'number' },
          notes: { type: 'string' }
        },
        required: ['sku', 'quantity']
      }
    }
  }
};

// When GPT returns a tool call, run the matching handler and feed the JSON back.
function runToolCall(toolCall, phone, lead) {
  const tool = TOOLS[toolCall.name];
  if (!tool) return { error: 'unknown tool ' + toolCall.name };
  let args = {};
  try { args = JSON.parse(toolCall.arguments || '{}'); }
  catch (e) { return { error: 'bad tool arguments' }; }
  return tool.handler(phone, args, lead);
}

The webhook that receives the WhatsApp message (via Twilio) and the loop that calls GPT with TOOLS are the same pattern I covered in Build a WhatsApp Sales Inbox in Google Sheets — a doPost that returns 200 fast and does the model round-trip on the message text. Drop these tools into that loop and you have a store.

I unit-tested the tool logic before shipping — the cases that matter are a blank stock cell reading as unlimited, an out-of-stock item never reaching the catalog, a case-insensitive SKU, and an order with a missing or zero quantity being rejected instead of written.

The economics

For a shop doing under ~500 messages and ~100 orders a month, the running cost breaks down to roughly $5–10 Twilio, $2–5 OpenAI on GPT-4o-mini, and $0–6 Google — call it under $20/month. A Shopify-plus-app stack for the same thing runs $80–140/month combined. The bigger win is that the orders live in your Drive, not a vendor's database.

When NOT to use this

Thousands of SKUs, or a catalog that changes hourly. This shines at 5–30 stable products; past that you want real inventory sync and a database.
Variants, tax rules, and international shipping. The model will happily log an order; it won't handle size/color matrices or VAT correctly.
Taking payment in-chat. This logs a pending order. Bolt on a Stripe payment link if you need money to move; don't fake a checkout.
Letting the model set prices. Note that toolLogOrder re-reads the price from the sheet. Never trust a total the model calculated — recompute it server-side, always.

Wrap-up

An online store, for a lot of small sellers, is three functions: list the catalog, look up an item, log the order — wired to GPT through function calling, backed by two spreadsheet tabs. No website, no monthly platform fee, and the data stays yours.

The production version — Stripe payment links, stock decrement on order, and a merchant dashboard — is written up on the MageSheet blog.

Built by the MageSheet team.

Build a Commission Attribution Engine in Google Sheets (Matching, Splits & Clawbacks)

Hayrullah Kar — Sun, 19 Jul 2026 10:42:16 +0000

A rep pings you: "My payout is $340 short." You open the sheet, and three hours later you find it — the customer paid from ali.veli+billing@gmail.com instead of the address on the account, so the sale never got attributed to anyone.

The math was never wrong. In the commission cleanups I've done for clients, nearly every dispute traced back to this: the payment didn't match the right rep. I'd put it around 95% — matching, not arithmetic. Yet almost everyone builds the calculator first.

I already wrote up the calculator — tiered rates, sales that span bands, refund reversal — in the tiered commission engine post. This post is the layer underneath it: the attribution engine that decides whose sale it was in the first place.

Why matching is where it breaks

A payment arrives from Stripe, Gumroad, or a WhatsApp deal. To pay commission on it you have to answer one question: which rep owns this? And the payment record fights you:

The buyer checked out with a personal address, not the one on the account.
They used a +tag alias, or Gmail dot variations (a.b@ vs ab@).
It's a renewal, and nobody set an owner on the original.
Two reps worked it — a BDR who sourced it and an AE who closed it.

Get this wrong and the calculator faithfully computes the wrong person's commission. That's why the matcher comes first.

1. Normalize the email before you compare anything

Half the misses disappear here. Strip +tags everywhere, and strip dots only for Gmail — because Gmail ignores dots in the local part, while other providers treat them as significant. Getting that distinction backwards silently merges different people.

function normalizeEmail(email) {
  const parts = String(email || '').toLowerCase().trim().split('@');
  if (parts.length !== 2 || !parts[1]) return '';
  let local = parts[0].split('+')[0];                              // drop +tags
  if (parts[1] === 'gmail.com') local = local.replace(/\./g, '');   // gmail ignores dots
  return local + '@' + parts[1];
}

function domainOf(email) {
  return String(email || '').toLowerCase().split('@')[1] || '';
}

Ali.Veli+shop@Gmail.com and aliveli@gmail.com are the same person. a.b@company.com and ab@company.com are not.

2. Match in tiers, and never trust a free domain

Exact email is a certain match. Company domain is a good guess. A free provider domain is not a signal at all — and this is the trap that quietly ruins commission data: if you allow domain matching on gmail.com, every consumer purchase in your system gets assigned to whichever rep happens to own one Gmail contact.

const FREE_DOMAINS = new Set([
  'gmail.com', 'outlook.com', 'hotmail.com', 'yahoo.com', 'icloud.com',
]);

// accounts: [{ email, domain, repId }]
function matchRep(payment, accounts) {
  const email = normalizeEmail(payment.email);
  if (!email) return { repId: null, confidence: 0, method: 'no-email' };

  const byEmail = accounts.find(a => normalizeEmail(a.email) === email);
  if (byEmail) return { repId: byEmail.repId, confidence: 1, method: 'email' };

  const dom = domainOf(email);
  // NEVER match on a free provider — it would assign every consumer sale to one rep.
  if (dom && !FREE_DOMAINS.has(dom)) {
    const byDomain = accounts.find(a => a.domain === dom);
    if (byDomain) return { repId: byDomain.repId, confidence: 0.8, method: 'domain' };
  }
  return { repId: null, confidence: 0, method: 'unmatched' };
}

Returning a confidence rather than a bare repId is what makes this usable. Auto-post anything at 1, route 0.8 to a dispatcher for a one-click confirm, and park 0 in an "unmatched" tab that someone clears weekly. An unmatched row is a known unknown; a wrongly-matched row is a dispute three months later.

3. Clawback windows and split credit

A refund shouldn't always claw back commission. Every commission policy needs a window — typically 30–90 days for refunds and 6–12 months for churn on annual contracts. Outside the window, the rep keeps it. Put that in the policy on day one, and encode it:

function withinClawbackWindow(saleDate, refundDate, windowDays) {
  const days = (new Date(refundDate) - new Date(saleDate)) / 86400000;
  return days >= 0 && days <= windowDays;   // negative = refund dated before the sale
}

function splitCredit(amount, splits) {
  const total = Object.keys(splits).reduce((s, k) => s + splits[k], 0);
  if (Math.abs(total - 1) > 1e-9) {
    throw new Error('splits must sum to 1, got ' + total);
  }
  const out = {};
  Object.keys(splits).forEach(k => { out[k] = amount * splits[k]; });
  return out;
}

splitCredit throws instead of silently normalizing. That's deliberate: a BDR/AE split that adds up to 0.9 means somebody fat-fingered the rules table, and you want to hear about it now rather than discover 10% of a quarter's credit evaporated.

I unit-tested all of this before shipping — the cases that matter are the Gmail dot/+tag variants, a free-provider address refusing to domain-match, a refund dated before its sale, and splits that don't sum to 1.

Pitfalls

Build the matcher before the calculator. A perfect tier engine on wrong attribution just pays the wrong person precisely.
Never domain-match a free provider. It's the single most damaging default in a commission system.
Store confidence, not just a match. Unmatched rows are cheap; wrong matches are disputes.
Write the clawback window into the policy first. Retro-applying one to sales already paid out destroys trust faster than a wrong number.
Splits drift. Re-check the rules table each quarter; a deal that changed hands mid-cycle is the usual culprit.
Give each rep a view of only their own rows. Full visibility into the ledger turns into salary politics.
Know the ceiling. This holds comfortably to roughly 15 reps on Sheets; past that the ledger wants a real database.

Wrap-up

Commission disputes almost never start in the math. They start when a payment can't find its rep — an alias, a personal address, a renewal with no owner. Normalize the email, match in tiers with an explicit confidence, refuse to guess on free domains, and keep unmatched rows visible. The calculator can be simple once the attribution is honest.

The production version — Stripe/Gumroad ingestion, the per-rep tokenized dashboard, and the finance export — is written up on the MageSheet blog.

Built by the MageSheet team.

Build a Real-Time Inventory Dashboard in Google Sheets (Reorder Points + Low-Stock Alerts)

Hayrullah Kar — Fri, 17 Jul 2026 16:57:58 +0000

A marketing campaign works, 150 units sell in a day — and 40 were in stock. Now you have 110 angry customers, refunds, and a marketplace penalty for overselling. The campaign that was supposed to make money just cost you money and trust.

The fix isn't a $200/month inventory app. It's a Google Sheet that knows your reorder point per product, tells the difference between stock you have and stock you can actually sell, and emails you before you run out. This post builds it, with the code.

Why manual stock tracking guarantees a stockout

Across a handful of SKUs you can eyeball it. Across a few hundred, you can't — each product has a different sales rate and a different supplier lead time, so "when do I reorder this one?" is a different answer for every row. Do that math by hand and you will be late on something.

There's one formula that removes the guessing — the reorder point:

ROP = (average daily sales × supplier lead time in days) + safety stock

If a product sells ~10/day, your supplier takes 14 days, and you keep 20 units of buffer: 10 × 14 + 20 = 160. When available stock hits 160, you reorder — and the shipment lands just as you'd otherwise run dry.

Set up an Inventory sheet with these columns and a matching constant:

// Inventory columns:
// sku | name | onHand | allocated | avgDailySales | leadTimeDays |
// safetyStock | available | rop | status
const INV = {
  SKU: 1, NAME: 2, ON_HAND: 3, ALLOC: 4, AVG: 5, LEAD: 6,
  SAFETY: 7, AVAIL: 8, ROP: 9, STATUS: 10,
};

1. The math: reorder point and the stock that actually matters

Three small functions carry the whole dashboard. The one people skip is availableStock — the difference between what's on the shelf and what's already promised to open orders. Overselling happens when you reorder against on-hand instead of available.

function reorderPoint(avgDailySales, leadTimeDays, safetyStock) {
  return avgDailySales * leadTimeDays + safetyStock;
}

function availableStock(onHand, allocated) {
  return onHand - allocated;   // what you can sell, not what's on the shelf
}

function stockStatus(onHand, allocated, rop) {
  const avail = availableStock(onHand, allocated);
  if (avail <= 0) return 'OUT';
  if (avail <= rop) return 'REORDER';
  return 'OK';
}

100 units on hand with 90 already allocated to unshipped orders is not "100 in stock" — it's 10. stockStatus reads the available number, so it flags REORDER while the shelf still looks full.

2. Refresh the whole dashboard in one pass

This recomputes available, ROP, and status for every row and writes them back in a single setValues call — fast, and gentle on Apps Script's quotas. Run it on an hourly trigger, or fire it from the Magento webhook that updates onHand and allocated.

function refreshDashboard() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
  const last = sheet.getLastRow();
  if (last < 2) return;

  const range = sheet.getRange(2, 1, last - 1, INV.STATUS);
  const rows = range.getValues();

  rows.forEach(r => {
    const onHand = Number(r[INV.ON_HAND - 1]) || 0;
    const alloc  = Number(r[INV.ALLOC - 1]) || 0;
    const rop = reorderPoint(
      Number(r[INV.AVG - 1]) || 0,
      Number(r[INV.LEAD - 1]) || 0,
      Number(r[INV.SAFETY - 1]) || 0
    );
    r[INV.AVAIL - 1]  = availableStock(onHand, alloc);
    r[INV.ROP - 1]    = rop;
    r[INV.STATUS - 1] = stockStatus(onHand, alloc, rop);
  });

  range.setValues(rows);   // one write for the whole sheet
}

Reading and writing the whole range once (instead of cell by cell in a loop) is the difference between a dashboard that refreshes in a second and one that trips the 6-minute limit at a few thousand SKUs.

3. Get told before you run out

A dashboard nobody looks at doesn't prevent a stockout. A daily trigger on sendLowStockAlert emails whoever handles purchasing the moment anything crosses into REORDER or OUT.

function sendLowStockAlert() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Inventory');
  const last = sheet.getLastRow();
  if (last < 2) return;

  const low = sheet.getRange(2, 1, last - 1, INV.STATUS).getValues()
    .filter(r => r[INV.STATUS - 1] === 'REORDER' || r[INV.STATUS - 1] === 'OUT')
    .map(r => `${r[INV.STATUS - 1]}  ${r[INV.SKU - 1]}  ${r[INV.NAME - 1]}  ` +
              `(available ${r[INV.AVAIL - 1]}, reorder point ${r[INV.ROP - 1]})`);

  if (!low.length) return;   // nothing to nag about today

  MailApp.sendEmail({
    to: PropertiesService.getScriptProperties().getProperty('ALERT_EMAIL'),
    subject: `⚠ ${low.length} product(s) need reordering`,
    body: 'Reorder these now:\n\n' + low.join('\n'),
  });
}

I unit-tested the math before shipping — the ROP worked example lands on 160, and the status boundaries (available exactly at the ROP, exactly at zero, and oversold into negatives) all behave.

Pitfalls

Reorder against available, not on-hand. The allocated column is what stops overselling; skip it and the dashboard lies to you while the shelf looks full.
Keep avgDailySales fresh. A number set once goes stale — recompute it from a rolling window (e.g. the last 14–30 days) so a seasonal spike actually moves the reorder point.
Read/write the range once. Cell-by-cell getValue/setValue in a loop is the classic way to hit the 6-minute execution cap on a big catalog.
Dedupe the Magento webhook. Sync events can arrive twice or out of order; key updates by SKU and ignore stale ones.
Know the ceiling. Sheets stays responsive into the thousands of SKUs and low-to-mid six-figure monthly orders; past that, keep the sheet as the dashboard and move the source of truth to a database.

Wrap-up

A real-time inventory dashboard is three ideas: a reorder point per product, the discipline to reorder against available (not on-hand) stock, and an alert that reaches you before the shelf is empty. That's a few dozen lines of Apps Script on a sheet you already own — not a $200/month subscription.

The production version — Magento two-way sync, per-warehouse stock, and supplier-aware lead times — is written up on the MageSheet blog.

Built by the MageSheet team.

WhatsApp Automation for Small Businesses in 2026: AI Replies, Lead Capture & Tiered Commissions

Hayrullah Kar — Thu, 16 Jul 2026 21:44:58 +0000

Your customers would rather message you on WhatsApp than fill in a contact form. That's fine at ten conversations a day. At a hundred, messages get missed, nobody knows which rep is on which deal, and at month-end somebody rebuilds the commission sheet by hand and gets it wrong.

The usual answer is a $49–$499/month WhatsApp SaaS platform, priced per seat, with your customer data living in someone else's database. This post is the other answer: the same workflow on Google Sheets + Apps Script — and the one piece I see teams get wrong every single time, with the code to fix it.

Where DIY WhatsApp automation actually breaks

It isn't the messaging. Wiring a WhatsApp webhook into a sheet is a couple of hours of work, and I've written that build up separately — the webhook, the AI reply, and the lock that stops two reps chasing the same lead are all in Build a WhatsApp Sales Inbox in Google Sheets. I won't repeat it here.

The part that breaks is the commission math. Someone writes =IF(revenue>10000, revenue*0.08, revenue*0.05) into a column, and three things kill it:

A single sale spans two tiers — the formula charges the whole amount at one rate.
The tiers change in July, and now every historical row recalculates at the new rate.
A customer refunds in August on a sale from June, and nobody can unwind it without breaking the audit trail.

So that's what this post builds: a tiered commission engine that survives rule changes and refunds.

1. Put the tiers in a table, never in a formula

This is the whole trick. Make a Commission Rules tab, one row per rule:

rule_id | rep_id   | effective_from | effective_to | tier_1_cap | tier_1_pct |
        |          |                |              | tier_2_cap | tier_2_pct | tier_3_pct
--------+----------+----------------+--------------+------------+------------+-----------
R1      | ALL      | 2026-01-01     |              | 10000      | 0.05       |
        |          |                |              | 50000      | 0.08       | 0.10
R2      | rep_ayse | 2026-06-01     |              | 10000      | 0.06       |
        |          |                |              | 50000      | 0.09       | 0.12

rep_id is either a specific rep or ALL (the house default). Percentages are decimals — 0.05 is 5%. When the plan changes, you close the old rule with an effective_to date and add a new row. History keeps calculating at the rate that was live on the day of the sale.

function findRule(rules, repId, saleDate) {
  const d = new Date(saleDate);
  const matches = rules.filter(r =>
    (r.rep_id === repId || r.rep_id === 'ALL') &&
    d >= new Date(r.effective_from) &&
    (!r.effective_to || d <= new Date(r.effective_to))
  );
  // A rule written for this rep always beats the "ALL" fallback.
  matches.sort((a, b) => (a.rep_id === 'ALL' ? 1 : 0) - (b.rep_id === 'ALL' ? 1 : 0));
  return matches[0] || null;
}

2. Split one sale across tiers

Here's the bug in every hand-written commission column. A rep has booked $45,000 this quarter and closes another $10,000. Tier 2 ends at $50,000. So $5,000 of that sale earns 8% and $5,000 earns 10% — not $10,000 at one rate.

// Commission for ONE sale, given how much the rep already booked this period.
// A single sale can span two or three tiers — that's what formulas get wrong.
function commissionForSale(bookedBefore, amount, rule) {
  const bands = [
    { upTo: Number(rule.tier_1_cap), pct: Number(rule.tier_1_pct) },
    { upTo: Number(rule.tier_2_cap), pct: Number(rule.tier_2_pct) },
    { upTo: Infinity,                pct: Number(rule.tier_3_pct) },
  ];
  let from = bookedBefore, left = amount, commission = 0;

  for (const band of bands) {
    if (left <= 0) break;
    const room = Math.max(band.upTo - from, 0);
    const take = Math.min(left, room);
    commission += take * band.pct;
    from += take;
    left -= take;
  }
  return commission;
}

With the R1 rule above: commissionForSale(45000, 10000, R1) returns 900 — $5,000 at 8% plus $5,000 at 10%. The naive formula returns either $800 or $1,000, and your rep notices.

3. Handle refunds without breaking the audit trail

Never edit or delete the original row. A refund is its own row with a negative amount and a reverses_transaction_id pointing back at the sale. Then the engine reverses exactly the commission that sale earned — not a recalculated guess.

function runCommissions(transactions, rules) {
  const sorted = transactions.slice()
    .sort((a, b) => new Date(a.date) - new Date(b.date));
  const booked = {};   // rep_id -> revenue booked so far
  const earned = {};   // transaction_id -> commission it earned
  const out = [];

  sorted.forEach(t => {
    if (t.reverses_transaction_id) {                    // refund row
      const original = earned[t.reverses_transaction_id] || 0;
      booked[t.rep_id] = (booked[t.rep_id] || 0) + Number(t.amount);  // negative
      out.push({ id: t.transaction_id, rep: t.rep_id, commission: -original });
      return;
    }
    const rule = findRule(rules, t.rep_id, t.date);
    if (!rule) {
      out.push({ id: t.transaction_id, rep: t.rep_id, commission: 0, note: 'no rule' });
      return;
    }
    const before = booked[t.rep_id] || 0;
    const c = commissionForSale(before, Number(t.amount), rule);
    booked[t.rep_id] = before + Number(t.amount);
    earned[t.transaction_id] = c;
    out.push({ id: t.transaction_id, rep: t.rep_id, commission: c });
  });
  return out;
}

Because it walks transactions in date order and keeps a running total per rep, the tier boundaries land where they actually landed in real life. I unit-tested this before shipping it — the cases that matter are a sale spanning all three tiers, a rule that isn't effective yet falling back to ALL, and a refund netting to exactly zero.

What the rest of the stack costs

At small-business volume, the running cost is LLM tokens plus Meta's conversation fees — roughly $0.005–$0.08 per conversation depending on category and country, which for most SMBs lands near $50/month all-in. Compare that to $49–$499/month per platform, plus per-seat fees. The bigger difference isn't the invoice, though: the sheet is yours, and so is the commission logic.

WhatsApp's often-quoted ~98% open rate is a vendor benchmark rather than a controlled study — but you don't need the exact number to know the channel outperforms email for a reply-now conversation.

Pitfalls

Don't hard-code tiers in formulas. Rules go in the rules table with effective dates, or your July change silently rewrites June's payouts.
Don't recalculate refunds. Reverse the commission the original sale actually earned, via reverses_transaction_id.
Process in date order. Tier boundaries depend on running totals; out-of-order rows produce wrong bands.
Use an official WhatsApp API (Twilio, 360dialog, Meta Cloud). Unofficial libraries get business numbers banned.
Apps Script limits: 6-minute execution cap and ~20k UrlFetchApp calls/day (100k on Workspace). Fine for SMB volume; above that, move the compute out and keep Sheets as the store.

Wrap-up

The messaging half of WhatsApp automation is easy and well covered. The commission half is where teams quietly lose money and trust — and it's solved by two ideas: keep the tiers in a dated rules table, and split each sale across the bands it actually crosses.

The production version — multi-agent splits, clawback windows, and the payout approval flow — is written up on the MageSheet blog.

Built by the MageSheet team.

Build a Gumroad Lead Machine in Google Sheets (Ping Webhook + License API + AI Upsell)

Hayrullah Kar — Wed, 15 Jul 2026 08:32:02 +0000

A traditional B2B funnel asks a stranger to fill a form, wait for a sales rep, and sit through a call — and it typically converts around 0.1–0.5%. I stopped running that. Instead I sell a small $29 product on Gumroad and let the purchase itself qualify the lead.

This post is the Apps Script that turns every Gumroad sale into a logged, license-verified lead with an AI-personalized follow-up email — no CRM, no Zapier, about 70 lines you can copy.

Why free lead magnets and demo forms both fail

A free download gets you emails from people who spent nothing, so they're not qualified. A demo form gets you almost nobody, because the friction is huge. A small paid "micro-commitment" sits in the middle: the buyer proved intent with their wallet, and you now have their email, the exact product they bought, and a valid license — everything you need to follow up with the right offer.

Gumroad handles the checkout. Google Sheets is the lead database. Apps Script is the glue: catch the sale, verify it's real, and send the next step.

Make a sheet named Leads and add this so the code and the columns stay in sync:

// Leads columns: timestamp | saleId | email | product | amount | license | status
const COL = { TS:1, SALE_ID:2, EMAIL:3, PRODUCT:4, AMOUNT:5, LICENSE:6, STATUS:7 };

In Gumroad, enable the ping under Settings → Advanced → "Ping", and point it at your Web App URL with a secret token in the query string: https://script.google.com/.../exec?token=my-secret.

1. Catch the sale: the Ping webhook

The one thing to get right: Gumroad posts application/x-www-form-urlencoded, not JSON. So you read e.parameter, not JSON.parse(e.postData.contents). This trips up everyone who copied a JSON webhook example.

function doPost(e) {
  const p = e.parameter;   // Gumroad is form-encoded — use e.parameter, NOT JSON.parse

  const expected = PropertiesService.getScriptProperties().getProperty('GUMROAD_TOKEN');
  if (expected && p.token !== expected) {
    return ContentService.createTextOutput('unauthorized');
  }

  const sale = saleToRow(p);
  if (!sale.saleId) return ContentService.createTextOutput('ignored');

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');

  // Gumroad can resend a ping — dedupe by sale_id.
  const seen = sheet.getRange(2, COL.SALE_ID, Math.max(sheet.getLastRow() - 1, 1), 1)
                    .getValues().flat();
  if (seen.indexOf(sale.saleId) !== -1) return ContentService.createTextOutput('duplicate');

  sheet.appendRow([new Date(), sale.saleId, sale.email, sale.product, sale.amount, sale.license, sale.status]);
  return ContentService.createTextOutput('ok');
}

function saleToRow(p) {
  return {
    saleId:  p.sale_id || '',
    email:   p.email || '',
    product: p.product_name || '',
    amount:  Number(p.price || 0) / 100,   // Gumroad sends price in cents
    license: p.license_key || '',
    status:  'new'
  };
}

2. Prove it's real: verify the license

A ping is just an HTTP POST — anyone who finds your URL can fake one. Before you provision anything valuable, verify the license against Gumroad's API. It returns the real purchase record, or nothing.

function verifyLicense(productPermalink, licenseKey) {
  const res = UrlFetchApp.fetch('https://api.gumroad.com/v2/licenses/verify', {
    method: 'post',
    muteHttpExceptions: true,
    payload: {
      product_permalink: productPermalink,
      license_key: licenseKey,
      increment_uses_count: 'false'   // just checking — don't burn a use
    }
  });
  const data = JSON.parse(res.getContentText());
  return data.success ? data.purchase : null;   // buyer + product details, or null
}

3. Follow up: the AI-personalized upsell

A time trigger (Triggers → Add trigger → sendUpsells, every 5 minutes) emails each new lead once, then marks it so it never double-sends. The message is written per-product by gpt-4o-mini — under $0.01 per email — and falls back to a plain template if no API key is set.

function sendUpsells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
  const last = sheet.getLastRow();
  if (last < 2) return;

  const rows = sheet.getRange(2, 1, last - 1, COL.STATUS).getValues();
  rows.forEach((row, i) => {
    if (row[COL.STATUS - 1] !== 'new') return;   // only fresh leads
    const email = row[COL.EMAIL - 1];
    const product = row[COL.PRODUCT - 1];
    const message = personalize(product);        // one API call
    GmailApp.sendEmail(email, `Your next step after ${product}`, message, { name: 'Hayrullah at MageSheet' });
    sheet.getRange(i + 2, COL.STATUS).setValue('emailed');
  });
}

function personalize(product) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
  if (!apiKey) {
    return `Thanks for grabbing ${product}. Reply to this email and I'll send the advanced setup.`;
  }
  const res = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', {
    method: 'post', contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey }, muteHttpExceptions: true,
    payload: JSON.stringify({
      model: 'gpt-4o-mini', temperature: 0.7,
      messages: [{ role: 'user',
        content: `Write a friendly 3-sentence follow-up email to someone who just bought "${product}". ` +
                 `Offer a paid done-for-you upgrade. No subject line, no placeholders.` }]
    })
  });
  if (res.getResponseCode() !== 200) {
    return `Thanks for grabbing ${product}. Happy to help you take it further — just reply.`;
  }
  return JSON.parse(res.getContentText()).choices[0].message.content;
}

I unit-tested saleToRow before shipping — the price field is the trap, because Gumroad reports it in cents (2900 means $29.00), and a missing field must default cleanly instead of writing NaN into the sheet.

Pitfalls I hit (so you don't)

Gumroad is form-encoded, not JSON. Read e.parameter.email, not JSON.parse(e.postData.contents). This is the single most common mistake.
doPost(e) can't read HTTP headers. Apps Script exposes query/form params only, so you can't check a signature header — put a secret ?token= in the ping URL and compare e.parameter.token.
Never trust the ping alone. Verify the license before provisioning anything a spoofed POST could steal.
Deduplicate. Gumroad resends pings on timeout; the sale_id check stops duplicate leads.
Gmail send limits. ~100 emails/day on consumer accounts, ~1,500/day on Workspace. Above that, queue and throttle.
Gumroad takes ~10%. Price your micro-commitment so the fee still leaves the offer worth running.

Wrap-up

That's an autonomous lead machine in about 70 lines: a Gumroad checkout becomes a logged, verified lead with a personalized upsell — all inside a Google Sheet, with the micro-commitment doing your lead qualification for you.

The production version — ad attribution back to the sale, license provisioning, and a multi-step follow-up sequence — is written up on the MageSheet blog.

Built by the MageSheet team.

Build a WhatsApp Sales Inbox in Google Sheets (Apps Script + GPT-4o-mini)

Hayrullah Kar — Tue, 14 Jul 2026 16:03:59 +0000

Last month a client's two sales reps both spent an hour chasing the same WhatsApp lead. Neither closed it — the customer felt double-teamed and went quiet. Their "system" was one shared WhatsApp login and a lot of hope.

I rebuilt it as a Google Sheets sales inbox: every WhatsApp message lands in a sheet, gets an AI buying-intent score, and gets locked to one rep for 15 minutes. This post is the whole build, with the code — about 80 lines of Apps Script you can copy and adapt.

Why the shared-inbox approach breaks

A shared WhatsApp number is fine until two people open it at once. There's no record of who's handling what, no way to see which leads are actually hot, and no lock to stop two reps replying to the same person. The usual fix — a $150/user/month CRM with a WhatsApp add-on — is overkill for a 3-person team and still doesn't score intent out of the box.

You already have a shared database your team knows: a Google Sheet. Apps Script can be the backend. We need exactly three pieces — a webhook to catch messages, an LLM call to score them, and a lock to assign them.

flowchart LR
  A[Customer WhatsApp msg] --> B[Provider webhook<br/>Green API / Twilio / Meta]
  B --> C[Apps Script doPost]
  C --> D[Append to Leads sheet]
  E[Time trigger, 1 min] --> F[scoreNewRows -> GPT-4o-mini]
  F --> G{tier}
  G -->|Hot / Warm| H[claimLead: owner + 15-min lock]
  G -->|Cold| I[Leave in queue]

Set up a sheet named Leads with these columns, then add this constant so the code and the sheet agree:

// Leads columns: timestamp | msgId | from | name | text | score | tier | owner | lockedAt
const COL = { TS:1, MSG_ID:2, FROM:3, NAME:4, TEXT:5, SCORE:6, TIER:7, OWNER:8, LOCKED_AT:9 };

1. Catch the message: the webhook

Deploy this as a Web App (Deploy → New deployment → Web app, Execute as: Me, Who has access: Anyone), then paste the deployment URL into your WhatsApp provider's webhook setting. The doPost runs on every inbound message.

function doPost(e) {
  try {
    const body = JSON.parse(e.postData.contents);
    const msg = extractMessage(body);
    if (!msg) return json({ status: 'ignored' });

    const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');

    // Providers occasionally resend the same message — dedupe by id.
    const seen = sheet.getRange(2, COL.MSG_ID, Math.max(sheet.getLastRow() - 1, 1), 1)
                      .getValues().flat();
    if (seen.indexOf(msg.id) !== -1) return json({ status: 'duplicate' });

    sheet.appendRow([new Date(), msg.id, msg.from, msg.name, msg.text, '', '', '', '']);
    return json({ status: 'logged' });
  } catch (err) {
    return json({ status: 'error', message: err.toString() });
  }
}

function extractMessage(body) {
  // Green API "incomingMessageReceived" shape. Twilio and Meta Cloud send a
  // different payload — you only rewrite THIS function to switch providers.
  if (body.typeWebhook !== 'incomingMessageReceived') return null;
  const md = body.messageData || {};
  const text = (md.textMessageData && md.textMessageData.textMessage) ||
               (md.extendedTextMessageData && md.extendedTextMessageData.text) || '';
  if (!text) return null;
  return {
    id:   body.idMessage,
    from: (body.senderData && body.senderData.sender) || '',
    name: (body.senderData && body.senderData.senderName) || '',
    text: text
  };
}

function json(obj) {
  return ContentService.createTextOutput(JSON.stringify(obj))
    .setMimeType(ContentService.MimeType.JSON);
}

The webhook only logs — it stays fast and returns a 200 immediately. Scoring happens separately, which matters because of the execution limit I'll cover in the pitfalls.

2. Score buying intent with GPT-4o-mini

Add a time-driven trigger (Triggers → Add trigger → scoreNewRows, time-driven, every minute). It finds rows that haven't been scored yet and rates each one.

function scoreNewRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
  const last = sheet.getLastRow();
  if (last < 2) return;

  const rows = sheet.getRange(2, 1, last - 1, COL.LOCKED_AT).getValues();
  rows.forEach((row, i) => {
    if (row[COL.SCORE - 1] !== '') return;         // already scored
    const { score, tier } = scoreLead(row[COL.TEXT - 1]);
    const r = i + 2;
    sheet.getRange(r, COL.SCORE).setValue(score);
    sheet.getRange(r, COL.TIER).setValue(tier);
  });
}

function scoreLead(messageText) {
  const apiKey = PropertiesService.getScriptProperties().getProperty('OPENAI_API_KEY');
  const res = UrlFetchApp.fetch('https://api.openai.com/v1/chat/completions', {
    method: 'post',
    contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + apiKey },
    muteHttpExceptions: true,
    payload: JSON.stringify({
      model: 'gpt-4o-mini',
      temperature: 0,
      response_format: { type: 'json_object' },   // forces valid JSON back
      messages: [{
        role: 'user',
        content: 'Rate this WhatsApp sales inquiry 1-100 for buying intent. ' +
                 'Reply ONLY as JSON {"score": <1-100>, "reason": "<max 8 words>"}. ' +
                 'Message: ' + messageText
      }]
    })
  });
  if (res.getResponseCode() !== 200) return { score: 0, tier: 'Unknown' };
  const out = JSON.parse(JSON.parse(res.getContentText()).choices[0].message.content);
  return { score: out.score, tier: tierFor(out.score) };
}

function tierFor(score) {
  if (score >= 80) return 'Hot';
  if (score >= 50) return 'Warm';
  if (score >= 20) return 'Cold';
  return 'Not a Lead';
}

Store your key once with PropertiesService.getScriptProperties().setProperty('OPENAI_API_KEY', 'sk-...') — never hard-code it. On gpt-4o-mini each score costs roughly $0.0001–0.001 depending on message length, so a few thousand messages a month is a couple of dollars. response_format: json_object is the part that makes parsing reliable; without it the model occasionally wraps the JSON in prose and JSON.parse throws.

3. Assign to one rep: the 15-minute lock

This is what stops the two-reps-one-lead collision. When a rep takes a lead (from a sheet button or a custom menu), claimLead writes their name and a timestamp. Another rep who tries within 15 minutes is blocked; after 15 minutes with no reply, the lock expires and the lead is fair game again.

const LOCK_TTL_MS = 15 * 60 * 1000; // 15 minutes

function claimLead(row, repName) {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Leads');
  const owner    = sheet.getRange(row, COL.OWNER).getValue();
  const lockedAt = sheet.getRange(row, COL.LOCKED_AT).getValue();

  if (owner && !lockExpired(lockedAt, new Date(), LOCK_TTL_MS)) {
    return { ok: false, owner: owner };            // still held by someone else
  }
  sheet.getRange(row, COL.OWNER).setValue(repName);
  sheet.getRange(row, COL.LOCKED_AT).setValue(new Date());
  return { ok: true, owner: repName };
}

function lockExpired(lockedAt, now, ttlMs) {
  if (!lockedAt) return true;
  return (now - new Date(lockedAt)) > ttlMs;
}

I unit-tested tierFor, extractMessage, and lockExpired before shipping this — the boundary cases (score exactly 80, a lock exactly 15 minutes old, an empty payload) are the ones that bite in production.

Pitfalls I hit (so you don't)

doPost(e) can't read HTTP headers. If you want to verify the request came from your provider, you cannot read a signature header like X-Webhook-Signature — Apps Script's event object only exposes query and form params, not headers. Pass a shared secret in the URL query string (?token=...) and check e.parameter.token instead.
The 6-minute execution limit is real. Don't call the LLM inside doPost at volume — a slow API round-trip can blow the webhook's time budget and make the provider retry. Log in the webhook, score on the time trigger (as above).
Deduplicate. Providers resend on timeout. The msgId check in step 1 stops the same lead appearing twice.
UrlFetchApp quota. ~20k calls/day on consumer Google accounts, ~100k on Workspace Business/Enterprise. One call per message is fine into the thousands/month; plan around it above that.
Concurrency ceiling. Sheets + Apps Script handle roughly 10–20 active reps and thousands of conversations/month before edits feel laggy. Past that, keep the sheet as the store and move the compute to Cloud Run.

Wrap-up

That's a working WhatsApp sales inbox in about 80 lines: every message logged, scored for intent, and locked to a single rep — on infrastructure you already pay for, at roughly the cost of the LLM calls (cents a day for a small team).

The production version — a one-click claim button, retry/backoff on the API call, multi-number routing, and a reply-from-the-sheet flow — is written up on the MageSheet blog.

Built by the MageSheet team.

Replace Expensive SaaS With Google Workspace + Apps Script (CRM, Billing, Inventory)

Hayrullah Kar — Mon, 13 Jul 2026 18:02:58 +0000

Most growing companies quietly bleed $1,000–$5,000 a month on software they barely use. It's subscription bloat — a CRM, a Zapier, a Calendly, a billing tool — each raising prices 10–30% a year, each built so you can't easily leave.

For a lot of SMBs, you can replace most of that stack with tools you already pay for: Google Workspace + Apps Script. Here's what to replace, the highest-ROI patterns, and — honestly — when not to.

Own your ecosystem

Apps Script is a serverless JavaScript runtime with direct API access to Gmail, Drive, Calendar, Docs, Forms, and Sheets. You can build a CRM, inventory system, or billing engine that runs inside your own Google account — at ~$0 marginal cost beyond the Workspace seats you already have.

The replacement index

SaaS	Typical cost	Google Workspace alternative
CRM (Salesforce, HubSpot)	$150–600/user/mo	Sheets pipeline + Apps Script
Inventory (Cin7, Fishbowl)	$100–300/mo	Event-sourced Sheets ledger
Billing (FreshBooks, Bill.com)	$50–150/mo	Calendar → Sheet → Google Doc PDFs
Scheduling (Calendly)	$8–16/user/mo	Apps Script booking page
Automation (Zapier, Make)	$50–500/mo	Native Apps Script triggers
BI (Tableau, Looker)	$70–840/mo	Sheets + BigQuery dashboard

Highest-ROI patterns

Headless CRM: lead form → auto sheet entry → lead scoring → personalized Gmail reply → calendar task → commission attribution.
Billing: calendar time-logging per client → billable-hours math → one-click invoice PDF emailed automatically.
WhatsApp sales + inventory: customer message → real-time Sheets logging → stock check → auto PDF invoice.

When NOT to do this

Keep your SaaS if your team is over ~100 (Sheets lags, Apps Script quotas bite), you're in a regulated industry (HIPAA/SOX need certified tools), you depend on deep integrations, or you have no Apps Script capacity.

Apps Script limits to know: a 6-minute execution cap, ~20k daily URL-fetch quota (100k on Business/Enterprise), and no WebSockets. SMB CRM/billing/inventory fits comfortably; high-throughput workloads graduate to Cloud Run with Sheets as the persistent data layer.

The number

A typical 10-person SMB saves $1,000–$5,000/month, amortizing the build in 12–18 months — and owns the result. No per-seat trap, no annual hike, and the data stays in your own Drive.

The full guide has the complete replacement index, the ROI patterns, and how to de-risk the "bus factor" with clasp + GitHub. Read the full guide on the MageSheet blog.

Built by the MageSheet team.

Extracting Invoices From WhatsApp Photos With AI Vision (Apps Script + Google Sheets)

Hayrullah Kar — Sun, 12 Jul 2026 10:31:27 +0000

Every logistics and field-sales team runs the same expensive process: a driver photographs a receipt into a WhatsApp group, and a back-office clerk manually types the invoice number, total, and date into a spreadsheet. Hundreds of receipts a week = transcription errors and thousands of wasted hours.

AI vision models kill that bottleneck. Here's the pipeline that turns a blurry field photo into clean structured data in seconds.

Why vision models beat traditional OCR

OCR reads characters. Modern vision models (Claude Vision, Gemini Vision, GPT-4 Vision) read structure — they distinguish a tax ID from a total, and a date from an amount, even on crumpled, angled, or poorly lit receipts. No brittle per-vendor parsers.

The pipeline (3–8 seconds end to end)

WhatsApp image → Apps Script doPost → forward to vision model
  → model returns JSON { InvoiceNumber, TotalAmount, VendorName,
                         Date, Category, confidence_score }
  → confidence routing:
       > 90  → auto-append to ledger
       70–90 → flag for human review
       < 70  → ask driver to re-photo
  → write row to Google Sheet (+ link to original image)
  → auto WhatsApp confirmation to driver

The confidence_score is the whole trick — it's what stops bad extractions from silently polluting your ledger.

Model selection (this drives your bill)

Gemini Vision — cost-efficient default, strong multilingual OCR, great on clean receipts.
Claude Vision — highest accuracy on degraded receipts; use for high-stakes flows.
GPT-4o Vision — competitive, strong structured extraction.

Pattern: Gemini for the first pass, escalate only low-confidence cases to Claude / GPT-4o.

The economics

~500 receipts/week: vision API $10–40 + WhatsApp API $30–60 + Apps Script free = ~$40–100/month. Versus a clerk at ~25 hrs/week = $2,000–4,000/month in loaded labor. Per-receipt cost: $0.005–0.02.

Accuracy: 92–97% on legible receipts, 75–85% on handwritten/damaged — hence the confidence routing.

The complete pipeline, categorization, and privacy controls are in the full guide on the MageSheet blog.

Built by the MageSheet team.

Turning WhatsApp Into a Mobile ERP for Field Logistics (Apps Script + Google Sheets)

Hayrullah Kar — Sun, 12 Jul 2026 09:32:28 +0000

Field-service software has an adoption problem: drivers won't use it. Heavy app, another login, crashes in low-signal areas. So the "real-time" data still shows up as end-of-shift phone calls.

The fix that actually sticks: stop building an app and use the one drivers already live in — WhatsApp. With Apps Script and Google Sheets behind it, WhatsApp becomes a frictionless mobile ERP. Here's the build.

WhatsApp as a data-entry terminal

A driver texts Status ABC-1234 Delivered. An Apps Script doPost webhook receives it, parses it, and updates the Sheet in real time. Latency goes from hours to milliseconds — and there's nothing to install, so adoption hits 90%+ in a week (vs. 50–70% for custom apps).

Two-stage parsing for messy input

Real drivers type "done," not clean commands. So:

Regex first pass — handles ~70% of messages (clean format) instantly and for free.
LLM fallback — the remaining ~30% goes to a cheap model (GPT-4o-mini / Gemini Flash) with the known cargo IDs and valid statuses. It returns normalized JSON + a confidence score.

Below-threshold messages surface to a dispatcher. The LLM normalizes correctly 95%+ of the time (~5% manual), and it handles multilingual input with zero extra code.

Driver msg → Apps Script doPost → regex pass
  → (fail) LLM fallback w/ confidence score
  → Sheet update (timestamp + raw-message log)
  → optional outbound (route change, POD photo request)

Why Google Sheets is the right backend

Dependent formulas: time-to-delivery, SLA-breach flags
Pivot tables for reporting
Apps Script triggers for automatic client emails
Conditional formatting dashboards
Native Calendar / Maps / Drive integration (POD photos → Drive folder)

It runs on free Google Workspace infrastructure with minimal API cost.

Bidirectional by default

The same integration pushes messages back to drivers: route changes, delivery instructions, shift reminders, exception alerts, proof-of-delivery photo requests — all in the same thread.

Pitfalls that get your number banned

The full guide details the five that matter: unofficial WhatsApp libraries (ban risk), missing opt-in workflow, no multilingual support, no raw-message audit log, and no offline/out-of-order resilience.

The complete architecture and parsing layer are in the full guide on the MageSheet blog.

Built by the MageSheet team.

Building a WhatsApp AI CRM in Google Sheets with Apps Script, Twilio & GPT-4

Hayrullah Kar — Fri, 10 Jul 2026 21:59:02 +0000

Google Sheets is a shockingly capable app backend. Pair it with Apps Script, the Twilio WhatsApp Business API, and OpenAI function calling, and you get an autonomous CRM that reads and writes your data over chat — for about $100/month instead of $600/seat. Here's the architecture, the tool design, and the one trick that cuts the AI bill 5–6×.
So we built the opposite: a WhatsApp AI CRM that lives entirely inside Google Sheets, powered by Google Apps Script, the Twilio WhatsApp Business API, and OpenAI's GPT-4. Total cost: around $100/month for 2,000–3,000 conversations. No SaaS subscription. No database migration. Your sales data stays in the spreadsheet your team already knows.

Here's the part most tutorials get wrong.

A chatbot answers. An AI agent acts.

The unlock isn't GPT generating nice text — it's OpenAI function calling (tools). When a customer says "can I book a demo Thursday?", GPT-4 doesn't just reply politely. It recognizes the intent and calls a function inside your Apps Script: it checks the calendar, writes the appointment to a Sheet, and confirms — in seconds.

That single capability turns a spreadsheet into an autonomous CRM.

The architecture that keeps it from blowing up

Naively wiring GPT straight to your data is how you get duplicate charges and corrupted rows. The pattern that works is a layered flow:

Customer → Twilio (WhatsApp API) → Apps Script doPost (webhook)
→ Context loader (reads Customers + Conversations + Orders)
→ AI brain (GPT-4 + tool library)
→ Action executor (the ONLY layer with write access)

The webhook returns 200 fast to dodge the timeout. The AI decides but never writes directly. A single action-executor layer handles all writes — with idempotency keys and an audit log — so the AI can't accidentally refund the same order twice.

The tool library is where the magic hides

You give the model three tiers of tools:

Read tools (safe, free to call): get_customer, get_orders, get_inventory, search_faq
Write tools (logged, idempotent): create_lead, update_status, book_appointment
Escalation tools (human-gated): request_human, process_refund — never auto-executed

One hard-won lesson: the description field on each tool schema matters more than the code. It's what teaches the model when to reach for each function.

The trick that cuts the AI bill 5–6×

Running every message through GPT-4 is what makes people say "AI is too expensive." The fix is model routing: a tiny GPT-4o-mini classifier (~$0.0001/message) sorts each message into FAQ / order_status / new_lead / complaint / escalation. The cheap 80% never touches GPT-4.

Result: 3,000 messages/month drops from ~$90–150 to ~$15–25.

What's in the full guide

The complete build on the MageSheet blog covers the parts I couldn't fit here: the stateless-memory problem (and the conversation-summarization fix), per-customer profile memory, the real cost breakdown vs. HubSpot, data-compliance notes (SOC 2 / GDPR), and where Apps Script's ~50–100 concurrent-conversation ceiling forces a move to Cloud Run.

Read the full guide with the complete architecture and tool schemas here:
👉 https://magesheet.com/blog/whatsapp-ai-crm-google-sheets

If you've built something similar in Sheets, I'd love to hear how you handled memory — drop a comment.

AI-Powered B2B Lead Scoring on Autopilot via Google Sheets & Gemini

Hayrullah Kar — Thu, 09 Jul 2026 16:57:16 +0000

Imagine dozens of new potential clients (leads) flooding into your B2B database or registration forms every single day. When your sales team clocks in, how do they know which prospect to call first?

Does an individual registration from j.doe@gmail.com carry the same weight on your list as a corporate procurement request from procurement@ibm.com?

Traditionally, setting up automated Lead Scoring requires bulky, rigid, rule-based CRM tools like Salesforce Pardot or Marketo that cost thousands of dollars a month. Worse yet, the keyword logic in these legacy systems is remarkably primitive. If you define a rule that says "Add 10 points if the job title includes 'Manager'", it will score an irrelevant "Office Manager" highly while completely missing high-intent enterprise buyers with titles like "Head of Operations" or "VP of Procurement."

The solution isn't sinking your engineering or ops budget into overpriced software subscriptions. Instead, you can keep the hyper-flexible Google Sheets CRM backend you already rely on and supercharge it with the semantic intent processing power of Gemini AI.

The Automation Loop

By deploying a scheduled hourly trigger via Google Apps Script, you can run a seamless, background enrichment workflow that transforms raw customer entries into structured sales data:

Semantic Profile Analysis: Instead of basic keyword matching, the AI evaluates the entire row dynamically—analyzing corporate vs. free domains, company size indicators, and professional authority levels as a cohesive profile.
0-100 Objective Scoring: The model weights the lead directly against your company's Ideal Customer Profile (ICP) and assigns a precise numerical score.
Human-Readable Explanations: To give your sales reps instant context before a call, the AI populates a concise, one-sentence justification explaining the exact reasoning behind the score.
Visual Triage: High-scoring VIP rows can be automatically color-coded in green using server-side formatting rules, instantly drawing the team's eyes to high-value targets.

The Engineering Challenge: Enforcing the Data Contract

The real engineering challenge when integrating LLMs into web-based spreadsheets is preserving your relational schema. Left to its default behavior, a text-generation model will write conversational text, introductory fluff, and conversational dialogue directly into your pristine sheet cells, breaking downstream automation.

To build a production-ready autopilot, we utilize the structured JSON output config (responseMimeType: "application/json") natively inside the Gemini API. This enforces a strict structural contract at the API level:

function scoreIncomingLeads() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Lead Pipeline");
  const data = sheet.getDataRange().getValues();
  const apiKey = "YOUR_AI_STUDIO_KEY";
  const url = `https://generativelanguage.googleapis.com/v1beta/models/gemini-pro:generateContent?key=${apiKey}`;

  for (let i = 1; i < data.length; i++) {
    const row = data[i];
    if (row[6] !== "") continue; // Skip if already scored in Column G

    const promptText = `Evaluate this B2B lead for enterprise hardware buying intent: 
    Company: ${row[1]}, Name: ${row[2]}, Email: ${row[3]}, Title: ${row[4]}, Notes: ${row[5]}.
    Respond STRICTLY in valid JSON format with two keys: "score" (0-100) and "reasoning" (1 sentence).`;

    const payload = {
      "contents": [{"parts": [{"text": promptText}]}],
      "generationConfig": {
        "responseMimeType": "application/json" // The enterprise secret weapon
      }
    };

    const options = {
      "method": "POST",
      "headers": { "Content-Type": "application/json" },
      "payload": JSON.stringify(payload)
    };

    try {
      const response = UrlFetchApp.fetch(url, options);
      const result = JSON.parse(JSON.parse(response.getContentText()).candidates[0].content.parts[0].text);

      // Safely commit exact types back to specific columns
      sheet.getRange(i + 1, 7).setValue(result.score);
      sheet.getRange(i + 1, 8).setValue(result.reasoning);

      if (result.score > 80) {
        sheet.getRange(i + 1, 1, 1, 8).setBackground("#e6f4ea"); // Highlight VIPs
      }
    } catch(e) {
      Logger.log("Execution failed: " + e.message);
    }
  }
}

Bridging the Operational Gap
By leveraging a serverless architecture with zero hosting costs, your data schema remains protected, your pipeline runs autonomously in the cloud, and your sales reps save hours of manual triage every week. When they log into the spreadsheet at 9:00 AM, they simply sort by the AI Score column—the most lucrative enterprise deals are already sitting right at the top, enriched, ranked, and ready to be closed.

Complete Blueprint & Source Code
If you want to copy the complete production scripts, check out the precise prompt structures, or explore more advanced webhook integration patterns for B2B e-commerce platforms, we have fully documented this setup.

👉 Read the Full Step-by-Step Guide on the MageSheet Blog.

Building a Scalable Inventory Web App with Google Sheets & Apps Script

Hayrullah Kar — Wed, 08 Jul 2026 19:51:13 +0000

For any growing business handling physical products, Google Sheets is the ultimate MVP database. It's free, highly flexible, and intuitive.

However, as operations scale, using a raw spreadsheet as a multi-user interface inevitably leads to data corruption and race conditions.

In a high-tempo warehouse environment where multiple employees are picking, packing, and receiving stock simultaneously, cell collisions are a mathematical certainty. A single accidental column sort can mismatch your entire SKU database, formulas get overwritten, and your system becomes highly unreliable.

The solution isn't moving to a bloated, expensive ERP. Instead, you can treat Google Sheets strictly as a backend database layer and build a hardened, asynchronous Web App frontend on top of it using Google Apps Script.

The System Architecture

By isolating your client-side UI from your database layer, you turn a simple spreadsheet into a secure corporate tool:

HTML5 Scanner Integration: The web interface leverages native browser APIs to use device cameras or USB hardware for real-time barcode scanning.
Input Validation & Sanitization: Front-end mutations prevent typical human errors (e.g., typing string values into numerical stock cells or generating negative inventory).
Immutable Audit Logging: Changes trigger server-side appends to hidden log sheets, tracking the exact timestamp, worker ID, and delta.
Mobile-First UX: Replaces massive 100-column grids with high-contrast, touch-optimized dashboards for warehouse tablets.

The Concurrency Bottleneck: Handling Simultaneous Mutations

The most critical engineering challenge in multi-user Apps Script environments is concurrency.

Imagine Worker A and Worker B both hit the "- Pick 1 Item" button for the exact same SKU at the exact same millisecond. Without a strict execution lock, both script instances read the current stock as 10, subtract 1, and write 9 back to the spreadsheet. Two physical items left the warehouse, but the database only accounted for one.

To prevent this data loss, we leverage LockService—the ultimate secret weapon for enterprise Apps Script builds. It acts as a server-side semaphore:

function adjustInventory(sku, adjustmentAmount, workerName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const inventorySheet = ss.getSheetByName("Master Inventory");

  // 1. Acquire a Script Lock to prevent race conditions
  const lock = LockService.getScriptLock();
  lock.waitLock(10000); // Wait up to 10 seconds for concurrent tasks to clear

  try {
    const data = inventorySheet.getDataRange().getValues();
    let rowIndex = -1;
    let currentStock = 0;

    // Scan array for SKU match
    for (let i = 1; i < data.length; i++) {
      if (data[i][0] === sku) {
        rowIndex = i + 1; 
        currentStock = parseInt(data[i][2]);
        break;
      }
    }


    if (rowIndex === -1) throw new Error("SKU not found");

    const newStock = currentStock + parseInt(adjustmentAmount);
    if (newStock < 0) throw new Error("Insufficient stock level");

    // Commit the locked mutation natively
    inventorySheet.getRange(rowIndex, 3).setValue(newStock);
    return { success: true, newStock: newStock };

  } catch (e) {
    return { success: false, error: e.message };
  } finally {
    // 2. Safely release the execution token
    lock.releaseLock();
  }
}

Going Serverless with Zero Hosting Cost
By deploying your custom code using the doGet() architecture and setting the execution wrapper to run as the administrator account, you create a completely serverless, decoupled application paradigm.

Your warehouse staff gets an isolated, high-speed interface, while your data schema remains protected in the cloud without spending a dime on hosting infrastructure or database compute cycles.

Complete Blueprint & Patterns
If you want to dive deeper into the complete asynchronous update patterns, boilerplate client-side google.script.run implementations, and production-ready relational schemas, we have fully documented the entire system.

👉 Read the Full Step-by-Step Guide on the MageSheet Blog.