Send email from Google Spreadsheet omitting the commas

0
votes

All,

I use a similar script to the below to send emails from a google spreadsheet:

// Sends an email when .......
function emailInfoRequired(row) { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var subject = "Info Needed: " + sheet.getRange("F19").getValues();
  var recipients = "[email protected]"
  var message = "<HTML><BODY>" 
    + "<P>" + sheet.getRange("K1").getValues()
    + "<BR>" + sheet.getRange("K2").getValues()
    + "</HTML></BODY>"; 
  MailApp.sendEmail(recipients, subject, "", {htmlBody: message});
}

What i would like to do is instead of using single cells,i want to include a range such as: sheet.getRange("K2:N2").getValues()

The problem is, when i do this, the email body has a comma between every cell within that range making the email not make any sense plus look ugly. I tried formatting the cells as plain text but that didnt help. Is there a way to send the email of a range and not show the commas?

2
google-apps-scriptgoogle-sheets

2 Answers

1
votes

You could even make it more elegant by using a html table like this :

// Sends an email when .......
function emailInfoRequired(row) { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var subject = "Info Needed: " + sheet.getRange("F19").getValues();
  var recipients = "[email protected]"
  var data = sheet.getRange('A2:C25').getValues();// define your range here
  var message = '<HTML><BODY><table style="background-color:lightblue;border-collapse:collapse;" border = 1 cellpadding = 5><tr>';// change eventually the color here
  for (var row=0;row<data.length;++row){
    for(var col = 0;col<data[0].length;++col){
      message += '<td>'+data[row][col]+'</td>';
    }
    message += '</tr><tr>';
  }
    message += '</tr></table></body></HTML>';
   MailApp.sendEmail(recipients, subject, "", {htmlBody: message});
}

Illustration example :

enter image description here

1
votes

Range.getValues() returns a 2D array and a string representation of this 2D array is printed in your email. Depending on how you want to show it in your email, you have to process each element individually. Something like...

function emailInfoRequired(row) { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var subject = "Info Needed: " + sheet.getRange("F19").getValues();
  var recipients = "[email protected]"
  var values = sheet.getRange("K2:N2").getValues();
  var message = "<HTML><BODY>" ;
  for (var row = 0 ; row < values.length ; row++){
    message +=  "<P>" ;
    for (var col= 0 ; col < values[row].length ; col++){
      message +=  "<BR>" + values[row][col];
    }
  }
  message  += "</HTML></BODY>"; 
  MailApp.sendEmail(recipients, subject, "", {htmlBody: message});
}