function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
    .addItem('Send Personalized Emails', 'sendPersonalizedEmailsFromDraft')
    .addToUi();
}

function sendPersonalizedEmailsFromDraft() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt('Send Personalized Emails', 'Please enter the email draft subject:', ui.ButtonSet.OK_CANCEL);
  
  if (response.getSelectedButton() == ui.Button.OK) {
    var draftSubject = response.getResponseText();
    
    if (draftSubject.trim() === "") {
      ui.alert('Draft subject cannot be empty. Please try again.');
      return;
    }
    
    var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var data = sheet.getDataRange().getValues();
    
    // Get the draft email
    var drafts = GmailApp.getDrafts();
    var draft = null;
    for (var i = 0; i < drafts.length; i++) {
      if (drafts[i].getMessage().getSubject() === draftSubject) {
        draft = drafts[i];
        break;
      }
    }
    
    if (!draft) {
      ui.alert('Draft email not found. Please check the subject and try again.');
      return;
    }
    
    var draftMessage = draft.getMessage();
    var draftBody = draftMessage.getBody();
    var subject = draftMessage.getSubject();
    
    var dailyLimit = 1000;
    var batchLimit = 100; // Batch limit of 100 emails
    var sentEmails = 0;
    
    for (var i = 1; i < data.length; i++) {
      if (sentEmails >= dailyLimit) {
        Logger.log('Daily limit reached. Emails sent: ' + sentEmails);
        ui.alert('Daily limit reached. Emails sent: ' + sentEmails);
        return;
      }
      
      var emailAddress = data[i][0];  // First column
      var name = data[i][1];          // Second column
      var id = data[i][2];            // Third column
      var sendTimeCell = sheet.getRange(i + 1, 4); // Send Date & Time column
      var statusCell = sheet.getRange(i + 1, 5);   // Status column
      
      // Replace placeholders with actual values
      var personalizedBody = draftBody.replace("{name}", name).replace("{id}", id);
      
      try {
        GmailApp.sendEmail(emailAddress, subject, "", {
          htmlBody: personalizedBody
        });
        var currentTime = new Date();
        sendTimeCell.setValue(Utilities.formatDate(currentTime, Session.getScriptTimeZone(), "yyyy-MM-dd HH:mm:ss"));
        statusCell.setValue("Sent");
        sentEmails++;
        
        // If batch limit is reached, pause for a moment
        if (sentEmails % batchLimit === 0) {
          Utilities.sleep(5000); // Sleep for 5 seconds to avoid hitting rate limits
        }
      } catch (e) {
        statusCell.setValue("Failed: " + e.message);
      }
    }
    
    ui.alert('Emails sent to ' + sentEmails + ' recipients.');
  } else {
    ui.alert('Email sending cancelled.');
  }
}