Send email from Google Spreadsheet omitting the co

2019-03-04 01:07发布

站内问答 / 前沿技术
1196 2 3

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@email.com"
  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条回答
地球回转人心会变
2楼-- · 2019-03-04 01:34

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 = "testmail@gmail.com"
  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

查看更多
0人赞 添加讨论(0) 举报
叛逆
3楼-- · 2019-03-04 01:38

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@email.com"
  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});
}
查看更多
0人赞 添加讨论(0) 举报
登录 后发表回答
相关问题
查看全部
相关文章
查看全部
收藏的人(3)