Send WhatsApp from Google Sheets with Apps Script

The list already lives in a spreadsheet: this week's fee reminders, tomorrow's class roster, the customers whose orders are ready. Someone copies each number into WhatsApp, pastes a message, changes the name, and repeats forty times. It is slow, it is error-prone, and nobody can tell afterwards who actually got the message.

You can send WhatsApp from Google Sheets with about 80 lines of Apps Script: one message per row, a status column the script fills in, and a guard so a rerun never messages anyone twice. No add-on to install and no server to run.

This guide builds that script against an API, explains the Apps Script limits that shape it, and covers the opt-in rules that matter more than the code.

Pick the API first, because it changes the script

A spreadsheet can't send WhatsApp messages by itself. It calls an API, and which API you call decides what each row can contain.

For a spreadsheet of reminders to people who already know you, the second column is usually simpler: the message in column C is what goes out. If you need a green tick, OTPs or thousands of sends an hour, use the Cloud API version in the last code section. The complete guide to the WhatsApp API covers the platform side in detail.

Lay out the sheet

Create a tab called Send with these columns:

Format column A as plain text (Format, Number, Plain text) before pasting numbers. If you don't, Sheets treats +919811122233 as a number, drops the plus and may show it in scientific notation. Numbers without a country code are a frequent reason a row fails.

The {name} placeholder is not just politeness. Tenashi pauses a send once the exact same text has gone to 5 chats within 10 minutes, returning 422 send_blocked, because identical blasts are what get numbers banned. A name in every message keeps each text unique.

The Apps Script

Open Extensions, Apps Script, replace the contents of Code.gs with the script below and save. Then open Project Settings and add a script property called TENASHI_KEY holding your tk_live_... key. Script properties keep the key out of the code and out of the sheet, where anyone with view access could read it.

// Code.gs: send one WhatsApp message per row through Tenashi's API
const SHEET = 'Send';
const COL = { phone: 0, name: 1, text: 2, optIn: 3, status: 4, id: 5, sentAt: 6 };
const PER_RUN = 40; // rows per run, far inside the 6-minute execution limit
const API = 'https://usetenashi.com/api/v1';

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('WhatsApp')
    .addItem('Send pending rows', 'sendPending')
    .addItem('Refresh delivery status', 'refreshStatuses')
    .addToUi();
}

function apiKey_() {
  const key = PropertiesService.getScriptProperties().getProperty('TENASHI_KEY');
  if (!key) throw new Error('Add TENASHI_KEY under Project Settings, Script properties');
  return key;
}

function sendPending() {
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return; // a previous run is still going
  try {
    const key = apiKey_();
    const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET);
    const rows = sheet.getDataRange().getValues();
    let sent = 0;

    for (let r = 1; r < rows.length && sent < PER_RUN; r++) {
      const row = rows[r];
      const phone = String(row[COL.phone]).trim();
      if (row[COL.status] || !phone) continue; // already handled, or blank

      if (row[COL.optIn] !== true) { // a checkbox column: unticked means no message
        sheet.getRange(r + 1, COL.status + 1).setValue('skipped: no opt-in');
        continue;
      }

      const text = String(row[COL.text]).replace(/\{name\}/g, row[COL.name] || 'there');
      const res = UrlFetchApp.fetch(API + '/messages', {
        method: 'post',
        contentType: 'application/json',
        headers: {
          Authorization: 'Bearer ' + key,
          // Same row + same phone = same key, so a rerun never sends twice within 24 hours.
          'Idempotency-Key': sheet.getSheetId() + '-' + (r + 1) + '-' + phone,
        },
        payload: JSON.stringify({ to: phone, text: text }),
        muteHttpExceptions: true,
      });

      const code = res.getResponseCode();
      const body = JSON.parse(res.getContentText() || '{}');
      if (code === 202) {
        sheet.getRange(r + 1, COL.status + 1, 1, 3).setValues([['queued', body.message.id, new Date()]]);
        sent++;
      } else if (code === 429) {
        break; // rate_limited or new_chat_limit: leave the row blank and stop until the next run
      } else {
        sheet.getRange(r + 1, COL.status + 1).setValue('error: ' + (body.error ? body.error.code : code));
      }
    }
  } finally {
    lock.releaseLock();
  }
}

function refreshStatuses() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET);
  const rows = sheet.getDataRange().getValues();
  const open = [];
  for (let r = 1; r < rows.length && open.length < 100; r++) {
    if (rows[r][COL.id] && ['queued', 'sent', 'delivered'].includes(rows[r][COL.status])) {
      open.push({ row: r + 1, id: String(rows[r][COL.id]) });
    }
  }
  if (!open.length) return;

  const key = apiKey_();
  const responses = UrlFetchApp.fetchAll(open.map((o) => ({
    url: API + '/messages/' + encodeURIComponent(o.id),
    headers: { Authorization: 'Bearer ' + key },
    muteHttpExceptions: true,
  })));
  responses.forEach((res, i) => {
    if (res.getResponseCode() !== 200) return;
    const message = JSON.parse(res.getContentText()).message;
    sheet.getRange(open[i].row, COL.status + 1).setValue(message.status); // sent, delivered, read, failed
  });
}

function installTriggers() {
  ScriptApp.newTrigger('sendPending').timeBased().everyMinutes(15).create();
  ScriptApp.newTrigger('refreshStatuses').timeBased().everyMinutes(30).create();
}

Reload the spreadsheet and a WhatsApp menu appears. The first run asks you to authorise the script to edit the sheet and connect to an external service.

Why the script is shaped like this

Every choice above traces back to a limit, either in Apps Script or in WhatsApp.

Forty rows per run. An Apps Script execution stops after 6 minutes, and triggers on a personal Google account get 90 minutes of total runtime a day (6 hours on Google Workspace), per Google's quotas page. Small batches finish well inside both, and because the status is written row by row, a run that does get cut off loses nothing. URL Fetch calls are capped too, at 20,000 a day on personal accounts and 100,000 on Workspace.

No sleep between sends. With Tenashi, each request returns 202 immediately with the message queued, and the number sends one message every 4 to 12 seconds on its own. Forty rows take well under a minute to submit and around four minutes to actually go out (a batch of 100 takes about 10). Pausing in the script would only burn your trigger runtime.

Stop on 429. Two limits come back as 429. rate_limited means more than 120 API requests in a minute from one key. new_chat_limit means the number has started 50 new chats through the API today, counting only people who have never written to you. Either way the right move is to stop and let the next trigger continue, so the script leaves the row blank and breaks.

The lock and the idempotency key. If a run is still going when the next trigger fires, LockService makes the second one exit. The Idempotency-Key covers the other failure, a run that sent the request but died before writing the status: a repeat within 24 hours returns the original response and sends nothing.

A separate status refresh. Apps Script web apps can't read request headers, so they can't verify a webhook signature, and their responses come back through a redirect, which Tenashi counts as a failed delivery. Polling GET /messages/:id for open rows sidesteps both. Capping each refresh at 100 rows keeps it under the per-key request limit.

Run installTriggers once from the editor to send every 15 minutes and refresh every 30. Or skip it and use the menu when you want control over exactly when a batch goes.

The Cloud API version: templates per row

On Meta's Cloud API, a business-initiated message has to be an approved template unless the person messaged you within the last 24 hours. So the spreadsheet holds template variables, not free text:

// Cloud API variant: business-initiated messages must use an approved template
function sendTemplateRow_(phone, name, invoiceNo) {
  const props = PropertiesService.getScriptProperties();
  const res = UrlFetchApp.fetch(
    'https://graph.facebook.com/v23.0/' + props.getProperty('PHONE_NUMBER_ID') + '/messages',
    {
      method: 'post',
      contentType: 'application/json',
      headers: { Authorization: 'Bearer ' + props.getProperty('WHATSAPP_TOKEN') },
      payload: JSON.stringify({
        messaging_product: 'whatsapp',
        to: phone,
        type: 'template',
        template: {
          name: 'payment_due',            // an approved utility template
          language: { code: 'en' },
          components: [{
            type: 'body',
            parameters: [{ type: 'text', text: name }, { type: 'text', text: invoiceNo }],
          }],
        },
      }),
      muteHttpExceptions: true,
    }
  );
  const body = JSON.parse(res.getContentText());
  if (res.getResponseCode() !== 200) {
    return { status: 'error: ' + body.error.code }; // 132001 = template/language mismatch
  }
  return { status: 'accepted', id: body.messages[0].id }; // delivery only arrives by webhook
}

Call it from the same loop in place of the Tenashi fetch, and use Graph API v23.0 or whatever version Meta's changelog currently lists. Three differences matter in practice.

First, accepted is as far as the sheet can see. Cloud API delivery status only arrives through webhooks, and for the same reasons as above Apps Script is a poor webhook receiver, so you need a small server that verifies Meta's signature and writes back through the Sheets API.

Second, messaging limits. A new business portfolio can reach 250 unique users outside the customer service window per 24 hours, and the tier rises as you scale. A 1,000-row sheet will start failing partway through. The messaging limits guide explains the tiers.

Third, category and cost. A payment reminder is a utility template; anything promotional in it can get it recategorised as marketing, which costs more. See WhatsApp template categories before you submit one, and our walkthrough of WhatsApp payment reminders through the API for wording that stays in the utility category.

Opt-in: the column that protects your number

The Opted in checkbox is in the script for a reason. Meta's WhatsApp Business Messaging Policy requires opt-in permission from people before you message them through the platform. The same principle protects your own number on Tenashi: people who didn't expect your message block or report it, and enough of that gets a number banned. Ban protection lowers the risk; it can't overrule recipients.

Practical rules for the sheet:

• Only tick the box for people who gave you their number for this purpose, and note where and when in a spare column.
• Keep the first message obviously from you, with a reason they will recognise.
• Respect replies like "stop" by unticking the box and leaving the row for the record.

The WhatsApp API opt-in policy guide covers what counts as valid consent, and building a WhatsApp opt-in list shows ways to collect it.

Where Tenashi fits

A spreadsheet sender works until the replies start. Then someone has to read forty conversations on one phone, and the sheet knows nothing about who answered what.

Tenashi links your existing WhatsApp or WhatsApp Business number by QR scan and puts every chat in a shared team inbox. Messages the script sends appear there like any other reply, so whoever picks up a conversation sees the reminder that started it, with contact details, deals and follow-ups under each chat. Sends are paced and new chats are capped to protect the number, which also means it is not a tool for blasting thousands of messages an hour, and WhatsApp can still ban numbers that spam. Plans start from $18/mo; broadcasts and automations are on Growth and Scale.

Read the API docs · Get started

FAQ

Can Google Sheets send WhatsApp messages without an API?

Not automatically. Sheets can generate click-to-chat links (https://wa.me/ followed by the number and a prefilled text), but a person still has to open each link and press send. Sending by itself needs an API that the script calls, either Meta's Cloud API or a service such as Tenashi that sends from your own number.

How many WhatsApp messages can I send from a sheet per day?

It depends on the API. Apps Script allows 20,000 URL fetches a day on a personal account. On the Cloud API, your messaging limit starts at 250 unique users per 24 hours outside the service window. On Tenashi, each number can start 50 new chats a day through the API, with replies to existing chats not counted.

Why does a row say error: invalid_phone?

The number is missing its country code or contains something that isn't a phone number. Sheets often strips the leading plus when column A is formatted as a number. Set the column to plain text, enter numbers like +919811122233, clear the status cell and run the script again.

Can I send an image or PDF from the sheet?

Yes. With Tenashi, add a media object with a public, direct file URL to the payload, such as a link from your own site or storage bucket; a normal Drive sharing link opens a web page, not the file. Images can be up to 5 MB and PDFs up to 25 MB. The text in column C becomes the caption.

Will the script send twice if I run it twice?

No. Rows with anything in the Status column are skipped, the script lock stops overlapping runs, and each request carries an idempotency key built from the row and phone number. If a run dies after sending but before writing the status, the repeat within 24 hours returns the first response without sending again.

---

A sheet, a script and a status column turn a forty-minute copy-paste job into a menu click. When the replies need a team behind them, get started with Tenashi and let the sheet keep feeding the inbox.