Bulk SMS send desk with cost checks and delivery results

Build me a bulk SMS send desk on top of ClickSend and Google Sheets. I open it whenever I need to text a list of customers, and it exists so I can see exactly who I am about to message and what it will cost before I commit, instead of fighting the ClickSend console. Three tabs: Compose, Scheduled, and Results.

Compose is the main screen. At the top I pick my audience one of two ways. Option one is an existing ClickSend contact list: use List Contact Lists to populate a dropdown, then List Contacts in List to load the members once I choose one. Option two is a Google Sheet: I give a spreadsheet, a tab and a range, and the handler uses Get Values to read it, letting me map which column holds the phone number and which columns hold merge fields like first name. Remember the last spreadsheet, range and column mapping I used so the next visit is one click.

Whichever source I pick, show the parsed recipients in a table with a running count above it. Validate every number against E.164, meaning a leading plus and a country code, and flag anything that fails with a visible warning on the row, because ClickSend rejects malformed numbers with INVALID_RECIPIENT. Trim whitespace and strip separators before validating, and where a number is obviously missing the country code offer a one click fix that applies a default country code I configure. Deduplicate repeated numbers and show how many duplicates were collapsed. Invalid rows are excluded from the send by default but stay visible in the table so I can correct them.

Bake in a suppression rule. My spreadsheet has an opt out tab, and anyone whose number appears there must be removed from the recipient set automatically, before both the cost estimate and the send. Read the audience range and the opt out tab together using Batch Get Values so it is a single request. Show a suppressed count in the table header, for example twelve recipients, three suppressed, so the removal is never silent, and let me click through to see which numbers were dropped.

Below the audience is the composer. Give me a message body field with a live character and segment counter that understands SMS encoding: 160 characters per segment for plain GSM characters and 153 per segment once a message spans multiple parts, dropping to 70 and 67 if I type an emoji or other Unicode character that forces UCS-2. Warn me at the moment a message tips into an extra segment, since that multiplies the cost of the whole send. Support merge fields from the sheet columns and show a live preview rendered against the first recipient. Include a test send button that fires Send SMS to just my own saved number so I can see the real thing on my phone before spending anything.

The pre send cost guard is the feature that makes this app worth opening. Call Get Account to read my balance and currency, and Calculate SMS Price to get ClickSend's own estimate for this exact audience and message rather than guessing at the math. Display estimated spend for this send next to remaining balance, plus what the balance would be afterwards. If the estimate exceeds the balance, block the send with a clear message that the account needs a top up, because balance is deducted on submission and an underfunded send fails with INSUFFICIENT_CREDIT. Let me set an optional spend ceiling that also locks the send button when an audience would cost more than that in one go.

Sending branches on the audience type. When the audience is a saved ClickSend contact list, use Send SMS Campaign against that list id. When it is an ad hoc sheet audience, use Send SMS with the recipients batched into the messages array rather than looping one call per person, respecting the 1000 message per request limit and chunking across several calls for bigger audiences. Both paths accept an optional scheduled time, passed as a Unix timestamp in seconds and omitted entirely when I want it to go immediately. Show a confirmation dialog restating the final recipient count, the segment count and the estimated cost before anything is submitted. Store the returned message ids alongside the phone number and, for sheet audiences, the source row number, because the Results tab needs that mapping later.

The Scheduled tab lists sends still sitting in the queue with their scheduled time, recipient count and the opening line of the message. Each row gets a cancel button backed by Cancel Scheduled SMS, and there is a cancel everything button backed by Cancel All Scheduled SMS for when I need to stop a send in a hurry. Confirm before either one runs and refresh the list afterwards.

The Results tab reports on sends that already went out. Use Get SMS History for the outbound records, Get SMS Delivery Receipts for carrier confirmed status, and Get SMS Statistics for account level totals. Show delivered versus failed as headline counts for the selected send, then a per recipient table with the number, status and, for failures, the specific reason so I can tell a bad number apart from a carrier rejection. Let me filter down to just the failures and copy that set out.

From the Results tab, a write back button pushes outcomes to the source Google Sheet using Update Values, matching on the stored row mapping so each customer's row records what happened to them, with a status column, a failure reason column and a timestamp. Only write back for audiences that came from a sheet, and tell me how many rows were updated.

Finally, when an ad hoc sheet audience turns out to be one I use repeatedly, give me a save as list button that creates a reusable ClickSend list with Create Contact List and then adds each valid recipient with Create Contact, carrying over name and merge fields where the sheet has them. Once saved, that list shows up in the audience dropdown on the next visit and can be sent to with Send SMS Campaign.

Throughout, remember my configuration between visits: the spreadsheet and range, the column mapping, the opt out tab name, my test number, the default country code, the default sender id and any spend ceiling. Keep a local history of past sends with their audience, message and results so the Results tab has something to select from. Handle rate limiting gracefully by backing off and retrying when ClickSend returns a throttling response, and surface any error message plainly rather than failing silently.