How to format email from spreadsheet data using arrays?

Question

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 = "[email protected]";
var subject = "Capacity Campaign Contact Form";
MailApp.sendEmail(emailAdress,subject,values);
}

Do you need to show some kind of headers in front of your values? Would these headers be on the first row of your sheet? — Serge insas
– Serge insas, Commented Jul 15, 2013 at 16:22

Serge insas · Accepted Answer · 2013-07-16 12:31:11Z

6

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>[email protected]</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>

enter image description here

"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.

edited Jul 16, 2013 at 12:31

answered Jul 15, 2013 at 17:13

Serge insas

46.9k7 gold badges113 silver badges135 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

dericcain Over a year ago

Awesome! I was going to write tons of lines of code to do this and you performed it in a very few. I will have to study this code to really make it sink in my brain. I will also work on formatting it and making it look nice. Say I want to email the data to a specific person, based on the name that is in cell "E" of the "Form Responses" sheet. I created a second sheet "Email" within the same spreadsheet with a list of those names. In my mind, I would say, "if cell "E" of sheet "Form Responses" contains "x", then refer to sheet "Email", lookup "x" and email the appropriate person. Make sense?

Serge insas Over a year ago

Hi, thanks for accepting, please see edit. with this code you'll have both text and html version of your item list.

dericcain Over a year ago

I was totally going to ask how to do that on this forum! I know some HTML, but I did not know how to integrate it with GAS. For some reason, the table is not formatting correctly in the email it is only making 2 rows of data. The first row is correct, but the second row continues with the rest of the data in it - like cells one next to another.

Serge insas Over a year ago

there was indeed a small error in my code (a missing <tr>)... thunderbird accepted it but gmail didn't... sorry about that ;-)

dericcain Over a year ago

Yes!! Props on finding that so quickly! I really do appreciate your help. Thank you.

dericcain · Accepted Answer · 2013-07-16 12:19:08Z

0

As you can see, I am missing a tr opening tag before "Business or Church". However, my code appears to be exactly like yours. You can see what it is doing in the screen shot.

[13-07-16 08:05:34:247 EDT] Here is the data you submitted :<br><br><table style="background-color:white;border-collapse:collapse;" border = 2 cellpadding = 5><th>Question</th><th>Answers</th><tr><td>Timestamp</td><td>Thu Jun 06 2013 10:18:31 GMT-0400 (EDT)</td></tr><td>Business or Church Name</td><td>Tim K</td></tr><td>Name</td><td>Test</td></tr><td>Business or Church Name</td><td>business</td></tr><td>Your Name</td><td>Blank, Gary</td></tr><td>Meeting Date</td><td>date</td></tr><td>Meeting Lead</td><td>Jerry

Screen shot

answered Jul 16, 2013 at 12:19

dericcain

2,3087 gold badges33 silver badges53 bronze badges

Collectives™ on Stack Overflow

How to format email from spreadsheet data using arrays?

2 Answers 2

5 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related