Mail merge Pre-filled Google form link

Question

I use Google sheet and app scripts to create mail merge and it works fine with name, email, and general text information.

I am adding a column with a pre-filled Google Form link, like this.

Gmail Mail Merge Template

Although the mail merge works fine, the link does not work. the email recipient cannot click on the Google form link with pre-filled information.

Recipient's Mail Merge Results

What I'd expect to see is a Google Form hyperlink in the email body and the email recipient can click on it and be directed to the Google form with pre-filled information.

Is there a way to include pre-filled information too?

Example of the Google sheet used for mail merge.

Mail merge app script [From Google app script template]

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: '[email protected]',
          //cc: '[email protected]',
          //from: '[email protected]',
          // name: '[email protected]',
          // replyTo: '[email protected]',
          // 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);

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

I would like to have the Google form link in the body of the email.

Can you also provide the script you're working on? It is difficult providing a solution if we are unaware of what we are working on. Also include what your current output and the expected output you want. — NaziA
@NaziA I included details of the script and expected results. Please see the edited question. Thank you! — jazz

NaziA NaziA · Accepted Answer · 2021-06-24T18:01:11

Issue:

You are filling your template with the actual text thus when sent in an email, it still has the exact value from the sheet.

Solution:

You need to convert your link properly using the built-in function encodeURI. I modified your function fillInTemplateFromObject_ and add a line there to use encodeURI as it will be the easiest way to fix the issue.

Code:

function fillInTemplateFromObject_(template, data) {
  let template_string = JSON.stringify(template);
  // convert your link into proper link using encodeURI
  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);
}

Mail merge Pre-filled Google form link

1 Answers

Issue:

Solution:

Code:

Output: