c:\Abhi\Java\New folder\final.js

const RECIPIENT_COL = "Recipient";
const EMAIL_SENT_COL = "Email Sent";
const SCHEDULE_DATE_COL = "Schedule Date";

const BATCH_SIZE = 1000;
const DAILY_LIMIT = 1000; // Adjust based on G Suite sending limit

// Change sender name
const senderName = 'Tube Boost';

// Adds a custom 'Mail Merge' menu to the Google Sheets UI
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Mail Merge')
    .addItem('Schedule Emails', 'scheduleEmails')
    .addToUi();
}

// Schedule emails based on the schedule date column
function scheduleEmails(sheet = SpreadsheetApp.getActiveSheet()) {
  const dataRange = sheet.getDataRange();
  const data = dataRange.getValues();
  const headers = data.shift(); // First row contains headers
  const scheduleDateColIdx = headers.indexOf(SCHEDULE_DATE_COL);
  const emailSentColIdx = headers.indexOf(EMAIL_SENT_COL);

  const triggers = ScriptApp.getProjectTriggers();
  const out = [];

  // Ensure no duplicate triggers
  triggers.forEach(trigger => {
    if (trigger.getHandlerFunction() === 'sendScheduledEmails') {
      ScriptApp.deleteTrigger(trigger);
    }
  });

  let scheduleDate;
  const now = new Date();
  const rowsToUpdate = []; // To keep track of the rows that need to be updated

  // Loop through the rows and find the earliest unsent scheduled email
  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const emailSent = row[emailSentColIdx];
    scheduleDate = new Date(row[scheduleDateColIdx]);

    if (!emailSent && scheduleDate > now) {
      // Schedule a trigger to send emails at the earliest schedule date
      ScriptApp.newTrigger('sendScheduledEmails')
        .timeBased()
        .at(scheduleDate)
        .create();

      // Prepare the status message for scheduling
      rowsToUpdate.push([`Scheduled for ${scheduleDate.toLocaleString()}`]);
    } else {
      // Keep existing status if already scheduled or sent
      rowsToUpdate.push([emailSent]);
    }
  }

  // Update the email sent column with the new statuses for all rows
  if (emailSentColIdx >= 0) {
    sheet.getRange(2, emailSentColIdx + 1, rowsToUpdate.length).setValues(rowsToUpdate);
  }
}

// Function to send scheduled emails (triggered by the schedule)
function sendScheduledEmails() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const dataRange = sheet.getDataRange();
  const data = dataRange.getValues();
  const headers = data.shift(); // First row contains headers
  const emailSentColIdx = headers.indexOf(EMAIL_SENT_COL);
  const scheduleDateColIdx = headers.indexOf(SCHEDULE_DATE_COL);
  const recipientColIdx = headers.indexOf(RECIPIENT_COL);

  const now = new Date();
  const out = [];
  let sentEmailsCount = 0; // Initialize sent emails count

  // Fetch Gmail draft (only once)
  const draft = getSingleGmailDraft();

  // Loop through data and send emails
  for (let i = 0; i < data.length && sentEmailsCount < BATCH_SIZE; i++) {
    const row = data[i];
    const recipient = row[recipientColIdx];
    const scheduleDate = new Date(row[scheduleDateColIdx]);
    const emailSent = row[emailSentColIdx];

    if (scheduleDate <= now) {
      Logger.log('sent');
      try {
        // Prepare the personalized email subject and body by replacing placeholders with actual values
        const personalizedEmailSubject = replacePlaceholders(draft.subject, headers, row);
        const personalizedEmailBody = replacePlaceholders(draft.html, headers, row);

        // Send email using the Gmail draft
        GmailApp.sendEmail(recipient, personalizedEmailSubject, draft.text, {
          htmlBody: personalizedEmailBody, // Send personalized email
          attachments: draft.attachments,
          inlineImages: draft.inlineImages,
          name: senderName,
        });

        // Mark email as sent with a success message
        out.push(["Sent Successfully"]);
        sentEmailsCount++; // Increment sent emails count
      } catch (e) {
        Logger.log('Error sending email to ' + recipient + ': ' + e.message);
        out.push([emailSent]); // Keep the old status if there's an error
      }
    } else {
      out.push([emailSent]); // Keep old status for emails not yet due to send
    }
  }

  // Update the email sent column with the statuses for all processed rows
  if (emailSentColIdx >= 0) {
    sheet.getRange(2, emailSentColIdx + 1, out.length).setValues(out);
  }

  // Delete trigger if all emails are sent or limit is reached
  if (sentEmailsCount >= BATCH_SIZE || !hasMoreEmailsToSend()) {
    const triggers = ScriptApp.getProjectTriggers();
    triggers.forEach(trigger => {
      if (trigger.getHandlerFunction() === 'sendScheduledEmails') {
        ScriptApp.deleteTrigger(trigger);
      }
    });
  } else {
    // If there are still unsent emails, create a new trigger for the next batch
    scheduleEmails(sheet);
  }
}

// Check if more emails are left to send
function hasMoreEmailsToSend() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data.shift();
  const emailSentColIdx = headers.indexOf(EMAIL_SENT_COL);
  const scheduleDateColIdx = headers.indexOf(SCHEDULE_DATE_COL);
  const now = new Date();

  for (let i = 0; i < data.length; i++) {
    const row = data[i];
    const scheduleDate = new Date(row[scheduleDateColIdx]);
    const emailSent = row[emailSentColIdx];

    if (!emailSent && scheduleDate <= now) {
      return true;
    }
  }
  return false;
}

// Function to retrieve a single Gmail draft (for all emails)
function getSingleGmailDraft() {
  const drafts = GmailApp.getDrafts();
  const draft = drafts[0]; // Get the first available draft (ensure only one draft is in Gmail)

  if (!draft) {
    throw new Error('No draft found.');
  }

  const msg = draft.getMessage();
  const allInlineImages = msg.getAttachments({ includeInlineImages: true, includeAttachments: false });
  const attachments = msg.getAttachments({ includeInlineImages: false });
  const htmlBody = msg.getBody();
  const imgObj = allInlineImages.reduce((obj, i) => (obj[i.getName()] = i, obj), {});

  const imgExp = RegExp('<img.*?src="cid:(.*?)".*?alt="(.*?)"[^\>]+>', 'g');
  const matches = [...htmlBody.matchAll(imgExp)];
  const inlineImagesObj = {};

  matches.forEach(match => inlineImagesObj[match[1]] = imgObj[match[2]]);

  return {
    subject: msg.getSubject(),
    text: msg.getPlainBody(),
    html: htmlBody,
    attachments: attachments,
    inlineImages: inlineImagesObj
  };
}

// Function to replace placeholders in the email body or subject with actual data
function replacePlaceholders(template, headers, row) {
  let result = template;

  // Loop through all the headers and replace the placeholders with actual values
  headers.forEach((header, idx) => {
    const placeholder = `{{${header}}}`; // Create placeholder like {{First name}}
    const value = row[idx]; // Get the actual value from the current row

    result = result.replace(new RegExp(placeholder, 'g'), value || ''); // Replace placeholder with value
  });

  return result; // Return the personalized email body or subject
}