# Text each new row in a Google Sheet > Send a text from your own number for every new row in a Google Sheet and write back when it went, with Zapier, Make or a short Apps Script that needs nothing else. Luis runs Northside Tutoring. Parents sign up through a Google Form, and every answer becomes a row in a sheet called “Sign-ups”. Luis wants each parent to get a text from his own number confirming the class they picked, and he wants the sheet to show which rows were texted and when, so his assistant never texts a parent twice by hand. ## What you’ll have - A text from your number for each new row, a few minutes after it appears (or at once, when a Google Form feeds the sheet). - Two columns on the sheet that say when each row was texted and which BlueBot text it was. - Rows that were already in the sheet left alone. ## What you need - A BlueBot account with your Mac connected. - A Google Sheet with a header row and one row per person, including a mobile number column. - People in the sheet who asked to hear from you by text. A sign-up form should say so beside the number. Luis’s sheet has these columns. Add the last two to yours, empty: | Column | Header | What’s in it | | ------ | ---------- | ---------------------------------------------- | | A | Timestamp | When the form was sent. Google Forms fills it. | | B | First name | The parent’s first name. | | C | Mobile | Their mobile number. | | D | Class | The class they picked. | | E | Texted at | Empty until the row is texted. | | F | BlueBot id | The text’s `msg_` id, for finding it later. | > **Keep the + on phone numbers.** Sheets reads `+15555550123` as a formula or a number and can drop the `+`. Select the Mobile column and choose **Format**, then **Number**, then **Plain text**. BlueBot reads US numbers with or without the `+1`, so a number that lost it still works. There are three ways to build it. Zapier and Make take a few minutes and no code. Apps Script runs inside Google with no third party at all, and is the way to go when you don’t use Zapier or Make already. ## With Zapier ### Start the Zap with the sheet In Zapier, make a Zap. For the trigger, pick **Google Sheets** and the **New Spreadsheet Row** event, sign in to Google, then pick the spreadsheet and the **Sign-ups** worksheet. Press **Test trigger** to load a row. ### Add BlueBot’s Send Text action Add an action step, pick **BlueBot** and the **Send Text** action. Connect your account with an API key from **Developers**, then **API keys**, in the BlueBot dashboard (the `messages:send` scope is enough). Then fill in: | BlueBot field | Map it to | | ------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | To | The **Mobile** column from the trigger. | | Text | `Hi`, the **First name** column, then `, this is Luis at Northside Tutoring. You’re signed up for`, the **Class** column, `. Reply here with any questions.` | | Metadata | `source` set to `google-sheets`, and `row` set to the trigger’s **Row ID**. | | Service | Leave it on Auto: iMessage when the parent uses it, otherwise SMS through your iPhone. | ### Write back to the row Add a second action: **Google Sheets**, **Update Spreadsheet Row**. Pick the same spreadsheet and worksheet, set **Row** to the trigger’s **Row ID**, then set **Texted at** to the Send Text step’s **Created At** and **BlueBot id** to its **ID**. ### Turn it on Turn the Zap on. Zapier looks for new rows on its schedule (every one to fifteen minutes, by plan) and sees rows added at the bottom of the sheet only. ## With Make ### Watch the sheet Make a scenario. Add **Google Sheets**, **Watch New Rows**, pick the spreadsheet and the **Sign-ups** sheet, say the table has headers, and choose to start from rows added from now on, so the parents already in the sheet aren’t texted. ### Send the text Add **BlueBot**, **Send Text**. Connect it with an API key. Map **To** to the Mobile column and write **Text** with the First name and Class columns mapped in, as in the Zapier step above. Set **Metadata** `source` to `google-sheets` and `row` to the row number Make gives. ### Write back Add **Google Sheets**, **Update a Row**, with the row number from the first module, and map **Texted at** and **BlueBot id** from the Send Text module’s output. ### Schedule it Set the scenario to run every few minutes and turn it on. ## With Apps Script Apps Script runs inside your Google account and can call BlueBot itself. The script below looks through the sheet, texts each row that has a number and no **Texted at**, and writes back when it went. A Google Form can start it the moment a parent presses Submit; a timer catches rows typed in by hand. ### Mark the rows already in the sheet Before anything runs, type `before BlueBot` in the **Texted at** cell of every row that’s already there. The script skips any row with something in that cell, so the parents who signed up last month won’t get a text today. ### Add the script In the sheet, choose **Extensions**, then **Apps Script**. Replace what’s in the editor with this script and save it: ```js title="Code.gs" /** * Texts each new sign-up from your own number through BlueBot, then writes when it went and the text's id back to * the row. Columns: A Timestamp, B First name, C Mobile, D Class, E Texted at, F BlueBot id. */ const BLUEBOT_URL = 'https://usebluebot.com/api/v1/messages'; const SHEET_NAME = 'Sign-ups'; function textNewSignUps() { const lock = LockService.getScriptLock(); if (!lock.tryLock(1000)) return; // the last run is still going; this row will be there next time try { const apiKey = PropertiesService.getScriptProperties().getProperty('BLUEBOT_API_KEY'); const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME); const rows = sheet.getDataRange().getValues(); for (let i = 1; i < rows.length; i++) { const [timestamp, firstName, mobile, className, textedAt] = rows[i]; if (!mobile || textedAt) continue; const rowNumber = i + 1; // One key per sign-up: if a run sends and stops before writing back, the next run gets the same text back // instead of sending a second one. const stamp = timestamp instanceof Date ? timestamp.getTime() : rowNumber; const idempotencyKey = ['sheets', sheet.getSheetId(), stamp, String(mobile).replace(/\D/g, '')].join('-'); const response = UrlFetchApp.fetch(BLUEBOT_URL, { method: 'post', contentType: 'application/json', headers: { Authorization: 'Bearer ' + apiKey, 'Idempotency-Key': idempotencyKey }, payload: JSON.stringify({ to: String(mobile), text: 'Hi ' + firstName + ', this is Luis at Northside Tutoring. You’re signed up for ' + className + '. Reply here with any questions.', metadata: { source: 'google-sheets', row: String(rowNumber) }, }), muteHttpExceptions: true, }); const answer = JSON.parse(response.getContentText()); if (response.getResponseCode() === 201) { sheet.getRange(rowNumber, 5, 1, 2).setValues([[new Date(), answer.id]]); } else if (answer.error.code === 'rate_limited') { return; // try the rest on the next run } else { // Leave the row for a person: the reason is in the sheet, and the row is skipped from now on. sheet.getRange(rowNumber, 5).setValue('Not sent: ' + answer.error.message); } } } finally { lock.releaseLock(); } } ``` ### Give it your API key In the Apps Script editor, open **Project Settings** (the gear), scroll to **Script Properties**, press **Add script property**, and add `BLUEBOT_API_KEY` with a key from BlueBot’s **Developers**, then **API keys**. The key stays in the script’s settings, out of the code and out of the sheet, where anyone the sheet is shared with could read it. ### Run it once by hand Pick `textNewSignUps` at the top of the editor and press **Run**. Google asks you to allow the script to read the sheet and call an outside service; allow it. Google shows a warning because you wrote the app yourself; choose **Advanced** and go on to your project. ### Run it on its own Open **Triggers** (the clock) and press **Add Trigger**: - To text as soon as a Google Form is sent, choose the function `textNewSignUps`, event source **From spreadsheet** and event type **On form submit**. - For rows typed in by hand, add a second trigger: event source **Time-driven**, **Minutes timer**, **Every 5 minutes**. The two can run side by side. The lock keeps them from texting the same row at once, and the key keeps a row from being texted twice even if one run stops halfway. Each run makes this request for every new row: ```bash curl -X POST "https://usebluebot.com/api/v1/messages" \ -H "Authorization: Bearer $BLUEBOT_API_KEY" \ -H "Content-Type: application/json" \ -H "Idempotency-Key: sheets-0-1762890000000-15555550123" \ -d '{ "to": "+15555550123", "text": "Hi Maria, this is Luis at Northside Tutoring. You’re signed up for Algebra I on Tuesdays. Reply here with any questions.", "metadata": { "source": "google-sheets", "row": "14" } }' ``` ```ts import BlueBot from '@usebluebot/sdk'; const client = new BlueBot({ apiKey: process.env.BLUEBOT_API_KEY }); const message = await client.messages.send({ to: '+15555550123', text: 'Hi Maria, this is Luis at Northside Tutoring. You’re signed up for Algebra I on Tuesdays. Reply here with any questions.', metadata: { source: 'google-sheets', row: '14', }, }, { idempotencyKey: 'sheets-0-1762890000000-15555550123' }); console.log(message); ``` ```python import os from bluebot import BlueBot client = BlueBot(api_key=os.environ["BLUEBOT_API_KEY"]) message = client.messages.send( to="+15555550123", text="Hi Maria, this is Luis at Northside Tutoring. You’re signed up for Algebra I on Tuesdays. Reply here with any questions.", metadata={ "source": "google-sheets", "row": "14", }, idempotency_key="sheets-0-1762890000000-15555550123", ) print(message) ``` ## Test it Add a row at the bottom of the sheet with your own first name and your own mobile number (or send your own Google Form), then wait for the trigger or press **Run**. | You got | It means | Do next | | ------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------- | | A text on your phone, and a time and a `msg_` id in your row | It works. | Delete your test row, or leave it: it won’t be texted again. | | `Not sent: …` in the **Texted at** cell | BlueBot refused the text, and the message says why: an empty or unreadable number, or a key without `messages:send`. | Fix the row, clear the cell, and the next run tries again. | | A time and an id in the row, but no text yet | BlueBot accepted the text and it’s waiting: quiet hours, your Mac asleep, or its turn in the pacing. | Look the id up in **Messages** in the dashboard; its hold says why and until when. | | Nothing in the row after the trigger ran | The run didn’t reach your row, or didn’t run. | In Apps Script, **Executions** lists every run with its log and any error. In Zapier, the Zap’s history says why it stopped. | | `Not sent: The request carried no key…` | The script property is missing or misspelled. | Check `BLUEBOT_API_KEY` in **Project Settings**. | ## When it goes wrong - **Every row in the sheet got a text.** The rows that were there before weren’t marked. BlueBot paces texts and stops at your daily limit for new people, so check **Messages** at once and cancel what’s still queued there. Then mark the old rows as in the first step. - **Rows sorted or inserted in the middle.** Zapier and Make watch for rows added at the bottom; a row inserted in the middle or a sheet sorted after the fact can be missed or seen again. Apps Script reads the **Texted at** column, so it doesn’t care where a row is. - **A long list arrives at once.** Pasting 300 rows is a broadcast, not a reply. Your sending rules space texts 20 seconds apart and hold new people over your daily limit, so the last ones go hours later or tomorrow. For a one-off list, use **Broadcasts** in the dashboard, which shows when the last text will go before you send. - **Somebody replied STOP.** They’re on your stop list, and BlueBot cancels any text to them from now on. The script writes the reason into the row the next time it tries. ## Next - [Google Sheets recipes](https://usebluebot.com/docs/recipes/google-sheets): More Sheets recipes, including logging replies to a sheet. - [Text each new sign-up from a form](https://usebluebot.com/docs/guides/form-replies): The same idea for Typeform and Jotform, through a Text Hook. - [Airtable as a texting CRM](https://usebluebot.com/docs/guides/airtable-crm): An Airtable base that sends texts and saves every reply. - [Zapier](https://usebluebot.com/docs/zapier): Every BlueBot trigger, action and search in Zapier. - [Rate limits](https://usebluebot.com/docs/concepts/rate-limits): What a script that sends in a loop should expect. - [Sending rules](https://usebluebot.com/docs/concepts/sending-rules): Pacing, quiet hours and the daily limit for new people.