4

My code sends email notifications every time a Google Form is submitted. It works, but I want the collected form data as bold in the email.

for(var i in headers) 
    message += headers[i] + ': '+ e.namedValues[headers[i]].toString() + "\n\n";  


function sendFormByEmail(e) 
{    
  var s = SpreadsheetApp.getActiveSheet();
  var headers = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];    
  var message = "";
  var data = s.getRange(1, 1, 1, s.getLastColumn()).getValues()[0];
  var subject = "";

  //Get active Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();

  //Get Sheet called 'Form responses 1'
  var fr1 = ss.getSheetByName("Form responses 1");

  //Get all emails from 'Emails' tab
  var emails = ss.getSheetByName("Emails");
  var numRows = emails.getLastRow();
  var emailTo = emails.getRange(2, 2, numRows, 1).getValues();

  // The variable e holds all the form values in an array.
  // Loop through the array and append values to the body.

  for(var i in headers) 
    message += headers[i] + ': '+ e.namedValues[headers[i]].toString() + "\n\n";     

  // Insert variables from the spreadsheet into the subject.
  // In this case, I wanted the part number as part of the
  // email subject. These are the 3rd and 16th columns in my form.
  // This creates an email subject like "Parts Inquiry Submission: 729302"
  subject += e.namedValues[headers[1]].toString();

  // Send the email
  MailApp.sendEmail(emailTo, subject, message); 

  // Based off of a script originally posted by Amit Agarwal - www.labnol.org
  // Credit to Henrique Abreu for fixing the sort order
}
Alan Wells
  • 30,746
  • 15
  • 104
  • 152
3simpl
  • 51
  • 1
  • 4
  • 1
    You'd either need to create them inside something like a `` element, or first create your string and do string.bold(), just depends how you're creating all your DOM elements. – Chris W. Jun 06 '17 at 18:27
  • Your `data` and your `headers` are exactly the same. You are getting the same range for both. Shouldn't your data be the last row just added? – Alan Wells Jun 07 '17 at 02:27
  • Thank you all for your suggestions. I ended up getting it work and I've posted the amended code. – 3simpl Jun 08 '17 at 16:11

2 Answers2

2

First of all, you must use the advanced parameter htmlBody in the 4th parameter of sendEmail() and you need to make the body parameter and empty string.

sendEmail(recipient, subject, body, options)

Code:

message = "<strong>" + message + "</strong>";//Wrap message in bold tags

var options = {
  "htmlBody":message
}

// Send the email
MailApp.sendEmail(emailTo, subject, "",options);//Make email body empty string
Alan Wells
  • 30,746
  • 15
  • 104
  • 152
1

Thank you all for your help. I ended up changing it up a bit. Here is what I ended up using.

function sendFormByEmail(e) 
{    

  var message = "";
  var subject = ""; 
  var emailTo = "";
  
  //Gets the active Spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  //Get Sheet called 'Form responses 1'
  var fr1 = ss.getSheetByName("Form responses 1");
  var headers = fr1.getRange(1,1,1,fr1.getLastColumn()).getValues()[0];  
  
  //Get all emails from 'Emails' tab
  var emails = ss.getSheetByName("Emails");
  var emailTo = emails.getRange(2, 2, emails.getLastRow()-1, 1).getValues().toString();

  // For each column in the spreadsheet, add the column name and it's new entry as a line in the email
  message = "<html>";
  for(var i in headers)
    message += headers[i] + ': <b>'+ e.namedValues[headers[i]].toString() + "</b><br><br>"; 
  message += "</html>";
  
  // Set the subject as the Item Number
  subject = e.namedValues[headers[1]].toString();

  // Send the email
  MailApp.sendEmail({
    to: emailTo,
    subject: subject,
    htmlBody: message
  });
  
}
3simpl
  • 51
  • 1
  • 4