qeet/importjsonapi

Use JSONPath to selectively extract data from any JSON or GraphQL API directly into Google Sheets.

importjsonapi's Issues

Working with multiple function calls in sheets

I have a worksheet with 100+ requests to an API; it works ok the first time I set it up, but returns a 429 error as soon as it auto-refreshes. since it tries refreshing every occurance of the function at the same time. I can't do just one request because the way the api organizes the data.
Is it possible to set up a delay so that Sheets doesn't try refreshing multiple instances of this function at the same time?

Not an issue

Sorry i'm not sure how to message people on here. This is not an issue but I was wondering if you could help answer a question. I found your IMPORTJSONAPI and I think it will work for what i'm trying to do but i'm not a programmer so I might be wrong.

I'm trying to pull auction items from this website

https://www.worldwideauctiongroup.com/

and put it inside a google sheet with each item on a row.

I believe the data is coming from an API but i'm not exactly sure. I was wondering if you could help and if i'm on the write path.

Thanks in advance for any help.

Need some help

Hi,
I'm trying to import a JSON, it's as follow (using an example with same format here)
{
Par1:xxxx,
Par2:yyyy,
List: [{
{
index1,
index2,
index3{
value1,
value2
}
},
{
index1,
index2,
index3{
value1,
value2
}
},
{
index1,
index2,
index3{
value1,
value2
}
}
}],
Par5:zzzz,
}
I'm trying to import Par2, Par5, and from List, all the index1&2 and value1
My formula goes as :
=ImportJSONAPI("URL","$.Par3[*]","^^Par2,^^Par5,index1,index2,index3.value1"

The problem I'm having is, once all the values for all the index are populated, Par2 and 5 also gets repeated in every rows at the beginning, how to prevent this?

Using the original ImportJSON, it will be
=ImportJSON("URL","/Par2,/Par5,/list/index1,/list,index2,/list/index3/value1","")
at with this, Par2 and 5 only once and does not repeat for every entry of index123

Thanks, and sorry for the formattingm new to here.

Invalid JSON because of quote marks in GraphQL query

Hello, first of all thank you for this awesome tool.

There are quote marks in the query (in owner) and it won't let me import it
It works fine if I replace it with null but it won't let me add my custom address and it won't work without the quote marks. I've also tried to put double quote marks but it won't work either
= IMPORTJSONAPI("https://axieinfinity.com/graphql-server-v2/graphql", "$.data.axies", "total", "method=get", "contentType=application/json", "payload={ 'query': 'query GetAxieBriefList { axies(auctionType: All, from: 0, sort: PriceAsc, size: 100, owner: "0xeec0d4e7745059e465ac8bc70c9f934476cb9f4b" ) { total results { ...AxieBrief } } } fragment AxieBrief on Axie { id name stage class breedCount image title genes battleInfo { banned } auction { currentPrice } stats { ...AxieStats } parts { id name class type specialGenes } } fragment AxieStats on AxieStats { hp speed skill morale }' }")

As you can see here, it works fine in the browser, but now then I try to import it
https://axieinfinity.com/graphql-server-v2/graphql?operationName=GetAxieBriefList&query=query%20GetAxieBriefList%20{%20axies(auctionType:%20All,%20from:%200,%20sort:%20PriceAsc,%20size:%20100,%20owner:%20%220xeec0d4e7745059e465ac8bc70c9f934476cb9f4b%22%20)%20{%20total%20results%20{%20...AxieBrief%20}%20}%20}%20fragment%20AxieBrief%20on%20Axie%20{%20id%20name%20stage%20class%20breedCount%20image%20title%20genes%20battleInfo%20{%20banned%20}%20auction%20{%20currentPrice%20}%20stats%20{%20...AxieStats%20}%20parts%20{%20id%20name%20class%20type%20specialGenes%20}%20}%20fragment%20AxieStats%20on%20AxieStats%20{%20hp%20speed%20skill%20morale%20}

not an issue, just asking for help.

1. how can i retrieve the value of url from this query:
   =IMPORTJSONAPI("https://www.balai.cv/wp-json/wp/v2/posts", "$..*", "id,date_gmt,title.rendered,excerpt.rendered,categories,tags,link,yoast_head_json.og_image.url")

using this query $..yoast_head_json.og_image..url in the browser demo i get the desired results . i tried a couple of ways but i can't get the intended results .

2. Additionally, can i get an help when filtering arrays. im the example above. i'm trying to filter all posts by categories , since they are in another array, i cant filter it.
   i tried =IMPORTJSONAPI("https://www.balai.cv/wp-json/wp/v2/posts", "$..[?(@.categories==='6')]*", "id,date_gmt,title.rendered,excerpt.rendered,categories,tags,link,yoast_head_json.og_image.url") ,and a couple variations to no avail.

thanks in advance.

Retrieve JSON with HMAC/SHA256 Auth and custom fields

An API I want to connect to requires the following fields:

X-Time: (current UTC timestamp in ms, if timestamp is more than five minutes apart from server's time, the request will be rejected, example: 1543597115712)
X-Nonce: (random 36 char string, each nonce can be used only once, example: 4ebd366d-76f4-4400-a3b6-e51515d054d6)
X-Organization-Id: (organization id, example: da41b3bc-3d0b-4226-b7ea-aee73f94a518)
X-Request-Id: (required, unique identifier of specific request, client should have local awareness that some app action should be done only once on server, if for some reason request is processed by server and client does not know anything about it (request failed). This ID will provide server information that it will not repeat action if it was already processed)
X-Auth: (API Key + ":" + HMAC signature, example: 4ebd366d-76f4-4400-a3b6-e51515d054d6:fb8484df7941a4d0c337939a73cc8fc09f50bd3309af5e1926baaee4d5059dfc)

Is this possible / supported with ImportJSONAPI? I have come up with INT((NOW()-25569)*86400) as a means of generating X-Time, but I get an error saying that the function is not allowed to reference a cell that uses NOW(), RAND() or RANDBETWEEN().

Thank you!

SCH00N3R

Importing Multiple URLs

Hello, Is it possible to import from multiple source URLs?

Cannot condition check and return both parent value as well as child (nested array) values

{
"code": 200,
"data": [
{
"currency": "usdt",
"assetType": 1,
"chains": [
{
"chain": "trc20usdt",
"displayName": "TRC20",
"baseChain": "TRX",
"baseChainProtocol": "TRC20",
"isDynamic": false,
"numOfConfirmations": 1,
"numOfFastConfirmations": 1,
"depositStatus": "allowed",
"minDepositAmt": "1",
"withdrawStatus": "allowed",
"minWithdrawAmt": "2",
"withdrawPrecision": 6,
"maxWithdrawAmt": "1000000.00000000",
"withdrawQuotaPerDay": "1000000.00000000",
"withdrawQuotaPerYear": null,
"withdrawQuotaTotal": null,
"withdrawFeeType": "fixed",
"transactFeeWithdraw": "0.00000000",
"addrWithTag": false,
"addrDepositTag": false
},
{
"chain": "usdt",
"displayName": "OMNI",
"baseChain": "BTC",
"baseChainProtocol": "OMNI",
"isDynamic": false,
"numOfConfirmations": 2,
"numOfFastConfirmations": 1,
"depositStatus": "allowed",
"minDepositAmt": "20",
"withdrawStatus": "allowed",
"minWithdrawAmt": "200",
"withdrawPrecision": 8,
"maxWithdrawAmt": "600000.00000000",
"withdrawQuotaPerDay": "600000.00000000",
"withdrawQuotaPerYear": null,
"withdrawQuotaTotal": null,
"withdrawFeeType": "fixed",
"transactFeeWithdraw": "5.00000000",
"addrWithTag": false,
"addrDepositTag": false
},
{
"chain": "usdterc20",
"displayName": "ERC20",
"baseChain": "ETH",
"baseChainProtocol": "ERC20",
"isDynamic": false,
"numOfConfirmations": 12,
"numOfFastConfirmations": 12,
"depositStatus": "allowed",
"minDepositAmt": "1",
"withdrawStatus": "allowed",
"minWithdrawAmt": "10",
"withdrawPrecision": 6,
"maxWithdrawAmt": "1000000.00000000",
"withdrawQuotaPerDay": "1000000.00000000",
"withdrawQuotaPerYear": null,
"withdrawQuotaTotal": null,
"withdrawFeeType": "fixed",
"transactFeeWithdraw": "3.00000000",
"addrWithTag": false,
"addrDepositTag": false
}
],
"instStatus": "normal"
},
{
"currency": "husd",
"assetType": 1,
"chains": [
{
"chain": "husd",
"displayName": "HUSD",
"baseChain": "ETH",
"baseChainProtocol": "ERC20",
"isDynamic": false,
"numOfConfirmations": 12,
"numOfFastConfirmations": 12,
"depositStatus": "allowed",
"minDepositAmt": "1",
"withdrawStatus": "allowed",
"minWithdrawAmt": "2",
"withdrawPrecision": 8,
"maxWithdrawAmt": "1000000.00000000",
"withdrawQuotaPerDay": "1000000.00000000",
"withdrawQuotaPerYear": null,
"withdrawQuotaTotal": null,
"withdrawFeeType": "fixed",
"transactFeeWithdraw": "1.00000000",
"addrWithTag": false,
"addrDepositTag": false
}
],
"instStatus": "normal"
},

Hi there, I am trying to retrieve the following based on satisfying all the following conditions:
Condition 1 - "chain" is not empty
Condition 2 - "instStatus is "normal"
Condition 3 - "depositStatus is not "prohibited

Return the following:

• currency
• depositStatus

URL endpoint - https://api.huobi.pro/v2/reference/currencies

I manage to return "currency" based the Condition 1 and Condition 2.

=IMPORTJSONAPI("https://api.huobi.pro/v2/reference/currencies","$.data[?(@.chains!='' && @.instStatus=='normal')]","currency")

However, when I tried adding Condition 3, it doesn't seem to do anything (I get the same result)
=IMPORTJSONAPI("https://api.huobi.pro/v2/reference/currencies","$.data[?(@.chains!='' && @.instStatus=='normal' && @.chains.depositStatus!='prohibited')]","currency")

Also, can't seem to get "depositStatus" value back either.
PLEASE HELP

Import JSON from sheet

Thanks for this function! I’m being emailed JSON output, and would like a way to quickly ingest it into Google Sheets. Is there any chance you could consider an ImportJSONFromSheet-style option similar to bradjasper/ImportJSON (which I can’t seem to get to work)?

Thanks, and no worries if you think that’s outside the wheelhouse of IMPORTJSONAPI 😅

not an issue, just asking for help !

how can i retrieve the value of ELS from this query...

=importJSONAPI("https://data.ripple.com/v2/accounts/rNJYAGgWCn6PcpNAaMnXWikn9eGbUkkMu5/balances", "$...balances[?(@Property===('currency') && @.match===('ELS'))]", "@")

been trying so many different ways, nothing seems to work ! and im sure that it is possible !
i couldn't find a link to any documents on using the query function ?
many thanks for ANY pointers !

request with api key element

При попытке сделать запрос api key выдает ошибку
=IMPORTJSONAPI("https://www.mexc.com/open/api/v2/asset/address/list&api_key='key'";"$..";"@")

ERROR: Request failed for https://www.mexc.com returned code 404. Truncated server response: {"timestamp":"2022-11-22T13:25:36.559+00:00","status":404,"error":"Not Found","message":"","path":"/open/api/v2/asset/address/list&api_key='key'"} (use muteHttpExceptions option to examine full response)

how should I write correctly to get an answer?

Can it output headers

Is it possible to include the headers in the table_data? So when the result set is returned to the sheet, the headers are the first row?

Ability to ONLY run on edit?

Is there a way to only run this function when the formula is edited?

Thank you a ton for sharing this @qeet! Lifesaver 💪

Can this script query a nested JSON?

Hi, I'm having trouble querying all the columns in a nested JSON. Is it possible to get all the results for a json like the one below in line with the rest of the data?

    "surveyResults":[{
            "entityName":"Cabin",
            "entityId":5290,
            "organizationId":6582,
            "surveyName":"Post Stay Survey 1",
            "surveyId":22881,
            "userName":"Jet",
            "userPhoneNumber":null,
            "surveyQuestion":[{
                        "questionText":"Q.1.How likely are you to recommend this cabin to your friends?",
                        "answer":"9"},
                        {"questionText":"Q.2.I met new people during my stay",
                        "answer":"Somewhat Agree "},
                        {"questionText":"Q.3.What room type did you stay in?",
                        "answer":"Glamping or Tepee"....

By the way, thanks for the help in advance.

Support ArrayFormula

I wonder if it's possible to have this one support the use of arrayformula, I believe a similar issue was opened previously that ended up using {;} to put all different importjson (IJ) into one cell Issue 18

recently I came across a fork of the OG IJ that has support for arrayformula and am wondering if it's possible to add that to this one, since that IJ has the bottom row duplicate problem.

Here

Split no header data into multiple rows

Is there a way to split data into multiple rows. I see that the column function can print to the line adjacent can this be standardized in some manner? Currently data only goes into a single cell.
This is what I have so far:
=IMPORTJSONAPI("https://api.coingecko.com/api/v3/coins/alephium/market_chart?vs_currency=usd&days=max&interval=daily", "$.prices","@,[2]", doNotDelete!B1)'

Using match on all items in an array?

Hello,

I'm trying to filter this data:

https://raw.githubusercontent.com/aEnigmatic/ffbe/master/skills_ability.json

And at first i tried with this function:
=IMPORTJSONAPI("https://raw.githubusercontent.com/aEnigmatic/ffbe/master/skills_ability.json","$.*.[?(@property==='name' && @.match('Gained'))]^","name,effects[0],effects[1],effects[2],effects[3],effects[4],effects[5]")

Which works well, but how would i go about filter in the effects column? I want to find results containing "9999" in any of the effects columns? I tried
"$.*.[?(@property==='effects' && @.match('9999'))]^"
"$.*.[?(@property==='effects[0]' && @.match('9999'))]^"
"$.*.[?(@property==='effects[*]' && @.match('9999'))]^"

To no avail, and then i ran out of ideas... is it possible to do what i want?

request to add type for 'image'

Since gSheets allows inline images, could you please consider adding a type for those or adding an option to show the URL +adding a column for the image itself?

=IMPORTJSONAPI("https://autocomplete.clearbit.com/v1/companies/suggest?query=microsoft","$[0]","domain, logo")

1. Option convert type to 'logo': A2 would then show the imamge instead of the URL
2. Option keep image URL + add column next to it containing the image =IMAGE(A2) (if possible even with parammeters? =IMAGE(A2,4,100,100)

BTW: Thank you for sharing this code at all. It's a massive time saver!

Using match for results with | or + characters?

Hello, i have this code:

=IMPORTJSONAPI("https://raw.githubusercontent.com/aEnigmatic/ffbe/master/recipes.json","$.[?(@property==='name' && @.match('"&C1&"'))]", "^item, @")

Which works well as a dynamic search from the cell C1, and with for example a "4" in that cell i get this result:

21:302011130	Dark Ragnarok (4★)
21:310007700	Dark Gambanteinn (4★)
21:304006400	Dark Masamune (4★)
21:312003900	Dark Gandiva (4★)
21:315023500	Dark Rising Sun (4★)
21:309006200	Dark Kaiser Knuckle (4★)
21:302014700	Ultima Blade (FFXV)+4
21:405013900	Battle-worn Fatigues (FFXV)+4
21:311006500	Scepter of the Pious (FFXV)+4
21:1100000155	GE-64
21:1100000156	U-24 Zabijak

Which is all correct, but how do i search for everything that includes a "+" or "|" for example? I probably need to escape them in some way?
I would want something like this: @.match('+'), instead of @.match('4')
I tried escaping them with \ or ` or ´ or ' but that doesn't work, neither does URL-encoding the characters so i must be missing something!

calling the API key from within the script

Hello

I have added a few lines of code to the original ImportJson script which calls the API key from withing the script and not from a cell everytime you run the function from a cell in google sheets. Can you do that in your script? The lines of my code is:

function ImportJSON_mycustomapi(url, api_key, query, parseOptions) {
var header = {
headers: {
'key_token_myapi': 'myapikey',
'Accept': 'application/json'
}
}
return ImportJSONAdvanced(url, header, query, parseOptions, includeXPath_, defaultTransform_)
}

Adding Data Binary

Hi @qeet , is it possible to insert data binary in importjsonapi?

I tried the curl method:POST on Postman and it worked. Here's the curl code:

curl 'https://portal.grab.com/foodweb/v2/search' \ -H 'authority: portal.grab.com' \ -H 'content-type: application/json;charset=UTF-8' \ -H 'origin: https://food.grab.com' \ -H 'sec-fetch-site: same-site' \ -H 'sec-fetch-mode: cors' \ -H 'sec-fetch-dest: empty' \ --data-binary '{"latlng":"-6.1164339,106.7896514","keyword":"kopi","offset":0,"pageSize":32}' \ --compressed

and the code I tried using importjsonapi:
`=IMPORTJSONAPI("https://portal.grab.com/foodweb/v2/search","$.target","target","method=post","headers={'authority':'portal.grab.com','content-type':'application/json;charset=UTF-8','origin':'https://food.grab.com','sec-fetch-site': 'same-site','sec-fetch-mode':'cors','sec-fetch-dest':'empty')")'

It return " ERROR: Invalid JSON and I still need to add the data binary.

Thank you

Google Sheets api error.

Hey when i try to get values from this API :
https://api.torn.com/torn/?selections=items&key=sjGKkYCzbfOflGjq
Gives me an error on formula .
I want to get all values from this api . Can you help me pls ?

Help importing orders with multiple items to Google Sheets

I have a json order file. Some orders have single items but the potential exists for an order to have more than one item. Please see the attached example. What I would like to do is to import to Google Sheets and have a new row for each order/item combination. I am fine with the order information repeating on each line.
json_orders.pdf

Does not receive data with a dot at the end

Exemple formula: =IMPORTJSONAPI(R3; "$.RADAR_DATA.PERIODS.*"; "~, @.'Активы', @.'Выручка тек.'")
String @.'Выручка тек.' doesn't work

Getting a max value from an array

---

So this will give me raw_data:

=IMPORTJSONAPI("https://ycharts.com/charts/fund_data.json?securities=include%3Atrue%2Cid%3ADBX%2C%2C&calcs=include%3Atrue%2Cid%3Amarket_cap%2C%2C&correlations=&format=real&recessions=false&zoom=5&startDate=&endDate=&chartView=&splitType=single&scaleType=linear&note=&title=&source=false&units=false&quoteLegend=true&partner=&quotes=&legendOnChart=true&securitylistSecurityId=&clientGroupLogoUrl=&displayTicker=false&ychartsLogo=&useEstimates=false&maxPoints=880","$..raw_data", "@")

In this case, 1st value in raw_data is a timestamp and 2nd is market cap. I'm trying to get max marker cap value regardless of timestamp... any suggestions, please?

Do wildcards (*) not work in Column paths? Or am I being stupid...

Trying to pull using this formula:
=IMPORTJSONAPI("https://www.espn.com/nfl/schedule/_/year/2021/week/1?xhr=1", "content.schedule.*", "games.*.date")

But keep getting "ERROR: unexpected character in column path: '*.date'". Have tried with brackets and on a different URL earlier. I swear this used to work.

Handle empty return

This is a great library! Seems like it is being updated much more than the other one.

I have a stock API service I am pulling data from. For some stocks, the data is there and everything works great. For some, there is no data at all. When I view the endpoint in the browser, I just view nothing, a blank page. Then when I click on 'raw data', I just see an empty array []. So when that gets pulled into my sheet, it looks empty in the cell. But I am running a formula on that cell: =IF(ISBLANK(TRIM(C3)), B3*E3, C3*E3) and for some reason ISBLANK is always returning false. The formula in C3 is: =IMPORTJSONAPI("https://cloud.iexapis.com/stable/stock/" & A3 & "/dividends/next/?token=" & P$7, "$", "amount")

Also, here is the returned JSON for a call to the same endpoint that does include data: {"exDate":"2020-04-14","paymentDate":"2020-05-15","recordDate":"2020-04-15","declaredDate":"2020-02-20","amount":"1.18","flag":"Cash","currency":"USD","description":"Ordinary Shares","frequency":"quarterly","symbol":"ABBV"}

IMPORTJSONAPI call from within script instead of formula

This is more of a question than anything, but I was not sure where to place it.

One thing I am trying to figure out is if there is a way to not place the IMPORTJSONAPI formula within the sheet itself, but rather just make the JSON call 1 time from the script itself which then populates the Google Sheet and then stops. It would only execute the ImportJSON whenever the Google Sheet is opened.

The big issue with the current approach is that the formula is within a cell on the sheet. If you for example try to add a column, delete a row, or just about anything else, the sheet immediately refreshes everything resulting in it resetting the columns, rows, etc. back.

Any ideas? So far, I have had no luck trying to get it to work this way and I have not found any other version of this that tries that.

Thanks

Google Apps Script 30 second timeout for custom functions

Hi there. I am running up against this timeout when trying to load data from an API using this importjsonapi function. Through research I have discovered that Google Apps Script/Sheets has a 30 second timeout for custom functions. Do you know of any way to raise this parameter or is there some other workaround ? Thank You

Return column fileds inside Array

Could someone help me.
I have the return below an API.

I need to get all the information that is inside PRODUCTS.
I'm using the expression:
=IMPORTJSONAPI("https://api.dslite.com.br/v1/CrossDocking/Catalogo/18?limit=50"; "$.produtos[?(@.preco_normal>20)]"; "produtoid,produtoid_empresa,fornecedorid,status_empresa,preco_normal,descricao,midias"; "headers={'token':'da363bf4445cd95d4031debdbe4788d2'}")

But the problem is in the MIDIAS field, which has other internal fields (type, index and value).
The JSONIMPORTAPI function does not return fields from within MIDIAS correctly.
I need each field within MIDIAS to be a COLUMN.
But I get only one column with all the images

Example:
[{"tipo":"imagem","indice":"0","valor":"http://www.oderco.com.br/arquivos/produtos/gg/10612_1.jpg"},{"tipo":"imagem","indice":"1","valor":"http://www.oderco.com.br/arquivos/produtos/gg/10612_2.jpg"}]

Is it possible to take the return from the media field and separate it by column?

{
"fornecedorid": 3,
"apelido": "teste123",
"detalhesConsulta": {
"offset": 0,
"limit": 1,
"registrosRetornados": 1,
"totalRegistros": 346
},
"produtos": [
{
"produtoid": "13499",
"produtoid_empresa": "",
"preco_dropshipping": 0,
"preco_crossdocking": 13.45,
"data_inclusao": {
"date": "2020-09-01 12:22:31.000000",
"timezone_type": 3,
"timezone": "Etc/GMT+3"
},
"data_atualizacao_preco": {
"date": "2020-09-01 12:22:31.000000",
"timezone_type": 3,
"timezone": "Etc/GMT+3"
},
"preco_revenda": "15.47",
"preco_revenda_promocional": "0.00",
"margem_lucro": 15,
"status_fornecedor": "A",
"numero_atualizacoes": 5996,
"local_estoque": "PR",
"cep_origem": "86031220",
"data_atualizacao_estoque": {
"date": "2020-09-11 14:35:03.000000",
"timezone_type": 3,
"timezone": "Etc/GMT+3"
},
"data_atualizacao_fornecedor": {
"date": "2020-09-18 03:02:09.000000",
"timezone_type": 3,
"timezone": "Etc/GMT+3"
},
"estoque": 0,
"produtoid_pai": null,
"variacao": "",
"midias": [
{
"tipo": "imagem",
"indice": "1",
"valor": "https://static.hayapek.com.br/produtos/13499/550/1.jpg"
}
{
"tipo": "imagem",
"indice": "2",
"valor": "https://static.hayapek.com.br/produtos/13499/550/3.jpg"
}
{
"tipo": "imagem",
"indice": "3",
"valor": "https://static.hayapek.com.br/produtos/13499/550/3.jpg"
}
]
}
],
"fornecedor": {
"fornecedorid": 3,
"venda_direta": "N",
"preco_minimo_venda_direta": 0,
"preco_maximo_venda_direta": 0,
"permite_pedido_crossdocking": "N"
}
}

possible to use the script in Excel?

Hi

I'm not allowed to use the google sheets tools at work for confidentiality reasons.

Is there a way to integrate this script in Excel?
Or is there i similar tool for Excel? I'm using the =WEBSERVICE() function, but it is not really as practical as IMPORTJSONAPI.

Thanks for the feedback

Adding cookies

Hi @qeet , I tried your script adding headers={ 'referer' : 'origin' and it worked.

Now I tried to fetch url that requires cookies. I tried to copy the cUrl

`curl 'https://seller.shopee.co.id/api/marketing/v3/pas/suggest/keyword/?SPC_CDS=b170594b-b97d-4c98-b46a-3817a4476b13&SPC_CDS_VER=2&keyword=&count=100&placement=0&itemid=3327563418' -H 'referer: https://seller.shopee.co.id/portal/marketing/pas/new?pid=3327563418'

-H 'cookie: SPC_F=B6DG9MuH6tqlCJcD7x4zYX1j1oZ8097d;SPC_EC="PP4R4d/SjONDGHb/QEOY/XPOXw5Tu7aUmm2c+cRWUE9rcKeiu436/tQcUCdXvmqXC1GzRsm5GgEH6tdgpkgLGy7RaXfT1HD0GyaMurb8e3IZpEIZo7Sx8mPJDG6K9tknWB1kZWJQLB4vCwBDpZWpky47GCkpVOzrona1OQR8JZ4="; SPC_SC_TK=6ca4bfc99e8734310995fb64ce6c1046;SPC_SC_UD=17702964;' --compressed`

and I tried to add into the importjsonapi
=IMPORTJSONAPI(K4, "$.data[*]", "keyword", "headers={ 'referer' : 'origin','cookie' : 'SPC_F=B6DG9MuH6tqlCJcD7x4zYX1j1oZ8097d;SPC_EC="PP4R4d/SjONDGHb/QEOY/XPOXw5Tu7aUmm2c+cRWUE9rcKeiu436/tQcUCdXvmqXC1GzRsm5GgEH6tdgpkgLGy7RaXfT1HD0GyaMurb8e3IZpEIZo7Sx8mPJDG6K9tknWB1kZWJQLB4vCwBDpZWpky47GCkpVOzrona1OQR8JZ4="; SPC_CT_9d9658d8="1587439461.hxTJLUJRoRytXKniHrrDw0N6S45gCrZATHnQkM1htDU="; SPC_SC_TK=6ca4bfc99e8734310995fb64ce6c1046;SPC_SC_UD=17702964;' }")

but it returns Formula Parse Error. Any suggestion to add the cookies?

Thank you

limited by 3 args when trying to use headers

Hi. Love the extension of ImportJSON to be able to include basic auth and headers in the import, but I'm getting hung up on 3 arg limit. So far I have:
=INPUTJSONAPI("https://url", "path", "@")

When I attempt to add any 4th arg sheets informs me of the 3 arg limit, and if I attempt it without the comma I get a parse error. This is what I'm trying to add:
,'headers={"API-KEY" : "key", "API-SECRET" : "secret"}'

I'm new to programming and sheets, so taking a look at the script didn't inform me as to why it wasn't working. Thanks mate

concatenate multiple array matches in a single cell

Hi, thx a lot for the function, works great !

I d like to be able to display a concatenated list in a single cell .

I have a feed with arrays of tags for each item, and I would like to concatenate all item tags in a single column

For example, I would target each item, and use columns: tags[].slug, and I would like it to return "gouvernement, remaniement,..."

Currently I can only think of creating multiple columns tags[0].slug,tags[1].slug,... and concatenate them in Sheet, but that relies on knowing the maximum number of tags

"tags": [
               {
           "slug": "gouvernement",
           "nodeId": "null"
       }
       ,                {
           "slug": "remaniement",
           "nodeId": "null"
       }
       ,                {
           "slug": "ministre-de-l-interieur",
           "nodeId": "null"
       }
       ,                {
           "slug": "tensions",
           "nodeId": "null"
       }
                   ], 

Stagger Requests

Is there a way to stagger requests correctly? For example I have 140 total requests in my google sheet and I want to only do 14 requests/s

Would something like this work?

Utilities.sleep(waitTime);


function do_fetch_(url, params) {
  if (params['contentType'] === "application/json" && typeof params['payload'] === 'object' ) {
     params['payload'] = JSON.stringify(params['payload'])
  }

const waitTime = Math.floor(Math.random() * 10);
Utilities.sleep(waitTime);
  
  var response = UrlFetchApp.fetch(url, params)
  return JSON.parse(response.getContentText());
}

Setting columns to a particular data type

I am pulling stock data with dates and prices. The problem is that it all comes back as strings instead of dates and numbers so when I try to do things like sums it does not work.

It would be very nice if you had the ability to cast columns of data to numeric or date datatypes.

If you really want to make it good, make it recognize percentages (so 5% becomes .05) and B, M, T so 39.5M becomes 39,500,000.

Formatting data with the API

Hi, I just started using this script to retrieve info from Instagram. I was using this ImportJSON script, but it stopped working, but luckily I found yours and now I'm able to retrieve data again, thank you so much !
The only problem that I have is with formatting. With the previous script, all the data was returned in a table. However, your script, I'm having trouble formatting the data correctly. I have tried several paths, but it just doesn't work the way I want to...

What am I doing wrong ?
Thanks in advance

PS : Here's some screenshots and the URL I'm using to retrieve the content

URL : https://www.instagram.com/graphql/query/?query_hash=f2405b236d85e8296cf30347c9f08c2a&variables=%7B%22id%22%3A%2226296967056%22%2C%22first%22%3A12%7D

Pass hyphen in column name?

Is there a way to pass a hyphen in a column's name?

I added ["hyphen", /^-/] to the patterns list which didn't throw an error anymore, but did skip those columns. Any advice greatly appreciated!

Filter results based on parent element name

Awesome function! I'm having trouble getting my JSONPath query to work.

The data source is: https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=IBM&apikey=demo

I'm trying to get all the of the "7. Dividend Amount" values by year. eg something like this:
=ImportJSONAPI("https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=IBM&apikey=demo", "$[Monthly Adjusted Time Series].2019*.[7. dividend amount]", "@")

I can get all the Dividend Amounts with this:
=ImportJSONAPI("https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=IBM&apikey=demo", "$[Monthly Adjusted Time Series].*.[7. dividend amount]", "@")

or an individual month also works:
=ImportJSONAPI("https://www.alphavantage.co/query?function=TIME_SERIES_MONTHLY_ADJUSTED&symbol=IBM&apikey=demo", "$[Monthly Adjusted Time Series].2021-02-26.[7. dividend amount]", "@")

But anytime I try to use a query to filter the date, it comes back empty. Thanks in advance for any input you have!

Array result was not expanded because it would overwrite data

Hi,
This is my JSON data:
{ "order_code": "505976303", "coupon_code": null, "status": "complete", "total_price_before_discount": 589000, "total_price_after_discount": 589000, "created_at": "2020-07-10 16:43:47", "updated_at": "2020-07-22 17:08:57", "purchased_at": "2020-07-10 16:43:47", "fulfillment_type": "tiki_delivery", "note": "", "deliveryConfirmed": "", "delivery_confirmed_at": "2020-07-20 00:00:00", "is_rma": 0, "tax": { "code": null, "name": null, "address": null }, "discount": { "discount_amount": 0, "discount_coupon": 0, "discount_tiki_point": 0 }, "shipping": { "name": "Thu Vuong", "street": "tổ 10", "ward": "Thị Trấn Mường Chà", "city": "Huyện Mường Chà", "region": "Điện Biên", "country": "VN", "phone": "", "email": "", "estimate_description": "Dự kiến giao hàng vào Thứ bảy, 18/07/2020", "shipping_fee": 0 }, "items": [ { "id": 153010118, "product_id": 50722606, "product_name": "Máy hút bụi cầm tay mini Deerma DX118C công suất 600W dung tích bình chứa bụi 1.2L - Hàng chính hãng - Xanh", "sku": "4441306511894", "original_sku": "DX118C-B", "qty": 1, "price": 589000, "confirmation_status": null, "confirmed_at": "", "must_confirmed_before_at": "", "warehouse_code": null, "inventory_type": "instock", "serial_number": [], "imei": [], "discount": { "discount_amount": 0, "discount_coupon": 0, "discount_tiki_point": 0 }, "fees": [ { "fee_type_id": 1, "name": "Chiết khấu", "key": "percent_per_item_sales_value", "fee_total_amount": 58900 }, { "fee_type_id": 2, "name": "Phí thanh toán", "key": "payment_processing_fee", "fee_total_amount": 0 }, { "fee_type_id": 23, "name": "Phí cố định", "key": "base_fee", "fee_total_amount": 20000 } ], "fee_total": 78900, "seller_id": 42421 } ], "payment": { "payment_method": "cod", "updated_at": "2020-07-22 17:08:57", "description": "Thanh toán tiền mặt khi nhận hàng" }, "handling_fee": 0, "collectable_total_price": 589000 }
And this is my formula in Google Sheets:
=importjsonapi("my-url","$..","status,purchased_at,delivery_confirmed_at,shipping.region,shipping.estimate_description,items[0].product_name,items[0].qty,items[0].price,items[0].fees[0].fee_total_amount,items[0].fees[1].fee_total_amount,items[0].fees[2].fee_total_amount,items[0].fee_total,payment.description","headers={'myapiname':'myapisecrectkey'}")
When I apply this formula to rows below, there is the error "Array result was not expanded because it would overwrite data..." in every row that formula was applied, excerpt last row.
How can I fix it? How every rows will show correct data?
Please help, thank you!

Multiple queries same cell

Hi,

Is there any way to import multiple queries?
That is, to add several IMPORTJSONAPI functions in the same cell and with different filters?

I need to return the following URLs:

https://api.xxxxx.com.br/v1/xxxx/Catalogo/18?&j= "
https://api.xxxxx.com.br/v1/xxxx/Catalogo/20?&j= "
https://api.xxxxx.com.br/v1/xxxx/Catalogo/25?&j= "

As you can see the code after the "/" is changed for each query, so I need to aggregate all of these queries in a single cell.

Concatenate strings and parsed data

i have this formula
=IMPORTJSONAPI("https://api.firetype.ru/getFakeSellers.php";"$[*]";"user_id,nickname,vk,clan")
the fourth column is json object and the third is number...
i want something like
=IMPORTJSONAPI("https://api.firetype.ru/getFakeSellers.php";"$[*]";"user_id,nickname,'https://vk.com/id'+vk,'['+clan.tag+'] '+clan.name")
so clan name and tag will be in the same cell and vk column will become a url...

Sometimes response output in the cell below formula

I think I've found an issue. If it's not a bug, I do apologize, as I'm not very technical.

If I use the following request:
=IMPORTJSONAPI("https://financialmodelingprep.com/api/v3/company/discounted-cash-flow/AAPL?apikey=demo";"$.";"dcf")

the output will be in the same cell as the formula.

But if you try this request:
=IMPORTJSONAPI("https://financialmodelingprep.com/api/v3/financial-growth/AAPL?limit=1&apikey=demo";"$..";"revenueGrowth")
it will list the results below the cell, which makes it impossible to have a list of stock on each row.

Is this intended behaviour?

Filtering out sub sub results

I am trying to get results for "name" as highlighted below, but it's pulling back all results for subcategories with the word name, I only want that specific one... is that possible?

{
    "objects": [
        {
            "source_created": null,
            "circles": [
                {
                    "id": xx,
                    "name": "NOT ME"
                }
            ],
            "feed_id": xx,
            "workgroups": [],
            "aliases": [
                {
                    "name": "xx"
                }
            ],
            "is_email": null,
            "published_ts": "xx",
            "id": xx,
            "source_modified": null,
            "type": [],
            "start_date": null,
            "status": null,
            "publication_status": "xx",
            "end_date": null,
            "tags": [
                {
                    "id": "1",
                    "name": "NOT ME"
                },
                {
                    "id": "2",
                    "name": "NOT ME"
                },
                {
                    "id": "3",
                    "name": "NOT ME"
                },
                {
                    "id": "4",
                    "name": "NOT ME"
                },
                {
                    "id": "5",
                    "name": "NOT ME"
                },
                {
                    "id": "6",
                    "name": "NOT ME"
                },
                {
                    "id": "7",
                    "name": "NOT ME"
                },
                {
                    "id": "8",
                    "name": "NOT ME"
                }
            ],
            "tlp": "amber",
            "name": "THIS IS THE ONLY ONE I AM AFTER",
            "model_type": "xx",

The script somehow blocks the debugger from Google Action Script

1. First, your code is amazing! Thanks for sharing it with us.

2. Second, does someone have problem to debug the script when using this code? With it in place the debug stuck loading forever, when I take it out (and the code that use it on my main script) the debug works. Any advice will be really appreciated. Thanks!

Add date conversion

Hello,

First let me thank you for the awesome work! helped me a lot in my google sheet 😃

So i have a json that has a date like this:

"creation_date": "2003-09-22"

and i want it to convert to a Date object.

Would you please consider adding something like this?

Best regards

Unexpected end of JSON input

When trying to import a json file that is very long I get an "ERROR: Unexpected end of JSON input". When I make the JSON file smaller (same formatting), then it works. Do you know what is causing this? Is it timing out and not loading the whole JSON once it gets to a certain size? If so is there a way to fix it or increase the time before timeout?

Importing paginated JSONs

Has anyone found a way to gracefully support paginated JSONs? I'm having trouble with https://localbitcoins.com/buy-bitcoins-online/.json where the data is split in buckets of 50 records given that the JSON schema follows this structure

{
data: {...},
pagination: {
next: "https://localbitcoins.com/buy-bitcoins-online/.json?page=2"
}
} 

Json import limit error

Is there a limit i can edit to increase this number for the following error:

text result of importjsonapi is longer than the limit of 50000 characters

How to parse the full response

First of all - beautiful function, just what I was looking for. I've got more of a question I guess than an issue. So I've got a JSON response like so:

{
  "metrics": [
    {
      "human_date": "2016-11-26",
      "active_customers": 642,
      "active_subscriptions": 642
    },
    {
      "human_date": "2016-11-27",
      "active_customers": 640,
      "active_subscriptions": 640
    }
  ]
}

And I'm querying it like so to get the metrics $.metrics.* and passing human_date, active_customers and active_subscription as column parameters, and I get back the data perfectly. But how do I get all the data from the metrics array and formatted with headers without actually knowing what kind of metrics will I get back from the API in advance?

I can manage getting the metrics themselves in a list like so $.metrics...~ but I cannot get them back as column names/headers.

Any ideas?