How to Populate HTML Table with Values from Google Sheet List?

Question

I am working in a Google Sheets file with a tab named 'POHistory' that includes a log of all order details placed by customers of our company. Each log entry can have one, or more, rows/lines depending upon how many different SKU's were ordered on that particular order. Each line/entry does include Order No., Vendor, date of order, and order total. Because each entry can repeat PO No (if multiple SKU's were ordered on the order), I want to list out (in table format) an overview of order history for our company that allows a user to quickly get a visual on a listing of all orders placed in an HTML pop-up, without SKU details. Therefore, I know I need to create a loop that grabs only the details I want for the table (order no., vendor, date, order total), but am unsure where to begin. I have done this before with select option lists, but not with tables. Thanks for your help.

APPS SCRIPT:

function htmlOrders() {
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("POHistory");
  var lastRow = sheet.getLastRow();
  var myRange = sheet.getRange("A2:A" + lastRow); 
  var data    = myRange.getValues();
  var optionsHTML = "";
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += '<td>' + data[i][0] + '</td>';
  };
   return optionsHTML;
   }

HTML:
<!DOCTYPE html>
<html>
<head>
</head>
<body>

<table>
  <tr>
    <th>PO Number</th>
    <th>Vendor</th>
    <th>Date</th>
    <th>Total</th>
  </tr>
    <td>
  < ?!= htmlOrders(); ?>
    </td>
</table>

</body>
</html>

Cooper Cooper · Accepted Answer · 2018-04-10T01:17:04

You can put something like this into your html:

<html>
<head>
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
   <script>
   $(function(){
     google.script.run
       .withSuccessHandler(displayData)
       .htmlOrders();
   });
   function displayData(hl){
     document.getElementById('puthere').innerHTML=hl;
   }
   console.log('MyCode');
   </script>
   </head>
   <body>
   <div id="puthere"></div>
   </body>
   </html>

And add orderHistory() to htmlOrders() in the server script and then insert it all at one time into a div clientside.

How to Populate HTML Table with Values from Google Sheet List?

1 Answers