CODEWITHSUNDEEP
htmlexceldatabasegoogle-sheetsembed
Ahmad
Ahmad

Reputation: 11

Can I display google sheet data on my site, without using embed <iframe > tag? It's boor

I have a sheet in google. I want to show it on the HTML page outside google sheets, such as for ## Heading ##free hosting or others! I use Embed in tag, but the table looks very boor. I want to show data by code! I tried to connect to google sheet, but I can't. Is this possible? Or am I doing the impossible? Who can explain it to me? Can I do that with excel!

Upvotes: 1

Views: 882

Answers (1)

Mike Steelson
Mike Steelson

Reputation: 15318

Inside your html page, create

<div id="json">json here</div>

then add this javascript to retrieve data by json end point (I leave an ID and GID of mines, change as necessary)

var id = '1n-rjSYb63Z2jySS3-M0BQ78vu8DTPOjG-SZM4i8IxXI';
var gid = '0';
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
}

tested here https://codepen.io/mikesteelson/pen/wvevppe

Upvotes: 1

Related Questions