Google sheets | Sending a cell range to email body

Question

I currently have a script that references one cell (D28) and places it in the email body. Is it possible to reference a range of cells? I wish to send cells (D28:D40) to the email body.

Any help much appreciated.....

function emailPdf(){ // this is the function to call
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sh = ss.getSheets()[3];
  var shName = sh.getName()

  sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange('D28').getValue());
}
function sendSpreadsheetToPdf(sheetNumber, pdfName, email, subject, date, htmlbody) {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var spreadsheetId = spreadsheet.getId();
  var sheetId = sheetNumber ? spreadsheet.getSheets()[sheetNumber].getSheetId() : null;
  var url_base = spreadsheet.getUrl().replace(/edit$/,'');

  var url_ext = 'export?exportFormat=pdf&format=pdf'   // export as pdf

      + (sheetId ? ('&gid=' + sheetId) : ('&id=' + spreadsheetId)) 
      // following parameters are optional...
      + '&size=A4'      // paper size
      + '&portrait=true'    // orientation, false for landscape
      + '&fitw=true'        // fit to width, false for actual size
      + '&sheetnames=true&printtitle=false&pagenumbers=true'  // hide optional headers and footers
      + '&gridlines=false'  // hide gridlines
      + '&fzr=false';       // do not repeat row headers (frozen rows) on each page

  var options = {
    headers: {
      'Authorization': 'Bearer ' +  ScriptApp.getOAuthToken(),
    }
  }

  var response = UrlFetchApp.fetch(url_base + url_ext, options);
  var blob = response.getBlob().setName(pdfName + '.pdf');
  if (email) {
    var mailOptions = {
      attachments:blob, htmlBody:htmlbody
    }
MailApp.sendEmail(
      email, 
      subject+" | "+date+" (" + pdfName +")",
      "html content only", 
      mailOptions);

MailApp.sendEmail(
      Session.getActiveUser().getEmail(), 
      subject+" | "+date+" (" + pdfName +")",
      "html content only", 
      mailOptions);
  }
}

Answer 1

This is a script I have that pulls multiple values and puts them into an array and emails them. Make sure after .getValues() you have .toString() to converts the value into strings which might be where you are encountering your "Ljava.lang.Object;@7d193b9b" problem. It will just produce a list with values separated by commas.

so like:

var list = refsheet.getRange(2, 14, 2+i).getValues().toString()

then for complete example:

    function AttendanceAlert() {
      var refsheet = SpreadsheetApp.getActive().getSheetByName("AttendanceNotification")
      var column = refsheet.getRange('N2:N');
      var cell = refsheet.getRange(1, 10).getValue(); //get the date I want to search for
      var celldate = new Date(cell);
      var date = Utilities.formatDate(celldate, "GMT","EEE, MM-dd-yy");
      var values = column.getValues(); // get all data in one call
      var ct = 0;
      while ( values[ct][0] != "" ) {
        ct++;
      }
     var numbers = ct

      for(var i = 0; i < numbers; i++) {
      var list = refsheet.getRange(2, 14, 2+i).getValues().toString()
      Logger.log(list) 
      }
       var recipientsTO = "recipient@email.com" + "," + "recipient@email.com";
      MailApp.sendEmail(recipientsTO, "Attendance Update :"+" "+ date , "These individuals have 5 or more total infractions (see parentheses for total), and one recent infraction as of "+ date + ":" +" "+ list + '\n' +

}

Now for your example:

sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange(28,4,13,1).getValues().toString());

Let me know if that works.

Update:

To get the values seperated by line breaks rather than commas, try this:

sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange(28,4,13,1).getValues().join("\n"));

if that doesn't work, try:

sendSpreadsheetToPdf(3, shName, ('email@gmail.com'), sh.getRange('B3').getValue(), Utilities.formatDate(sh.getRange('B4').getValue(), "NZ", "EEE MMM dd"), sh.getRange(28,4,13,1).getValues().join('<br/>'));