Log WhatsApp messages to Google Sheets and send from a sheet

How does each WhatsApp message become a row?

Meta posts every message on the number to the webhook destination you set, here an n8n Webhook node that also answers Meta's verification request. The next node is Google Sheets with the Append Row operation, mapping fields from the payload into columns: the sender's number, the profile name, the text, the WhatsApp message id, the timestamp and the receiving phone number id.

Use Append or Update Row keyed on the message id instead of a plain append if you want retries to be harmless. Meta redelivers a message when it thinks a delivery failed, and keying on the id turns the second delivery into an update of the same row.

On a Coexistence number, replies typed in the WhatsApp Business app arrive at the same webhook as smb_message_echoes events. Log them with a direction column and the sheet holds both sides.

Where does the Sheets quota bite?

On writes per user. The Sheets API allows 300 write requests per minute per project and 60 per minute per user per project, and an n8n credential is one user. A workflow that appends one row per message can therefore take about one message a second, sustained.

That is plenty for most shop numbers. It stops being plenty after a broadcast template, when a few hundred customers reply within minutes. The API then answers 429 Too Many Requests and, without retries, rows go missing.

Two fixes, either of which is enough. Turn on retries with a wait on the Google Sheets node, which matches Google's own advice to back off exponentially. Or batch: have n8n collect messages for thirty seconds and append them in one request, which turns many writes into one.

How do you send templates from a sheet?

With Apps Script, from the sheet's Extensions menu. A function reads the rows that have not been sent, calls the Cloud API messages endpoint with UrlFetchApp for each, and writes the returned message id and the time into the row. A time-driven trigger can run it every few minutes, or you can run it by hand from a custom menu.

Store the access token in the script properties rather than in the code, so anyone who can view the script does not see it.

Keep the quotas in mind. A consumer Google account gets 20,000 URL Fetch calls a day and a Workspace account 100,000, and each execution stops after six minutes. Process the rows in chunks and mark each as sent before moving on, so a stopped run resumes where it left off rather than sending twice.

function sendPending() {
  const token = PropertiesService.getScriptProperties().getProperty("WHATSAPP_TOKEN");
  const sheet = SpreadsheetApp.getActive().getSheetByName("Send");
  const rows = sheet.getDataRange().getValues();
  for (let i = 1; i < rows.length; i++) {
    const [phone, name, sentAt] = rows[i];
    if (sentAt) continue;
    const res = UrlFetchApp.fetch("https://graph.facebook.com/v23.0/<PHONE_NUMBER_ID>/messages", {
      method: "post",
      contentType: "application/json",
      headers: { Authorization: "Bearer " + token },
      payload: JSON.stringify({
        messaging_product: "whatsapp", to: String(phone), type: "template",
        template: { name: "order_ready", language: { code: "en" },
          components: [{ type: "body", parameters: [{ type: "text", text: name }] }] },
      }),
    });
    sheet.getRange(i + 1, 3).setValue(new Date());
  }
}

Common mistakes

• Appending without a key on the message id. Meta's retries then write the same message into two rows.
• Writing one row per message through a single credential during a reply burst. The 60 writes per minute per user limit returns 429 and rows are lost.
• Sending free text from the sheet to people who have not written in 24 hours. Only an approved template delivers outside that window.

Frequently asked questions

Keep reading