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);
  }
}

Can you use getRange(row, column, numRows, numColumns) , and then getValues() with the array? — Warren Meroney, Mar 09 '20 at 15:05
You can also embed a table in the email: https://stackoverflow.com/questions/50027628/google-sheets-script-email-with-a-html-table-that-only-has-a-set-of — Warren Meroney, Mar 09 '20 at 15:16
Thanks for the reply. I have tried your suggestion ```sh.getRange(28,4,13,1).getValue()```, but unfortunately it only returns the first cell (D28) in the email body. I might have to give allot of thought to embed a table. It is a bit beyond me. I don't need any formatting, just the data from cells (D28:D40) with simple line breaks between the cells. — Craig, Mar 11 '20 at 07:32
https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues — Warren Meroney, Mar 11 '20 at 14:53
Hi there, I have tried both getVaule() and getvaules(). getValues() returns the message "Ljava.lang.Object;@7d193b9b" in the email body. — Craig, Mar 13 '20 at 03:01

Warren Meroney · Accepted Answer · 2020-03-16T15:16:54.010

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/>'));

Yes, that is working. Thanks heaps! Text is now populated to the email body (in a string) with a comma separating each row of the spreadsheet. Formatting this with line breaks rather than commas would require an HTML template...correct? — Craig, Mar 14 '20 at 06:40

Google sheets | Sending a cell range to email body

1 Answers1