Comment on page
How to Send WhatsApp Media & Text Templates Using Google Sheets
This guide will show you how to add WhatsApp notifications to a Google Sheet using MessageBird’s FlowBuilder and the scripting capabilities of Google Sheets.
- A Google Sheet for which you have edit rights
- A MessageBird account
- One or multiple WhatsApp channels installed
- Approved Templates. These can be either Message Templates or Media Templates.
- 1.
- 2.Add your template variablesIn this example, we will use the name and phoneNumber variables. We will pull that data later from the Google Spreadsheet.
- The phoneNumber will be the recipient’s phone number (WhatsApp needs this to identify the recipient)
- The name will be used to personalise the sent message, but depending on your use case, you can use these variables to represent different information, like orderId, balance, arrival date, etc.
image13.png - 3.[If you are sending a text template] Add a “Send WhatsApp template message” step
[If you are sending a media template] Add a HTTP Request step
- Set the Method to POST
- Enable the Body option and paste the body of the request. You can check our guide below on how to format this HTTP request. This is the same HTTP request that you can refer to in our Developer Documentation here.
- Set Content-Type header to application/json
- 4.Publish the flow
And that’s it! Make sure to trigger the Webhook flow with the correct variable and values in the body of the request. Remember to copy the URL below as you will be needing it later on.
- 1.Create a copy of the spreadsheet templateOnce you make a copy of this spreadsheet template, access the Script Editor in Google Sheets under the Tools tab:
Once you get to the Script Editor page, you will see this:
Screenshot_2021-08-12_at_15.50.09.png - 2.Input your Webhook URLIn the Script Editor you will see several functions. Functions are "self contained" modules of code that accomplish a specific task. This first code you see is a generic function to our sheet that calls our FlowBuilder webhook by using the UrlFetchApp class to make an HttpRequest. This function is shown in the screenshot below and sample code for that function can be found here.In your first function, replace the value of addyourWebhookUrlHere with the value received from FlowBuilder in step 1.4 by copying the full link. In the image below, you can see where you should add the URL you received in step 1.4:
- 3.Ensure that the "name" and "phoneNumber" variables matchTo successfully send messages, we should make sure that the variables are listed in the Sheet as well as the code.In this example we have used the name and phoneNumber variables. You can add more variables as needed but do remember to check that all variables are accounted for in the code. This should match in the following places:
- 1.The variables you have included in the code
- 2.The cell names in your file
- 4.Triggering campaigns from the Google SheetTo send your WhatsApp campaign upon pressing a button like below, we need to take a few more steps. For this we will look at the second function, as pictured below. We'll do the following:
- 1.Where the 1 is indicated in the image, make sure that this matches the Sheet Name where you are inputting the contact details.
- 2.Where the 2 is indicated in the image, select the function name sendHSMforEachRow and copy this. We will use this in step 2.5.
- 3.Where the 3 is indicated in the image, make sure that all variables are listed here and these matcht the respective columns in your Sheet. No changes need to be made if you only require the variables name and phoneNumber. If you are adding extra variables, please see below for the corresponding number-column combination: // A B C D E F G H I J K L M // 0 1 2 3 4 5 6 7 8 9 10 11 12
- 4.Where the 4 is indicated in the image, you will see code related to the status of the messages. This line of code will make sure the Sheet updates the indicated column with the corresponding status of any message. In the example above, we are asking the Sheet to update the 3rd column, or column C, with the status of the message. If you would like to change the output column, simply change the number according to the number-column combination listed above.
- 5.Copy the function name sendHSMforEachRow into the buttonBack on the spreadsheet, right-click on the button and press the three buttons on the top-right and click on “Assign a script”
You will then be prompted to assign a script. Here, paste the function name which you copied in step 2.4.2 (sendHSMforEachRow).
image15.png - 6.Run your campaign by click on the buttonOnce this is done, you are now ready to run your campaign. Input the variables (name, phoneNumber) on column A and B. Make sure your phone numbers are formatted correctly and these contain the country code. For more information about formatting, check here: Formatting your contact numbers When you are ready to send your messages, press the Click to Run button
image10.pngGoogle Sheet will prompt you to authorise specific triggers as shown below. Go ahead and accept when you are prompted.
image9.pngOnce the script has run, you will see that the MessageStatus column is updated to “SENT". You have now sent your first WhatsApp messages via Google Sheets!
There are four parts to the API call:
The Method for the API will be POST
The header will be:
- Key: Authentication
- Value: AccessKey {{your_live_access_key}}
The request body will have three “parts”:
- namespace: If in doubt, check with your TAM or [email protected]
- template_name: This must be exactly the same as what you have submitted for approval and only in small letters
Basic information
{
"to": "{{phoneNumber}}",
"type": "hsm",
"from": "{{channel_id}}",
"content": {
"hsm": {
"namespace": "{{namespace}}",
"templateName": "{{template_name}}",
"language": {
"policy": "deterministic",
"code": "{{language_code}}"
},
Header information
"components": [
{
"type": "header",
"parameters": [
{
"type": "{{image_or_document}}",
"image": {
"url": "{{media_url}}"
}
}
]
},
Template body information
{
"type": "body",
"parameters": [
{
"type": "text",
"text": "{{variable1_name}}"
},
{
"type": "text",
"text": "{{variable2_name}}"
},
{
"type": "text",
"text": "{{variable3_name}}"
}
]
}
- The example above is for a template with 3 variables. If it has 1 variable, for example, you will have to remove the extra text values:
{
"type": "body",
"parameters": [
{
"type": "text",
"text": "{{variable1_name}}"
}
]
}
(Please note that the last variable never has a comma (,) at the end)
Put together, below is an example you can use:
{
"to": "{{phoneNumber}}"
"type": "hsm",
"from": "6d77e6fe-d700-4440-8b54-817099b4cea7",
"content": {
"hsm": {
"namespace": "a8c8dba0_0613_4926_9311_84125fdd8ff6",
"templateName": "cgb_add_commission_1",
"language": {
"policy": "deterministic",
"code": "id"
},
"components": [
{
"type": "header",
"parameters": [
{
"type": "image",
"image": {
"url": "https://p-9WF55Xm.t2.n0.cdn.getcloudapp.com/items/o0ueXNoj/d205236e-2433-484c-980b-146dc514435c.png?v=cbf00734f6efba862506165e0f926649"
}
}
]
},
{
"type": "body",
"parameters": [
{
"type": "text",
"text": "{{variable1_name}}"
}
]
}
]
}
}
}
Note: the {{variable1_name}} can also be replaced with a static value. This applies if you want to use the same value for that variable for all messages. To do so, you can replace "{{variable1_name}}" with a "variable1_value"
function callFLBflow(name, phoneNumber) {
var url = 'addyourWebhookUrlHere';
var data = {
"name": name,
"phoneNumber": phoneNumber
};
var options = {
'method' : 'post',
'contentType': 'application/json',
// Convert the JavaScript object to a JSON string.
'payload' : JSON.stringify(data)
};
UrlFetchApp.fetch(url, options);
}
var onDemandSheetName = "On Demand";
function sendHSMforEachRow()
{
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName(onDemandSheetName);
var dataRange=sheet.getDataRange();
var data = dataRange.getValues();
// A B C D E F G H I J K L M
// 0 1 2 3 4 5 6 7 8 9 10 11 12
for (i in data) {
//get the message Status from Column C
var messageStatus = data[i][2];
//get the user's phone number from column B
var phoneNumber = data[i][1];
//get the user's name from column A
var name = data[i][0];
//only sent for valid row data and if messageStatus is not set
if(typeof(phoneNumber)!='undefined' && phoneNumber != null && phoneNumber && phoneNumber!=="" && messageStatus =="") {
callFLBflow(name,phoneNumber);
var cellToUpdate = dataRange.getCell(Number(i)+1,3);
cellToUpdate.setValue("SENT");
}
}
}