Get WhatsApp Alerts for Google Form Submissions via Twilio

Rohit Gupta
2d
244
0
3

Article

Abstract / Overview

Goal: send instant WhatsApp alerts when a Google Form is submitted. Transport: Twilio WhatsApp API. Orchestration: Google Sheets + Make (formerly Integromat). Secondary option: Google Apps Script direct to Twilio.

Assumption: “Free up to 100 messages/month via Twilio trial” means your trial credit covers approximately 100 outbound alerts in a typical test setup. Actual free volume depends on Meta/Twilio pricing by country and conversation type.

Output: a working flow that captures form answers, formats a message, and posts to WhatsApp with basic retries, deduplication, and budget guards.

Diagram

WhatsApp with Twilo

Conceptual Background

Google Forms → Google Sheets: Every submission writes a row. Sheets is the reliable trigger and audit log.
Make.com scenario: A low-code pipeline that monitors new rows and triggers a call to Twilio. Good for non-developers.
Twilio WhatsApp: Two modes: sandbox for development and a verified business sender for production. WhatsApp enforces opt-in and template rules for business-initiated messages, as well as a 24-hour customer care window.
Trial constraints: Trial accounts add a “Sent from your Twilio trial account” prefix and can message only verified numbers or sandbox-participating testers. Treat the trial as development capacity, not production scale.
Templates vs session messages.
- Business-initiated messages require a pre-approved template.
- If the end user messaged you in the last 24 hours, you may reply freely (session).
Phone number format: Use E.164, e.g., +919876543210.
Reliability. Add simple deduplication using the Sheet row ID, and idempotent sends. Add rate limiting.
Privacy. Avoid sending PII in WhatsApp unless users consent. Mask sensitive fields.

Step-by-Step Walkthrough

1. Prerequisites

Google account with permission to create Forms and Sheets.
Twilio account with WhatsApp sandbox enabled.
Make.com account.
One recipient number you control for testing, reachable on WhatsApp.

2. Build the Google Form and Sheet

Create Form. Example fields: Name, Email, Phone, Topic, Message.
In Form settings, link a response destination. Create a new Sheet.
Note the header row. The Sheet is now your source of truth.

3. Prepare Twilio WhatsApp

Enable WhatsApp sandbox in Twilio.
From your test phone, join the sandbox using the code and number shown in Twilio. This authorizes your device as a tester.
Copy credentials: ACCOUNT_SID, AUTH_TOKEN.
Copy the sandbox “From” number, for example whatsapp:+14155238886.
For production later: set up a Messaging Service, add a WhatsApp sender, and submit templates for approval.

4. Create the Make.com scenario

Trigger module: Google Sheets → Watch Rows.
- Connect to the Sheet linked to your Form.
- Select the tab with responses.
- Choose “On new row” or set a schedule (e.g., every minute).
Formatter (optional): Text parser to normalize phone numbers to E.164 and trim whitespace.
Filter (dedupe): Use the row’s unique ID or timestamp as a key. Store keys in Make’s Data Store or a hidden Sheet column to prevent double sends.
Twilio module: Twilio → Create a Message.
- Connection: use ACCOUNT_SID and AUTH_TOKEN.
- From: your WhatsApp sandbox sender, e.g., whatsapp:+14155238886.
- To: whatsapp:+<recipient_number> (your number for alerts during development).
- Body: map fields from the new row. Example template:
```
New Google Form submission
Name: {{Name}}
Phone: {{Phone}}
Topic: {{Topic}}
Message: {{Message}}
Submission ID: {{Row ID}}
```
Router (optional)
- Branch A: send to an internal on-call number if Topic = “Urgent”.
- Branch B: send to a general team number otherwise.
Error handler
- Add a Sleep module and retry 2–3 times on transient 5xx or 429 errors.
Budget guard
- Before sending, check the counter in Data Store for the current month. If count >= 100 stop or route to email.

5. Test

Submit the Form with a test payload.
Verify that a new Sheet row appears.
Confirm a WhatsApp message arrives on the tester's phone with the expected fields.
Inspect the Make execution logs. Resolve any mapping errors.

6. Production hardening (when you leave the sandbox)

Obtain a WhatsApp Business sender through Twilio and get templates approved.
Replace the sandbox From number with your production sender.
For messages to respondents, use a pre-approved template and variables, e.g., form_alert_1.
Store consent and honor opt-out.
Monitor delivery status via Twilio message status callbacks.

7. Alternative: No-code + webhook hybrid

If your form includes files or complex data, push a structured JSON payload to Make via webhook from Apps Script instead of polling the Sheet. See code in Section 4.

Code / JSON Snippets

All snippets are minimal and runnable with placeholders you must replace.

1. Direct Twilio API with cURL (sandbox or production)

Use this when you want to bypass Make.com. Works well for quick tests or from CI.

# Send a WhatsApp alert via Twilio
# Prereq: export TWILIO_ACCOUNT_SID and TWILIO_AUTH_TOKEN
curl -X POST "https://api.twilio.com/2010-04-01/Accounts/$TWILIO_ACCOUNT_SID/Messages.json" \
  --user "$TWILIO_ACCOUNT_SID:$TWILIO_AUTH_TOKEN" \
  --data-urlencode "From=whatsapp:+14155238886" \
  --data-urlencode "To=whatsapp:+919876543210" \
  --data-urlencode "Body=New Google Form submission%0AName: Alice%0ATopic: Demo%0AMessage: Hello"

2. Google Apps Script: trigger on form submit and call Twilio

Bind this script to the response Sheet. Install an On form submit trigger.

/**
 * On form submit → send WhatsApp via Twilio.
 * Replace placeholders before use.
 */
const TWILIO_ACCOUNT_SID = 'YOUR_TWILIO_ACCOUNT_SID';
const TWILIO_AUTH_TOKEN  = 'YOUR_TWILIO_AUTH_TOKEN';
const TWILIO_FROM_WA     = 'whatsapp:+14155238886'; // Sandbox or production sender
const ALERT_TO_WA        = 'whatsapp:+YOUR_VERIFIED_TEST_NUMBER';

function onFormSubmit(e) {
  const row = e.values; // [Timestamp, Name, Email, Phone, Topic, Message, ...]
  const payload = {
    From: TWILIO_FROM_WA,
    To: ALERT_TO_WA,
    Body: [
      'New Google Form submission',
      `Name: ${row[1] || ''}`,
      `Email: ${row[2] || ''}`,
      `Phone: ${row[3] || ''}`,
      `Topic: ${row[4] || ''}`,
      `Message: ${row[5] || ''}`,
      `RowID: ${e.range ? e.range.getRow() : 'n/a'}`
    ].join('\n')
  };

  const options = {
    method: 'post',
    payload: payload,
    headers: {
      Authorization: 'Basic ' + Utilities.base64Encode(TWILIO_ACCOUNT_SID + ':' + TWILIO_AUTH_TOKEN)
    },
    muteHttpExceptions: true
  };

  const url = `https://api.twilio.com/2010-04-01/Accounts/${TWILIO_ACCOUNT_SID}/Messages.json`;
  const res = UrlFetchApp.fetch(url, options);
  const code = res.getResponseCode();
  if (code >= 300) {
    console.error('Twilio error', code, res.getContentText());
    // Optional: write error to a "Logs" sheet
  }
}

3. Make.com: Webhook entry instead of Sheet polling

Push JSON directly at submit time. Faster and less brittle for high volume.

/**
 * Apps Script → Make webhook POST on submit
 * Set WEBHOOK_URL from your Make scenario.
 */
const WEBHOOK_URL = 'https://hook.integromat.com/YOUR_UNIQUE_WEBHOOK';

function onFormSubmit(e) {
  const headersRange = e.range.getSheet().getRange(1, 1, 1, e.range.getSheet().getLastColumn());
  const headers = headersRange.getValues()[0];
  const row = e.values;

  const body = {};
  headers.forEach((h, i) => body[h] = row[i]);

  const options = {
    method: 'post',
    contentType: 'application/json',
    payload: JSON.stringify({
      meta: { rowId: e.range.getRow(), sheet: e.range.getSheet().getName() },
      data: body
    }),
    muteHttpExceptions: true
  };
  const resp = UrlFetchApp.fetch(WEBHOOK_URL, options);
  if (resp.getResponseCode() >= 300) {
    console.error('Webhook error', resp.getResponseCode(), resp.getContentText());
  }
}

Make a scenario after the webhook:

Webhooks → Custom webhook (trigger).
Twilio → Create a Message. Map To, Body from data fields.
Data Store counter for budget.

4. Twilio WhatsApp Template example (production)

Template name: form_alert_1
Category: Utility
Language: en
Content:
New submission from {{1}} about {{2}}.
We captured: {{3}}.
Ref: {{4}}

Send with the Messages API by setting Body the rendered text, or use Twilio’s template channels API when available in your account. Always ensure the recipient has opted in.

5. Minimal sample workflow JSON (portable spec)

This JSON models the end-to-end path. Use it as documentation and for reproducible setups.

{
  "name": "google-forms-to-whatsapp-alerts",
  "assumptions": {
    "trial_budget_msgs_per_month": 100,
    "timezone": "UTC",
    "consent_required": true
  },
  "trigger": {
    "type": "google_sheets_watch_rows",
    "spreadsheet_id": "YOUR_SHEET_ID",
    "worksheet": "Form Responses 1",
    "interval_seconds": 60
  },
  "steps": [
    {
      "id": "normalize_phone",
      "type": "transform",
      "fn": "to_e164",
      "input": "{{row.Phone}}",
      "default_country": "IN"
    },
    {
      "id": "dedupe",
      "type": "datastore_check",
      "key": "{{row._row_id}}",
      "store": "sent_rows",
      "on_exists": "abort"
    },
    {
      "id": "budget_guard",
      "type": "counter_check",
      "counter": "wa_sends_monthly",
      "limit": 100,
      "window": "calendar_month",
      "on_exceed": "route:email_fallback"
    },
    {
      "id": "compose_body",
      "type": "template",
      "value": "New Google Form submission\\nName: {{row.Name}}\\nPhone: {{row.Phone}}\\nTopic: {{row.Topic}}\\nMessage: {{row.Message}}\\nRowID: {{row._row_id}}"
    },
    {
      "id": "send_whatsapp",
      "type": "twilio_message",
      "from": "whatsapp:+14155238886",
      "to": "whatsapp:+YOUR_VERIFIED_TEST_NUMBER",
      "body": "{{steps.compose_body.output}}",
      "retry": { "retries": 3, "backoff_seconds": 5 }
    },
    {
      "id": "mark_sent",
      "type": "datastore_put",
      "key": "{{row._row_id}}",
      "store": "sent_rows"
    }
  ],
  "fallbacks": [
    {
      "id": "email_fallback",
      "type": "email_send",
      "to": "[email protected]",
      "subject": "[Budget Reached] New Google Form submission",
      "body": "{{steps.compose_body.output}}"
    }
  ],
  "secrets": {
    "TWILIO_ACCOUNT_SID": "YOUR_ACCOUNT_SID",
    "TWILIO_AUTH_TOKEN": "YOUR_AUTH_TOKEN"
  }
}

6. Budget calculation helper

Assumption for planning only:

messages_per_month = floor(trial_credit_usd / effective_cost_per_alert_usd)

Example:
trial_credit_usd ≈ 15
effective_cost_per_alert_usd ≈ 0.15
messages_per_month ≈ floor(15 / 0.15) = 100

Use your actual regional rates and conversation categories to refine the estimate. The guard in the scenario enforces an upper bound of 100.

Use Cases / Scenarios

Internal alerts only: Notify yourself or an ops group when a submission arrives. Works well in the sandbox.
Lead capture acknowledgment: Send the respondent a templated WhatsApp confirmation with a ticket ID. Requires production sender and approved template.
On-call escalation: Route urgent topics to an on-call number between set hours.
Triage workflows: Branch by “Topic” and notify different teams.
Compliance forms: Push masked payloads to a compliance officer while storing full details in the Sheet.

Limitations / Considerations

Trial mode: Trial adds a prefix to messages and restricts recipients. Use it for development only.
Template enforcement: Business-initiated notifications to end users require approved templates. Build and test templates early.
24-hour window: If the user has not messaged you in 24 hours and you do not use a template, delivery will fail.
Opt-in and consent: Store opt-ins. Provide a clear opt-out path.
Phone formatting: Always E.164. Add validation in the Form.
Throughput and quotas: Respect Make’s scenario limits and Twilio rate guidance.
Data protection: Do not include secrets or sensitive PII in alerts. Use IDs and links to internal systems.
Monitoring: Capture Twilio status callbacks if you need delivery analytics.
International messaging: Costs and deliverability vary by country. Re-evaluate your budget guard when you go live.

Conclusion

You can ship WhatsApp alerts for Google Forms within one hour using Sheets, Make.com, and Twilio. Start in the Twilio sandbox, verify the data flow, and add a monthly cap near 100 trial messages as a practical ceiling. When ready, move to a production WhatsApp sender with templates and consent. The same pattern scales to other sources and channels with minimal change.

Fixes (Common Pitfalls and Troubleshooting)

No message received in the sandbox: Ensure your test phone is joined to the sandbox, and that From begins with whatsapp:. Confirm your phone number includes + and country code.
Duplicate alerts: Turn on dedupe. Use the Sheet row number or a UUID from the webhook as the idempotency key.
HTTP 401 from Twilio: Check ACCOUNT_SID and AUTH_TOKEN. In Make, reauthorize the Twilio connection.
HTTP 400 “To” invalid: Fix number to E.164. Remove spaces and dashes. Prefix with whatsapp:.
Template not approved / message blocked: Use a sandbox for development or submit a WhatsApp template and wait for approval before production tests.
Budget exceeded mid-month: The Make counter stops sends and routes to email. Reset at the month boundary or raise the cap after evaluating costs.
Apps Script trigger not firing: Install an On form submit trigger, not On edit. Give the script authorization.
Make scenario idle: Increase polling frequency on Watch Rows, or switch to webhook push.
Garbled characters or emojis: Ensure UTF-8 handling. In cURL, avoid shell expansion; encode the body or use --data-urlencode.