In this article we will show you how to send a WhatsApp message from Google Sheets in just a few clicks. Simply follow the steps below.
There is no official integration between WhatsApp and Google Sheets but there is a WhatsApp Application Programming Interface (API) you can connect to that allows you to send WhatsApp messages from Google Sheets for free.
Follow the Download instructions for your operating system or use WhatsApp Web in your browser (for older Mac Operating Systems).
Connect your account via your mobile app by scanning the QR code that is displayed on your desktop.
In our example spreadsheet we will add the following details:
You can add more columns if necessary.
We will leave our message column blank as we will be adding dynamic messages in the next steps. You can add static text in this column if you wish to do so.
Tip: Input your phone number with their area code in the beginning of the 10 digit mobile number.
When encoding the phone number, to ensure that the addition sign does not become a function, follow the format:
=“+(Area Code)(10 digit number)”
Remember that the quotation marks are important to display the correct number format.
We can create dynamic messages that can change content based on other cells.
For our example, we will make messages that change based on our “Name” Column.
To be able to do so in our example, we will use this formula in cell C2 and drag it down to other cells:
="Hi "&A2&". I'm learning how to send WhatsApp Messages from Google Sheets with Lido Tutorials!"
To use this formula for other combinations of text, simply enclose your non-changing (static) text in quotation marks (“”) and add your cell references for the dynamic text by using the ampersand sign (&). Use the & sign before/after text you want to join together.
The hyperlink to the WhatsApp API will be in the form of:
=hyperlink("https://api.whatsapp.com/send?phone="& phone number &"&text="& message,"display text")
Formula Breakdown:
Hyperlink: Creates a link to the WhatsApp API.
Phone number: References the cell containing the mobile number, in our example this is in column B so we will reference cell B2.
Message: This will reference the cell containing our dynamic message and will be cell C2 for our example.
Display Text: This is the text that will become our hyperlink text. We will make this display “Send Message” for our current workbook.
Following the breakdown, the final formula for cell D2 will be:
=hyperlink("https://api.whatsapp.com/send?phone="&B2&"&text="&C2,"Send Message")
Simply copy this formula or drag it down to the other cells to be able to create hyperlinks for all of the contacts in your sheet.
Hyperlinked cells will be blue and underlined. Our example workbook when all the WhatsApp links to send the message have been added.
The link will redirect you to the application you’ve installed.
Give permission and this will now launch the WhatsApp application. You can also use WhatsApp Web for operating systems other than Mac.
You can see that your dynamic message is automatically populated in the message portion and the number is set to your intended recipient.
Simply click the send icon beside your message to successfully send it.
If you are interested in creating Google Sheets with timestamp tracking when you’ve sent your messages, here’s a quick and easy guide to do so.
We will be using Google Apps Script to achieve this. This usually requires some programming knowledge but we have added all the sample code below so that anyone can follow the steps.
You can view the worksheet for the example above and it’s Apps Script code by going to: Send WhatsApp Messages Example
In our example sheet, we will select column E which is beside our WhatsApp hyperlinks.
These are checkboxes that we press when we have successfully sent a message using WhatsApp.
Our sheet will now look like this:
This is the column where our Google Sheets App Script automation will populate the current timestamp when the checkbox has been ticked.
This will open a new tab for App Script. This is where we will create our program to automate the adding of a timestamp.
const TABSIN = ['Sheet1']; // Put your included sheet names in here
const COLUMNSIN = [insert column number here]; // column where checkboxes are located
function onEdit(e) {
if (!e) throw "Do NOT run this script from the editor !";
checkedDate(e);
}
function checkedDate(e) {
const src = e.source.getActiveSheet();
const r = e.range;
const date = new Date();
if (r.rowStart > 1 && COLUMNSIN.includes(r.columnStart) != false && TABSIN.indexOf(src.getName()) !== -1)
if (e.value === "TRUE"){
e.range.setValue('FALSE');
r.offset(0,1).setValue(date).setNumberFormat("yyyy-MM-dd hh:mm");
}
}
To use this code, you only have to change the two bolded and italicized variables:
‘Sheet 1’ - is the sheet name of your active worksheet
insert column number here - is where you put the column number of the column where your checkboxes are located. In our case, the column number for column E is 5.
For our example the final code will be:
const TABSIN = ['Sheet1']; // Put your included sheet names in here
const COLUMNSIN = [5]; // column where checkboxes are located
function onEdit(e) {
if (!e) throw "Do NOT run this script from the editor !";
checkedDate(e);
}
function checkedDate(e) {
const src = e.source.getActiveSheet();
const r = e.range;
const date = new Date();
if (r.rowStart > 1 && COLUMNSIN.includes(r.columnStart) != false && TABSIN.indexOf(src.getName()) !== -1)
if (e.value === "TRUE"){
e.range.setValue('FALSE');
r.offset(0,1).setValue(date).setNumberFormat("yyyy-MM-dd hh:mm");
}
}
This will save the code that you have made. You can also rename your untitled project.
We will create a trigger that will run our program when the appropriate checkboxes have been ticked.
This will be at the bottom right side of your triggers tab.
Use the following configurations:
Choose which function to run: onEdit
Select Event Source: From Spreadsheet
Event Type: OnEdit
Creating a trigger and saving it will open a pop-up that will ask you to log-in and authorize your program since this is custom code.
When asked to verify, click advanced options and proceed to “Go to Untitled Project”. Grant access to your account to be able to edit your spreadsheet.
Once clicked, you should see a timestamp of the current date and time appear in the timestamp column beside your checkbox.
You can also press the checkbox again if you need to change the timestamp that was placed. Now you can easily track sent messages through your Google Sheets workbook.
We hope this article has helped you and given you a better understanding of how to send WhatsApp messages from Google Sheets for free. You might also like our articles on how to find the best alternative to Google Sheets and how to import JSON to Google Sheets.
To optimize your workflow, we recommend reading our guide on how to extract a domain from an email address in Google Sheets and trying our software to help you set up reminders for contract renewal.
