Figure out how to import JSON-P data

[fastfedora]

JSON-P adds JavaScript into the JSON format so it can be parsed using a <script> tag and be assigned a variable or passed as a parameter into a callback function. The current ImportJSON function expects pure JSON, without the JavaScript code added.

The problem is that JSON-P is not a standard. There code can be anything that is valid JavaScript (although this may change if the "application/json-p" MIME type gets adopted). Auto-detecting a JSON-P stream seems problematic.

One potential solution might be to ignore the first x number of characters and the last y number of characters, then parse the substring between the two. The user of ImportJSON would need to figure out these counts and then pass them in. This would only work if the JSON-P used the same JavaScript code each time, which is highly likely.

In the meantime, users wanting to import JSON-P data can manually change the code in ImportJSONAdvanced to:

{
  var jsondata    = UrlFetchApp.fetch(url, fetchOptions);
  var content     = jsondata.getContentText();
  var ignoreStart = 0;
  var ignoreEnd   = 0;
  var object      = JSON.parse(content.substring(ignoreStart, content.length - ignoreEnd);

  return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
}

Modifying the values of ignoreStart and ignoreEnd to the length of the JavaScript that leads into the JSON and the characters at the end of the stream that finish off the JavaScript statement.

If you try this, please post whether this was successful for you as a comment to this issue. If it's working for enough people without errors, then I can add it into the main codebase in a more extensible way.

[mbencho]

Hi I was wondering if this would be applicable to ImportJSON as well? Is there any reason I couldn't use it there? I'm not that familiar with where this piece of code should be inserted in the larger code. Thanks for your help

[mbencho]

So I got this to work - but only if I hard code the var object length.

http://live.nhle.com/GameData/20132014/2013020758/gc/gcbx.jsonp

Using this with 6989 hardcoded it imports the data. I don't know enough code to know why "content.length" doesn't work

function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) { var jsondata = UrlFetchApp.fetch(url, fetchOptions); var content = jsondata.getContentText(); var ignoreStart = 10; var ignoreEnd = 1; var object = JSON.parse(content.substring(ignoreStart, 6989-ignoreEnd));

return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc); }

[yppo]

I think this can be handled like this.

function ImportJSONAdvanced(url, fetchOptions, query, parseOptions, includeFunc, transformFunc) {
  var jsondata = UrlFetchApp.fetch(url, fetchOptions);
  
  try {
    var object   = JSON.parse(jsondata.getContentText());
    return parseJSONObject_(object, query, parseOptions, includeFunc, transformFunc);
  }
  catch(err) {
    var object   = jsondata.getContentText();
    var firstBr = object.indexOf("[") > object.indexOf("{") ? object.indexOf("{") : object.indexOf("[");
    var lastBr = object.lastIndexOf(")");
    var newObject=  JSON.parse(object.substring(firstBr, lastBr));
    return parseJSONObject_(newObject, query, parseOptions, includeFunc, transformFunc);
    
  }
}