How I used Google Sheets and Google Apps Script to send a WhatsApp Message.

How I used Google Sheets and Google Apps Script to send a WhatsApp Message with each new entry.

Listen

Share

Photo by Kevin Ku

Recently I Have been working more with Google Sheets, mainly for a clients project and I have discovered how versatile and powerful the whole Google Suite is. Like many of you, I used Sheets and others from time to time, When I want to write a document o when I want to use sheets to accomplish some tasks. But I never gave much thought to I just did the work and moved on, I never tried to find out if it had more potential than what it seemed it was created for.

So recently a client wanted to receive a message each time a new row was added to their Google Sheets, and I had no choice but to find a way to implement this. So without any further due let's see how to implement this solution.

WhatsApp API Account

To be able to send WhatsApp Messages, youre going to need a Meta Account, once created youll be able to create an app, select a type and click next, then youll have to choose a name for your app and set a contact email. Once you have created the app you will have access to different types of products, you want to select and set up the WhatsApp API.

Once you get to this screen your WhatsApp API is almost ready. You can already send a test message to your phone with the test number. You can use it with the following POST command:

curl -i -X POST \\
  [https://graph.facebook.com/v15.0/](https://graph.facebook.com/v15.0/111115438387090/messages)**Phone\_number\_ID**[/messages](https://graph.facebook.com/v15.0/111115438387090/messages) \\
  -H 'Authorization: Bearer **TEMPORARY\_ACCESS\_TOKEN**' \\
  -H 'Content-Type: application/json' \\
  -d '{ "messaging\_product": "whatsapp", "to": "**YOUR PHONE NUMBER**", "type": "template", "template": { "name": "hello\_world", "language": { "code": "en\_US" } } }'

Google Sheets

Now in Google Sheets select Extensions then Apps Script to write the code to check the page and then send the messages.

var ss = SpreadsheetApp.openById(id);
var sheet = ss.getSheetByName("Sheet1");
var POST\_URL = "YOUR\_WHATSAPP\_API URL";
var WHATSAPP\_ACCESS\_TOKEN ="YOUR\_TEMPORARY\_ACCESS\_TOKEN";
var phone\_number = "15551234567";

/\*\*
\* Function to get the paramereters of the post request
\* and sent the WhatsApp message
\*\*/
function doPost(request) {
  var firstName = request.parameter\["fName"\];
  var lastName = request.parameter\["lName"\];
  var phoneNumber = request.parameter\["phoneNumber"\];

  appendNewRow(firstName, lastName, phoneNumber);

  var options = {
      "method": "POST",
      "headers": { Authorization: \`Bearer ${WHATSAPP\_ACCESS\_TOKEN}\` },
      "contentType": "application/json",
      "payload": JSON.stringify({
          type: 'template',
          messaging\_product: 'whatsapp',
          to: phone\_number,
          template: {
            name: "WHATSAPP\_TEMPLATE",
            language: { code: "en-US" },
            components: \[
              {
                type: 'body',
                parameters: \[
                  { type: 'text', text: firstName.trim()},
                  { type: 'text', text: lastName.trim()},
                  { type: 'text', text: phoneNumber.trim()}
                \],
              },
            \],
          },
        }),
  };
  try {
    console.log(options);
    UrlFetchApp.fetch(POST\_URL,options);
  }catch (error){
    console.log(error);
  }
}

/\*\*
\* Function to post a new line to a sheet of your choice
\*\*/
function appendNewRow(firstName, lastName, phoneNumber){
  sheet.appendRow(\[firstName, lastName, phoneNumber\]);
}

Now that the script is ready, you must deploy it and get an URL to POST to the script. You can set a description, and execute it as your-email@gmail.com, then you can deploy the Web app.

Now copy the URL and then you can test it with Postman or something similar to send a POST request.

Google sheet App Script should receive the request to add a new row to the google sheets and also send the message to your chosen WhatsApp number.

Conclusion.

With this, you can enter new information into Google Sheets and also send the user a message using the WhatsApp API at the same time. Do you want to implement this solution, if so you can follow this guide?