CODEWITHSUNDEEP
google-apps-script
jazz
jazz

Reputation: 49

Mail merge Pre-filled Google form link

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 enter image description here

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 enter image description here

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.

Upvotes: 2

Views: 530

Answers (2)

Thang Dang
Thang Dang

Reputation: 493

The easiest way to fix this problem is format your Google Form Link to "Plain Text" enter image description here

I have a little issue with this samples too, I figure it out that the curly bracket {} from my keyboard different than the curly bracket {} this sample required. By changing the curly bracket, I've fixed my problem:

Upvotes: 0

NightEye
NightEye

Reputation: 11214

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

Output:

output

Upvotes: 2

Related Questions