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:

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.

CodePudding user response：

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:

• reduce()