0

I am trying to export a specific range of cells in the form of an email. The cells are formatted a certain way, and also have conditional formatting associated for color-coding.

Here is my demo sheet: https://docs.google.com/spreadsheets/d/1ibB87Vhz7wTjKrIrasYSRLoAiadQHtNqqmyl-xywtOI/edit?usp=sharing

Ive gotten the email to send successfully, however I cant figure out how to take the associated formatting, conditional formatting, fonts, borders...etc.

Currently, it's taking the displayValues, and listing them with comas. I'm also looking to get rid of the comas, and if possible, limit the range to only cells with data within the range.

Here is what I've got:

    function sendReport(){
      var incidents = SpreadsheetApp.getActiveSheet().getRange(2,1,4,1).getDisplayValues();
      var subjectRange = SpreadsheetApp.getActiveSheet().getRange("A1");
      var subjectCell = subjectRange.getValues();
      for (i in subjectCell) {
        var rowData = subjectCell[i];
        var subject = rowData [0];
      }
      MailApp.sendEmail("emailaddress@domain.com",
                subject,
                incidents);
    }
Tom Kaplan
  • 1

1 Answers1

0

That's because you are literally sending an Array in your email (since getValues() returns a 2-dimensional Array). If you want to keep formatting, you should construct an html table from this array by iterating over it and append the result to email body via htmlBody property in options.

Take a look at the documentation for detailed info on how to get styles from Range. You will have to map each styling into style="" attribute for HtmlElements you want to be formatted, like so (to save resources, get styling in bulk and include into Array loop):

  1. getFontColors() - color:{method result} (if one, apply to HtmlTableElement);
  2. getFontFamilies() - font-family:{method result};
  3. ...etc...

or simply get all styles in bulk with getTextStyles() and access individual style property with appropriate methods (note that you will still need to access background colors with getBackgrounds() method, height with getHeight(), etc).

Oleg Valter is with Ukraine
  • 9,619
  • 8
  • 37
  • 59
  • So I found this script online, and adjusted it for my data. However, I can't figure out how to attribute my cell formatting to the html table format in the script with getBackgrounds or getTextStyles... Any help is greatly appreciated. – Tom Kaplan Jun 10 '19 at 22:18
  • https://stackoverflow.com/questions/36529890/emailing-google-sheet-range-with-or-without-formatting-as-a-html-table-in-a-gm – Tom Kaplan Jun 10 '19 at 22:24
  • Tom, I assume the library version worked? Take a look at its [source code](https://github.com/mogsdad/SheetConverter/blob/master/SheetConverter/Code.gs) - it creates shorthand methods with the same principle as I've explained (you can always use the library itself if you don't want to get into details of how it works) by concatenating strings with `style` attribute rules – Oleg Valter is with Ukraine Jun 11 '19 at 02:28