ImportJSON

I can’t figure out what the xPath would be for something like this:

{
   blah: "blah",
   aaData: [
      [
         "John",
         "Smith",
         "jsmith@example.com"
      ],
      [
         "Toby",
         "Harris",
         "tharris@example.com"
      ]
}

…transformed to a table like this:

John  |  Smith  |  jsmith@example.com
Tody  |  Harris  |  tharris@example.com

Am I barking up the wrong tree?

This is so great! I’ve been running this for a bit and pretty quickly hit the Google Docs rate limit. It returns #ERROR! and the following message:
Service invoked too many times in a short time: urlfetch. Try Utilities.sleep(1000) between calls. (line 167, file “Code”)

I don’t know how to add this myself. How could I add this pause so that I wouldn’t keep hitting the rate limit? Thanks!

Hi

Thanks for the info, this is exactly what I needed

But I have some issues and I don’t know how to deal with them (it’s 3 in the morning and I cant go to bed :))

So here it is:

I’m trying to parse some info from the http://prodvigator.ua/api/v1/domain_info?query=glasko.com.ua&token=8f678b10caa5cc28d3f9b66e3a94376d

I need the “phrases_count” data

I’ve tried different variations based on your example, but I get the parse error every time 🙁

I tried to use this one:

=ImportJSON(“http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json”, “/feed/entry/title,/feed/entry/content”, “noInherit,noTruncate,rawHeaders”)

And I got the damn error

But everything works just fine if I use only the following code:

=ImportJSON(“http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json”)

I don’t get any errors, just lots of tables and data.

So my question is:

Why do your examples return the error and can you please tell me how can I get only phrases_count data from http://prodvigator.ua/api/v1/domain_info?query=glasko.com.ua&token=8f678b10caa5cc28d3f9b66e3a94376d

And here’s the screenshot if the link wouldn’t work:

http://clip2net.com/s/5uknu1

You sir, are a feckin’ genius.

A few hours on Google to find ImportJSON and http://fixer.io/ has given me EXACTLY what I was looking for in my Google Doc.

Can’t thank you enough! 🙂

cheers

Pete

Do you know if this can handle cursor paging with Facebook’s GRAPH API?

Essentially what happens is it only displays ONE child data elements, and other child elements appear as a paginated link…yet the last parent element shows all it’s child elements.

JSON:
https://graph.facebook.com/102158363185/likes?fields=likes.limit(51).fields(talking_about_count,likes,link,name)&limit=200&access_token=CAAHrXZB23ZCxgBAKsi4JkENJyhZB0LZC02jOWzB8ZAFPQdhaqqh7ZCshLSwyQKhPPZBuNkipV3icIE2ufJZBh15ZBVjzUzG8B2MQjn6oKSULU3xjrku7yDEjE8xBjDSSXU09IXem67STZC5qZBN6mbuxY3tpZB5Snx0R3ZCnXUO978lqABvkjndATYOUP

Hey Trevor,
Thanks for the great script. I’m trying to use it to pull data from basecamp into a spreadsheet, but it seems to get tripped up by the html basic authentication. Any way to get around this?

Here’s my function call formatting:

=ImportJSON(“https://username:password@basecamp.com/1111111/api/v1/projects/1111111-my-project/todolists.json”, “/name”, “noHeaders”)

And this is the error I get:

error: Unexpected error: https://username:password@basecamp.com/1111111/api/v1/projects/1111111-my-project/todolists.json (line 130, file “Code”)

Hi, This is a great script. I got it working in one of my spreesheets but not in another.

In the spreed I got it working I’ve set the JSON URL and it displays data. My question is, how do I set it to refresh data automatically. I have done everything. I changed spreedsheet configurations, set a trigger in script and even added Minute(Now()) at the end of my JSON url. I can’t get it to refresh data in minutes. Can you help me in this task.

The other thing is, when I load the script using ImportJSON button in the script editor it gives me an error at line 130. It says DNS error: http://undefined (line 130, file “importJSON”).

Thank you.

Hi Trevor, thanks a lot for your great library.
I’d like to use it to extract the direct iTunes link out of an iTunes Search API result.

The Search Results URL would be e.g.:
https://itunes.apple.com/search?term=%22notorious%20big%20juicy%22&country=de&limit=1%22

So I’ve set up a spreadsheet with your script.
=Importjson(“https://itunes.apple.com/search?term=”&A5&”&country=de&limit=1″)
works fine and extracts me the entire search result into the spreadsheet. However, I’m only interested in the URL thats located in

trackViewUrl”: “https://itunes.apple.com/de/album/juicy/id204669326?i=204669559&uo=4”

Unfortunately, whenever I’m trying to use a query to only extract this part, I’m getting a Parsing Error.

Then I noticed that even when I paste the example code (see below) from your page, it creates a parsing error.

=ImportJSON(“http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json”, “/feed/entry/title,/feed/entry/content”, “noInherit,noTruncate,rawHeaders”)
-> Error

However,
=ImportJSON(“http://gdata.youtube.com/feeds/api/standardfeeds/most_popular?v=2&alt=json”)
works fine.

Do you have any ideas what I’m missing? Everything works except the use of the query path.

Thanks a lot!
Tim

Hi I am trying to install the script to a spreadsheet, but cannot find it in the add-ons library. How do I install? I cannot find script manager as it has been deprecated in light of add-ons. Any thoughts? Thanks!

Receiving error ” exceeded maximum execution time” on some calls. What gives?

Thanks,

Hello Trevor,

Im traying to import data from game API to google Spredsheet but i constantly get error: The coordinates or dimensions of the range are invalid. (line 19, file “Code”)
Im new in this and would very much appreciate any help

function readJSON() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ss = SpreadsheetApp.openById(“1taOtpenF4-VQez2Pq4-ZpctRtiuagMk_ha14ZGnHcsU”);
var sheet = ss.getActiveSheet();
var startRow = 1; // Change if needed

var url=”http://www.cscpro.org/secura/citizen/86935.json”; // Paste your JSON URL here
var response = UrlFetchApp.fetch(url); // get feed
var dataAll = JSON.parse(response.getContentText()); //
var dataSet = dataAll;

var rows = [],
data;

for (i = 0; i < dataSet.length; i++) {
data = dataSet[i];
rows.push([data.id, data.name,data.age, data.is_online, data.alive, data.ban, data.level, data.exp, data.strengh]); //your JSON entities here
}
dataRange = sheet.getRange(1, 1,rows.length, 3); // 3 Denotes total number of entites
dataRange.setValues(rows);

}

Hi

This script is just what I was looking for and I have it working. But I’ve run into a problem, possibly caused by the json format of the API call I am making.

The json I am loading can be seen at http://jsonviewer.stack.hu/#http://europeana.eu/api//v2/search.json?wskey=ISXVIQBYBQ&query=*:*&start=1&rows=0&profile=facets

The call I am using is =ImportJSON(“http://europeana.eu/api//v2/search.json?wskey=ISXVIQBYBQ&query=*:*&start=1&rows=0&profile=facets”, “/facets/name,/facets/fields/label,/facets/fields/count”, “noTruncate”) which can be seen at https://docs.google.com/spreadsheets/d/18CBvDafs8t8vSc0Y88gx12C-WAbSTrgLDgRk97tTXl4/edit?usp=sharing

But what i want to do is just list for example all the label/count pairs within name:LANGUAGE as currently only the very first entry is being displayed.

Is this possible?

Many thanks, James

I’ve noticed the script seems to have issues when values have empty strings as such
[
{“key”:”value”},
{“key”:””}
]

Both rows will import as having “value” under “key” despite one being empty. I’ve been looking through the code but I’m not certain where the fix may lie.

I love this. I have a feeling it can do something more advanced I want to see, but I’m new to scripting and having trouble with some of the lingo.

How do I use this to do a series of json imports by inserting different {client} variables into a url formatted like “https://example.com/{client}/search?term={item}”

I want to find the way to insert the list if

https://example.com/{client1}/search?term={item}
https://example.com/{client2}/search?term={item}
https://example.com/{client3}/search?term={item}…

Hello,

It looks really useful, two questions:

1. Do you know how to import this script onto the NEW google spreadsheets? I created the script, but then none of the functions appear on the new spreadsheets… do we need to do anything else?

2. Do you know the query that I’d have to run here, http://d.yimg.com/autoc.finance.yahoo.com/autoc?query=carnival%20plc&callback=YAHOO.Finance.SymbolSuggest.ssCallback , to get the symbol just for the LSE exch for example?

Many thank in advance!!!

Hi , is it possible to recreate the json from spreadsheet? . So that we can make it as a db sync app for low security applications . After editing on spreadsheet , generate json and make a put request to the url .

Hi there!

I have an array like: [ [item1], [item2]….]
However when running through it it omits item1, otehr than that it is working fine.

JSON sample:https://api.guildwars2.com/v2/commerce/transactions/current/sells?access_token=64C6C7DF-0362-D94D-B8D4-00E7EB9F3922E4EF2FE0-D999-4169-86AF-753EBE4324AF

Thanks!

Hi Trevor! Using Your script a lot in my sheet, and love it. However, I did once ask for a menu button that makes me able to manually refresh the data.

However, since I got my own custom menu, is it any way I can add it there instead? I lose the ability to refresh it since my menu overrides Yours (which it must, so I don’t lose tons of handy features I need for sorting my sheets).

Also, is there any way to make it not update/refresh the info unless I manually Call for it to do so? I am hitting the Google limit when the Blizzard Armory API (for World of Warcraft) gets issues, it seems like it is trying to pull the info many times, and another script I got updates everytime ImportJSON updates its info. Makes havoc, got a backup and a backup backup sheet on other users to be sure I get the info in case something goes wrong.

Wouldn’t mind an email if you got the time, as I am travelling a bit these days and can’t check around on all these websites 😉

Jim, Norway

Hello Trevor,

I have been trying for a couple of days to find a way to download Json data into a database table (either stata, access, google, or anything similar).
Earlier today I installed your code for installing Jason with google. I ran across one problem. When I perform a command to fetch data a run into the following error:
SyntaxError: Unexpected token: < (line 170, file "")
Dismiss

line 170 reads:
var object = JSON.parse(jsondata.getContentText());
and is from the script you provided for downloading the intall json program.

the command i entered is the following:
ImportJSON("https://www.govtrack.us/data/congress-legislators/&quot;, "actions/acted_at/committee", "noInherit,noTruncate,rawHeaders")

If you can offer me any advice in this endeavor I would greatly appreciate it. The data I need is in bulk, and there is a lot of it:

Best regards,
Michael

Hi Trevor

I ve been following your posts and I am overwhelmed,
I tried using the ImportJSON code but i keep getting unknown function every time i try pasting or write a formula

what can be wrong

thanks

Hi, I keep on coming back to this script and use it across many sheets. But each time I want to use it I have to add it as a new script on that sheet. I had a quick look into this and it seems you could publish this as an add-on and then it would be simple for anyone to add. Maybe I’m wrong, but there’s more info at https://developers.google.com/apps-script/add-ons/publish

Cheers

Hi Trevor,

Firstly, thanks for creating this tool – very useful.

I’m currently experiencing a problem, though, which I was hoping you might have some insight into.

I have a Google Sheet that makes four ImportJSON calls.

It pulls in data that is new each day. It’s worked fine for months, but as of October 20 has ceased to work – when I check the spreadsheet now it always displays October 20’s data.

Here are the things I’ve tried, and the results:

1. Reloading the page without using the cache (Ctrl + F5) – no change
2. Loading the spreadsheet in Firefox (I mainly use Chrome) – the same as with Chrome; 20 October data
3. Reauthorising the ImportJSON code – I did this by trying to run the ImportJSON code in Script Editor, as I think I had to the first time I used it. Doing so obviously throws an error, as there’s no URL from which to carry out the import, but I was prompted to authorise the code (which surprised me, as, like I say, I thought I’d authorised it when I first started using it). In any case, this didn’t make any difference.
4. Using the exact same ImportJSON code in a new spreadsheet – in this case, I did get up-to-date data i.e. fresh 30 October data. So obviously I could just recreate the dashboard that I’ve made in Sheets, but I’d rather not do that if it’s not necessary (and given I’d be concerned the same issue could arise again).

If you had any thoughts on other things to try then that would be much appreciated – it’s a great tool, so I’m missing being able to use it!

Thank you.

Philip Nye

Thank you for these wonderful IMPORTJSON formula… You, have help me alot… Now, it’s my time to help for the auto-update issues in new googlesheets. For those who wants their IMPORTJSON auto-updated in gSheets, this is what I did to have it auto-updated every minute…. I have just added a new script in my script editor below, and have it trigger every minute…. Hope this helps…

function ImportJSONupdate() {
var queryString = Math.random();

var PHP = ‘=IMPORTJSON(“‘ + SpreadsheetApp.getActiveSheet().getRange(‘A16’).getValue() + ‘?’ + queryString + ‘”)’;
SpreadsheetApp.getActiveSheet().getRange(‘C16’).setValue(PHP);

var CNY = ‘=IMPORTJSON(“‘ + SpreadsheetApp.getActiveSheet().getRange(‘A19’).getValue() + ‘?’ + queryString + ‘”)’;
SpreadsheetApp.getActiveSheet().getRange(‘C19’).setValue(CNY);
}

Hey Trevor,

As everyone else is saying – awesome script.

I’m trying to pull Facebook API data with this and the JSON looks something like this:

{
“data”: [
{
“actions”: [
{
“action_type”: “link_click”,
“value”: 37
},
{
“action_type”: “offsite_conversion.fb_pixel_add_to_cart”,
“value”: 9
},
{
“action_type”: “offsite_conversion.fb_pixel_purchase”,
“value”: 1
},
{
“action_type”: “offsite_conversion.fb_pixel_view_content”,
“value”: 85
},
{
“action_type”: “post_like”,
“value”: 20
},
{
“action_type”: “page_engagement”,
“value”: 57
},
{
“action_type”: “post_engagement”,
“value”: 57
},
{
“action_type”: “offsite_conversion”,
“value”: 95
}
],
“ad_id”: “6041647382313”,
“date_start”: “2016-03-27”,
“date_stop”: “2016-03-30”
},

Now, I want to pull out of here the value from the “offsite_conversion.fb_pixel_add_to_cart” field.

I tried the following:

=ImportJSON(A1,”/data/actions/action_type/offsite_conversion.fb_pixel_add_to_cart”)

(where my URL is in A1,) but it didn’t seem to like that.

Also tried a bunch of other combinations which didn’t work.

Any tips would be greatly appreciated.

Thanks!

dude, I want to suggest an edition, I’m not sure what are you doing in there but in line 403 you are ignoring 0s:

what if I need the 0s to appear?

Instead of:

if (!data[row][column]) {
    if (row < 2 || hasOption_(options, "noInherit")) {
      data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  }

Im changing it to:

if (!data[row][column]) {
    if (row < 2 || hasOption_(options, "noInherit")) {
        if(data[row][column] != 0)
            data[row][column] = "";
    } else {
      data[row][column] = data[row-1][column];
    }
  }

But I’m not sure at all of what I’m doing so, I hope you can help me because this is provisional, but I hope that I don’t mess the whole thing up.

Hi there – I’m trying to use ImportJSONWithToken function you suggested above to use a Google Sheet to access my Clash of Clans data. I added in the ImportJSONWithToken code:

function ImportJSONWithToken(url, query, parseOptions, token) {
   var fetchOptions = {
      "headers" : {
         "Authorization: Bearer " : token
      },
      muteHttpExceptions: true
   };

The call I’m making is to get the members of my clan (/members), I’ve put my clan ID in B1, and my Developer API token in B2

=ImportJSONWithToken(“https://api.clashofclans.com/v1/clans/”&”%23″&B1,”/memberList”,”noTruncate”,B2)

When I use ImportJSONWithToken, it tells me “Error
Attribute provided with invalid value: Header:Authorization: Bearer (line 164)”

According to the Developer page, the curl for the data I’m trying to access is:

curl -X GET –header ‘Accept: application/json’ –header “authorization: Bearer ” ‘https://api.clashofclans.com/v1/clans/%[CLAN ID]’

Any ideas on how to successfully pass the authorization bearer?

Really awesome! Thank you very much!

I’m trying to read data from an API which stands behind AD credentials.
Is the API capable to also send credentials?

Thanks in advance,
Giuseppe

Good afternoon, I have a problem I’m using this on a Google Drive but I need to update only every 1 or 2 hours. It updates me live and the Drive takes time to recalculate it. Can somebody help me? Thank you

After some hours to find a way to ask http request with headers in spreadsheet cell, I finally found:

add these following lines to the script (v1.2.1) at line 108:

(...)
if (postOptions["headers"] != null) {
postOptions["headers"] = JSON.parse(postOptions["headers"]);
}
(...)

in cell use ImportJSONViaPost function like this

=ImportJSONViaPost("https://bravenewcoin-v1.p.mashape.com/convert?from=btc&qty=1&to=usd";"";"headers={""X-Mashape-Key"":""1uPWfE36tomshdxFZSmlqut49XDop1LThHQjsnECklt9SFLSy0""}";"";"noInherit,noTruncate,rawHeaders,muteHttpExceptions=false")


Hi Trevor

Many thanks for this from me too.

I’m having quite some trouble trying to get the sheet to update automatically. I’ve followed many of the suggestions mentioned in the forum but nothing seems to work. Here are my observations:

1) Setting a trigger doesn’t seem to fire
2) When i try use now() as an additional parameter, I get an error that says “This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()”
3) Trying shahrul’s suggestion of dynamically creating the call from another cell, I get the error “You do not have permission to call setValue”

Furthermore, I would need to run the automated update regularly (Every 10 seconds) and it seems most solutions will only run every minute. Does anybody have any suggestions or a working copy that I could have a look at please?