AppSScript Code

Copy Code

function onOpen() {
  var ui = SpreadsheetApp.getUi();
 
  // Add a custom menu to the spreadsheet
  ui.createMenu('Bulk Message')
    .addItem('Generate Bulk Message', 'createWhatsAppHyperlink') // Add menu item
    .addToUi(); // Attach menu to the UI
}


function createWhatsAppHyperlink() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 
  // Clear existing headers and contents
  var headersRange = sheet.getRange(1, 1, 1, 5);
  headersRange.clearContent();
 
  // Adding headers to the first row
  var headers = ["Name", "Phone Number", "Purpose", "Final Message", "WhatsApp Link"];
  sheet.getRange(1, 1, 1, headers.length).setValues([headers]);
 
  // Now get the data starting from the second row
  var lastRow = sheet.getLastRow();
  if (lastRow < 2) {
    Logger.log("No data to process.");
    return; // Exit if there's no data
  }
 
  var dataRange = sheet.getRange(2, 1, lastRow - 1, 3); // Get data from columns A, B, C
  var data = dataRange.getValues();
  var finalMessageData = [];
 
  // Create Final Message values
  for (var i = 0; i < data.length; i++) {
    var name = data[i][0]; // Assuming names are in column A (index 0)
    var phoneNumber = data[i][1]; // Assuming phone numbers are in column B (index 1)
    var purpose = data[i][2]; // Assuming Purpose is in column C (index 2)
   
    // Create Final Message
    var finalMessage = "Hii " + name + ", " + purpose; // Adjust as needed
    finalMessageData.push([finalMessage]);
  }
 
  // Store the Final Message in column D, starting from row 2
  var columnD = sheet.getRange(2, 4, finalMessageData.length, 1);
  columnD.setValues(finalMessageData);
 
  // Now create WhatsApp links based on the filled Final Message
  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 finalMessage = finalMessageData[i][0]; // Get the Final Message from the array
   
    // Create WhatsApp link
    var whatsappLink = "https://api.whatsapp.com/send?phone=" + phoneNumber + "&text=" + encodeURIComponent(finalMessage);
    var displayText = "click to send"; // The text you want to display as the hyperlink
    var hyperLinkFormula = '=HYPERLINK("' + whatsappLink + '", "' + displayText + '")';
    whatsappLinks.push([hyperLinkFormula]);
  }
 
  // Store the WhatsApp links in column E, starting from row 2
  var columnE = sheet.getRange(2, 5, whatsappLinks.length, 1);
  columnE.setFormulas(whatsappLinks);


  Logger.log("Final Messages filled and WhatsApp hyperlinks created successfully!");
}