Send WhatsApp Messages with a Google Sheets Function

Learn how to send WhatsApp messages with a Google Sheets function

In a previous tutorial, you learned how to send WhatsApp messages from Google Sheets using the official WhatsApp API. The first 1,000 messages per month for each WhatsApp Business Account are free and then you pay per use based on the country of the message sender and the message recipient.

WhatsApp API Pricing

For instance, if you are sending a WhatsApp message from the US phone number to a WhatsApp user in France, the cost would be 14¢ per message. However, if you send messages from WhatsApp number in India to another number in India, the cost would be around 0.006¢ per message. The rate cards for WhatsApp API pricing are available here.

In addition to the cost factor, the WhatsApp Business API requires you to have a verified business on Facebook (see verification requirements) and the terms require that you will only send message to WhatsApp users who have opted-in to receive future messages from you on WhatsApp.

The other limitation of WhatsApp API is that you can only send messages that are based on templates pre-approved by WhatsApp. You can however send free-form messages within 24 hours of the last user message in a conversation.

Send WhatsApp Message

WhatsApp Function for Google Sheets

If you are a small business that is looking for an alternate route to message customers on WhatsApp without paying for the API, here’s a semi-automated technique. You can use the Click to Chat feature of WhatsApp to quickly send a personalized message to any phone number that’s registered on WhatsApp.

For this example, we have a sheet that list the customer’s name, amount that they have to pay and the due date for payment. We can use the CONCATENATE or TEXTJOIN function of Google Sheets to create a personalized message for each client in column D.

Create Personalized Message

The column E of the Google Sheet contains the phone numbers of each WhatsApp user. We can use the WHATSAPP custom function to create a personalized chat link for different customers in the Google Sheet. When you click this chat link, it automatically open a WhatsApp conversation with the user and the message is pre-filled in the chat box.

This method does require a few extra click but there’s no cost involved and it works for both WhatsApp Business and WhatsApp personal accounts.

WhatsApp Click to Chat

WhatsApp Function

Here’s the underlying WHATSAPP function that generates the Click to Chat link in Google Sheets. It also supports Array Formulas.

The third parameter determines whether the link should launch the WhatsApp website or the WhatsApp desktop client. You can play with the live sheet here.

 * Create WhatsApp Click to Chat Link
 * @param {string} phone The phone number with country code
 * @param {string} message The text message
 * @param {boolean} web Open the message in WhatsApp web?
 * @return The pre-filled message link for WhatsApp.
 * @customfunction
function WHATSAPP(phone, message, web) {
  if (Array.isArray(phone)) {
    return, index) => WHATSAPP(row[0], message[index][0]), web);
  const phoneNumber = String(phone).replace(/[^\d]/g, '');
  const messageText = encodeURIComponent(message);
  return web === true
    ? `${phoneNumber}&text=${messageText}`
    : `${phoneNumber}?text=${messageText}`;
Amit Agarwal

Amit Agarwal

Google Developer Expert, Google Cloud Champion

Amit Agarwal is a Google Developer Expert in Google Workspace and Google Apps Script. He holds an engineering degree in Computer Science (I.I.T.) and is the first professional blogger in India.

Amit has developed several popular Google add-ons including Mail Merge for Gmail and Document Studio. Read more on Lifehacker and YourStory


Awards & Titles

Digital Inspiration has won several awards since it's launch in 2004.

Google Developer Expert

Google Developer Expert

Google awarded us the Google Developer Expert award recogizing our work in Google Workspace.

ProductHunt Golden Kitty

ProductHunt Golden Kitty

Our Gmail tool won the Lifehack of the Year award at ProductHunt Golden Kitty Awards in 2017.

Microsoft MVP Alumni

Microsoft MVP Alumni

Microsoft awarded us the Most Valuable Professional (MVP) title for 5 years in a row.

Google Cloud Champion

Google Cloud Champion

Google awarded us the Champion Innovator title recognizing our technical skill and expertise.

Email Newsletter

Sign up for our email newsletter to stay up to date.

We will never send any spam emails. Promise.