If you run a small business or manage a massive sales team, you might have a Google Sheet filled with customer names and phone numbers. To contact them quickly, you might want to create a column containing a custom, clickable WhatsApp link that automatically drafts a pre-written message (e.g., “Hello [Name], your order is ready!”).
The standard WhatsApp API URL structure is: https://wa.me/1234567890?text=YourMessageHere
The problem arises when you try to insert spaces or special characters into that URL. If you try to create a link that says ?text=Hello John!, the web browser will instantly break because standard URLs absolutely cannot contain physical spaces or exclamation points. To fix this, you must “URL encode” the text—translating the space into %20 and the exclamation point into %21. Rather than doing this manually, you can use the ENCODEURL function to instantly sanitize the text.
The Syntax of ENCODEURL
The function is incredibly simple. It takes a string of messy, human-readable text and mathematically translates every single invalid character into its exact, browser-safe hexadecimal equivalent.
=ENCODEURL(text_to_encode)
Step 1: Set Up Your Database
Assume your spreadsheet is set up as follows:
- Cell A2: Phone Number (e.g.,
15555551234) - Cell B2: The message you want to send (e.g.,
Hello John! Your order is ready.)
Step 2: Build the Dynamic URL
We are going to use the HYPERLINK function to make the link clickable, and the ENCODEURL function to sanitize the text inside cell B2.
- Click on cell C2.
- Paste the following formula:
=HYPERLINK("https://wa.me/" & A2 & "?text=" & ENCODEURL(B2), "Send WhatsApp") - Press Enter.
How the Formula Works
Let’s break down the concatenation (the & symbols):
- The formula starts with the hardcoded base API link:
https://wa.me/ - It attaches the phone number from A2.
- It attaches the required API trigger:
?text= - The Magic: It looks at B2 (“Hello John! Your order is ready.”), runs it through
ENCODEURL, and outputs the perfectly safe string:Hello%20John%21%20Your%20order%20is%20ready. - Finally, it wraps the entire ugly URL behind the clean, clickable hyperlink text: “Send WhatsApp”.
The Result
You can now drag that formula down 1,000 rows. When your sales team clicks the “Send WhatsApp” link in any row, their browser will seamlessly open the WhatsApp application, automatically target the correct phone number, and perfectly paste the custom sentence into the chat box, complete with all spaces and punctuation intact.