Maintaining "table status" when fetching Google Sheets values

Question

I have a list of people that I need to send a schedule to every week. The schedule, and the list of emails for the people it needs to go to, are on a Google Sheet. I'd like to create a script that (a) gets the schedule, and (b) sends it to the list of emails.

My problem, right now, is my Google Apps Script code only sends the schedule as a string (e.g. "role1, person1, role2, person2, role3, person3"), instead of "keeping" its formatting as a table,

I've considered a loop that would grab the values of every row and add a "\n" value at the end, which would break the string after each person. That's not an unworkable solution, but I'd prefer to keep the table because it's more readable.

I suspect that I need to create an HTML table within Google Apps Script, populate it with values from my spreadsheet, and set that HTML table as my email body. That seems odd, because it's already a table (in the spreadsheet).

Is there a way to fetch values while maintaining their "table status"? If not, what are the bare-bones of creating/populating an HTML table?

Here's my code so far, for reference:

function WeeklyReminder() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(); // this directs the script to the spreadsheet
  var sheet = ss.getSheetByName("Automated WVS Weekly Reminder Email"); // this directs the script to the right sheet

  var schedule_values = sheet.getRange("D2:E").getDisplayValues(); // this gets the schedule values; "display values" because we want "what's seen," and not the function that's used to populate the cell

  Logger.log("schedule_values = " + schedule_values);

  var test_email = "[email protected]";
  var volunteer_values = sheet.getRange("B2:B").getDisplayValues();

  Logger.log("volunteer_values = " + volunteer_values);

  MailApp.sendEmail({
     to: test_email,
     subject: 'WVS Weekly Reminder',
     htmlBody: schedule_values.toString(),
     });
}

Note: I'm aware my htmlBody sends...toString() at the moment, that's what I'm looking to change.

So you want to format the Sheet data using HTML. Are you familiar with HTML? You'll need to manually convert the schedule_values, which is a 2D array, into HTML and use that in your email. — IMTheNachoMan

Cooper Cooper · Accepted Answer · 2020-02-27T20:24:52

Building the html table

function WeeklyReminder() {
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Automated WVS Weekly Reminder Email");
  var vs=sheet.getRange(1,4,sheet.getLastRow(),2).getDisplayValues();
  var html='<style>td,th{border:1px solid black;}</style><table>';
  vs.forEach(function(r,i){
    if(i==0) {
      html+=Utilities.formatString('<tr><th>%s</th><th>%s</th></tr>',r[0],r[1]);//I changed the range so that it included the headers
    }else{
      html+=Utilities.formatString('<tr><td>%s</td><td>%s</td></tr>',r[0],r[1]);
    }
    html+='</table>';
  });
  var test_email="[email protected]";
  var volunteer_values=sheet.getRange(2,2,sheet.getLastRow()-1,1).getDisplayValues();
  MailApp.sendEmail({to: test_email,subject: 'WVS Weekly Reminder',htmlBody:html});
}

Maintaining "table status" when fetching Google Sheets values

1 Answers