# Send automated SMS from Google Sheets (Twilio): Production Guide

**Send automated SMS from Google Sheets (Twilio)** without paying for another monthly SaaS “SMS marketing” platform. This guide is for US and Canadian business owners and developers who want **appointment reminders**, **order notifications**, or **ops alerts** using a **Google Sheets SMS integration**, the **Twilio Apps Script tutorial** pattern, and **Apps Script Twilio API** calls you own end to end.

---

## Introduction: SMS SaaS vs Twilio + Apps Script

| Approach | Typical cost model | Data control | Flexibility |
|----------|-------------------|--------------|-------------|
| All-in-one SMS marketing apps | Monthly subscription + per-contact tiers | Data lives in vendor CRM | Fast start, limited customization |
| **Twilio API + Google Apps Script** | Pay per message + small infra (your time) | Numbers and logs stay in **your** Sheet + Twilio console | Full control: validation, batching, retries, audit columns |

Most “simple” tutorials stop at ten lines of `UrlFetchApp`. That fails in production: invalid numbers, duplicate sends on double-clicks, leaked API keys in source code, and no status column your team can trust. This guide gives you a **robust, production-ready** pattern: **E.164 validation**, **PropertiesService** for secrets, **Status** and **MessageSid** columns, **duplicate prevention**, and optional **Twilio StatusCallback** logging via a Web App.

---

## Prerequisites

### 1. Twilio account and credentials

1. Create a [Twilio](https://www.twilio.com/) account.
2. From the Console, copy **Account SID** and **Auth Token**.
3. Buy or use a trial **From** phone number capable of SMS (trial accounts can only send to **verified** destination numbers until upgraded).

### 2. Google Sheet + Apps Script

1. Create a Google Sheet (or use an existing ops sheet).
2. **Extensions → Apps Script** — create a **container-bound** script (recommended so `onOpen` runs in the sheet).
3. In Apps Script: **Project Settings → Script properties** add:

| Property name | Value |
|---------------|--------|
| `TWILIO_ACCOUNT_SID` | Your Account SID (`AC...`) |
| `TWILIO_AUTH_TOKEN` | Your Auth Token |
| `TWILIO_FROM_NUMBER` | E.164, e.g. `+15551234567` |
| `SMS_MAX_PER_RUN` | (optional) default `40` — batch size per run |
| `SMS_DEFAULT_REGION` | (optional) `US` or `CA` for parsing local numbers |
| `TWILIO_STATUS_CALLBACK_URL` | (optional) your deployed Web App `/exec` URL for delivery callbacks |

**Security (US enterprise best practice):** never paste the Auth Token into the script editor as a string literal. Always use **Script properties** (or Secret Manager for larger orgs). The sample `.gs` file follows this pattern.

### 3. Authorize the script

First run will prompt for permission to call **external URLs** (`UrlFetchApp`). Approve for the account that should send messages (usually the business Workspace user or a dedicated automation account).

---

## The “golden” spreadsheet setup

Use **row 1** exactly as headers (so ops teams and support docs stay consistent):

| Column | Header | Purpose |
|--------|--------|---------|
| A | **Name** | Optional personalization (e.g. `Hi {{name}}` in message templates) |
| B | **Phone** | Recipient — will be normalized to **E.164** |
| C | **Message** | SMS body (watch Twilio segment length / encoding) |
| D | **Status** | `Sent`, `Failed`, or `Skipped` after processing |
| E | **MessageSid** | Twilio message SID (for support + StatusCallback matching) |
| F | **LastError** | API / validation error text (truncated) |

**Duplicate prevention:** rows with **Status = Sent** are skipped if you run the menu again — so accidental double-runs do not blast customers twice.

---

## Code implementation overview

Full commented source:

- **`/public/code/send-sms-twilio-google-sheets.gs`** (download from your site’s `public` folder in dev, or copy from the repo)

### Section 1: `sendSmsCore_` — Twilio REST via `UrlFetchApp`

- POST to `https://api.twilio.com/2010-04-01/Accounts/{AccountSid}/Messages.json`
- **HTTP Basic** auth: Base64(`AccountSid:AuthToken`)
- Body: `application/x-www-form-urlencoded` with `To`, `From`, `Body`
- Optional **`StatusCallback`** when `TWILIO_STATUS_CALLBACK_URL` is set

### Section 2: `onOpen` — custom menu **“🚀 Send SMS”**

- **Send pending SMS rows** → runs batch send with caps
- **Validate phone numbers (dry run)** → checks E.164 without calling Twilio

### Section 3: Helpers you actually need in production

- **`normalizeToE164_`**: strips formatting, enforces plausible NANP for US/CA, requires `+` for international raw input when not 10/11-digit NANP
- **`sendPendingSmsRows`**: loops rows, skips **Sent**, validates, sends, writes **Status** / **MessageSid** / **LastError**
- **`doPost`**: optional Web App endpoint for Twilio **status callbacks** — finds row by **MessageSid** in column E and updates Status when Twilio reports delivered/failed

---

## Advanced troubleshooting (US / Canada)

### 10DLC registration (brief)

For **application-to-person (A2P)** SMS at scale in the United States, carriers expect **10DLC** brand and campaign registration for long codes and certain traffic types. If messages to US mobiles fail or Twilio’s debugger shows policy errors, check Twilio’s **Messaging** compliance docs and your **Messaging Service** / campaign setup. Canada has different carrier rules; Twilio’s console will surface country-specific errors—read the **error code** returned in the API response (also written to **LastError** in the sheet).

### Apps Script six-minute execution limit

Google Apps Script has a **maximum execution time** per run (on the order of six minutes for many triggers). For large lists:

1. Keep **`SMS_MAX_PER_RUN`** conservative (e.g. 40–80).
2. Run from a **time-driven installable trigger** every few minutes until all pending rows are cleared.
3. Optionally split work across tabs or use a “queue” sheet.

The provided script **paces** requests slightly (`Utilities.sleep`) to reduce burst rate issues.

---

## FAQ

### Can I send bulk SMS?

Yes, in controlled batches. Twilio and carriers enforce acceptable use and throughput. Use batching, respect **opt-in** consent, and include **STOP** handling where required for marketing use cases. For pure transactional messages (e.g. “Your appointment is tomorrow at 3 PM”), follow Twilio and TCPA guidance.

### How much does it cost per message in the US?

Pricing is **per segment** and varies by number type and destination. Check Twilio’s current **Programmable SMS** pricing page for the US and Canada. Your total cost is Twilio usage plus engineer time—**no** per-seat SMS SaaS layer when you use Sheets + Apps Script.

### Can I receive replies?

Yes, but not from this sheet-only tutorial alone. You configure a **Twilio webhook** on your phone number to hit an endpoint (often the same Apps Script **Web App** `doPost`) and then append inbound SMS to a sheet. That is a small second project: verify Twilio’s request signature in production.

---

## Step-by-step: wire the script

| Step | Action |
|------|--------|
| 1 | Create headers in row 1: `Name`, `Phone`, `Message`, `Status`, `MessageSid`, `LastError` |
| 2 | Paste `send-sms-twilio-google-sheets.gs` into the bound Apps Script project |
| 3 | Set Script properties (`TWILIO_*`, optional `SMS_MAX_PER_RUN`) |
| 4 | Save → Run `sendPendingSmsRows` once → authorize external requests |
| 5 | Reload the Sheet → open **🚀 Send SMS** → **Validate** then **Send** |

## Pricing comparison (illustrative)

| Line item | SMS SaaS bundle | Twilio + Sheets + Apps Script |
|-----------|-----------------|------------------------------|
| Platform subscription | Often $50–300+/mo depending on seats | None required beyond Workspace |
| Per message | Bundled or opaque | Twilio list pricing per segment |
| Engineering setup | Low | One-time script + review |
| Custom rules | Limited | Full control in code |

Exact SaaS pricing varies by vendor—use this table for **decision framing**, not a quote.

## StatusCallback (optional deep dive)

1. Deploy the script as a **Web App** (Execute as you, access: anyone with link—or stricter if your org allows).
2. Copy the `/exec` URL into `TWILIO_STATUS_CALLBACK_URL`.
3. Twilio will POST `MessageStatus` updates; the sample `doPost` locates the row where **MessageSid** (column E) matches and updates **Status** to `Sent` or `Failed` for terminal states.

For high-security orgs, add **Twilio signature validation** on `doPost` (HMAC-SHA1 against your Auth Token) before updating any row.

## Need a custom integration?

If you need **HIPAA-style governance**, **high-volume queues**, **inbound reply routing**, or integration with CRMs and calendars, contact **[AppScriptExpert](https://appscriptexpert.com/contact)** for a scoped build and handoff documentation.