Text each new row in a Google Sheet

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:

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.

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.

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:

/**
 * 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.