# Google Sheets recipes

> Text each new row, and keep a sheet of every reply.

Three recipes for Google Sheets. Each names its trigger and the DogBot step, lists the fields to map and says how to test it with your own number before anyone else gets a text.

## How Google Sheets connects

- **[Zapier](https://getdogbot.com/docs/zapier)**: DogBot’s Zapier app has a Send Text action and instant triggers for incoming texts and every change of state.
- **[Make](https://getdogbot.com/docs/make)**: DogBot’s Make app sends texts and starts scenarios when a text comes in or changes state.

## Text each new row

| Part             | What it is                                           |
| ---------------- | ---------------------------------------------------- |
| Trigger          | Google Sheets’ New Spreadsheet Row trigger in Zapier |
| Action           | DogBot’s **Send Text** action                        |
| Connects through | [Zapier](https://getdogbot.com/docs/zapier)                               |

### Steps

1. Keep one row per person with their first name and mobile number.
2. Start a Zap with Google Sheets’ New Spreadsheet Row trigger.
3. Add DogBot’s Send Text action, map the phone column to To and write the text with the name column in it.

### Map the fields

| DogBot field                 | Map it to                                                                                                                                          |
| ---------------------------- | -------------------------------------------------------------------------------------------------------------------------------------------------- |
| To                           | The person’s mobile number from Google Sheets. US numbers can leave off the +1.                                                                    |
| Text                         | Hi {{first_name}}, your {{service}} is booked for {{date}}. Reply here if you need to change it.                                             |
| `{{first_name}}` in the text | Google Sheets’s first name field.                                                                                                                  |
| `{{service}}` in the text    | Google Sheets’s service field.                                                                                                                     |
| `{{date}}` in the text       | Google Sheets’s date field.                                                                                                                        |
| Metadata                     | `source` set to `google-sheets`, and `record_id` set to the record’s id, so the status and reply webhooks for this text can find the record again. |
| Service                      | Auto, the default: iMessage when the person uses it, otherwise SMS through your iPhone.                                                            |

### Test it

Put your own number in **To** and press **Test step**. A text arrives on your phone from your own number, and Zapier shows the message DogBot returned with `state: "queued"`. Then map Google Sheets’s field back into **To** and turn the Zap on.

## Log every reply in a sheet

| Part             | What it is                                                                         |
| ---------------- | ---------------------------------------------------------------------------------- |
| Trigger          | DogBot’s New Incoming Text trigger in Zapier                                       |
| Action           | DogBot’s **New Incoming Text** trigger starts it, and Google Sheets takes the text |
| Connects through | [Zapier](https://getdogbot.com/docs/zapier)                                                             |

### Steps

1. Start a Zap with DogBot’s New Incoming Text trigger.
2. Add Google Sheets’ Create Spreadsheet Row action.
3. Map the sender’s number, the words and the time to the sheet’s columns.

### Map the fields

| From DogBot     | Into Google Sheets                                                                                                                                                 |
| --------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------ |
| **From**        | The phone number to find the person by in Google Sheets. It’s in E.164 (`+15555550123`); search by the last 10 digits if Google Sheets stores numbers another way. |
| **Text**        | The words, for a note, a row or a message. Null when the person sent only a picture.                                                                               |
| **Received At** | When it came in, in UTC.                                                                                                                                           |
| **Files**       | Pictures and voice memos. Each `url` works for an hour, so copy a file you want to keep.                                                                           |
| **Chat ID**     | Set when the text came in a group chat; null for a one-to-one text.                                                                                                |

### Test it

Text your DogBot number from another phone, one you’ve texted through DogBot before, then press **Test trigger** in Zapier. It loads that text with its fields, ready to map. DogBot copies texts from people you’ve texted through it, unless you set **Which chats to copy** to **Everything**.

## Mark each row delivered or failed

| Part             | What it is                                                                            |
| ---------------- | ------------------------------------------------------------------------------------- |
| Trigger          | A text is delivered or fails (DogBot’s message.delivered and message.failed webhooks) |
| Action           | Google Sheets acts on what DogBot reports                                             |
| Connects through | [Make](https://getdogbot.com/docs/make)                                                                    |

### Steps

1. Send each row’s text with DogBot’s Send Text module and put the row number in metadata.
2. Start a second scenario from DogBot’s webhooks for message.delivered and message.failed.
3. Use the row number DogBot echoes back to update that row’s Status column with Google Sheets’ Update a Row.

### Map the fields

| From DogBot        | Into Google Sheets                                                                        |
| ------------------ | ----------------------------------------------------------------------------------------- |
| **State**          | `sent`, `delivered`, `read`, `failed` or `expired`, for a field or a filter.              |
| **Metadata**       | The `record_id` you sent with the text, to find the record it was for.                    |
| **Failure Reason** | Why a failed text didn’t go: `not_on_imessage`, `messages_refused` or `file_unavailable`. |
| **To**             | Who the text was for.                                                                     |

### Test it

Send one text to your own number, then look for the event. To try the step without sending anything, `POST /v1/webhooks/{id}/test` sends an example of any event type to your webhook.

## Before you turn it on

- Text only people who asked to hear from you. Anyone who replies STOP goes on your stop list, and DogBot cancels texts to them. [Stop words](https://getdogbot.com/docs/concepts/stop-words)
- A text that can’t go yet waits with a hold rather than failing: texting hours, your Mac away, a daily limit. [Holds](https://getdogbot.com/docs/concepts/holds)
- [Google Sheets with DogBot](https://getdogbot.com/integrations/google-sheets) on the integrations page says what else the two do together.
