How to Integrate WhatsApp Business API with Google Sheets (Free, Step-by-Step)

Most Indian SMBs don't need a server. They need someone to add a phone number, a name, and a note in a Google Sheet — and have WhatsApp messages go out, and replies come back, automatically.

That's what this tutorial builds: a complete WhatsApp Business API ⇄ Google Sheets integration using only Google Apps Script, which runs free on Google's infrastructure. No VPS, no domain, no Heroku free-tier funeral. By the end you'll have:

1. A SEND BROADCAST button in a sheet that sends an approved WhatsApp template to every row
2. A webhook (/exec URL) that lands every inbound WhatsApp message and delivery status back into sheet rows
3. A mini status machine: queued → sent → delivered → read / replied
4. Cost tracking per send, using Meta's per-message pricing

Total infrastructure cost: ₹0/month (you only pay Meta's per-message rates — ~₹0.86 per marketing message, ~₹0.115 per utility message in India, plus GST).

Honest scope note: Apps Script is perfect for lists up to a few thousand rows and moderate reply volume. Its execution quota (6–90 min/day) and 6-minute per-trigger limit make it wrong for 100k-message campaigns — for that, you outgrow Sheets and move to the Python broadcast engine I cover in another article. But for clinics, coaching institutes, real-estate teams, and early D2C brands, this is a real CRM.

Why Apps Script is secretly a backend

Apps Script gives you the three things a backend needs:

• Outbound HTTP: UrlFetchApp.fetch() → call the Graph API
• Inbound HTTP: deploy as a Web App → any /exec URL becomes a public HTTPS endpoint that Meta can webhook into
• Persistence: the spreadsheet itself is your database

You also get the SpreadsheetUI for free — your non-technical coworker already knows how to use it.

Step 1: Set up the Cloud API (10 minutes)

1. Create a Meta Business Portfolio, add a WhatsApp Business Account, and register a phone number in WhatsApp Manager.
2. Grab your Phone Number ID and a permanent access token: Business Settings → Users → System Users → create one (Admin) → Generate token → scopes whatsapp_business_messaging, whatsapp_business_management. Never paste a personal 24-hour token into a sheet — broadcasts will silently die tomorrow.
3. Create and get one template approved (Utility category, e.g. appointment_reminder) in WhatsApp Manager. Until Meta approves it (usually hours, sometimes a day), you can only reply to customers who message you first.

Store the three constants at the top of the script later. Note your phone number ID and token.

Step 2: The sheet

Create a Google Sheet with three tabs:

Contacts
| A: phone | B: name | C: template_var | D: status | E: wa_id | F: last_update |

Inbox (append-only)
| A: timestamp | B: from | C: name | D: message | E: direction |

Log
| A: timestamp | B: wa_id | C: event | D: detail |

Phone numbers in international format without +: 919876543210.

Step 3: Sending — the broadcast function

Extensions → Apps Script, and start with the send side:

// ===== CONFIG =====
const TOKEN      = 'EAAG...';            // permanent system-user token
const PHONE_ID   = '123456789012345';    // Cloud API phone number id
const API        = 'https://graph.facebook.com/v23.0';
const VERIFY_TOK = 'any-random-string';  // for the webhook handshake

function sendBroadcast() {
  const sh = SpreadsheetApp.getActive()
    .getSheetByName('Contacts');
  const data = sh.getDataRange().getDisplayValues(); // row 0 = header
  const sent = [];

  for (let i = 1; i < data.length; i++) {
    const [phone, name, tvar, status] = data[i];
    if (!phone || status !== 'queued') continue;      // idempotent: resumable

    const payload = {
      messaging_product: 'whatsapp',
      to: phone,
      type: 'template',
      template: {
        name: 'appointment_reminder',
        language: { code: 'en_IN' },
        components: [{
          type: 'body',
          parameters: [{ type: 'text', text: name || 'there' },
                       { type: 'text', text: tvar || '' }]
        }]
      }
    };

    try {
      const res = UrlFetchApp.fetch(`${API}/${PHONE_ID}/messages`, {
        method: 'post', contentType: 'application/json',
        headers: { Authorization: 'Bearer ' + TOKEN },
        payload: JSON.stringify(payload),
        muteHttpExceptions: true             // don't abort the loop on 4xx
      });
      const body = JSON.parse(res.getContentText());
      const row = i + 1;                    // sheet rows are 1-based

      if (res.getResponseCode() === 200) {
        sh.getRange(row, 4).setValue('sent');
        sh.getRange(row, 5).setValue(body.messages[0].id);  // wa_id
        sent.push(phone);
      } else {
        // permanent vs retryable, same logic as any API client
        sh.getRange(row, 4).setValue('failed: ' +
          (body.error && body.error.message || res.getResponseCode()));
      }
      sh.getRange(row, 6).setValue(new Date());
      Utilities.sleep(120);   // ~8 msg/s: gentle on the entry messaging tier
    } catch (e) {
      sh.getRange(i + 1, 4).setValue('error: ' + e.message);
    }
  }
  SpreadsheetApp.flush();
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(),
    'Broadcast done: ' + sent.length + ' sent',
    'Ran at ' + new Date());
}

// menu button
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('WhatsApp')
    .addItem('Send queued broadcasts', 'sendBroadcast')
    .addItem('Process webhook queue', 'processQueue')
    .addToUi();
}

Three details that separate this from a beginner snippet:

• status !== 'queued' skip: if the script times out mid-run at 4,000 rows, re-running the button resumes instead of re-sending. Status column = your crash recovery.
• Utilities.sleep(120): the entry messaging tier is ~80 template messages/sec, but new numbers have soft limits and error-based throttling — one 429 in a tight loop can pause the number for a while. 120ms is a boring, safe default.
• wa_id stored per row: this is the join key for delivery statuses coming back from webhooks.

Link the custom function to a button (onOpen above) and hit Send queued broadcasts for a 3-row test list. You should see green sent values and actual messages land on test phones.

Step 4: Receiving — the webhook

Now the part that makes it a two-way system. Meta needs to verify your endpoint first. Add:

// GET → Meta's subscription handshake
function doGet(e) {
  if (e.parameter['hub.mode'] === 'subscribe' &&
      e.parameter['hub.verify_token'] === VERIFY_TOK) {
    return ContentService
      .createTextOutput(e.parameter['hub.challenge']);
  }
  return ContentService.createTextOutput('forbidden');
}

// POST → inbound messages + delivery statuses
function doPost(e) {
  const lock = LockService.getScriptLock();
  lock.tryLock(3000);                       // Apps Script can fire concurrently
  try {
    const body = JSON.parse(e.postData.contents);
    const value = body.entry?.[0]?.changes?.[0]?.value || {};
    const out = [];

    // real customer messages
    for (const m of (value.messages || [])) {
      out.push([new Date(), m.from,
                value.contacts?.[0]?.profile?.name || '',
                m.text?.body || m.type, 'in']);
    }
    // delivery receipts: sent/delivered/read/failed
    for (const s of (value.statuses || [])) {
      out.push([new Date(), s.recipient_id || '', s.id,
                'status:' + s.status, 'status']);
    }
    if (out.length) {
      const sheet = SpreadsheetApp.getActive().getSheetByName('Inbox');
      sheet.getRange(sheet.getLastRow() + 1, 1, out.length, 5)
           .setValues(out);
    }
  } finally {
    lock.releaseLock();
  }
  return ContentService.createTextOutput('{}');  // must return 200 fast
}

Deploy → New deployment → type Web app → execute as Me, access Anyone → copy the /exec URL. Then in Meta's app dashboard: WhatsApp → Configuration → Webhook = your /exec URL, verify token = VERIFY_TOK. Subscribe to messages.

Gotcha that eats beginners: always respond to Meta's POST within a few seconds with HTTP 200. Meta retries failed webhooks but rate-limits chatty endpoints — hence the lock + minimal work inside doPost.

Updating broadcast status from receipts

Delivery statuses arrive referencing wa_id. Map them back to rows in processQueue() (bound to the second menu item — or add a time-based trigger every 5 min):

function processQueue() {
  const inbox = SpreadsheetApp.getActive().getSheetByName('Inbox');
  const contacts = SpreadsheetApp.getActive().getSheetByName('Contacts');
  const inData = inbox.getRange(2, 1, inbox.getLastRow() - 1, 5)
                      .getValues();
  const idx = {};   // wa_id → row number on Contacts
  const cData = contacts.getDataRange().getValues();
  cData.forEach((r, i) => { if (r[4]) idx[r[4]] = i + 1; });

  for (const [ts, from, ref, event, dir] of inData) {
    if (dir !== 'status' || !ref) continue;
    const row = idx[ref];
    if (row) contacts.getRange(row, 4)
      .setValue(event.replace('status:', ''));
  }
}

Run it on a trigger (Edit or a time-driven one). Your Contacts sheet now self-updates delivered/read without anyone touching it. That's your campaign dashboard — filter the status column and COUNTIF it.

Step 5: Auto-replies the sheet-owner actually wants

Two free (service-category) superpowers now that inbound works. A clinic's "what are your timings?" auto-reply, entirely in doPost:

const AUTO = {
  'timing':  'We are open Mon–Sat, 9am–8pm. 🕐',
  'price':   'Consultation ₹500. Book here: https://cal.com/your-clinic',
  'address': '2nd Floor, Apex Tower, Civil Lines, Nagpur.'
};

function maybeAutoReply(m) {
  const text = (m.text?.body || '').toLowerCase();
  const hit = Object.keys(AUTO).find(k => text.includes(k));
  if (!hit) return;
  UrlFetchApp.fetch(`${API}/${PHONE_ID}/messages`, {
    method: 'post', contentType: 'application/json',
    headers: { Authorization: 'Bearer ' + TOKEN },
    payload: JSON.stringify({
      messaging_product: 'whatsapp',
      to: m.from,
      type: 'text',
      text: { body: AUTO[hit] }
    }),
    muteHttpExceptions: true
  });
}

Free-form replies like this are only allowed inside the 24-hour customer service window (opened by the customer's message — so within it by definition) and they cost ₹0. Keyword coverage for your top 5 FAQs recovers hours a week for a one-person business.

Step 6: Cost tracking — the sheet nobody shows you

Add a Cost column and log the category per send:

const RATES = { marketing: 0.86, utility: 0.115, auth: 0.115 }; // ₹, mid-2026 — verify on Meta's pricing page
function logCost(waId, category) {
  SpreadsheetApp.getActive().getSheetByName('Log')
    .appendRow([new Date(), waId, 'cost', RATES[category]]);
}

At month end, =SUM(Log!D:D) * 1.18 approximates your Meta bill with 18% GST, and =COUNTIF(Contacts!D:D,"read") gives you read-based cost effectiveness. Reconcile against Meta's actual billing UI before trusting it — Apps Script's clock and Meta's delivery timing won't be second-exact, and Meta only bills delivered templates.

Where this architecture breaks (know your ceiling)

• Quota: ~90 min/day execution on consumer Google accounts, 6 min per execution on Workspace. A 20k-row broadcast with 120ms spacing = 40 min + overhead per run; split with chunked ranges or accept multi-day sends.
• No realtime inbox: statuses land on a trigger cadence, not a stream. Fine for a clinic; wrong for a support team.
• One sheet, one number. Multi-brand = multi-deployment; manageable but manual.
• Security: your /exec URL is unauthenticated on the POST side (Meta doesn't sign payloads; you can validate a token Meta sends since 2024 — check current docs). Keep the sheet itself access-restricted; treat the token like a password.

When you hit these ceilings, the migration path is mechanical: the payload shapes are identical, so the JavaScript above ports line-for-line into the Python service — I cover that server-side version in my broadcast-engine article.

What you just built for ₹0/month

• ✅ Template broadcasts from a spreadsheet with crash-safe resumption
• ✅ Delivery/read receipts flowing back automatically
• ✅ Inbound message logging = a shared CRM your team can open on their phones
• ✅ Keyword auto-replies that cost nothing
• ✅ Per-message cost ledger

For a coaching institute filling batch enquiries, a physio clinic sending appointment reminders, or a D2C brand at 500 orders/month, this replaces a ₹2,000–5,000/month SaaS with something you own and can reason about. If you sell automation as a service, this is the easiest ₹15–25k setup project you'll close this month — and the natural upsell is the Python version when they outgrow the sheet.

More WhatsApp-for-SMB builds on my profile: raw API broadcasts at scale, BSP platform comparisons, and the COD-confirmation bot that pays for itself in a week.