David Stockdale's Scrapcode

Send WhatsApp Messages From Google Sheets

I was recently asked to make a google sheet that allowed the sending of WhatsApp messages to a list of recipients in a Google Spreadsheet.

This was not possible in bulk as far as I knew but I did manage to find a way to automate most of the process by creating hyperlinks which reference specific cells (credit to Lido tutorials btw).

These hyperlinks send you to the WhatsApp api portal and prepares a message to an individual recipient in the WhatsApp app.

To do this first you will need columns for the recipients name, phone number, the message itself, the hyperlink and (if you want to record when you send these messages) a check box and timestamp.

The first handy part of this setup is the message referencing the name cell so you can copy and paste the same message between recipients without having to manually change anything.

="Hi "&A3&". I'm learning how to send WhatsApp Messages from Google Sheets with Lido Tutorials!"

The next part is the hyperlink (the most important part of this method) which is done with this simple function:

=hyperlink("https://api.whatsapp.com/send?phone="&B2&"&text="&C2,"Send Message")

When you click on the hyperlink it should take you to this page:

I would recommend ticking the “Always allow” here to speed up future message sending.

But either way if your WhatsApp is open you should get a chat opened with the recipient with a message already typed up and ready to send like this:

If your WhatsApp wasn’t open you might just find it opened but no message, simply click the hyperlink again and this shouldn’t repeat itself so long as the app is left open.

This is enough for the actual sending of messages, but if you want to record when you sent it one option is to use checkbox triggered Apps Script.

After filling in your spreadsheet click “Extensions” and open “Apps Script”.

This will open a new Apps Script project connected to this spreadsheet.

Now input this code (making changes where appropriate) and save the project.

/** 
 * ================================= WHATSAPP =================================
 */
const TABSIN = ['<YOUR SHEET NAME HERE>'];
const COLUMNSIN = [5]; // column where checkboxes are located

function onMyEdit(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("dd/MM/yyyy hh:mm:ss");
  }
}

While you are on the Apps Script page (and after saving the project) you will need to go to the “Triggers” section (which looks like an alarm clock on the left side of the page).

Here you will need to click the “Add Trigger” button and save a trigger with these settings:

With this you should be able to click the checkboxes and automatically get a timestamp in the appropriate column.

Hopefully this guide will help you, comments and questions are always appreciated.

Leave a Reply