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:
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?
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:
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:
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.
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.
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.
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.
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:
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.
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).
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.