将图像添加到Google表格电子邮件应用脚本 [英] Add Image to Google Sheets email App Script

问题描述

我正在尝试在邮件末尾添加图片，但我不知道该怎么做.我尝试添加内联图像，但是因为我已经在MailApp中使用该选项，所以我认为它不会过时.

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

我希望它出现在"Kathy McKeown"之后和"nebigdatahub.org" URL之前

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

谢谢您的帮助！

这是代码:

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 [e-mail]." + "<p>"+
"All the best," + "<p>"
+"<p>"+"René Bastón"+"<br/>"+ 
"Kathy McKeown"+ "</p>" +
+"</p>"+ "<p>"+"nebigdatahub.org" + "</p>";
var msgPlain = msgHtml.replace(/\<br\/\>/gi, '\n').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();
   }
  }  
 }
}

推荐答案

此修改如何?

• 在进行循环之前，图像已导入到blob.
• 使用<img src="cid:image"><br/>和inlineImages: {image: blob}添加图像.

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

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 [e-mail]." + "<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, '\n').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();
      }
    }  
  }
}

注意:

• 从您的脚本来看，我不了解I tried adding an inline image but because I am already the using the option in MailApp, I think it is not going trough..因此，如果您不希望执行此修改，很抱歉.
• 如果要使用MailApp，也可以使用MailApp.sendEmail({to: email, subject: subject, body: msgPlain, htmlBody: msgHtml, inlineImages: {image: blob}});.

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.

这篇关于将图像添加到Google表格电子邮件应用脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！