UNIVERSAL MAIL MERGE (File ID or Draft Subject) by Remote BB

universalMailMergeByRemoteBB.gs

/**
 * UNIVERSAL MAIL MERGE (File ID or Draft Subject) by Remote BB
 *
 * OVERVIEW:
 * 1) Prompts the user for a single piece of text.
 *    - We first assume it's a Google Docs/Slides File ID.
 *    - If we can open the file AND it is Docs or Slides, we do a Docs/Slides merge.
 *    - Otherwise, we treat the input as part of a Gmail draft's subject and do an email merge.
 *
 * 2) Merges the data from the currently selected sheet:
 *    - For Docs, we copy the doc for each row, replacing {{ColumnName}} placeholders,
 *      then rename the copy to "OriginalDocName - <Email/Name/fallback>".
 *    - For Slides, we duplicate the first slide for each row, replacing {{ColumnName}} placeholders.
 *    - For Email, we search your drafts for a subject containing the text. 
 *      For the first match, we replace placeholders in the draft’s subject & body, then send an email per row.
 *
 * BASIC SETUP (1, 2, 3):
 * 1) In your Google Sheet, go to Extensions > Apps Script and paste this code into a script file.
 * 2) Click the Run (►) button, then grant any permissions requested.
 * 3) When prompted for "File ID or Draft Subject," paste in your Doc/Slides ID or partial draft subject.
 *    - If it’s a valid Docs/Slides ID, we do a Docs/Slides merge.
 *    - If not valid, we do an email merge using a matching Gmail draft.
 */

function startMailMerge() {
  const ui = SpreadsheetApp.getUi();
  
  // Prompt for a single string that might be a file ID or partial draft subject
  const userPrompt = ui.prompt(
    "Universal Mail Merge",
    "Enter EITHER a Google Docs/Slides File ID (from the URL), " +
    "OR text from a Gmail draft’s subject. We’ll figure it out automatically.",
    ui.ButtonSet.OK_CANCEL
  );
  
  // If user canceled
  if (userPrompt.getSelectedButton() !== ui.Button.OK) {
    Logger.log("User canceled.");
    return;
  }
  
  // Get the user's input
  const inputText = userPrompt.getResponseText().trim();
  if (!inputText) {
    ui.alert("No input provided. Merge canceled.");
    return;
  }
  
  // Attempt to treat input as a File ID first
  let didFileMerge = tryFileMerge(inputText);
  
  // If it wasn't a valid Docs/Slides file, do an email merge
  if (!didFileMerge) {
    emailFlow(inputText);
  }
}


/**
 * Try treating 'inputText' as a Google Docs/Slides File ID.
 * If successful, merges the active sheet’s data into that file.
 * Returns true if it succeeded (Docs/Slides), false otherwise.
 */
function tryFileMerge(inputText) {
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data.shift(); // First row are headers

  try {
    const file = DriveApp.getFileById(inputText);
    const mimeType = file.getMimeType();

    // If it's a Google Doc
    if (mimeType === "application/vnd.google-apps.document") {
      docMailMerge(file, data, headers);
      return true;
    }
    // If it's Google Slides
    else if (mimeType === "application/vnd.google-apps.presentation") {
      slidesMailMerge(inputText, data, headers);
      return true;
    }
    // Otherwise, not supported => return false
    return false;
    
  } catch (err) {
    // Could not open => not a valid file ID
    Logger.log("Not a valid Docs/Slides file. Error: " + err);
    return false;
  }
}


/**
 * If input wasn't a valid Docs/Slides ID, we do an email merge:
 *  - 'inputText' is treated as part of the draft’s subject.
 *  - Find matching draft, confirm with user, replace placeholders, send.
 */
function emailFlow(draftSubjectText) {
  const ui = SpreadsheetApp.getUi();

  // Grab data from the active sheet
  const sheet = SpreadsheetApp.getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data.shift(); // first row = headers

  // Search for drafts whose subject includes draftSubjectText
  const allDrafts = GmailApp.getDrafts();
  const matchingDrafts = allDrafts.filter(d =>
    d.getMessage().getSubject().includes(draftSubjectText)
  );
  
  if (matchingDrafts.length === 0) {
    ui.alert(
      "No Draft Found",
      `No Gmail draft found containing: "${draftSubjectText}"`,
      ui.ButtonSet.OK
    );
    return;
  }

  // Use the first matching draft, confirm with user
  const selectedDraft = matchingDrafts[0];
  const draftMsg = selectedDraft.getMessage();
  const foundSubject = draftMsg.getSubject();

  const confirm = ui.alert(
    "Confirm Draft",
    `We found a draft whose subject contains "${draftSubjectText}":\n\n"${foundSubject}"\n\nUse this draft?`,
    ui.ButtonSet.OK_CANCEL
  );
  if (confirm !== ui.Button.OK) {
    Logger.log("User canceled draft usage.");
    return;
  }

  // Great, do the email merge
  emailMailMergeUsingDraft(draftMsg, data, headers);
}


/**
 * ========================
 * DOC MERGE HELPER FUNCTION
 * ========================
 * Copies the Doc for each row, replaces {{ColumnName}} placeholders,
 * and renames the copy as "OriginalDocName - <Email/Name/fallback>".
 */
function docMailMerge(file, data, headers) {
  // The original doc’s name
  const originalDocName = file.getName();
  const docId = file.getId();

  // Identify the Email or Name columns if they exist
  const emailIndex = headers.indexOf("Email");
  const nameIndex = headers.indexOf("Name");

  data.forEach(row => {
    // 1) Copy the original doc
    const docCopy = file.makeCopy();
    const docCopyId = docCopy.getId();
    const doc = DocumentApp.openById(docCopyId);
    const body = doc.getBody();

    // 2) Replace placeholders
    headers.forEach((header, i) => {
      const placeholder = `{{${header}}}`;
      const value = row[i] || "";
      body.replaceText(placeholder, value);
    });

    // 3) Build a "unique ID" portion for the doc name:
    //    Prefer "Email" if present, else "Name", else fallback to the first column
    let mergeId = "";
    if (emailIndex !== -1 && row[emailIndex]) {
      mergeId = row[emailIndex];
    } else if (nameIndex !== -1 && row[nameIndex]) {
      mergeId = row[nameIndex];
    } else {
      // fallback: use the first non-empty column if possible
      let fallbackIndex = row.findIndex(val => val && val.toString().trim() !== "");
      fallbackIndex = fallbackIndex === -1 ? 0 : fallbackIndex;
      mergeId = row[fallbackIndex];
    }

    // 4) Rename copy to: "OriginalDocName - mergeId"
    //    (If mergeId is empty, we won't add the hyphen.)
    const newDocName = mergeId ? `${originalDocName} - ${mergeId}` : originalDocName;
    doc.setName(newDocName);

    doc.saveAndClose();
  });

  Logger.log("Doc merge complete! Check your Drive for new copies.");
}


/**
 * ===========================
 * SLIDES MERGE HELPER FUNCTION
 * ===========================
 * Duplicates the first slide for each row, replaces {{ColumnName}} placeholders.
 * (No special rename logic here, but you could add it if you wanted to rename slides.)
 */
function slidesMailMerge(slidesId, data, headers) {
  const slidesDeck = SlidesApp.openById(slidesId);
  const templateSlide = slidesDeck.getSlides()[0];

  data.forEach(row => {
    const newSlide = templateSlide.duplicate();
    headers.forEach((header, i) => {
      const placeholder = `{{${header}}}`;
      const value = row[i] || "";
      newSlide.replaceAllText(placeholder, value);
    });
  });

  // (Optional) remove the original template slide if you no longer need it
  // templateSlide.remove();

  Logger.log("Slides merge complete! Check your Slides deck.");
}


/**
 * ============================
 * EMAIL MERGE HELPER FUNCTION
 * ============================
 * Uses the chosen draft’s subject & body, replaces {{ColumnName}} placeholders, sends an email per row.
 */
function emailMailMergeUsingDraft(draftMsg, data, headers) {
  const ui = SpreadsheetApp.getUi();

  const originalSubject = draftMsg.getSubject();
  const originalBody = draftMsg.getBody(); // HTML
  const colEmail = headers.indexOf("Email");

  if (colEmail === -1) {
    ui.alert(
      'Missing "Email" Column',
      'Please ensure your sheet has a column named "Email" for the recipient address.',
      ui.ButtonSet.OK
    );
    return;
  }

  data.forEach(row => {
    const recipient = row[colEmail];
    if (!recipient) return; // skip rows without an email

    let subject = originalSubject;
    let body = originalBody;

    headers.forEach((header, i) => {
      const placeholder = `{{${header}}}`;
      const value = row[i] || "";
      subject = subject.replaceAll(placeholder, value);
      body = body.replaceAll(placeholder, value);
    });

    GmailApp.sendEmail(recipient, subject, "", { htmlBody: body });
  });

  Logger.log("Email merge complete! Check your Sent Mail in Gmail.");
}