SMS from Google Sheets: Apps Script, statuses and replies

What you need

• A Google Sheet with an SMS tab: A phone, B name, C message, D status (empty).
• A smsportal account with an Android phone and SIM card connected.
• An API key from the smsportal dashboard (API Keys → Generate API Key).
• For replies: a Replies tab and the script deployed as a web app.

How to send SMS from Google Sheets

1. 1

   Set up the columns

   Phone in column A, ideally as plain text (+14155550123), because Sheets turns it into a number and drops the plus. The message in column C can be a formula, for example ="Hi "&B2&", a reminder about your appointment tomorrow.". Rows with an empty message are skipped.

2. 2

   Store the key in script properties

   Extensions → Apps Script, then Project Settings → Script Properties → Add script property: name SMSPORTAL_API_KEY, value is your key. The key is not in the code, so a copy of the sheet does not leak it.

3. 3

   Paste the script

   In the Apps Script editor replace the contents of Code.gs with the code below and save. Set COUNTRY_CODE to your own. The script sends 50 rows per request and stops at the first error, writing it to column D.

   Apps Script: SMS menu and batched sendingCopy

   // Send SMS from a sheet through smsportal.
   // Key: Project Settings → Script Properties → SMSPORTAL_API_KEY.
   const SHEET_NAME = 'SMS';
   const PHONE_COL = 1;   // A: phone
   const MESSAGE_COL = 3; // C: message (can be a formula)
   const STATUS_COL = 4;  // D: status, written by the script
   const BATCH_SIZE = 50;
   const COUNTRY_CODE = '1'; // your country code without the plus: 1 US/Canada, 44 UK
   const SENT_PREFIX = 'Queued';
   const API_URL = 'https://smsportal.app/api/v1/gateway/send-bulk-sms';
   
   function onOpen() {
     SpreadsheetApp.getUi()
       .createMenu('SMS')
       .addItem('Send unsent rows', 'sendPendingSms')
       .addToUi();
   }
   
   function sendPendingSms() {
     const apiKey = PropertiesService.getScriptProperties().getProperty('SMSPORTAL_API_KEY');
     if (!apiKey) throw new Error('Add SMSPORTAL_API_KEY to the script properties.');
     const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
     const rows = sheet.getDataRange().getValues();
   
     const pending = [];
     for (let i = 1; i < rows.length; i++) {
       const rawPhone = rows[i][PHONE_COL - 1];
       const message = String(rows[i][MESSAGE_COL - 1]).trim();
       const status = String(rows[i][STATUS_COL - 1]);
       if (rawPhone === '' || !message || status.startsWith(SENT_PREFIX)) continue;
       const phone = normalizePhone(rawPhone);
       if (!phone) {
         sheet.getRange(i + 1, STATUS_COL).setValue('Invalid number');
         continue;
       }
       pending.push({ row: i + 1, phone: phone, message: message });
     }
   
     let queued = 0;
     for (let start = 0; start < pending.length; start += BATCH_SIZE) {
       const batch = pending.slice(start, start + BATCH_SIZE);
       const response = UrlFetchApp.fetch(API_URL, {
         method: 'post',
         contentType: 'application/json',
         headers: { 'x-api-key': apiKey },
         payload: JSON.stringify({
           messages: batch.map(item => ({ recipients: [item.phone], message: item.message })),
         }),
         muteHttpExceptions: true,
       });
       const code = response.getResponseCode();
       const ok = code >= 200 && code < 300;
       const status = ok
         ? SENT_PREFIX + ' ' + Utilities.formatDate(new Date(), Session.getScriptTimeZone(), 'yyyy-MM-dd HH:mm')
         : 'Error ' + code + ': ' + response.getContentText().slice(0, 150);
       batch.forEach(item => sheet.getRange(item.row, STATUS_COL).setValue(status));
       if (!ok) break; // 400, 401 and 429 will not go away on the next batch
       queued += batch.length;
     }
     SpreadsheetApp.getActive().toast('Texts queued: ' + queued);
   }
   
   // (415) 555-0123 -> +14155550123. Numbers starting with + or 00 are kept.
   function normalizePhone(value) {
     const raw = String(value).trim();
     let digits = raw.replace(/\D/g, '');
     if (!digits) return '';
     if (raw.startsWith('+')) {
       // already international
     } else if (digits.startsWith('00')) {
       digits = digits.slice(2);
     } else if (!(digits.startsWith(COUNTRY_CODE) && digits.length > 10)) {
       digits = COUNTRY_CODE + digits.replace(/^0+/, '');
     }
     return digits.length >= 8 && digits.length <= 15 ? '+' + digits : '';
   }

4. 4

   Run it from the menu

   Reload the sheet. A new menu appears: SMS → Send unsent rows. The first run asks you to authorize access to the sheet and to external URLs. Start with two rows that hold your own numbers.

5. 5

   Check the statuses

   Column D gets "Queued" with a timestamp. That means the texts are waiting for the phone, which sends one every 5 seconds (about 720 per hour). A row with an error shows the reason and is retried on the next run.

How to collect customer replies in the sheet

smsportal POSTs the MESSAGE_RECEIVED event as JSON, and the script deployed as a web app appends a row: time, sender, text. Apps Script cannot see request headers, so it cannot check X-Signature. Instead the URL carries a secret token, and repeated deliveries of the same event are filtered by idempotencyKey.

1. Add a Replies tab, a new file with the code below in the same Apps Script project, and a WEBHOOK_TOKEN script property holding a random string.
2. Deploy → New deployment → Web app. Execute as: Me, Who has access: Anyone. Copy the URL ending in /exec.
3. In the smsportal dashboard open Webhooks → Create Webhook: URL .../exec?token=YOUR_TOKEN, a signing secret (at least 20 characters), event MESSAGE_RECEIVED.
4. Apps Script answers a POST with a redirect, and smsportal follows it. Reply to one of your texts and check the result in Webhooks → Deliveries. If deliveries do not go through, receive replies in n8n or Make instead.

// Customer replies into the "Replies" tab.
// Script property WEBHOOK_TOKEN: a random string, the same one as in the webhook URL.
const REPLIES_SHEET = 'Replies';

function doPost(e) {
  const token = PropertiesService.getScriptProperties().getProperty('WEBHOOK_TOKEN');
  if (!token || e.parameter.token !== token) {
    return ContentService.createTextOutput('forbidden');
  }
  const data = JSON.parse(e.postData.contents);
  if (data.webhookEvent !== 'MESSAGE_RECEIVED') {
    return ContentService.createTextOutput('ignored');
  }
  const lock = LockService.getScriptLock();
  lock.waitLock(10000);
  try {
    const sheet = SpreadsheetApp.getActive().getSheetByName(REPLIES_SHEET);
    const keys = sheet.getRange(1, 4, Math.max(sheet.getLastRow(), 1)).getValues().flat();
    if (!keys.includes(data.idempotencyKey)) {
      sheet.appendRow([data.receivedAt, data.sender, data.message, data.idempotencyKey]);
    }
  } finally {
    lock.releaseLock();
  }
  return ContentService.createTextOutput('ok');
}

Ready-made scenarios

• Reminders for tomorrow’s appointments

  Column E holds the date, F the time as text, and C the formula =IF(E2=TODAY()+1, "Reminder: your appointment is tomorrow at "&F2, ""). A time-driven trigger (Triggers → Add Trigger, daily 5-6 pm) runs sendPendingSms, and rows for other dates have an empty message and wait.

• Overdue invoice reminders

  A sheet with amount and due date, the text built by formula: "Invoice 12/10 for $450 was due on Oct 20". The status in D shows who already got the reminder, so a second run does not text them again.

• A change for event attendees

  The sign-up list from a form, the same text in every row: "The workshop has moved to room 2". 120 people means three requests of 50 rows, and the phone sends them all in about 10 minutes.

Errors and fixes

Column D says "Error 401"

Cause: A wrong key or a typo in the SMSPORTAL_API_KEY property name.

Fix: Check the property in Project Settings. The script sends an x-api-key header, not Authorization.

Column D says "Error 429"

Cause: The SMS limit of your plan is used up, or the batch is larger than your plan allows.

Fix: The error text says which limit ran out. For the batch limit, lower BATCH_SIZE.

"Invalid number" in many rows

Cause: Numbers from another country without a country code, or too short once spaces are removed.

Fix: Type numbers with + and the country code. Numbers without one get COUNTRY_CODE added.

Replies stop reaching the sheet after a code change

Cause: The /exec URL serves the deployed version, not your latest code.

Fix: Deploy → Manage deployments → edit → New version. The URL stays the same.

Status "Queued" but the text never arrives

Cause: The phone is offline or Android puts the app to sleep.

Fix: Check signal, internet and the phone status in the dashboard. Queued messages wait up to 72 hours, then expire.

Frequently asked questions

Sources

1. Google Apps Script: quotas and limits
2. Google Apps Script: UrlFetchApp
3. Google Apps Script: script properties
4. Google Apps Script: custom menus
5. Google Apps Script: web apps (doPost)
6. smsportal API documentation

More in the guides

• Bulk SMS from a CSV or Excel file
• Receive SMS with a webhook
• n8n SMS: send and receive