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

A developer built a WhatsApp sales inbox in Google Sheets using Apps Script and GPT-4o-mini, solving the problem of multiple sales reps chasing the same lead. The system logs messages, scores buying intent via AI, and locks leads to one rep for 15 minutes. The entire solution runs on about 80 lines of Apps Script code.

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. 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. php 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 }; 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. js 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. 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. js 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. 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. js 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. doPost e can't read HTTP headers. 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. 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 . msgId check in step 1 stops the same lead appearing twice.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 https://magesheet.com/blog/automating-whatsapp-sales-google-sheets . Built by the MageSheet team.