Mail merge question: pass a hyperlink into gmail from a google sheet

Question

I am in the middle of a mail merge project, and have created a google sheet that includes all of my standard fields. One field includes a hyperlink. In my current code I am only able to pass the text for the hyperlink into gmail. Below are the HTML code and google app script that I am working with. I suspect that I need to modify my HTML code but I am uncertain how to proceed. The HTML codes in question is <?= fs ?>. Any support would be greatly appreciated.

HTML Code:

<body>
  <p>Please review and update your project management spreadsheet by [insert date] using the link: <?= fs ?></p>
</body>

Google Apps Script

function myFunction() {      
  var school = 0;
  var contact = 2;
  var email = 5;
  var followUpSheet = 6;
  
  var emailTemp = HtmlService.createTemplateFromFile("File_name");
 
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet_Name");
  
  var data = ws.getRange("A2:P" + ws.getLastRow()).getValues();
  
  data.forEach(function(row){
  
    emailTemp.contact = row[contact];
    emailTemp.fs = row[followUpSheet];
    
    var htmlMessage = emailTemp.evaluate().getContent();
    
    GmailApp.sendEmail(
      row[email], 
      "Name of eMail", 
      "Your email doesn't support HTML.",
      {name: "name of sender", htmlBody: htmlMessage});
  });
}

The output for this code is:

Please review and update your project management spreadsheet by [insert date] using the link: hyperlink text name

Hi. This is really quite difficult to follow; you haven't include your spreadsheet nor explained the fields in the code. Please read [How to create a Minimal, Reproducible Example](https://stackoverflow.com/help/minimal-reproducible-example), and then edit your question to provide more information, and more resources. — Tedinoz, Oct 31 '19 at 23:15
Hello @PSR, you seem to be using the HTML tags just as they are meant to be used. Could you please provide an example Sheet and the output you'd get by using it, so we can better understand the context? Thanks a lot:) — carlesgg97, Nov 01 '19 at 13:16

score 0 · Answer 1 · edited Jul 18 '21 at 00:53

I decided to insert the url into the email. This resolved my challenge. The HTML and GAS code are presented below.

//HTML Code

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
    
    <p>Dear <?= contact ?>,</p>
    <p>Insert paragraph 1</p>
    <p>Please review and update your project management spreadsheet by [insert date].  Select the link (or attachment) below to open your spreadsheet:</p>
    <ul style="list-style-type:disc;">
    <li><?= fs ?></li></ul>
    <p>If you have any questions please contact [team member name] (email: xxxx, phone: xxxx)</p>
    <p>Have a great day.</p>
    <p>Signature</p>
    
  </body>
</html>

//Google Apps Script

function myFunction() {
  
  var school = 0;
  var contact = 2;
  var email = 5;
  var URL = 7;
  
  var emailTemp = HtmlService.createTemplateFromFile("SpreadsheetName");
 
  var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  
  var data = ws.getRange("A2:P" + ws.getLastRow()).getValues();
  
  data.forEach(function(row){
  
    emailTemp.contact = row[contact];
    emailTemp.fs = row[URL];
    
    var htmlMessage = emailTemp.evaluate().getContent();
    
    GmailApp.sendEmail(
      row[email], 
      "Follow Up Email", 
      "Your email doesn't support HTML.",
      {name: "Representative", htmlBody: htmlMessage})
  
  });
  
}

Mail merge question: pass a hyperlink into gmail from a google sheet

HTML Code:

Google Apps Script

1 Answers1

Linked