While there are countless CRM and ERP systems out there, for companies small and large, I still see businesses and teams relying on the tried and tested spreadsheet to keep track of and automate their work:
- Fulfillment teams using a master spreadsheet to store new orders and returns
- Sales teams tracking leads and opportunities
- Finance teams keeping tracking of overdue invoices and collection interactions
My explanation for this phenomenon is that when faced with the need to model and keep track of business processes, people default to the “most powerful” and “best known” tool in their toolbox; and that tends to be, more often than not, the spreadsheet.
So, how can businesses that run on spreadsheets use MessageBird to send notifications to their customers?
Making spreadsheets speak
Now that you and your team have created a spreadsheet with beautiful formulas to meticulously track your work and work to be done, how do you inform team members of their tasks? Or how do you notify a customer that progress has been made? Our customers often ask:
- “Can we send an SMS notification to the customer when their order goes to “accepted”?”
- “Can we send an WhatsApp message to the collection team when the bill goes to overdue?”
In this article I will show you how to add SMS and WhatsApp notifications to a Google Sheet using MessageBird’s FlowBuilder and the scripting capabilities of Google Sheets.
What you’ll need:
- A Google Sheet (you need edit rights)
- A MessageBird account
- One or multiple channels installed: SMS, WhatsApp, Telegram, Facebook Messenger (depending on which communication channel you would like to use)
Creating the Flow with Flow Builder
If you’re unfamiliar, Flow Builder is our drag and drop communication automation engine. Each Flow begins with a Trigger, like an incoming message or Webhook, and is followed by a set of steps, or actions like reply messages. For our spreadsheet communication, we will create a new Flow with a Webhook trigger with steps for sending our messages. . In this case, our message will be a predefined HSM template on our WhatsApp channel.
For the trigger, we’ll add a couple of variables: name and phoneNumber. We will pull that data from the spreadsheet.
- The phoneNumber will be the recipient’s phone number (WhatsApp needs this to identify the recipient)
- The name will be used to personalize the sent message, but depending on your use case, you can use these variables to represent different information, like orderId, balance, arrival date, etc.
We will add just one step to our flow, which is a Send WhatsApp HSM step, and we’ll use the previously defined variables, name and phoneNumber, from our trigger:
When we will publish the flow we will be shown the address of the newly created webhook and how to pass the parameters.
Adding the trigger to Google Spreadsheet
In the spreadsheet we want to modify, access the Script Editor under the Tools tab:
In your function, make sure to replace the value of addYourWebhookUrlHere with the value received from FlowBuilder above.
Three Common Implementations
We often get requests from customers to send a notification under 3 different circumstances: 1) on button press, 2) on value change, or 3) on a certain time interval. Here are examples showing off how to achieve each of those.
Send notification upon pressing a button
To implement this scenario, you’ll need to write another function that looks something like the code below:
We will connect that function to a button on the sheet by adding a Drawing in the sheet and specifying that the sendHSMforEachRow function should be ran when clicked:
Send notifications when changing a value in a row
For this scenario, the function code will look something like what we have below:
We will connect this function to the sheet’s Edit event by adding an onEdit trigger:
Now anytime the value is changed, the message is sent.
Send notifications every hour, for new rows
For time events, the function code will look something like what we have below:
In Google Sheets, we will create a temporal trigger and that will run the runEveryHour function on specific intervals (in our case every hour).
Conclusion (and template)
While Spreadsheets might not be the best tool for businesses, their power and functionality are very real. We hope users can take advantage of these implementations and create better customer experiences.
To make it easier, see the sample google spreadsheet here (make a copy).
Note: during the development process, Google Sheet will prompt you to authorize specific triggers (like the button trigger, the onEdit trigger and the temporal trigger), go ahead and accept when prompted.