App Script to send WhatsApp messages using 2Chat and Google Sheets

whatsapp-for-google-sheets.js

// 
// App Script to send WhatsApp messages using 2Chat and Google Sheets
//
const _2CHAT_API_KEY              = '<PASTE YOUR API KEY HERE>';
const _2CHAT_SOURCE_PHONE_NUMBER  = '<PASTE YOUR WHATSAPP PHONE NUMBER HERE>';

const STATUS_PENDING  = 'Pending';
const STATUS_QUEUED   = 'Queued';
const STATUS_SENDING  = 'Sending';
const STATUS_FAILED   = 'Failed';
const STATUS_SENT     = 'Sent';

function generatePendingMessages() {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Queue");
  var data  = sheet.getDataRange().getValues();

  var statusCol   = data[0].indexOf("Status");
  var nameCol     = data[0].indexOf("Name");
  var messageCol  = data[0].indexOf("Message");

  for ( i = 1; i < data.length; i++) {

    const status  = sheet.getRange(i + 1, statusCol + 1).getValue();

    if ( status.toString().toLocaleLowerCase() != STATUS_PENDING.toLocaleLowerCase() ) {
      Logger.log(`row=[${i}] has no pending status: [${status}]`); 
      continue;
    }

    const firstName   = data[i][nameCol];

    Logger.log(`Generating message for row=[${i}] with state=[${status}]`);
    sheet.getRange(i + 1, messageCol + 1).setValue(createMessage(firstName));

  }
}

function confirmedSendingMessagesModal(count) {
  const ui  = SpreadsheetApp.getUi();

  var result = ui.alert(
                  'Confirm sending messages',
                  `${count} message(s) are queued to be sent.\n⚠️ Make sure all messages were properly generated.\n\nDo you want to continue?`,
                    ui.ButtonSet.YES_NO);

  return result == ui.Button.YES;
}

function sendQueuedMessages() {
  var sheet     = SpreadsheetApp.getActive().getSheetByName("Queue");
  var data      = sheet.getDataRange().getValues();
  var statusCol = data[0].indexOf("Status");

  var queuedMessages  = data.filter(row => row[statusCol].toString().toLocaleLowerCase() === STATUS_QUEUED.toLocaleLowerCase());

  if (!queuedMessages || queuedMessages.length === 0) {
    SpreadsheetApp.getUi().alert("No messages to send", 
                                 `There are no messages in ${STATUS_QUEUED} state to be processed.`,
                                 SpreadsheetApp.getUi().ButtonSet.OK);
    return;
  }

  if (!confirmedSendingMessagesModal(queuedMessages.length)) {
    Logger.log("Sending was cancelled");
    return;
  }

  Logger.log("Sending messages");

  var sendingTimeCol  = data[0].indexOf("Sending time");
  var messageCol      = data[0].indexOf("Message");
  var phoneNumberCol  = data[0].indexOf("Phone number");

  for ( i = 1; i < data.length; i++) {

    const status  = sheet.getRange(i + 1, statusCol + 1).getValue();

    if ( status.toString().toLocaleLowerCase() != STATUS_QUEUED.toLocaleLowerCase() ) {
      Logger.log(`row=[${i}] is not queued: [${status}]`); 
      continue;
    }

    Logger.log(`Sending message on row=[${i}] status=[${status}]`); 

    sheet.getRange(i + 1, statusCol + 1).setValue(STATUS_SENDING);

    const phoneNumber = data[i][phoneNumberCol];
    const message     = data[i][messageCol];

    sendMessage(phoneNumber.toString(), message.toString(), i);

    Utilities.sleep(Math.random() * 5000); // wait between 0 and 15 seconds
  }

}

function createMessage(nameCell) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Params");

  const message = sheet.getRange('B2').getValue();

  if ( !message ) {
    SpreadsheetApp.getUi().alert("In Params sheet, cell B2 must have a valid message");
    return;
  }
  
  return message.toString().replace("{{primerNombre}}", "{{firstName}}").replace("{{firstName}}", nameCell);
}

function getAttachmentURL() {
    const sheet = SpreadsheetApp.getActive().getSheetByName("Params");

    const url = sheet.getRange('B3').getValue().toString();

    if ( !url || !url.startsWith("http") ) {
      Logger.log("No attachment to be sent witht this message");
      return;
    }
    
    return url.trim();
  
}

function sendMessage(toNumber, message, rowIndex) {
  var sheet = SpreadsheetApp.getActive().getSheetByName("Queue");
  var data = sheet.getDataRange().getValues();

  var sendingTimeCol  = data[0].indexOf("Sending time");
  var statusCol       = data[0].indexOf("Status");

  try {

    Logger.log("Sending message to " + toNumber);

    if (!message) {
      throw new Error("Message can't be empty");
    }

    if (!toNumber) {
      throw new Error("Phone number can't be empty");
    }

    if ( !toNumber.startsWith("+") ) {
      toNumber = "+" + toNumber;
    }
    
    sheet.getRange(i + 1, statusCol + 1).setValue(STATUS_SENDING);
    sheet.getRange(i + 1, sendingTimeCol + 1).setValue(null);

    var data = {
      "to_number":    toNumber,
      "from_number":  _2CHAT_SOURCE_PHONE_NUMBER,
      "text":         message
    }

    var attachmentURL = getAttachmentURL();

    if ( attachmentURL ) {
      data["url"] = attachmentURL;
    }

    var requestOptions = {
      method: 'POST',
      headers: {
        "X-User-API-Key": _2CHAT_API_KEY,
        "Content-Type": "application/json",
        'Accept': 'application/json',
      },
      payload: JSON.stringify(data),
      // redirect: 'follow'
    };

    var response = UrlFetchApp.fetch("https://api.p.2chat.io/open/whatsapp/send-message", requestOptions)
    
    var res = JSON.parse(response.getContentText());

    sheet.getRange(rowIndex + 1, statusCol + 1).setValue(STATUS_SENT);
    sheet.getRange(rowIndex + 1, sendingTimeCol + 1).setValue(new Date().toLocaleString());

    if ( res?.error ) {
      sheet.getRange(rowIndex + 1, statusCol + 1).setValue(STATUS_FAILED);
      sheet.getRange(rowIndex + 1, sendingTimeCol + 1).setValue("❌ " + error?.error_message);
    }
  }
  catch (err) {
    console.error(err);

    sheet.getRange(rowIndex + 1, statusCol + 1).setValue(STATUS_FAILED);
    sheet.getRange(rowIndex + 1, sendingTimeCol + 1).setValue("❌ " + err.message);
  }
}

//
// triggered to create the dropdown menu
//
function onOpen()  {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('🔷 2Chat')
      .addItem('🔁 Generate Pending Messages', 'generatePendingMessages')
      .addItem('✅ Send Queued Messages', 'sendQueuedMessages')
      .addToUi();
}