Dynamic html Table from an array values using javascript in Code.gs

Question

I am a total novice to javascript, but i am willing to learn this somehow.

As you can see in the attached picture, I have managed to get some data extracted from a google sheet in to an array. I am struggling to this array data into an html page, can someone please guide me how this could be done javascript. The table should be dynamic, since each time a search is done the No of rows return to the array will change. the data is in a variable on the code.gs file I need to link it to javascript. much much gratitude.

Execution Log

score 1 · Answer 1 · answered Nov 13 '21 at 12:14

If you want to fetch data from your spreadsheet and include in a dynamic html page, try : (change id and gid as necessary)

<html>
<title>Google Sheets json endpoint V4</title>
<author>Mike Steelson</author>
<style>
table {border-collapse: collapse;}
th,td{border: 1px solid black;}
</style>
<body>
<div id="json">json here</div>
<script>
var id = '_______your id_________';
var gid = '_____your gid_________';
var url = 'https://docs.google.com/spreadsheets/d/'+id+'/gviz/tq?tqx=out:json&tq&gid='+gid;
fetch(url)
  .then(response => response.text())
  .then(data => document.getElementById("json").innerHTML=myItems(data.substring(47).slice(0, -2))  
  );
function myItems(jsonString){
  var json = JSON.parse(jsonString);
  var table = '<table><tr>'
  json.table.cols.forEach(colonne => table += '<th>' + colonne.label + '</th>')
  table += '</tr>'
  json.table.rows.forEach(ligne => {
    table += '<tr>'
    ligne.c.forEach(cellule => {
        try{var valeur = cellule.f ? cellule.f : cellule.v}
        catch(e){var valeur = ''}
        table += '<td>' + valeur + '</td>'
      }
    )
    table += '</tr>'
    }
  )
  table += '</table>'
  return table
}           
</script>
</body></html>

Dynamic html Table from an array values using javascript in Code.gs

1 Answers1