Apps Scripts Code

Copy Code

function createWhatsAppHyperlink() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lastRow = sheet.getLastRow();
  var dataRange = sheet.getRange(2, 1, lastRow - 1, 4); // Assuming data starts from row 2 and you have 4 columns (A, B, C, D)


  var data = dataRange.getValues();
  var whatsappLinks = [];


  for (var i = 0; i < data.length; i++) {
    var phoneNumber = data[i][1]; // Assuming phone numbers are in column B (index 1)
    var message = data[i][0] + " - " + data[i][2] + " - " + data[i][3]; // Merge data from columns A, C, and D
    var whatsappLink = "https://api.whatsapp.com/send?phone=" + phoneNumber + "&text=" + encodeURIComponent(message);
    var displayText = "click to send"; // The text you want to display as the hyperlink
    var hyperLinkFormula = '=HYPERLINK("' + whatsappLink + '", "' + displayText + '")';
    whatsappLinks.push([hyperLinkFormula]);
  }


  var columnE = sheet.getRange(2, 5, whatsappLinks.length, 1); // Column E (index 5) to store the hyperlinks
  columnE.setFormulas(whatsappLinks);


  Logger.log("WhatsApp hyperlinks with display text have been created successfully!");
}