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

  • A Google Sheet for which you have edit rights

    • You can make a copy of the template Google Sheet here

  • 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

  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

    • 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.

Step 2: Prepare your Google Sheet

  1. Create a copy of the spreadsheet template

    Once 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:

  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 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 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.

    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 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

The URL endpoint will be: https://conversations.messagebird.com/v1/send

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.

Body

The request body will have three “parts”:

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");
   }
 }
}
PreviousFetching data during conversations with Flow BuilderNextHow to block SPAM contacts in Inbox

Last updated