Google Sheets
Connect a Google account and your spreadsheets can work with WizMessage. Bot leads arrive as new rows, a sheet can supply an audience list, a chatbot flow can add, find or update rows while it talks to a customer, and your data can be exported to a new sheet in one click. This page covers connecting, each of those four uses, and what to do if a sheet stops updating.
What it does and where to find it
Four uses
Every sheet you connect is set up for one purpose. Exports are the exception: each one creates a new file and needs no setup.
Which of them you can use depends on your plan:
| Use | Plans that include it |
|---|---|
| Bot leads | Wiz Bot, Wiz Pro |
| Flow steps | Wiz Bot, Wiz Pro |
| Audience lists | Wiz Campaign, Wiz Pro |
| Exports of contacts, list members and campaign recipients | Wiz Campaign, Wiz Pro |
| Exports of bot leads | Wiz Bot, Wiz Pro |
Where to find it
On the web, open Google Sheets under Campaigns or Automation in the sidebar, or use the Google Sheets button in the quick links on My Dashboard. They all lead to the same page, headed Integrations.
In the app, go to Settings and then Google Sheets. What differs there is covered near the end of this page.
Only the account owner can see or change any of this. Team members do not see it, and they cannot connect a Google account to your WizMessage account.
Connecting Google
What you need
- A Google account that holds the sheet, or that has edit access to it. View-only access is refused, because most uses need to write.
- A real Google Sheets file. An Excel file stored in Drive cannot be used until you save it as Google Sheets (in Sheets: File, then Save as Google Sheets).
What WizMessage can reach
We ask Google for one permission, called drive.file. It covers the files you choose and the files WizMessage creates for you, and nothing else in your Drive.
The privacy policy sets out what we keep, for how long, and who receives it.
Choosing a sheet you already have
- Press Choose a sheet.
- Pick your Google account and allow access. Google will say that WizMessage wants to see, edit, create and delete only the specific files you use with it.
- Google's file picker opens. Select your spreadsheet and press its insert button.
- You come back to WizMessage, on the setup page for that file.
Starting with a new sheet
If you have no sheet yet, press Create a new sheet beside your account. WizMessage creates an empty file in that account's Drive, named WizMessage and today's date, and opens its setup page. Rename the file whenever you like: nothing depends on the name.
If you have not connected an account yet, press Connect Google account first. That signs you in without opening the file picker.
Setting up: nothing happens until you save
Choosing or creating a file does nothing by itself. The setup page shows the Google account and the file so you can check them, then asks which tab to use and what the sheet should do.
What should this sheet do? offers three answers, each covered below: Receive bot leads as new rows, Supply an audience list, and Be used by flow steps.
If you leave the page, the sheet stays listed under Sheets with a Set up button. To use the same file for a second purpose, choose it again: that gives you a second entry to set up.
Sending bot leads to a sheet
Turning it on
On the setup page, choose the tab, choose Receive bot leads as new rows, and press Send bot leads here. From then on, every lead your bots capture is added as a row. It also stays in Bot Leads as usual, so the sheet is an extra copy and never the only one.
What each row contains
| Column | What goes in it |
|---|---|
| Date | When the lead was captured, in server time |
| Name | The customer's name |
| Phone | The customer's number |
| Flow | The flow that captured the lead |
| Outcome | The outcome set in the flow |
| One column per answer | Each answer, under the label of its question |
Row 1 of the tab is the header, and WizMessage finds each column by that header's name. So:
- Moving columns around is fine.
- Any missing column is added for you, including a new question you add to a flow later.
- Renaming a column starts a new one with the original name. Nothing is lost, but later rows go in the new column.
- Everything is written as plain text, so a customer who types a formula cannot make your sheet run it.
Rows are written in small batches, so a new lead usually appears within a minute.
Changing the sheet
Your account sends leads to one sheet at a time. Setting up another one replaces it, and any rows still waiting go to the new sheet. Rows already written stay in the old file.
Building an audience list from a sheet
Setting it up
On the setup page, choose the tab and choose Supply an audience list. WizMessage reads the first rows and suggests which column is which. Check its suggestions:
| Field | What to choose |
|---|---|
| Phone number column | Required. The column holding the numbers |
| Name, Email | Optional. Choose Not used to leave them out |
| Custom field 1 to 7 | Optional. Extra details you can use as template variables |
| Do the numbers include a country code? | Yes, or No and the country they belong to, such as IN |
The preview shows the first rows with the exact number each would use. Not a valid number on most rows usually means the country code setting is wrong. Hidden and filtered rows in the sheet are included.
If the tab is empty, the page says so. Add a header row with a phone column and your numbers in Google Sheets, then press Check again.
A new list or an existing one
This choice decides whether people can ever be removed from the list by a sync.
Press Connect sheet to list to finish.
Syncing
Nothing comes in until you press Sync now, and a sheet is never read on a schedule. Press it again whenever the sheet has changed.
The sheet's row then shows the result, for example how many people are in the sheet, how many were added and how many removed. It also lists any rows skipped for an invalid number, by row number, so you can fix them. If nothing changed since last time it says Already up to date.
What a sync changes, and what it leaves alone
- It updates only the columns you mapped. Anything else on a contact is untouched.
- A blank cell keeps what is already stored, so a sheet cannot erase a contact's details.
- Someone who opted out stays opted out, even if they are in the sheet.
- A contact is never deleted. A mirrored list removes people from the list only.
- A contact you delete by hand comes back on the next sync if they are still in the sheet.
- A campaign already running is not affected. A scheduled campaign uses whoever is on the list when it runs.
When a sync asks or stops
A wrong tab looks exactly like a big change, so a large change waits for you. It asks first when a sync would remove over half of a list and at least 3 people, or add more than 100 people and more than twice the list's size on a list that has synced before. The question shows both numbers, and you decide whether to go ahead. If the sheet changes again before you answer, it asks again.
| Message | What it means | What to do |
|---|---|---|
| The phone column is gone | The header was renamed or removed | Rename it back, or press Change setup |
| No valid phone numbers | No row had a usable number | Check the column and the country code setting |
| The sheet has over 50,000 rows | Too many rows of data to sync | Split the sheet into smaller tabs |
| The tab has over 100,000 rows | The tab itself is too large, even if most of it is empty | Delete the empty rows, or use a smaller tab |
| Over your contact limit | The new contacts would not fit your plan | Remove contacts or upgrade |
| The audience list was deleted | Its list no longer exists | Press Change setup and choose a list |
Using a sheet in a chatbot flow
Getting ready
First set up the sheet: choose the tab, choose Be used by flow steps, and press Use in flows. Then, in the flow builder, add a Google Sheets step, choose the sheet and choose an action.
The step sends the customer nothing. It does its work and the flow moves straight on to the next step.
The three actions
Add a row writes a new row. Under Columns to write, give each one a Column name and a value. A value can be fixed text or a variable, such as {phone} for the customer's number. A column the sheet does not have yet is added.
Find a row looks a row up and hands its values to later steps. Fill in:
- Column to match and Value to match, such as a Phone column and
{phone}. - Name for the results: lowercase letters, numbers and underscores, such as
sheet. - Columns to read: the columns you want to use.
Later steps then have these to use, here with sheet as the name and a column called City:
| Variable | What it holds |
|---|---|
{sheet_found} | true if a row matched, false if not. Use this in a Condition |
{sheet_city} | The City value from that row, or blank |
{sheet_error} | Why the sheet could not be read, or blank if it was fine |
A column name becomes part of the variable in lowercase, with spaces and symbols turned into underscores. Use Latin letters for the columns a flow reads: a name written only in another script does not make a predictable variable.
Update a row changes a row that already exists. Choose what to match as with Find a row, then the Columns to write. A blank value leaves that cell as it is. If no row matches decides what happens otherwise: Do nothing, or Add a new row.
How matching works
- The whole value must match, ignoring upper and lower case and spaces at either end. So "John" does not match "Johnny".
- Phone numbers compare on their digits, so
+91 98765 43210matches919876543210. - If several rows match, the first one is used.
{phone}is the customer's number with its country code. A sheet that stores numbers without a country code will not match it.
Matching on something a customer types lets them read any row whose value they can guess.
{phone}is the safe choice, because a customer cannot choose their own number.
Good to know
- Find a row can be a minute or two behind the sheet, because recent results are remembered to keep replies fast.
- A row written by a flow normally appears within a minute. It is sent the same way as a bot lead, so it waits safely if Google has a problem.
- If a sheet cannot be reached, the flow carries on and the customer sees nothing unusual.
{sheet_error}lets you handle that case if you want to. - Test Flow reads your real sheet but never writes to it. To test Add a row or Update a row, send a real WhatsApp message to your number.
Exporting to a new sheet
Press Export to Google Sheets on one of these pages. Each export creates a new file in your Drive and needs no setup.
| Page | What is exported | Named |
|---|---|---|
| Contacts | The contacts, following the filters you have applied | Contacts and the date |
| Bot Leads | Your bot leads | Bot leads and the date |
| Audience lists | The members of one list. Select exactly one list to see the button | The list's name, members and the date |
| A campaign's page | That campaign's recipients | Campaign recipients and the date |
The button shows progress and then becomes Open sheet. Each export can hold as many rows as the matching file download. If an export fails part-way, we delete the partial file, so you can simply try again.
If you have not connected a Google account yet, the button offers to do that first and brings you back. Press it again once you are back.
Keeping it working
What the page shows
| Status | Meaning |
|---|---|
| Connected | Working normally |
| Setup needed | Chosen or created, but not in use until you finish setup |
| Problem | Something stopped it, and the row says what |
| Needs reconnecting | On an account: Google no longer accepts our access |
A sheet may also show Rows waiting to be added. A few waiting rows are normal. A number that keeps growing means the sheet has a problem.
When a sheet stops updating
You get a notification, a push to your phone and a banner on the page, and a reminder each week until it is fixed. Your leads are safe: they stay in Bot Leads, and their rows wait for up to 30 days and are added once the problem is fixed.
If Google itself stays down for many hours, a row that still cannot be written is dropped from the sheet's queue. The lead itself stays in Bot Leads.
| The sheet says | What to do |
|---|---|
| We lost access, or the file was deleted | Restore the file if it was deleted, choose it again with Choose a sheet, then press Try again |
| The Google Drive is full | Free up space in Google Drive, then press Try again |
| The sheet has reached Google's size limit | Remove old rows or use a new sheet, then press Try again |
| Google refused the last update | Press Try again. If it comes back, contact support |
| The tab was deleted | Press Choose another tab |
| Its Google account is disconnected | Connect that account again |
Reconnecting
If an account shows Needs reconnecting, press Reconnect and sign in with that same account. You do not need to choose your sheets again, and waiting rows are then sent. If you sign in with a different account, the page tells you which account holds those sheets.
Removing a sheet or disconnecting
Remove takes one sheet out of WizMessage. The file stays in your Google Drive, unchanged.
Disconnect turns off a whole Google account. Its sheets stop updating, rows still waiting are dropped, and we delete the access we held. New leads stay in Bot Leads but no longer go to a sheet. If you connect the same account again later, its sheets start working again.
You can also remove WizMessage from your Google account at any time, in Google's third-party connections page. Contacts and lists from earlier syncs stay in WizMessage until you delete them, and rows already written stay in your sheets.
On the phone
Everything above works from the WizMessage app too, with the latest version installed. Open Settings, then Google Sheets.
- Signing in opens your browser, because Google does not allow it inside an app. After you choose a sheet, you return to the app and its setup opens. If the app does not come back by itself, open it and the new sheet is waiting under Finish setting up.
- I don't have a sheet yet on the first screen signs you in without the file picker. Create a new sheet then appears.
- Audience lists: on a list, Add people offers Google Sheets as a source. Tap a sheet under Your sheets to sync it or change its setup.
- Exports: the export button asks where to send the data. Choose New Google Sheet, and the app tells you when it is ready.
- Flow steps: the on-device flow builder has the same Google Sheets step, and a flow you build on the phone also opens on the web.
An older version of the app shows a flow's Google Sheets step as a plain box. It keeps the step's settings when you save the flow, and the step still runs.
Reference
Limits
| Item | Limit |
|---|---|
| Rows of data in one sync | 50,000 |
| Rows in a tab being synced, including empty ones | 100,000 |
| Custom fields mapped from a sheet | 7 |
| Columns one flow step can write | 50 |
| Columns one flow step can read | 20 |
| Characters kept from each value read by a flow | 1,000 |
| Rows held waiting for one sheet | 50,000 |
| How long waiting rows are held | 30 days |
Good to know
- Row 1 of the tab is the header for bot leads and flow steps. Audience lists find the header row themselves, so a title above it is fine.
- Dates in bot lead rows are in server time, the same as the Bot Leads file download.
- Renaming a tab or a file breaks nothing, because WizMessage tracks them by their ID rather than their name.