Create hyperlink in Mail Merge Google Sheet

Question

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

Expected Output

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);
  }
}

Next time, try to search for it on google. There are many posts like: https://stackoverflow.com/questions/32602495/how-to-add-a-hyperlink-in-a-google-docs-using-a-google-script — Malte K.oder, Jun 28 '21 at 14:51

score 1 · Accepted Answer · answered Jun 28 '21 at 16:08

1

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:

answered Jun 28 '21 at 16:08

NightEye

10,634
2
5
24

score 0 · Answer 2 · answered Jun 28 '21 at 14:50

0

Do it like that:

var value = '=HYPERLINK("www.yourLink.com", "Click Here")';

answered Jun 28 '21 at 14:50

Malte K.oder

65
7

1

I don't believe adding to google docs/sheets is the one the OP wants. He is generating an email from the sheets and wants the link in the generated mail to be hyperlink instead of the full link. – NightEye Jun 28 '21 at 16:17

Create hyperlink in Mail Merge Google Sheet

2 Answers2

Code:

Output: