innerHTML from JSON-file

Question

I just created a Google sheet document and converted it to JSON-file.

Sheet: https://docs.google.com/spreadsheets/d/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/edit#gid=0

JSON: https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic?alt=json

Now, on the sheet I have text on cells A1 and A2.

I would like to display these lines on HTML-document.

Would somebody know how I can do this?

You're going to have to be a lot more clear about what you're having a problem with. I can only assume you're asking for someone to come up with a way for your page to use JavaScript to access and consume remote JSON data. Which noone is going to do because you are expected to have tried something first and to be able to show us what you've tried and are having a problem with. — Stuart Frankish

Mara Black Mara Black · Accepted Answer · 2020-01-09T14:08:51

I think something is wrong at your JSON file, because Line 1 is missing, but based on your json here is how you can access the text:

const jsonResult = {"version":"1.0","encoding":"UTF-8","feed":{"xmlns":"http://www.w3.org/2005/Atom","xmlns$openSearch":"http://a9.com/-/spec/opensearchrss/1.0/","xmlns$gsx":"http://schemas.google.com/spreadsheets/2006/extended","id":{"$t":"https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic"},"updated":{"$t":"2020-01-09T13:49:33.116Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"Taulukko1"},"link":[{"rel":"alternate","type":"application/atom+xml","href":"https://docs.google.com/spreadsheets/d/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/pubhtml"},{"rel":"http://schemas.google.com/g/2005#feed","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic"},{"rel":"http://schemas.google.com/g/2005#post","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic"},{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic?alt\u003djson"}],"author":[{"name":{"$t":"contact.tjolanki"},"email":{"$t":"[email protected]"}}],"openSearch$totalResults":{"$t":"1"},"openSearch$startIndex":{"$t":"1"},"entry":[{"id":{"$t":"https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic/cokwr"},"updated":{"$t":"2020-01-09T13:49:33.116Z"},"category":[{"scheme":"http://schemas.google.com/spreadsheets/2006","term":"http://schemas.google.com/spreadsheets/2006#list"}],"title":{"type":"text","$t":"Line2"},"content":{"type":"text","$t":""},"link":[{"rel":"self","type":"application/atom+xml","href":"https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic/cokwr"}]}]}}

const entries = jsonResult.feed.entry;

entries.forEach(function (arrayItem) {
  const titleObj = arrayItem.title
  const text = titleObj['$t'];
  console.log("title object: " + JSON.stringify(titleObj, null, 2))
  console.log("Text: " + text)
  
  document.getElementById("result").innerHTML += text;
    
});

<div id="result"> Text: </div>

You can use json-pretty-print to see the json more clearly.

EDIT

I don't know if the json link is updated real time when you modify the sheet but you can do this:

Make a call to your json link with $.ajax({ type: "GET".. }) in order to retrieve the whole object that I pasted in the previews snippet (Read more about request from here jquery.get )
Make a callback function in order get the content after the request is finished (Read more about callbacks from Callback_function )
Apply the function who print the text

const jsonURL = "https://spreadsheets.google.com/feeds/list/18A7tTdRRIRe4rQedoee5PXj0iQdbVFCa2GVQx6p9ep8/od6/public/basic?alt=json"

function printTextFromJson(json) {
  const entries = json.feed.entry;

  entries.forEach(function(arrayItem) {
    const titleObj = arrayItem.title
    const text = titleObj['$t'];
    console.log("title object: " + JSON.stringify(titleObj, null, 2))
    console.log("Text: " + text)

    document.getElementById("result").innerHTML += text;
  });
}

function getJsonContent(callbackFunction) {
  $.ajax({
    type: "GET",
    url: jsonURL,
    success: function(returnValue) {
      console.log(returnValue);
      callbackFunction(returnValue); // callbackFunction is called when returnValue is ready.
    },
    error: function(jqXHR, textStatus, errorThrown) {
      alert(jqXHR.status);
    },
    dataType: "json"
  });
}

getJsonContent(function(returnValue) {
  printTextFromJson(returnValue)
});

<script src="https://cdnjs.cloudflare.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<div id="result"> Text: </div>

Sorry if the answer is a bit too complex, but everything has a start. If you have questions feel free to ask! xD

PS: try to change the sheet and run this snippet again, if it's not updating, then the two files are not in sync.

innerHTML from JSON-file

2 Answers