Populating HTML table with Google Sheet data (rows & columns)

user_10368015 0
Viewed 85

Having issues with what it might be a rather easy fix.

  • Context: My code is currently pulling data from Google Sheets, crafting some sort of table and sending it back to HTML where it repopulates an already existing table.

  • Issue: I am unable to make it so that it builds columns as well as rows. It pastes the data back all in one go (see image for context).

  • Files: GS & HTML. I believe the issue is on how I'm crafting the table. I know the current disposition of '' doesn't make sense, bu

HTML table with Gsheet values: HTML table with GSheet values

Original Gsheet table: Original Gsheet table

Google Script

function populateStratTb2(){
  var tablerows = SpreadsheetApp.getActive().getSheetByName('supp_str').getRange(1, 5, 1000).getValue();
  var tablevalues = SpreadsheetApp.getActive().getSheetByName('supp_str').getRange(4, 1, tablerows).getValues();
  var tvlen = tablevalues.length

 
  var active = SpreadsheetApp.getActive();
  var sheet = active.getSheetByName("supp_str");
  var myRange = sheet.getRange("d3:m" + tvlen); 
  var data    = myRange.getValues();
  var optionsHTML = "";

for ( var r = 0; r < 10; r+=1) {
  for (var i = 0; i < data.length; i+=1) {
    optionsHTML += '<tr><td>' + data[i][r] + '</td></tr>';
  }};
   return optionsHTML;

}

HTML Script

<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
   <script>
   $(function(){
     google.script.run
       .withSuccessHandler(displayData)
       .populateStratTb2();
   });
   function displayData(hl){
     document.getElementById('strattable').innerHTML=hl;
   }
   console.log('MyCode');
   </script>

PS. I have spent a good couple hours scrolling though the forum picking bits and improving my original code. I am sure this question (or similar) has been answered already but I can't manage to find it.

google-apps-script google-sheets html javascript
1 Answers

In your script, how about the following modifications?

Modification 1:

If your for loop is used, how about the following modification?

function populateStratTb2() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('supp_str');
  var tablerows = sheet.getRange(1, 5, 1000).getValue();
  var tablevalues = sheet.getRange(4, 1, tablerows).getValues();
  var tvlen = tablevalues.length
  var myRange = sheet.getRange("d3:m" + tvlen);
  var data = myRange.getValues();
  var optionsHTML = "";
  for (var r = 0; r < 10; r += 1) {
    var row = "";
    for (var i = 0; i < data.length; i += 1) {
      row += '<td>' + data[i][r] + '</td>';
    }
    optionsHTML += '<tr>' + row + '</tr>';
  }
  optionsHTML = '<table border="1" style="border-collapse: collapse">' + optionsHTML + "</table>";
  return optionsHTML;
}
  • I'm worried that your for loop might not be your expected result. So, I would like to proposed one more modified script as "Modification 2".

Modification 2:

If your data is converted to the HTML table, how about the following modification?

function populateStratTb2() {
  var sheet = SpreadsheetApp.getActive().getSheetByName('supp_str');
  var tablerows = sheet.getRange(1, 5, 1000).getValue();
  var tablevalues = sheet.getRange(4, 1, tablerows).getValues();
  var tvlen = tablevalues.length
  var myRange = sheet.getRange("d3:m" + tvlen);
  var data = myRange.getValues();
  var optionsHTML = '<table border="1" style="border-collapse: collapse">' + data.reduce((s, r) => s += "<tr>" + r.map(c => `<td>${c}</td>`).join("") + "</tr>", "") + "</table>";
  return optionsHTML;
}

Note:

  • If you don't want to add the border, please modify <table border="1" style="border-collapse: collapse"> to <table>.

  • From your reply, I added 2 sample scripts for the script for obtaining the same result from reduce and for loop as follows.

    • reduce

        var optionsHTML = '<table border="1" style="border-collapse: collapse">' + data.reduce((s, r) => s += "<tr>" + r.map(c => `<td>${c}</td>`).join("") + "</tr>", "") + "</table>";

    • for loop

        var optionsHTML = "";
  for (var r = 0; r < data.length; r++) {
    var row = "";
    for (var c = 0; c < data[r].length; c++) {
      row += '<td>' + data[r][c] + '</td>';
    }
    optionsHTML += '<tr>' + row + '</tr>';
  }
  optionsHTML = '<table border="1" style="border-collapse: collapse">' + optionsHTML + "</table>";

Reference:

user_71086530
Related