Send WhatsApp Order Updates From Google Sheets With 80 Lines of Apps Script

Plenty of small businesses run their orders from a Google Sheet. When an order ships, someone copies the customer's number into WhatsApp and types "your order has shipped". This post replaces that with about 80 lines of Google Apps Script: mark a row "Shipped", and within five minutes the customer gets an approved WhatsApp template with their name and order number. It runs entirely in the Sheet, with no server, no Zapier and no npm.

It uses WAPGROWAI's public API, but the parts that need care are the same with any WhatsApp API: not sending twice, handling rate limits, and knowing when a failed call might still have gone out. The script was tested in Node against fake Sheets and HTTP services, covering each case below, so the tests never sent a real message.

What you need

• A Google Sheet with a tab named Orders and these columns: Phone, Name, Order, Status, Sent at, Result.
• An approved template, here called order_shipped, whose body has two variables: Hi {{1}}, your order {{2}} has shipped. Templates are what WhatsApp allows outside the 24-hour customer service window, which is when most order updates go out.
• An API token with the messages:send scope. In WAPGROWAI that's Connectors → API → Create API Token. The token starts with wa_live_ and is shown once.

Format the Phone column as plain text, and write numbers with the country code: 919820011223 or [phone].

The script

Open Extensions → Apps Script, replace the contents of Code.gs with this, and save:

// Sends a WhatsApp template for every order marked "Shipped" that hasn't had one yet.
// Sheet "Orders": A Phone | B Name | C Order | D Status | E Sent at | F Result
const API_BASE = "https://api.wpautoai.com/api/public";
const SHEET_NAME = "Orders";
const TEMPLATE_NAME = "order_shipped"; // body: "Hi {{1}}, your order {{2}} has shipped."
const SEND_WHEN_STATUS = "Shipped";
const MAX_PER_RUN = 50; // stays well inside the 6-minute run limit
const COL = { phone: 0, name: 1, order: 2, status: 3, sentAt: 4, result: 5 };

function setup() {
  // One-time: store the token outside the sheet, and run every 5 minutes.
  PropertiesService.getScriptProperties().setProperty("WAPGROWAI_TOKEN", "wa_live_paste_your_token_here");
  ScriptApp.newTrigger("sendShippedUpdates").timeBased().everyMinutes(5).create();
}

function sendShippedUpdates() {
  const token = PropertiesService.getScriptProperties().getProperty("WAPGROWAI_TOKEN");
  if (!token || !token.startsWith("wa_live_")) throw new Error("Set WAPGROWAI_TOKEN in Script properties first.");

  // A manual run and the timer must never work on the same rows at once.
  const lock = LockService.getScriptLock();
  if (!lock.tryLock(5000)) return;
  try {
    const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
    const rows = sheet.getDataRange().getValues();
    let sent = 0;

    for (let i = 1; i < rows.length && sent < MAX_PER_RUN; i++) {
      const row = rows[i];
      if (row[COL.status] !== SEND_WHEN_STATUS || row[COL.sentAt] !== "") continue;

      // Claim the row before calling the API: if the run dies mid-call,
      // the row isn't sent again blindly on the next run.
      writeResult(sheet, i, new Date(), "sending…");
      const outcome = sendTemplate(token, row);

      if (outcome.retryLater) {
        writeResult(sheet, i, "", ""); // nothing was sent; the next run picks it up
        break;
      }
      writeResult(sheet, i, new Date(), outcome.note);
      sent++;
    }
  } finally {
    lock.releaseLock();
  }
}

function sendTemplate(token, row) {
  const response = UrlFetchApp.fetch(API_BASE + "/messages/send", {
    method: "post",
    contentType: "application/json",
    headers: { Authorization: "Bearer " + token },
    payload: JSON.stringify({
      type: "template",
      // A number cell arrives as a number; the API wants digits with an optional +.
      phone: String(row[COL.phone]).replace(/[^\d+]/g, ""),
      template_name: TEMPLATE_NAME,
      parameters: { body: [String(row[COL.name]), String(row[COL.order])] },
    }),
    muteHttpExceptions: true, // read the error body instead of throwing
  });

  const status = response.getResponseCode();
  let body = {};
  try {
    body = JSON.parse(response.getContentText());
  } catch (e) {
    body = {};
  }

  if (status === 200 || status === 201) return { ok: true, note: "sent: " + body.data.id };
  if (status === 429) return { retryLater: true };
  // 4xx: refused before or by WhatsApp; nothing was delivered. Fix the row, then clear Sent at to retry.
  if (status < 500) return { ok: false, note: "error " + status + ": " + (body.message || "request refused") };
  // 5xx or a timeout: it may or may not have gone out. A person decides.
  return { ok: false, note: "check manually: server error " + status };
}

function writeResult(sheet, rowIndex, sentAt, note) {
  sheet.getRange(rowIndex + 1, COL.sentAt + 1, 1, 2).setValues([[sentAt, note]]);
}

Then:

1. Put your token in setup, select setup in the toolbar, and press Run once. Google asks you to authorise the script to call external services. After that, remove the token from the code: it now lives in Project Settings → Script properties, not in the sheet or the script.
2. Mark a row "Shipped". Within five minutes the Result column says sent: <message id>.

Why it's built this way

A timer, not onEdit

The obvious trigger is onEdit: send the moment someone types "Shipped". But simple triggers can't use services that require authorisation, and calling an external API does. A time-driven trigger every five minutes avoids that, sends rows in batches, and handles several orders marked at once. everyMinutes accepts 1, 5, 10, 15 or 30.

Claim the row before the call

The script writes Sent at and sending… before calling the API. If the run dies halfway (Apps Script stops any run at six minutes), the row is already marked, and the next run skips it instead of sending again. A row stuck on sending… is a signal for a person to check, which is the right outcome: the customer may already have the message.

That's also why MAX_PER_RUN exists. Fifty sends finish well inside six minutes, and the next run picks up the rest.

A lock against overlapping runs

If someone runs the function by hand while the timer is running it, both could read the same "Shipped" rows. LockService.getScriptLock() with tryLock makes the second run give up straight away instead of sending duplicates.

Read the status code, not an exception

muteHttpExceptions: true makes UrlFetchApp.fetch return 4xx and 5xx responses instead of throwing, so the script can decide what each one means:

Response	What it means	What the script does
200	Sent	Writes sent: and the message ID
400	Refused: a bad number, a template that isn't approved, the wrong number of variables, or WhatsApp's own refusal	Writes the API's message; clear Sent at to retry after fixing the row
429	Rate limit reached	Un-claims the row and stops; the next run sends it
5xx or no response	Unknown: it may or may not have gone out	Writes check manually and never retries on its own

The last row is the one most scripts get wrong. A retry after a server error can send a second message to a customer who already got the first. When you can't know, ask a person.

Phone numbers from a sheet

A number cell comes back from getValues() as a number, and people type spaces and dashes. The script turns the value into a string and keeps only digits and a leading +. A number without a country code is read as Indian by this API, so write the country code for anyone else.

The token stays out of the sheet

Anyone who can open the sheet can read its cells, and the sheet gets shared. Script properties aren't visible from the sheet. Give the token only the messages:send scope, so a leaked token can't read your contacts.

What the tests check

The tests load Code.gs into a Node vm context with fake SpreadsheetApp, UrlFetchApp, LockService and PropertiesService, so they exercise the real script without sending anything:

• Selection: only rows with Status "Shipped" and an empty Sent at are sent; the request has the right URL, bearer token, template name and { body: [name, order] } parameters.
• Idempotency: a second run sends nothing.
• Phone cleanup: [phone] is sent as [phone].
• Refusals: a 400 records the API's message and isn't retried automatically.
• Rate limits: a 429 leaves the row unclaimed and stops the run; the next run sends it and the row after it.
• Server errors: a 502 is marked check manually and never resent.
• Safety: no token means no sends; a held lock means no sends.
• Batching: a 60-row backlog goes out as 50, then 10.

Going further

• More statuses, more templates. Map "Out for delivery" and "Delivered" to their own templates with a small lookup object.
• Delivery status. The send response includes a message ID. A webhook from your WhatsApp platform can update a Delivered or Read column against it.
• Volume. Apps Script allows 20,000 URL fetches a day on a personal account and 100,000 on Google Workspace. That's plenty for order updates; for bulk sends, use a campaign instead of a sheet.

---

WAPGROWAI is built by DI Solutions, a custom software and AI development company. This series covers what we've learned building WAPGROWAI on the WhatsApp Cloud API. Related: designing a public API for a WhatsApp platform and phone numbers will break your WhatsApp integration.