# 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.
* [Before we start: What you’ll need](#h_01FCDTAPD6SMR83KC2SYN55CM7)
* [Step 1: Set up in Flow Builder](#h_01FCDTASP4XDP2CGF3ENMJR3B8)
* [Create a Webhook trigger flow on Flow Builder](#h_01FCDTAXZB51H708ASVNCSRRB8)
* [Add your template variables](#h_01FCDTB3B1QJCFFEJNG2DQ8W6A)
* [\[If you are sending a text template\] Add a “Send WhatsApp template message” step](#h_01FCDTB7CDTMMVDENH78SJYRCF)
* [\[If you are sending a media template\] Add a HTTP Request step](#h_01FCDTBDKT56RSKK5ZT54HGQZZ)
* [\[If you are sending a media template\] Add a HTTP Request step](#h_01FCDTBDKT56RSKK5ZT54HGQZZ)
* [Publish the flow](#h_01FCDTBRM9GJCQK8F7S4PJ9RC8)
* [Step 2: Prepare your Google Sheet](#h_01FCDTBYN5GJFGNPDCQ8FK66H4)
* [Create a copy of the spreadsheet template](#h_01FCDTC4DJEZZP5MNVMEER5D2N)
* [Input your Webhook URL](#h_01FCDTCAD95Q44Q30JT8R04161)
* [Ensure that the "name" and "phoneNumber" variables match](#h_01FCDTCEPYPWRZZ6N82W702NC3)
* [Triggering campaigns from the Google Sheet](#h_01FCDTCM5NRYWNW8VBGA0DVNP2)
* [Copy the function name sendHSMforEachRow into the button](#h_01FCDTCRMNMDNXBXRAVA25NZYF)
* [Run your campaign by click on the button](#h_01FCDTCXX23E1N2J7DZ8SQF4B0)
### Before we start: What you’ll need
* A Google Sheet for which you have edit rights
* You can make a copy of the template Google Sheet [here](https://docs.google.com/spreadsheets/d/10qQan4wObTe3BrB1Xh2mWp8pxVW6dlv0y7q4XRnUU40/edit#gid=0)
* A MessageBird account
* One or multiple WhatsApp channels installed
* Approved Templates. These can be either Message Templates or Media Templates.
### Step 1: Set up in Flow Builder
1. **Create a Webhook trigger flow on** [**Flow Builder**](https://dashboard.messagebird.com/en-sg/flow-builder)\
\
\
2. **Add your template variables**
In 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.
\
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**
* In the URL field add [**https://conversations.messagebird.com/v1/send**](https://conversations.messagebird.com/v1/send)
* Enable the Body option and paste the body of the request. You can check our [guide below](#h_01FCDX9D7D5CF54J3V8HZK1FQD) on how to format this HTTP request. This is the same HTTP request that you can refer to in our Developer Documentation [here](https://developers.messagebird.com/api/conversations/#whatsapp-api).
\
* 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.
\
### Step 2: Prepare your Google Sheet
1. **Create a copy of the spreadsheet template**
Once you make a copy of [this spreadsheet template](https://docs.google.com/spreadsheets/d/10qQan4wObTe3BrB1Xh2mWp8pxVW6dlv0y7q4XRnUU40/edit?usp=sharing), access the Script Editor in Google Sheets under the Tools tab:
Once you get to the Script Editor page, you will see this:
2. **Input your Webhook URL**
In 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](https://developers.google.com/apps-script/guides/sheets/functions) to our sheet that calls our FlowBuilder webhook by using the [UrlFetchApp](https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app) class to make an HttpRequest. This function is shown in the screenshot below and sample code for that function can be found [here](#h_01FCDZ0YBB0J27291XZEA8ARE7).
In your first function, replace the value of **addyourWebhookUrlHere** with the value received from FlowBuilder in [step 1.4](#h_01FCDTBRM9GJCQK8F7S4PJ9RC8) 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 match**
To 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 Sheet**
To 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.](#h_01FCDTCRMNMDNXBXRAVA25NZYF)
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 button**
Back 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**).
6. **Run your campaign by click on the button**
Once 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](https://docs.bird.com/connectivity-platform/contacts/formatting-your-contact-numbers)\
\
When you are ready to send your messages, press the **Click to Run** button
Google Sheet will prompt you to authorise specific triggers as shown below. Go ahead and accept when you are prompted.
Once 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!
### \[For HTTP Request Step] Prepare the API call
There are four parts to the API call:
* [URL endpoint](#h_01FCDXAJG1SNPTTCR0N8MJ0VQN)
* [Method](#h_01FCDXAT535M6CM3HF33VHGD2A)
* [Header](#h_01FCDXB1RHWA96VR8TBKRYYJ7N)
* [Body](#h_01FCDXB785Q6ZV1BZ72FJJ6D8D)
#### URL endpoint
The URL endpoint will be:
#### Method
The Method for the API will be **POST**
#### Header
The header will be:
* Key: Authentication
* Value: AccessKey {{your\_live\_access\_key}}
* You can find your Live access key in the Dashboard [here](https://dashboard.messagebird.com/en-gb/developers/access).
#### Body
The request body will have three “parts”:
* [Basic information](#h_01FCDYS9FXHGBB8M978EXE8FQ7)
* channel\_id: You can find this information [here](https://dashboard.messagebird.com/en/channels/whatsapp)
* namespace: If in doubt, check with your TAM or
* template\_name: This must be exactly the same as what you have submitted for approval and only in small letters
* [Header (media) information](#h_01FCDYSGAP00DK5TN87WJBKJYF)
* [Template body information](#h_01FCDYSVANQS4V28ETFF1FFG55)
**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*"
### \[For Google Sheet Script] Function: Call the FlowBuilder webhook
```
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);
}
```
### \[For Google Sheet Script] Function: Trigger WhatsApp messages by pressing a button
```
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");
}
}
}
```
