WhatsApp-berichten in Google Sheets loggen en vanuit een sheet versturen

Hoe wordt elk WhatsApp-bericht een rij?

Meta post elk bericht op het nummer naar de webhook-bestemming die je instelt, hier een n8n Webhook-node die ook het verificatieverzoek van Meta beantwoordt. De volgende node is Google Sheets met de bewerking Append Row, die velden uit de payload aan kolommen koppelt: het nummer van de afzender, de profielnaam, de tekst, de WhatsApp bericht-id, het tijdstip en de phone number id van het ontvangende nummer.

Gebruik Append or Update Row met de bericht-id als sleutel in plaats van een gewone append, als je wilt dat nieuwe pogingen geen kwaad kunnen. Meta levert een bericht opnieuw af als het denkt dat een aflevering is mislukt, en met de id als sleutel wordt de tweede aflevering een update van dezelfde rij.

Bij een Coexistence-nummer komen antwoorden die in de WhatsApp Business app worden getypt binnen op dezelfde webhook als smb_message_echoes-events. Log ze met een kolom voor de richting en de sheet bevat beide kanten van het gesprek.

Waar zit de limiet van Sheets?

Bij het aantal schrijfacties per gebruiker. De Sheets API staat 300 schrijfverzoeken per minuut per project toe en 60 per minuut per gebruiker per project, en een n8n-credential is één gebruiker. Een workflow die per bericht een rij toevoegt, kan dus ongeveer één bericht per seconde aan, aanhoudend.

Dat is ruim voldoende voor de meeste winkelnummers. Het is niet meer genoeg na een broadcast-template, wanneer een paar honderd klanten binnen enkele minuten reageren. De API antwoordt dan met 429 Too Many Requests en zonder nieuwe pogingen gaan er rijen verloren.

Twee oplossingen, elk op zich voldoende. Zet nieuwe pogingen met een wachttijd aan op de Google Sheets-node, wat overeenkomt met het advies van Google zelf om exponentieel af te bouwen. Of verwerk in batches: laat n8n dertig seconden berichten verzamelen en ze in één verzoek toevoegen, zodat veel schrijfacties er één worden.

Hoe verstuur je templates vanuit een sheet?

Met Apps Script, via het menu Extensies van de sheet. Een functie leest de rijen die nog niet zijn verstuurd, roept voor elke rij het messages-endpoint van de Cloud API aan met UrlFetchApp en schrijft de teruggegeven bericht-id en de tijd in de rij. Een tijdgestuurde trigger kan de functie om de paar minuten uitvoeren, of je start hem met de hand vanuit een eigen menu.

Bewaar het token in de scripteigenschappen in plaats van in de code, zodat niet iedereen die het script kan bekijken het ziet.

Houd rekening met de limieten. Een consumer Google-account krijgt 20.000 URL Fetch-aanroepen per dag en een Workspace-account 100.000, en elke uitvoering stopt na zes minuten. Verwerk de rijen in delen en markeer elke rij als verzonden voordat je verdergaat, zodat een gestopte uitvoering verdergaat waar hij bleef in plaats van dubbel te versturen.

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());
  }
}

Veelgemaakte fouten

• Toevoegen zonder sleutel op de bericht-id. De nieuwe pogingen van Meta schrijven hetzelfde bericht dan naar twee rijen.
• Tijdens een golf aan antwoorden één rij per bericht schrijven via één credential. De limiet van 60 schrijfacties per minuut per gebruiker geeft 429 terug en er gaan rijen verloren.
• Vrije tekst vanuit de sheet versturen naar mensen die de afgelopen 24 uur niet hebben geschreven. Buiten dat venster komt alleen een goedgekeurd template aan.

Veelgestelde vragen

Lees verder