google-apps-scriptgmail

Create hyperlink in Mail Merge Google Sheet

I created unique pre-filled Google form links in Google Sheet and would like to send this unique link for each participant using Mail Merge.

The link works thanks to the help. But I want to have embed hyperlinks in the text in the template rather than sending the link directly. Any suggestion? Still trying to learn App scripts using others' templates so any help is appreciated. Thanks a lot!

Current Output enter image description here

Expected Output

enter image description here

const RECIPIENT_COL  = "Email";
const EMAIL_SENT_COL = "Email Sent";
 
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Mail Merge')
      .addItem('Send Emails', 'sendEmails')
      .addToUi();
}
 
function sendEmails(subjectLine, sheet=SpreadsheetApp.getActiveSheet()) {
   if (!subjectLine){
    subjectLine = Browser.inputBox("Mail Merge", 
                                      "Type or copy/paste the subject line of the Gmail " +
                                      "  ",
                                      Browser.Buttons.OK_CANCEL);
                                      
    if (subjectLine === "cancel" || subjectLine == ""){ 
    // if no subject line finish up
    return;
    }
  }
  
  // get the draft Gmail message to use as a template
  const emailTemplate = getGmailTemplateFromDrafts_(subjectLine);
  
  // get the data from the passed sheet
  const dataRange = sheet.getDataRange();
  const data = dataRange.getDisplayValues();

  // assuming row 1 contains our column headings
  const heads = data.shift(); 
  
  const emailSentColIdx = heads.indexOf(EMAIL_SENT_COL);
  
  const obj = data.map(r => (heads.reduce((o, k, i) => (o[k] = r[i] || '', o), {})));

  // used to record sent emails
  const out = [];

  // loop through all the rows of data
  obj.forEach(function(row, rowIdx){
    // only send emails is email_sent cell is blank and not hidden by filter
    if (row[EMAIL_SENT_COL] == ''){
      try {
        const msgObj = fillInTemplateFromObject_(emailTemplate.message, row);

        GmailApp.sendEmail(row[RECIPIENT_COL], msgObj.subject, msgObj.text, {
          htmlBody: msgObj.html,
          //bcc: 'fusip@gmail.com',
          //cc: 'fusip@gmail.com',
          //from: 'fusip@gmail.com',
          // name: 'fusip@gmail.com',
          // replyTo: 'a.reply@email.com',
          // noReply: true, // if the email should be sent from a generic no-reply email address (not available to gmail.com users)
          attachments: emailTemplate.attachments
        });
        // modify cell to record email sent date
        out.push([new Date()]);
      } catch(e) {
        // modify cell to record error
        out.push([e.message]);
      }
    } else {
      out.push([row[EMAIL_SENT_COL]]);
    }
  });
  
  // updating the sheet with new data
  sheet.getRange(2, emailSentColIdx+1, out.length).setValues(out);
  
  function getGmailTemplateFromDrafts_(subject_line){
    try {
      const drafts = GmailApp.getDrafts();
      const draft = drafts.filter(subjectFilter_(subject_line))[0];
      const msg = draft.getMessage();
      const attachments = msg.getAttachments();
      return {message: {subject: subject_line, text: msg.getPlainBody(), html:msg.getBody()}, 
              attachments: attachments};
    } catch(e) {
      throw new Error("Oops - can't find Gmail draft");
    }

    function subjectFilter_(subject_line){
      return function(element) {
        if (element.getMessage().getSubject() === subject_line) {
          return element;
        }
      }
    }
  }
  
  function fillInTemplateFromObject_(template, data) {
    let template_string = JSON.stringify(template);
     data['Google Form Link'] = encodeURI(data['Google Form Link']);

    // token replacement
    template_string = template_string.replace(/{{[^{}]+}}/g, key => {
      return data[key.replace(/[{}]+/g, "")] || "";
    });
    return  JSON.parse(template_string);
  }
}
Solution

  • You need to enclose your link inside an href tag to create a hyperlink instead.

    Code:

    data['Google Form Link'] = "<a href='" + encodeURI(data['Google Form Link']) + "'>Click Here</a>";

    Output:

    output