How to get HTML formatted table in Google Apps Script into a document?

Question

I am trying to add the formatted table in the script to a document. But using the append.Paragraph just puts the text with all the HTML tags in it. Ideally the goal is to have the form that feeds this display the output on the page the form is on. But I started here thinking I can redirect from the form to a page that has this document and sort of do the same thing.

function rowOf(team1, dataRange) {
 var ss = SpreadsheetApp.openById("1FyJm85IyKCMskYA4xM-Nj9TxiJt1Qorik7U1gDgbX4o");
 var sheet1 = ss.getSheetByName("Stats1");
 var dataRange = sheet1.getSheetValues(1,14,300,1);
 var dataRange2 = sheet1.getSheetValues(1,2,300,1);
 var ss1 = SpreadsheetApp.openById("1a19U4ifctLtvJTZGf4PcOAEHHvOpg-JP2OwoAw_xkl8");
 var sheet = ss1.getSheetByName("Form Responses 1");
 var data = sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getValues();
 var email = data[0][2];
 var team1 = data[0][1];
 var magellan = data[0][3];
 var subject = team1 +" Team Stats";
 var outRow = null;
 var outRow2 = null;

  for (var i = 0; i < dataRange.length; i++){

  if (dataRange[i][0] == team1){

     outRow = i+1;

    }
  }

 var Header = sheet1.getRange('d1:y1').getValues();
 var u13gneongoalsg1 = sheet1.getRange(outRow,4).getValues();
 var u13gneonshotsg1 = sheet1.getRange(outRow,5).getValues();
 var u13gneontopg1 = sheet1.getRange(outRow,6).getValues();
 var u13gneontop1g1 = sheet1.getRange(outRow,7).getValues();
 var u13gneonfirstg1 = sheet1.getRange(outRow,8).getValues();
 var u13gneonpassesg1 = sheet1.getRange(outRow,9).getValues();
 var u13gneonpasscompg1 = sheet1.getRange(outRow,10).getValues();
 var u13gneonnumpassstringg1 = sheet1.getRange(outRow,11).getValues();
 var u13gneonavgpassstringg1 = sheet1.getRange(outRow,12).getValues();
 var u13gneonlongpassstringg1 = sheet1.getRange(outRow,13).getValues();
 var u13gneonogoalsg1 = sheet1.getRange(outRow,16).getValues();
 var u13gneonoshotsg1 = sheet1.getRange(outRow,17).getValues();
 var u13gneonotopg1 = sheet1.getRange(outRow,18).getValues();
 var u13gneonotop1g1 = sheet1.getRange(outRow,19).getValues();
 var u13gneonofirstg1 = sheet1.getRange(outRow,20).getValues();
 var u13gneonopassesg1 = sheet1.getRange(outRow,21).getValues();
 var u13gneonopasscompg1 = sheet1.getRange(outRow,22).getValues();
 var u13gneononumpassstringg1 = sheet1.getRange(outRow,23).getValues();
 var u13gneonoavgpassstringg1 = sheet1.getRange(outRow,24).getValues();
 var u13gneonolongpassstringg1 = sheet1.getRange(outRow,25).getValues();


  var message = '<HTML><BODY><p>';
 message +=   '<table border="1">';
 message +=   '<tr><td align="center"><b>U13G Neon</b></td><td align="center"><b>Game Totals</b></td><td align="center"><b>'+team1+ '</b></td></tr>';
 message +=   '<tr><td align="center">' +u13gneongoalsg1+ '</td><td align="center"><b>Goals</b></td><td align="center">' +u13gneonogoalsg1+ '</td></tr>';
 message +=   '<tr><td align="center">' +u13gneonshotsg1+ '</td><td align="center"><b>Shots</b></td><td align="center">' +u13gneonoshotsg1+ '</td></tr>';
 message +=   '<tr><td align="center">'+u13gneontopg1 +'</td><td align="center"><b>Time of Possession</b></td><td align="center">'+u13gneonotopg1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneontop1g1 +'</td><td align="center"><b>Time of Possession %</b></td><td align="center">'+u13gneonotop1g1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneonfirstg1 +'</td><td align="center"><b>First Touches</b></td><td align="center">'+u13gneonofirstg1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneonpassesg1 +'</td><td align="center"><b>Passes</b></td><td align="center">'+u13gneonopassesg1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneonpasscompg1 +'</td><td align="center"><b>Pass Completion %</b></td><td align="center">'+u13gneonopasscompg1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneonnumpassstringg1 +'</td><td align="center"><b>Number of Pass Strings</b></td><td align="center">'+u13gneononumpassstringg1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneonavgpassstringg1 +'</td><td align="center"><b>Average Pass String Length</b></td><td align="center">'+u13gneonoavgpassstringg1 +'</td></tr>';
 message +=   '<tr><td align="center">'+u13gneonlongpassstringg1 +'</td><td align="center"><b>Longest Pass String</b></td><td align="center">'+u13gneonolongpassstringg1 +'</td></tr>';
 message +=   '</table>';
 message +=   '</p><p>';
 message += '</body></HTML>';

 // MailApp.sendEmail(email, subject, "", {htmlBody: message});
 var doc = DocumentApp.openById("1vVxSXAOTApq3vV93Kxx-oylp3eew-a7295CVieDGpoU");
 var body = doc.getBody();
  body.clear();
  body.appendParagraph(message);
 return outRow;
 }

Mogsdad Mogsdad · Accepted Answer · 2015-11-04T23:26:39

Sorry to have to say this, but you can't get there from here. You don't put an HTML table into a document - you put an unformatted table in and then format it.

I'll set aside your expressed long-term goal, and just concentrate on getting the content of a spreadsheet into a document table. That can be done with body.appendTable(cells) - where cells is a two-dimensional array of values, such as you'll get from Range.getValues().

function appendTable( cells ) {
  var body = DocumentApp.getActiveDocument().getBody();
  body.appendTable(cells);
}

There's no custom formatting there, just the spreadsheet contents with the document's default "Normal text" style. If that's not to your liking, you can now go through the appended Table and use the DocumentApp methods to format it.

Here's how you'd duplicate a whole sheet into a document:

function demo() {
  var ssId = '--spreadsheet-id--';
  var sheet = SpreadsheetApp.openById(ssId).getSheets()[0];
  var inTable = sheet.getDataRange().getValues();    // 2D array of values

  appendTable(inTable);
}

How to get HTML formatted table in Google Apps Script into a document?

1 Answers