如何使用数组格式化电子表格数据中的电子邮件？ [英] How to format email from spreadsheet data using arrays?

问题描述

我对编码完全陌生，我需要一些代码的帮助。这是我的问题：我想在Google电子表格中输入许多单元格的数据并通过电子邮件发送。我想出了如何将数据导入电子邮件并发送电子邮件，但数据仅以逗号分隔，难以区分。我怎样才能让每个单元格的数据出现在电子邮件的单独一行中？我相信我可以用数组来做到这一点，但正如我所说，我是新来的，我不知道如何用我迄今读过的东西来做到这一点。如果你能提供一个解释，那也将不胜感激 - 希望这会让我不再问同样的问题并教我。感谢您的耐心和帮助！

 函数emailData（）{
 
 //从响应表格
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var responses = ss.getSheetByName（Form Responses）; 
 var lastRow = respond.getLastRow（）; 
 var data = respond.getRange（A+（lastRow）+：AK+（lastRow））; 
 var values = data.getValues（）; 
 
 //从电子邮件表中收集电子邮件地址（我还没有收到）
 var email = ss.getSheetByName（Email）; 
 var startRow = 2; 
 var emailAdress =[e-mail]; 
 var subject =容量营销联系表单; 
 MailApp.sendEmail（emailAdress，主题，值）; 
 
  

解决方案

是一个2维数组，对应于表中的一行，这意味着您必须遍历其第一个元素。
我写了一个带有函数的小脚本，用这样的标题和值组成消息：

  function test ）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var responses = ss.getSheetByName（Form Responses）; 
 var lastRow = respond.getLastRow（）; 
 var values = respond.getRange（A+（lastRow）+：AK+（lastRow））。getValues（）; //获取范围和值一步
 var headers = response.getRange（A1：AK1）。getValues（）; //对标题
做同样的事情var message = composeMessage（headers，values）; //用2个数组作为参数调用函数
 Logger.log（message）; //检查结果，然后将带有消息的电子邮件作为文本体发送
} 
 
函数composeMessage（标头，值）{
 var message = '这里是数据：'
 for（var c = 0; c< values [0] .length; ++ c）{
 message + ='\ n'+ headers [0] [c ] +'：'+ values [0] [c] 
} 
返回消息; 
} 
  

请注意，您可以使用相同的结构来构建更好看的电子邮件HTML格式，使用 html表格（请参阅参考资料）

---

EDIT2

我写了一段代码，可以在文本和HTML中生成表格，随意改善HTML格式与颜色等......

  function testMail（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var responses = ss.getSheetByName（Sheet1）; 
 var lastRow = respond.getLastRow（）; 
 var values = responses.getRange（A+（lastRow）+：M+（lastRow））。getValues（）; 
 var headers = respond.getRange（A1：AK1）。getValues（）; 
 var message = composeMessage（headers，values）; 
 var messageHTML = composeHtmlMsg（headers，values）; 
 Logger.log（messageHTML）; 
 MailApp.sendEmail（Session.getEffectiveUser（）。getEmail（），'test html'，message，{'htmlBody'：messageHTML}）; 
 
 
函数composeMessage（headers，values）{
 var message ='这里是你提交的数据：\\\
'
 for（var c = 0 ; c< values [0] .length; ++ c）{
 message + ='\ n'+ headers [0] [c] +'：'+ values [0] [c] 
 } 
返回消息; 
} 
 
 
函数composeHtmlMsg（标题，值）{
 var message ='以下是您提交的数据：< br>< br>< table style =background-color：lightblue; border-collapse：collapse; （var c = 0; c< values [0] .length;< th>数据< th>< th>< th>数据< th>< th>< ++ c）{
 message + ='< tr>< td>'+ headers [0] [c] +'< / td>< td> + values [0] [c] + '< / td>< / tr>'
} 
返回消息+'< / table>'; 
} 
  

编辑3

下面你的评论：这是我的测试表+屏幕截图的日志。检查你的日志，看看你的数据是否有类似的结构。

：920 CEST]以下是您提交的数据：< br>< br>< table style =background-color：lightblue; border-collapse：collapse; < th>>< tr>< td>< th>第>< / td>< td>第< / td> ;< / TR>< TR>< TD> NOM< / TD>< TD>威尔士< / TD>< / TR>< TR>< TD>prénom< / TD>< TD> xavier< td>< tr>< td> adresse< / td>< td> Sunset Bld，45678< / td>< tr>< < td>< td>< td>< td>< td>< td>< tr>< td> ville< tr>< td>< ; TD>支付< / TD>< TD>美国< / TD>< / TR>< TR>< TD>电子邮件< / TD>< TD> [e-mail]< / TD> < / TR>< TR>< TD> TEL1< / TD>< TD> 1212345654345< / TD>< / TR>< TR>< TD> TEL2< / TD>< TD>< ; / TD>< / TR>< TR>< TD> COMMUN< / TD>< TD>剧院< / TD>< / TR>< TR>< TD> GROUPE< / TD>< ; TD>节< / TD>< ; / TR>< TR>< TD> organisme< / TD>< TD> XXX和LT; / TD>< / TR>< /表>

• th是标题标签（在<>之间，我不能在这里写它，因为浏览器不显示它）


• td是单元格标签（在<>之间，因为浏览器不显示它，所以我不能在这里写入）


• tr是行标记（在<>之间，因为浏览器不显示它，所以我不能在这里写）

这些标签中的每一个都必须以/ t ...结束标签（与周围的<>）有效为终止。脚本中的循环结构会自动处理，就像您在日志数据中看到的一样。

还要注意，我在循环结尾添加了一个表尾标记...我在第一个代码中忘了它，但它在我的测试中就像那样工作。

I am totally new to coding and I need some help on a bit of code. Here is my problem: I want to take many cells of data in a Google spreadsheet and send it in an email. I figured out how to pull the data into an email and the email sends, however, the data is only separated by commas and it is hard to distinguish. How can I make each cell's data appear on a separate line in the email? I believe I can do this with arrays, but as I said, I am new to this and I cannot figure out how to do it with what I have read so far. If you could provide an explanation, that would be greatly appreciated as well - this will, hopefully, keep me from asking the same question again and teach me. Thank you for your patience and help!

function emailData() {

//Gather data from "Responses" sheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var responses = ss.getSheetByName("Form Responses");
var lastRow = responses.getLastRow();
var data = responses.getRange("A"+(lastRow)+":AK"+(lastRow));
var values = data.getValues();

//Gather email addresses from "Email" sheet (I have not gotten this far yet)
var email = ss.getSheetByName("Email");
var startRow = 2;
var emailAdress = "[e-mail]";
var subject = "Capacity Campaign Contact Form";
MailApp.sendEmail(emailAdress,subject,values);
}

解决方案

Your array (called Values) is a 2 dimension array corresponding to a row in your sheet, it means that you have to iterate through its first element. I wrote a small script with a function that composes the message with headers and values like this :

function test(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responses = ss.getSheetByName("Form Responses");
  var lastRow = responses.getLastRow();
  var values = responses.getRange("A"+(lastRow)+":AK"+(lastRow)).getValues();// get the range and values in one step
  var headers = responses.getRange("A1:AK1").getValues();// do the same for headers
  var message = composeMessage(headers,values);// call the function with 2 arrays as arguments
  Logger.log(message);// check the result and then send the email with message as text body
}

function composeMessage(headers,values){
  var message = 'Here are the data :'
  for(var c=0;c<values[0].length;++c){
    message+='\n'+headers[0][c]+' : '+values[0][c]
  }
  return message;
}

note that you could use the same structure to build an even better looking email in html format using a html table (see reference here)

---

EDIT2

I wrote a little piece of code that generates both in text and HTML in a table, feel free to improve HTML formating with colors etc...

function testMail(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var responses = ss.getSheetByName("Sheet1");
  var lastRow = responses.getLastRow();
  var values = responses.getRange("A"+(lastRow)+":M"+(lastRow)).getValues();
  var headers = responses.getRange("A1:AK1").getValues();
  var message = composeMessage(headers,values);
  var messageHTML = composeHtmlMsg(headers,values);
  Logger.log(messageHTML);
  MailApp.sendEmail(Session.getEffectiveUser().getEmail(),'test html', message,{'htmlBody':messageHTML});
}

function composeMessage(headers,values){
  var message = 'Here are the data you submitted :\n'
  for(var c=0;c<values[0].length;++c){
    message+='\n'+headers[0][c]+' : '+values[0][c]
  }
  return message;
}


function composeHtmlMsg(headers,values){
  var message = 'Here are the data you submitted :<br><br><table style="background-color:lightblue;border-collapse:collapse;" border = 1 cellpadding = 5><th>data</th><th>Values</th><tr>'
  for(var c=0;c<values[0].length;++c){
    message+='<tr><td>'+headers[0][c]+'</td><td>'+values[0][c]+'</td></tr>'
  }
  return message+'</table>';
}

EDIT 3

following your comment : here is the log of my test sheet + screen capture. Check your log to see if you get a similar structure with your data.

[13-07-16 14:29:40:920 CEST] Here are the data you submitted :<br><br><table style="background-color:lightblue;border-collapse:collapse;" border = 1 cellpadding = 5><th>data</th><th>Values</th><tr><tr><td>Titre</td><td>Mr</td></tr><tr><td>nom</td><td>Wales</td></tr><tr><td>prénom</td><td>xavier</td></tr><tr><td>adresse</td><td>Sunset Bld, 45678</td></tr><tr><td>code</td><td>5000</td></tr><tr><td>ville</td><td>Los Angeles</td></tr><tr><td>pays</td><td>USA</td></tr><tr><td>email</td><td>[e-mail]</td></tr><tr><td>tél1</td><td>1212345654345</td></tr><tr><td>tél2</td><td></td></tr><tr><td>Commun</td><td>Théâtre</td></tr><tr><td>GROUPE</td><td>Festival</td></tr><tr><td>organisme</td><td>xxx</td></tr></table>

• "th" is the header tag (between <>, I cannot write it here because the Browser doesn't show it)
• "td" is the cell tag (between <>, I cannot write it here because the Browser doesn't show it)
• "tr" is the "row" tag (between <>, I cannot write it here because the Browser doesn't show it)

Each of these tags must be terminated by a /t... closing tag (with surrounding <>)to be valid. The loop structure in the script takes care of that automatically as you can see in the log data.

Note also that I added a table end tag at the end of the loop... I forgot it in my first code but it worked like that in my tests.

这篇关于如何使用数组格式化电子表格数据中的电子邮件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！