html - Add Image to Google Sheets email App Script

Question

I am trying to add a image at the end of my message but I don't know how to do it. I tried adding an inline image but because I am already the using the option in MailApp, I think it is not going trough.

This is the image I want to add: https://industry.datascience.columbia.edu/sites/default/files/images/NSF-NORTHEAST-BIG-DATA-INDUSTRY-LOGO.png

I wan it to go after "Kathy McKeown" and before the "nebigdatahub.org" url

Thank you for your help !!

This is the code:

function personalguest2018 () {
var ss = SpreadsheetApp.getActiveSpreadsheet();
ss.setActiveSheet(ss.getSheetByName("2018Invites"));
var sheet = SpreadsheetApp.getActiveSheet();
var startRow = 8; 
var dataRange = sheet.getRange("A8:J164");
var data = dataRange.getValues();
for (var i = 0; i < data.length; ++i) {
   var row = data[i];
   var Lastname = row[0];       // First column
   var Firstname= row[1];       // Second column
   var organization = row[2];   // Third column
   var email = row[3];          // Fourth column
   var sector = row[4];         // Fifth column
   var role = row[5];           // Sixth column
   var typeofinvite = row[6];   // Seventh column
   var emailSent = row[7]       // Eighth column
   var subject = "2018 Northeast Big Data Innovation Annual Summit";
   var msgHtml = "Dear " + Firstname + "," + "</p>"
   +"<p>"+"We are reaching out to personally invite you to the <b>2018       Annual Summit of the Northeast Big Data Innovation Hub</b>, on Tuesday,   March 27th, at Columbia University."+"<p>"
+"<p>"+"Please join us and learn how the Hub has grown over the past year, including updates on new cross-sector initiatives, lightning talks from our Big Data Spoke PIs, and opportunities to collaborate with "+ 
"our stakeholders in breakout sessions on data literacy, ethics, and health. Keynote speaker Corinna Cortes (Google Research, New York) will highlight her team's data-driven approach to fighting fake news. "+
"A panel of leaders drawn from academia and the private sector will discuss how? they address the challenges of rapid advances in digital media ?that may ?outpace our ability to ?maximize its benefits"+
" and minimize the? potential drawbacks."+"<p>"
+"<p>"+"Your perspective would be a valued contribution to the day's discussions, and we hope very much to see you there! <b>Registration and further information is available at</b> bit.ly/RegisterNE2018." 
+"<p>"+"Please feel free to share with members of your team who may be interested in joining. Should you have any questions, please don't hesitate to reach out to us via rb70@columbia.edu." + "<p>"+
"All the best," + "<p>"
+"<p>"+"René Bastón"+"<br/>"+ 
"Kathy McKeown"+ "</p>" +
+"</p>"+ "<p>"+"nebigdatahub.org" + "</p>";
var msgPlain = msgHtml.replace(/<br/>/gi, '
').replace(/(<([^>]+)>)/ig, ""); // clear html tags and convert br to new lines for plain mail
if (emailSent !="EMAIL_SENT"){  // Prevents sending duplicates
  if(row[6]=="Personal" && row[5]== "Katy") { 
    GmailApp.sendEmail(email, subject, msgPlain,{ htmlBody: msgHtml });
    sheet.getRange(startRow + i, 8).setValue("EMAIL_SENT");
  // Make sure the cell is updated right away in casethe script is     inaterrupted
    SpreadsheetApp.flush();
   }
  }  
 }
}

Answer 1

How about this modification?

Modification points :

• The image is imported to blob before for loop.
• The image is added using<img src="cid:image"><br/> and inlineImages: {image: blob}.

Modified script :

function personalguest2018 () {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  ss.setActiveSheet(ss.getSheetByName("2018Invites"));
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 8; 
  var dataRange = sheet.getRange("A8:J164");
  var data = dataRange.getValues();
  var blob = UrlFetchApp.fetch("https://industry.datascience.columbia.edu/sites/default/files/images/NSF-NORTHEAST-BIG-DATA-INDUSTRY-LOGO.png").getBlob(); // Added
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var Lastname = row[0];       // First column
    var Firstname= row[1];       // Second column
    var organization = row[2];   // Third column
    var email = row[3];          // Fourth column
    var sector = row[4];         // Fifth column
    var role = row[5];           // Sixth column
    var typeofinvite = row[6];   // Seventh column
    var emailSent = row[7]       // Eighth column
    var subject = "2018 Northeast Big Data Innovation Annual Summit";
    var msgHtml = "Dear " + Firstname + "," + "</p>"
      +"<p>"+"We are reaching out to personally invite you to the <b>2018       Annual Summit of the Northeast Big Data Innovation Hub</b>, on Tuesday,   March 27th, at Columbia University."+"<p>"
      +"<p>"+"Please join us and learn how the Hub has grown over the past year, including updates on new cross-sector initiatives, lightning talks from our Big Data Spoke PIs, and opportunities to collaborate with "+ 
      "our stakeholders in breakout sessions on data literacy, ethics, and health. Keynote speaker Corinna Cortes (Google Research, New York) will highlight her team's data-driven approach to fighting fake news. "+
      "A panel of leaders drawn from academia and the private sector will discuss how? they address the challenges of rapid advances in digital media ?that may ?outpace our ability to ?maximize its benefits"+
      " and minimize the? potential drawbacks."+"<p>"
      +"<p>"+"Your perspective would be a valued contribution to the day's discussions, and we hope very much to see you there! <b>Registration and further information is available at</b> bit.ly/RegisterNE2018." 
      +"<p>"+"Please feel free to share with members of your team who may be interested in joining. Should you have any questions, please don't hesitate to reach out to us via rb70@columbia.edu." + "<p>"+
      "All the best," + "<p>"
      +"<p>"+"René Bastón"+"<br/>"+ 
      "Kathy McKeown"+ "</p>" +
      '<img src="cid:image"><br/>' // Added
      +"</p>"+ "<p>"+"nebigdatahub.org" + "</p>";
    var msgPlain = msgHtml.replace(/<br/>/gi, '
').replace(/(<([^>]+)>)/ig, ""); // clear html tags and convert br to new lines for plain mail
    if (emailSent !="EMAIL_SENT"){  // Prevents sending duplicates
      if(row[6]=="Personal" && row[5]== "Katy") { 
        GmailApp.sendEmail(email, subject, msgPlain,{ htmlBody: msgHtml, inlineImages: {image: blob} }); // Modified
        sheet.getRange(startRow + i, 8).setValue("EMAIL_SENT");
        // Make sure the cell is updated right away in casethe script is     inaterrupted
        SpreadsheetApp.flush();
      }
    }  
  }
}

Note :

• From your script, I couldn't understand about I tried adding an inline image but because I am already the using the option in MailApp, I think it is not going trough.. So if this modification is what you don't want, I'm sorry.
• If you want to use MailApp, you can also use MailApp.sendEmail({to: email, subject: subject, body: msgPlain, htmlBody: msgHtml, inlineImages: {image: blob}});.

If I misunderstand your question, I'm sorry.