Mailing Spreadsheet data

Question

I would like to send an email to a list of people that contains a table and charts extracted from a spreadsheet.

Doing this is manually is simple - copy the range to clip board, paste into the message editor. Similarly with charts. However, my application requires multiple charts and ranges which makes the process time consuming and error prone.

I have tried to build the message as follows:

function makeMail(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var dataTable = ss.getSheetByName("calc")
                    .getRange("B116:I125")
                    .getDataTable(true);
  var sData = ss.getSheetByName("Results")                    
                .getRange("A5:F22")
                .getValues();

  var chartImage = Charts.newLineChart()
                      .setTitle('Top Teams')
                      .setDataTable(dataTable)
                      .build()
                      .getAs('image/png'); //get chart as image

  var mB = "<h2> Testing Mail </h2>";
  mB +=  "<style> table,th,td { border:1px solid black;} </style>";
  mB +=  " <table> <tr> <th>Team Name</th> <th> Owners </th> <th>This Week$</th>"
  mB +=  " <th>Rank</th><th>YTD pts</th><th>YTD Rank</th></tr></tr>";
  for (var i=0;i<6;i++){
    mB += "<td> " + sData[2][i] + "</td>";
    mB += "</tr>";
  } 
  mB += "</table>";
MailApp.sendEmail({
  to: "xyzzy@gmail.com",
  subject: "Chart",
  htmlBody: mB,
  inlineImages:{
  chartImg: chartImage
  }
 });  
}

The main problems with his approach are the complexity of the code to perform such a trivial task and there seems to be a problem with the way that HTML is rendered by the mailer - the style tags for the table have no effect.

Is there a better way to approach this problem.

style must be inserted in the table tag itself (for example : and again in the tr ot th tags for color, padding, backgrounds...) and I'm afraid there is no simpler solution ;-| note also that there are some limitations in the tags you can use , here is a ref for UiApp but it is roughly the same for html messages (not really sure about this) https://developers.google.com/apps-script/ui_supportedStyles — Serge insas, Apr 03 '14 at 18:06
see also this interesting post from Mogsdad : http://stackoverflow.com/questions/20570361/how-to-use-css-in-google-scripts-specifically-to-design-hyperlink-button/20572401#20572401 — Serge insas, Apr 03 '14 at 18:14
@Serge got it. If you post the above as an answer I will mark it as answered — MalcL, Apr 03 '14 at 22:34
Thanks but my answer is just partial, that's why I used comments :-) — Serge insas, Apr 04 '14 at 05:43

score 1 · Accepted Answer · answered Apr 03 '14 at 19:03

1

Besides styles already addressed in a comment, you can speed it up by using pre-created charts in rhe sheets and change range if needed. You can also use named ranges to make it easier to read and maintain by editing ranges from the spreadsheet instead of modifying code.

answered Apr 03 '14 at 19:03

Zig Mandel

19,571
5
26
36

I agree with comments about named ranges. I have tried using interactively created charts. Old sheets produce a chart gibberish and new sheets deliver nothing. I used the following to test: – MalcL Apr 03 '14 at 22:28
I used the following to test: function sendChart(){ var ss = SpreadsheetApp.getActiveSpreadsheet(); var chartsheet = ss.getSheetByName("Results"); var chart = chartsheet.getCharts(); var chartImage = chart[0].getAs("image/png"); MailApp.sendEmail({ to: "xyzzy@gmail.com", subject: "test chart", htmlBody: "

", inlineImages: { chartImg: chartImage } }); } – MalcL Apr 03 '14 at 22:31
I went back and looked at this again. I found that the chart has to be "published" in order for it to be sent in an email. It now works in "new" sheets – MalcL Apr 03 '14 at 22:43

Mailing Spreadsheet data

1 Answers1