How To Send WhatsApp Messages With A Google Sheets using the WhatsApp API

How To Send WhatsApp Messages With A Google Sheets using the WhatsApp API

In this article, we’ll show you how to send customized WhatsApp messages straight from your Google Sheets by using the new WhatsApp API with Google Apps Script. You can use this technique to have Google Forms notify you via WhatsApp anytime a new submission is made.

Create a WhatsApp App:

Go to developers.facebook.com and select the “Create App” button to begin going. As for the app type, pick “Business”. Avoid using Facebook trademarks like Facebook or WhatsApp in the name of your app. Once built, select the “WhatsApp” button to give your app the ability to send messages using WhatsApp.

You must connect your Facebook business account to your WhatsApp app. You can make a new business account if you don’t already have one.

Add Recipient’s Phone Number:

Facebook will provide you a test WhatsApp phone number that will serve as your app’s default sending address. Up to 5 phone numbers may be added as recipients during the development process without incurring any fees.

A 23-hour temporary access token will be generated by your WhatsApp application. This token should be noted because we’ll need it in the following step.

By choosing them from the “Recipient Phone Number” menu, you can now add up to 5 distinct WhatsApp phone numbers as recipients. These new numbers will obtain a verification code, and you will only be able to send WhatsApp messages to verified numbers after that.

Create WhatsApp Message Template:

Create a new WhatsApp message template by going to the template manager. We’ll select the category “Account Update” for this example, and we’ll give the template a special name. Remember to write down the code language if you communicate in a language other than English because it is required for message delivery.

Write the Personalized Message:

We’ll utilize variable parameters like {{1}} and {{2}} in our message template to indicate the customer name and item name, respectively. When sending messages, these variables will be changed to reflect the real data from the Google Sheet.

Send WhatsApp Messages:

Let’s set up the Google Sheet to deliver customized WhatsApp messages automatically now that all Facebook/WhatsApp configurations are complete.

To copy the WhatsApp Sheet to your personal Google account, click here.

Include the phone numbers (with country code) in the Google Sheet’s “Phone number” column. Add only the figures you have already checked in the previous step.

To open the underlying script, select “Apps Script” from the “Extensions” menu. The values you copied before should be used in place of “WHATSAPP_ACCESS_TOKEN” and “WHATSAPP_TEMPLATE_NAME”.

When you click the “Run” button inside the Apps Script editor, the WhatsApp message is immediately sent to the selected phone numbers.

Technical Details: How WhatsApp API Works with Google Apps Script

For the messages, the Google Apps Script connects to Google Sheets and retrieves customer information, including phone numbers.

// Get data from Google Sheets for sending messages through WhatsApp
const getSheetData_ = () => {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [header, ...rows] = sheet.getDataRange().getDisplayValues();
  const data = [];
  rows.forEach((row) => {
    const recipient = {};
    header.forEach((title, column) => {
      recipient[title] = row[column];
    });
    data.push(recipient);
  });
  return data;
};

// Send Message with WhatsApp Cloud API
const sendMessage_ = (e) => {
  const apiUrl = 'https://graph.facebook.com/v13.0/114746974570888/messages';
  const request = UrlFetchApp.fetch(apiUrl, {
    muteHttpExceptions: true,
    method: 'POST',
    headers: {
      Authorization: `Bearer ${WHATSAPP_ACCESS_TOKEN}`,
      'Content-Type': 'application/json',
    },
    payload: JSON.stringify({
      type: 'template',
      messaging_product: 'whatsapp',
      to: e.recipient_number,
      template: {
        name: WHATSAPP_TEMPLATE_NAME,
        language: { code: LANGUAGE_CODE },
        components: [
          {
            type: 'body',
            parameters: [
              { type: 'text', text: e.customer_name },
              { type: 'text', text: e.item_name },
              { type: 'text', text: e.delivery_date },
            ],
          },
        ],
      },
    }),
  });

  const { error } = JSON.parse(request);

  if (error) {
    Logger.log(`?? ${error}`);
  } else {
    Logger.log(`Message sent to ${e.recipient_number}`);
  }
};

const main = () => {
  getSheetData_().forEach((row) => {
    sendMessage_({
      recipient_number: row['Phone Number'].replace(/[^\d]/g, ''),
      customer_name: row['Customer Name'],
      item_name: row['Item Name'],
      delivery_date: row['Delivery Date'],
    });
  });
};

I’m done now! You now understand how to send customized WhatsApp messages straight from your Google Sheets using the WhatsApp API and Google Apps Script. Happy texting!

Leave a Comment