Israel Isassi Israel Isassi - 2 years ago 84
JSON Question

How to get specific property from Google Spreadsheet JSON feed

I am reading JSON from a Google Spreadsheet and need help getting to the text within entry.content.$t. The text is a column named "description" in the spreadsheet. The feed for the spreadsheet is (removed)

So far, my script is

function listChapters(root) {
var feed = root.feed;
var entries = feed.entry || [];
var html = ['<ul>'];
for (var i = 0; i < entries.length; ++i) {
var chlist = entries[i];
var title = (chlist.title.type == 'html') ? chlist.title.$t : escape(chlist.title.$t);
var chapters = chlist.content.$t;
html.push('<li>', chapters, '</li>');
document.getElementById("chapterlist").innerHTML = html.join("");

The question is - How do I read "description" from $t to place in the var chapters?

Answer Source

The text within chlist.content.$t is almost, but not quite, properly formatted JSON. Since it's not properly formatted, you cannot use JSON.parse() to create an object that you could then get a description property from.

Here's a brute-force approach that will extract the description, used in place of the original html.push('<li>', chapters, '</li>');:

// Get the text between 'description: ' and 'Chapter website:'
var descStart = chapters.indexOf('description:')+13;  //+length of 'description: '
var descEnd = chapters.indexOf('Chapter website:');
var description = chapters.substring(descStart,descEnd);

html.push('<li>', description, '</li>');

Tested with this, checking results in debugger:

function test() {
  var url = '---URL---';
  var result = UrlFetchApp.fetch(url);
  var text = result.getContentText();
  var bodytext = Xml.parse(text,true).html.body.getText();
  var root = JSON.parse(bodytext);
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download