thisdavej/getJSON-google-sheets

Failures on line 9

IADDIC opened this issue · 3 comments

Hi Dave! I have implemented your script but am unable to get it to work. In short, when I enter the url for an API in a browser (chrome) I get an expected response. Take your example: Return the timestamp of the current ISS location.

If I copy and paste the url in chrome ( http://api.open-notify.org/iss-now.json ) I can see a response, ( {"iss_position": {"longitude": "-155.7640", "latitude": "38.3718"}, "message": "success", "timestamp": 1558697781}) ).

Unfortunately, when I enter the values in the Google sheet exactly as shown in the first example I see an error." Request failed for http://api.open-notify.org/iss-now.json,%7B%7Btimestamp%7D%7D returned code 404 (line 9).

I found this error when I tried a URL with authentication. I was able to get a response using chrome but when I entered it into the spreadsheet I received the following error message " Request failed for https://api.noodle.com/v1/project_stages?user=xxxxxxx%2540xxxxx.xxx&api_key=e567sakjhilqa9768932bdca007616472a4e6,%7B%7Btimestamp%7D%7D returned code 401. Truncated server response: {"errorcode":1,"message":"The user was not found"} (use muteHttpExceptions option to examine full response) (line 9)."

Note I've changed all the details above so the url shown will not work except for your public example.

So in both instances, the error involves line 9.

Have I missed something?

Thank you,

Rich

Hi Rich, I think I see what is happening based on the error code. I believe you are entering your formula like this:

=GETJSON("http://api.open-notify.org/iss-now.json,{{timestamp}}")

Instead, you need to ensure that both the URL and the second parameter (template) are both enclosed in their own double quotes like this:

=GETJSON("http://api.open-notify.org/iss-now.json","{{timestamp}}")

You should then be good to go.

Also, my script unfortunately does not currently support URLs with authentication. The Google Sheets function I am able to leverage (UrlFetchApp) has limited support for authentication since it only supports "basic authentication" which is a weak form of authentication and covers relatively few URL authentication scenarios on the web.

Hi Dave, Nice code, JSON handling is cruelly missing from gsheets...
One addition, and a question.

API KEY authentication :
I was able to modify your code slightly to pass through an API Key and it worked. Like this:

`function JSONtoObject(url) {

var apiKey = "xxx-yyy-xxxxxxxxxxxxxxxxxxxxx" ;

var url = encodeURI(url);
var response = UrlFetchApp.fetch(
url,
{
"headers":{
"api-auth":apiKey
}
}
)`

Templates
I am having difficulty make your template input work with this attached JSON example that uses some kind of nested objects or multiple dimensions. When I use {{ac}} as template, I get entire rows of [object,object] pairs.
Sorry for the question, beginner here, any help appreciated.

https://drive.google.com/file/d/1ztfC3wFwuXFmRBYNJzO_pHt6Cvm7CPLk/view?usp=sharing

@mthrum sorry for the late reply, but I just updated the code to handle your use case which relates to nested arrays. In your example, you can use the following template to access the postime value in the first record:

{{ac[0].postime}}

To access the postime value in the second record, use {{ac[1].postime}}, etc.