Email Google Sheet cells and format

问题:

Background: I am a teacher. I gave a test through Forms. I graded the test by using various background colors on each cell (which represented an answer to a question by a student). Each row of the sheet has their email address in Column B.

Problem: I would like to email the entire row, including formatting, to that address in Column B so that each student has a record of their answers and how I graded them.

Question: How can I email a row of data, including formatting?

I am working with the following script, which works well for emailing a single cell without formatting:

`function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 1;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[1];  // Second column
    var message = row[0];       // I want the whole row, including formatting.
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
    ContentService.createTextOutput("hello world!");
  }
}`

回答1:

Here it is.

I decided to add another function as it makes it a little cleaner. You'll be able to adjust the styles of the output by playing with the css styles. If you keep the commented lines your can use them for debugging. I tested the code with them and it looks good. So let me know how it works on the emails.

  function sendEmails() 
  {
  var br='<br />';
  var sheet=SpreadsheetApp.getActiveSheet();  
  var dataRange=sheet.getDataRange();
  var dataA=dataRange.getValues();
  var backA=dataRange.getBackgrounds();
  //var s='';//Please leave the commented lines.  If needed for the future they are handy to have
  for (var i=1;i<dataA.length;i++) 
  {
    var emailAddress=dataA[i][1];  
    var message=formatRow(sheet.getName(),dataA[i],backA[i],dataA[0]);
    var subject="Sending emails from a Spreadsheet";
    //s+=br + '<strong>EmailAddress:</strong>' + emailAddress + br + '<strong>Subject:</strong>' + subject + br + message + '**************************************' + br;
    MailApp.sendEmail({to:emailAddress,subject:subject,htmlBody:message});

  }
  //var userInterface=HtmlService.createHtmlOutput(s);
  //SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Email Message')
}

I just noticed the yellow background so I quickly added another section for it.

   //assume Timestamp,EmailAddres,Score,FirstName,LastName,Section...
function formatRow(sheetName,rowA,rowbackA,titleA)
{
  var br='<br />';
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getSheetByName(sheetName);
  var html='';
  if(rowA && rowbackA)
  {
    html='';
    for(var j=0;j<rowA.length;j++)
    {
      switch(rowbackA[j])
      {
        case '#ff0000':
          html+=br + '<span style="font-weight:600;font-size:20px;">' + titleA[j] + ':</span>' + br + '<span style="background-color:#ff0000;">' + rowA[j] + '</span>' + br;
          break;
        case '#ffff00':
          html+=br + '<span style="font-weight:600;font-size:20px;">' + titleA[j] + ':</span>' + br + '<span style="background-color:#ffff00;">' + rowA[j] + '</span>' + br;
          break;
        case '#ffffff':
          html+=br + '<span style="font-weight:600;font-size:20px;">' + titleA[j] + ':</span>' + br + '<span style="background-color:#ffffff;">' + rowA[j] + '</span>' + br;
          break
      }
    }
  }
  return html;
}

Just a reminder I'm using #ff0000 for red so don't change to a different shade without making a change to the code.

In the event that one student's email gets eaten by the dog, you might like to send just one email.

function sendOneEmail(firstName,lastName) 
{
  if(firstName && lastName)
  {
    var br='<br />';
    var sheet=SpreadsheetApp.getActiveSheet();  
    var dataRange=sheet.getDataRange();
    var dataA=dataRange.getValues();
    var backA=dataRange.getBackgrounds();
    //var s='';//Please leave the commented lines.  If needed for the future they are handy to have
    for (var i=1;i<dataA.length;i++) 
    {
      if(firstName==dataA[i][3] && lastName==dataA[i][4])
      {
        var emailAddress=dataA[i][1];  
        var message=formatRow(sheet.getName(),dataA[i],backA[i],dataA[0]);
        var subject="Sending emails from a Spreadsheet";
        //s+=br + '<strong>EmailAddress:</strong>' + emailAddress + br + '<strong>Subject:</strong>' + subject + br + message + '**************************************' + br;
        MailApp.sendEmail({to:emailAddress,subject:subject,htmlBody:message});
       }
    }
    //var userInterface=HtmlService.createHtmlOutput(s);
    //SpreadsheetApp.getUi().showModelessDialog(userInterface, 'Email Message')
  }
}

Here's a birds eye view of the Spreadsheet.