Google Sheet Script, How do I format the data range in my email

Question

My emails include a range from my Google Sheet. The email body looks like this now with comma's in between all the names in one loge sentence:

Tom,Willaims,Terry,Lord,Jack,Masters

The A column has the first name the B column has the last name.

I don't know how to format the sheet range

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
var dataRange = sheet.getRange(startRow, 1, numRows, 1);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
for (i in data) {
var row = data[i];
var emailAddress = row[0]; // First column
var message = sheet.getRange("Sheet1!A19:B21").getValues();
var subject = 'Your Current Bus Roster';
MailApp.sendEmail(emailAddress, subject, message);
}
}

The body of the email should look like a data table: Tom Willaims

see e.g. [this question](https://stackoverflow.com/questions/15164655/generate-html-table-from-2d-javascript-array/15164958) — sinaraheneba, Jun 18 '19 at 04:52

score 0 · Accepted Answer · answered Jun 18 '19 at 07:33

0

You're not distinguishing between the data in Column A and Column B. You need to loop through the message data and build up the message each time you go through the loop; hence messagetext.

Something like this snippet ought to do the trick. I've left in a logger statement so that you can view the Message.

for (i in data) {
    var row = data[i];
    var emailAddress = row[0]; // First column
    var messagetext = "";
    var message = sheet.getRange("Sheet1!A19:B21").getValues();
    for (var i=0; i<message.length;i++){
        messagetext = messagetext + message[i][0]+" "+message[i][1]+"\n";  
    }
    Logger.log(messagetext);
    var subject = 'Your Current Bus Roster';
    MailApp.sendEmail(emailAddress, subject, messagetext);
}

answered Jun 18 '19 at 07:33

Tedinoz

5,911
3
25
35

I want to add more columns of data so I changed A19:B21 to A19:E21. Column C, D, E do not show up? only A &B? – bciepiela10gmailcom Jun 18 '19 at 11:12
Are you asking me or telling me? All I would say is that you originally said: ColA=first name, ColB=last name. So what is the data in ColC, D and E. – Tedinoz Jun 18 '19 at 11:19
Yes, I want to change the data from the original, it is now: ColA=1, ColB=City, ColC=First Name, ColD=Last Name, ColE=Phone Number – bciepiela10gmailcom Jun 18 '19 at 11:30
Go right ahead. Not sure what a value of 1 in every row of ColA will achieve, but that's up to you. – Tedinoz Jun 18 '19 at 11:54
Let me clarify, ColA=Row Number I have changed the range to A19:E21 the data in columns C, D, E do not show in the email, Why? – bciepiela10gmailcom Jun 18 '19 at 12:01
_the data in columns C, D, E do not show in the email, Why?_ Because **you** have not written the code to include them in the message. `messagetext = messagetext + message[i][0]+" "+message[i][1]+"\n";` constructs the message from ColA (first name)="message[i][0]" and ColB (last name)="message[i][1]". If you want the message to include values from Columns C, D & E, you need to edit this line to add those values. Something like:`messagetext = messagetext+message[i][0]+" "+message[i][1]+" "+message[i][2]+" "+message[i][3]+" "+message[i][4]+"\n";` – Tedinoz Jun 18 '19 at 12:37
Thank you! What code needs to be added to set specific column widths? Here is the email now: 1 NORTH VIRGINIA Tom Willaims 5615612211 2 SOUTH VIRGINIA Terry Lord 7895845555 3 LEXINGTON Jack Masters 4578754412 I would like the columns to be the max width so they line up. – bciepiela10gmailcom Jun 18 '19 at 13:01
You have to decide whether you are sending an html email (in which case you "might" (I don't know) be able to have fixed width columns, OR plain text, where (I think) the column widths can't be adjusted. Either way, you are introducing a new topic and you should open a new question to deal with it. – Tedinoz Jun 18 '19 at 21:39
If you found the code in this answer helpful, you should consider accepting it. – Tedinoz Jun 18 '19 at 21:40

Google Sheet Script, How do I format the data range in my email

1 Answers1