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 phone.map((row, index) => WHATSAPP(row[0], message[index][0]), web);
  }
  const phoneNumber = String(phone).replace(/[^\d]/g, '');
  const messageText = encodeURIComponent(message);
  return web === true
    ? `https://web.whatsapp.com/send?phone=${phoneNumber}&text=${messageText}`
    : `https://wa.me/${phoneNumber}?text=${messageText}`;
}


source:https://ift.tt/QDRHlSm

Comments

Post a Comment

Popular posts from this blog

The 101 Most Useful Websites

-
Image
Presenting the 101 most useful websites on the internet. These sites, well most of them, solve at least one problem really well and they all have simple web addresses (URLs) that you can memorize thus saving you a trip to Google. The Most Useful Websites and Web Apps ctrlq.org/screenshots  – for capturing  screenshots of web pages  on mobile and desktops. dictation.io  – online voice recognition in the browser itself. zerodollarmovies.com  – find full-length movies on YouTube. screenr.com  – record movies of your desktop and send them straight to YouTube. goo.gl  – shorten long URLs and convert URLs into  QR codes . unfurlr.come  – find the original URL that’s hiding behind a short URL. qClock  – find the local time of a city using a  Google Map . copypastecharacter.com  – copy special characters that aren’t on your keyboard. postpost.com  – a better search engine for twitter. lovelycharts.com  – c...
Read more

Compare MacBook Prices Worldwide with Google Sheets

-
Image
Looking to buy the new Macbook Pro with M3 chips? Wondering if it would be cheaper to purchase a Macbook in your local Apple store, or ask a friend who is travelling from Singapore or Japan to bring one for you? Here’s a Google Sheet that can help you compare prices of MacBooks in different countries. It takes the current prices of MacBooks from Apple online stores in different countries and converts them to a common currency (US Dollars). The exchange rates are fetched directly from Google Finance so the prices will update automatically when the exchange rates change. How the Macbook Price Comparison Sheet Works I have written a Node.js script that fetches the current prices of MacBooks from the Apple website and writes them to Google Sheets. Here’s the code that scrapes the Apple website and parses the HTML to extract the prices. Get Macbook Prices from Apple Website Apple uses JSON-LD to embed structured pricing data in their web pages that can be easily parsed using che...
Read more

Interesting Websites to Visit When You’re Bored

-
The 15 Interesting Websites Dear Photograph  – The website features photographs of people holding up old pictures that were taken at the exact spot several years ago. Some of the best photographs are now compiled in a  book  (dearphotograph.me). Textastrophe  – This website is a reminder of why you shouldn’t post your cell phone number on public websites. The creator starts a text conversation with random numbers found on Craigslist ads and publishes the transcript on the internet. The chats are funny and I hope its real because  screenshots can be faked  too (textastrophe.com). Stratocam  – This is like an online self-running slideshow showcasing some of the best satellite images found on Google Maps. Run the website in full-screen mode (Ctrl+Shift+F) and you may discover pretty and unexpected place on our beautiful earth (stratocam.com). Awkward Family Photos  – This is a place where people share embarrassing pictures of their loved ones i...
Read more