I have created a spreadsheet for our coaches. They choose a sport and then spreadsheet fills with a roster of possible students. They click the check box next to the name of the student(s) attending the event, type the date, and click the Send button. It sends an email to the teachers listed (2nd tab has all rosters and emails). The script I wrote does all this no problem. The issue I am having deals with formatting. The names print out horizontally with a comma separating each name:

Student One, Student Two (etc.)

[This was in the original post, but I figured out how to skip blank spots in an array If a student in the roster is skipped the printout looks like this:

Student One,,Student Three, Student Four,,Student Six (etc.) ]

I don't want the name to print if the checkbox isn't checked but I would like for the printout to look a little cleaner on an email. I used an array to read the names and I realize it's just printing out the array and it has an empty box. (solved the empty name part) I would like the email to look like:

Student One

Student Two

I am unsure how to accomplish this and have searched around quite a bit. What I have is functional, but doesn't look great. Another loop could accomplish this but I don't know how to do that while also formatting the email. It definitely doesn't like when I try to put a loop inside of there.

Here's the spreadsheet: Sample Sports Email Spreadsheet

Here is the code I have typed:

function emailRoster() 
{
  var teacher = SpreadsheetApp.getActive().getRange("Rosters!J2:J4").getValues();
  var roster = SpreadsheetApp.getActive().getRange("Sheet1!A6:B").getValues();
  var sport = SpreadsheetApp.getActive().getRangeByName("Sport").getValue();
  var date = SpreadsheetApp.getActive().getRangeByName("Date").getValue();
  var lenT = teacher.length;
  var lenR = roster.length;
  var playerTrue = [];
  
  for(var i=0; i<lenR; i  ){
      if(roster[i][1])
      playerTrue[i] = roster[i][0];
      
    }
    playerTrue = playerTrue.filter(String); //recently added...fixed the printout so it ignores blank parts of the array
    playerTrue.forEach(function(name) {
          Logger.log(name);
         
      });
   
  for(var p=0; p<lenT-1; p  ){
     

    var email = teacher[p];
    var subject = "Students out for "   sport;
    var body = "Teachers,\nThe following students will be out for "  sport  " on "  date  ": \n\n"   playerTrue  "\n";
   
    
    GmailApp.sendEmail(email,subject,body);
  }
};

EDIT

I have created another function to try and get it to return each name with a return after each name, but I can only get it to do the first name:

function createRoster(){
  var roster = SpreadsheetApp.getActive().getRange("Sheet1!A6:B").getValues();
  var playerTrue = [];
  var lenR=roster.length;
  
  for(var i=0; i<lenR; i  ){
      if(roster[i][1])
      playerTrue[i] = roster[i][0];    }
    playerTrue = playerTrue.filter(String);
  Logger.log(playerTrue);
  for(var b=0; b<lenR-1; b  ){
    return playerTrue[b]   "\n";
   
  }
   Logger.log(playerTrue);
 };

So now the body variable in the original function looks like this:

var body = "Teachers,\nThe following students will be out for "  sport  " on "  date  ": \n\n"   createRoster()  "\n";

CodePudding user response：

From your showing script, it seems that playerTrue is an array. When the array is directly used as the text body, such a situation occurs. When you want to align the value to the vertical direction, how about the following modification using join?

From:

var body = "Teachers,\nThe following students will be out for "  sport  " on "  date  ": \n\n"   playerTrue  "\n";

To:

var body = "Teachers,\nThe following students will be out for "   sport   " on "   date   ": \n\n"   playerTrue.join("\n")   "\n";

Or, when you want to put the value every 2 lines, how about the following modification?

var body = "Teachers,\nThe following students will be out for "   sport   " on "   date   ": \n\n"   playerTrue.join("\n\n")   "\n";

Reference:

• join()