How can I import json from url using only existing formulas of google sheets (without scripts or apps or whatever else additional there is)?

Question

I want to get data from this url: https://api.monobank.ua/bank/currency

It has currency exchange rate data. But it's json. So whenever I'm trying to use IMPORTXML or IMPORTDATA it gives me error whatever parameters I was trying to pass there. I found multiple articles online about importing json, but those solutions require either manual coding which I don't want to use or third party scripts/apps (not sure about the name it's a bit confusing for me). The reason is that firstly it's much more complicated and I don't really want to share my data with third parties when it's not really needed, secondly, when I tried one it worked on desktop, but it didn't work automatically on mobile. I believe I needed to turn on something manually every time I was opening my sheet on mobile and I don't want that.

So what I wan't is just to be able to get that data from that json. I don't even care about it being pretty or sorted and filtered for now. I'll figure that out. I just need it to be imported at all somehow. Any ideas how to get that using only existing default functions of google sheets?

P.S. For some time I was using IMPORTXML and different website which is not the official website of the bank I need. But after some time that website stopped working. And I don't want to experiment with another one not official website because it doesn't always have correct up to date data and it might be down as well one day. So I'd rather use official resource of the bank. Bu this bank doesn't have currency rates on their website. Only at this url via json.

Update: There's a url that contains json. I need that json. And I don't want to use anything besides default functions (custom scripts), because they don't work proparly and automatically when I open same excel sheet from my phone.

Here's sample json from url:

[{"currencyCodeA":840,"currencyCodeB":980,"date":1675116074,"rateBuy":36.65,"rateCross":0,"rateSell":37.4406},{"currencyCodeA":978,"currencyCodeB":980,"date":1675173374,"rateBuy":39.75,"rateCross":0,"rateSell":40.8497},{"currencyCodeA":978,"currencyCodeB":840,"date":1675173374,"rateBuy":1.077,"rateCross":0,"rateSell":1.092},{"currencyCodeA":826,"currencyCodeB":980,"date":1675175425,"rateBuy":0,"rateCross":46.5052,"rateSell":0},{"currencyCodeA":392,"currencyCodeB":980,"date":1675174879,"rateBuy":0,"rateCross":0.2896,"rateSell":0},{"currencyCodeA":756,"currencyCodeB":980,"date":1675175426,"rateBuy":0,"rateCross":40.7803,"rateSell":0},{"currencyCodeA":156,"currencyCodeB":980,"date":1675175316,"rateBuy":0,"rateCross":5.5376,"rateSell":0},{"currencyCodeA":784,"currencyCodeB":980,"date":1675175427,"rateBuy":0,"rateCross":10.2109,"rateSell":0},{"currencyCodeA":971,"currencyCodeB":980,"date":1663425223,"rateBuy":0,"rateCross":0.4252,"rateSell":0},{"currencyCodeA":8,"currencyCodeB":980,"date":1675175334,"rateBuy":0,"rateCross":0.3496,"rateSell":0},{"currencyCodeA":51,"currencyCodeB":980,"date":1675175251,"rateBuy":0,"rateCross":0.0955,"rateSell":0},{"currencyCodeA":973,"currencyCodeB":980,"date":1675091759,"rateBuy":0,"rateCross":0.0742,"rateSell":0},{"currencyCodeA":32,"currencyCodeB":980,"date":1675175217,"rateBuy":0,"rateCross":0.2006,"rateSell":0},{"currencyCodeA":36,"currencyCodeB":980,"date":1675175192,"rateBuy":0,"rateCross":26.6757,"rateSell":0},{"currencyCodeA":944,"currencyCodeB":980,"date":1675175204,"rateBuy":0,"rateCross":22.0296,"rateSell":0},{"currencyCodeA":50,"currencyCodeB":980,"date":1675171101,"rateBuy":0,"rateCross":0.3539,"rateSell":0},{"currencyCodeA":975,"currencyCodeB":980,"date":1675175398,"rateBuy":0,"rateCross":20.8201,"rateSell":0},{"currencyCodeA":48,"currencyCodeB":980,"date":1675171805,"rateBuy":0,"rateCross":99.5818,"rateSell":0},{"currencyCodeA":108,"currencyCodeB":980,"date":1538606522,"rateBuy":0,"rateCross":0.0158,"rateSell":0},{"currencyCodeA":96,"currencyCodeB":980,"date":1673166020,"rateBuy":0,"rateCross":28.08,"rateSell":0},{"currencyCodeA":68,"currencyCodeB":980,"date":1675056126,"rateBuy":0,"rateCross":5.4578,"rateSell":0},{"currencyCodeA":986,"currencyCodeB":980,"date":1675174577,"rateBuy":0,"rateCross":7.3674,"rateSell":0},{"currencyCodeA":72,"currencyCodeB":980,"date":1670477598,"rateBuy":0,"rateCross":2.9174,"rateSell":0},{"currencyCodeA":933,"currencyCodeB":980,"date":1675169161,"rateBuy":0,"rateCross":14.1449,"rateSell":0},{"currencyCodeA":124,"currencyCodeB":980,"date":1675175386,"rateBuy":0,"rateCross":28.1588,"rateSell":0},{"currencyCodeA":976,"currencyCodeB":980,"date":1655462332,"rateBuy":0,"rateCross":0.0163,"rateSell":0},{"currencyCodeA":152,"currencyCodeB":980,"date":1675175149,"rateBuy":0,"rateCross":0.0461,"rateSell":0},{"currencyCodeA":170,"currencyCodeB":980,"date":1675174367,"rateBuy":0,"rateCross":0.0082,"rateSell":0},{"currencyCodeA":188,"currencyCodeB":980,"date":1675171706,"rateBuy":0,"rateCross":0.0676,"rateSell":0},{"currencyCodeA":192,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":1.5237,"rateSell":0},{"currencyCodeA":203,"currencyCodeB":980,"date":1675175425,"rateBuy":0,"rateCross":1.7161,"rateSell":0},{"currencyCodeA":262,"currencyCodeB":980,"date":1675085761,"rateBuy":0,"rateCross":0.2109,"rateSell":0},{"currencyCodeA":208,"currencyCodeB":980,"date":1675175419,"rateBuy":0,"rateCross":5.5021,"rateSell":0},{"currencyCodeA":12,"currencyCodeB":980,"date":1675167443,"rateBuy":0,"rateCross":0.2762,"rateSell":0},{"currencyCodeA":818,"currencyCodeB":980,"date":1675175358,"rateBuy":0,"rateCross":1.2523,"rateSell":0},{"currencyCodeA":230,"currencyCodeB":980,"date":1674249095,"rateBuy":0,"rateCross":0.701,"rateSell":0},{"currencyCodeA":981,"currencyCodeB":980,"date":1675175419,"rateBuy":0,"rateCross":14.3773,"rateSell":0},{"currencyCodeA":936,"currencyCodeB":980,"date":1675175055,"rateBuy":0,"rateCross":3.0364,"rateSell":0},{"currencyCodeA":270,"currencyCodeB":980,"date":1675085308,"rateBuy":0,"rateCross":0.6113,"rateSell":0},{"currencyCodeA":324,"currencyCodeB":980,"date":1674127391,"rateBuy":0,"rateCross":0.0043,"rateSell":0},{"currencyCodeA":344,"currencyCodeB":980,"date":1675169082,"rateBuy":0,"rateCross":4.7833,"rateSell":0},{"currencyCodeA":191,"currencyCodeB":980,"date":1674429800,"rateBuy":0,"rateCross":5.3274,"rateSell":0},{"currencyCodeA":348,"currencyCodeB":980,"date":1675175419,"rateBuy":0,"rateCross":0.1047,"rateSell":0},{"currencyCodeA":360,"currencyCodeB":980,"date":1675175419,"rateBuy":0,"rateCross":0.0025,"rateSell":0},{"currencyCodeA":376,"currencyCodeB":980,"date":1675175426,"rateBuy":0,"rateCross":10.858,"rateSell":0},{"currencyCodeA":356,"currencyCodeB":980,"date":1675174203,"rateBuy":0,"rateCross":0.46,"rateSell":0},{"currencyCodeA":368,"currencyCodeB":980,"date":1675172294,"rateBuy":0,"rateCross":0.0256,"rateSell":0},{"currencyCodeA":364,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.0009,"rateSell":0},{"currencyCodeA":352,"currencyCodeB":980,"date":1675175332,"rateBuy":0,"rateCross":0.2643,"rateSell":0},{"currencyCodeA":400,"currencyCodeB":980,"date":1675173769,"rateBuy":0,"rateCross":52.8843,"rateSell":0},{"currencyCodeA":404,"currencyCodeB":980,"date":1675173621,"rateBuy":0,"rateCross":0.3012,"rateSell":0},{"currencyCodeA":417,"currencyCodeB":980,"date":1675173761,"rateBuy":0,"rateCross":0.4341,"rateSell":0},{"currencyCodeA":116,"currencyCodeB":980,"date":1675090399,"rateBuy":0,"rateCross":0.009,"rateSell":0},{"currencyCodeA":408,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":16.6221,"rateSell":0},{"currencyCodeA":410,"currencyCodeB":980,"date":1675174157,"rateBuy":0,"rateCross":0.0304,"rateSell":0},{"currencyCodeA":414,"currencyCodeB":980,"date":1675166923,"rateBuy":0,"rateCross":122.81,"rateSell":0},{"currencyCodeA":398,"currencyCodeB":980,"date":1675175279,"rateBuy":0,"rateCross":0.0813,"rateSell":0},{"currencyCodeA":418,"currencyCodeB":980,"date":1675156565,"rateBuy":0,"rateCross":0.0023,"rateSell":0},{"currencyCodeA":422,"currencyCodeB":980,"date":1675121943,"rateBuy":0,"rateCross":0.0009,"rateSell":0},{"currencyCodeA":144,"currencyCodeB":980,"date":1675175426,"rateBuy":0,"rateCross":0.1024,"rateSell":0},{"currencyCodeA":434,"currencyCodeB":980,"date":1674670757,"rateBuy":0,"rateCross":7.8783,"rateSell":0},{"currencyCodeA":504,"currencyCodeB":980,"date":1675164585,"rateBuy":0,"rateCross":3.7012,"rateSell":0},{"currencyCodeA":498,"currencyCodeB":980,"date":1675175404,"rateBuy":0,"rateCross":2.0001,"rateSell":0},{"currencyCodeA":969,"currencyCodeB":980,"date":1674658901,"rateBuy":0,"rateCross":0.0087,"rateSell":0},{"currencyCodeA":807,"currencyCodeB":980,"date":1675174364,"rateBuy":0,"rateCross":0.6598,"rateSell":0},{"currencyCodeA":496,"currencyCodeB":980,"date":1675173443,"rateBuy":0,"rateCross":0.0108,"rateSell":0},{"currencyCodeA":478,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.1012,"rateSell":0},{"currencyCodeA":480,"currencyCodeB":980,"date":1675169710,"rateBuy":0,"rateCross":0.8519,"rateSell":0},{"currencyCodeA":454,"currencyCodeB":980,"date":1633949773,"rateBuy":0,"rateCross":0.0325,"rateSell":0},{"currencyCodeA":484,"currencyCodeB":980,"date":1675174965,"rateBuy":0,"rateCross":1.9971,"rateSell":0},{"currencyCodeA":458,"currencyCodeB":980,"date":1675174184,"rateBuy":0,"rateCross":8.8458,"rateSell":0},{"currencyCodeA":943,"currencyCodeB":980,"date":1674973048,"rateBuy":0,"rateCross":0.592,"rateSell":0},{"currencyCodeA":516,"currencyCodeB":980,"date":1675166113,"rateBuy":0,"rateCross":2.1822,"rateSell":0},{"currencyCodeA":566,"currencyCodeB":980,"date":1675174556,"rateBuy":0,"rateCross":0.0811,"rateSell":0},{"currencyCodeA":558,"currencyCodeB":980,"date":1675140373,"rateBuy":0,"rateCross":1.0388,"rateSell":0},{"currencyCodeA":578,"currencyCodeB":980,"date":1675175397,"rateBuy":0,"rateCross":3.806,"rateSell":0},{"currencyCodeA":524,"currencyCodeB":980,"date":1675164125,"rateBuy":0,"rateCross":0.2873,"rateSell":0},{"currencyCodeA":554,"currencyCodeB":980,"date":1675150084,"rateBuy":0,"rateCross":24.3812,"rateSell":0},{"currencyCodeA":512,"currencyCodeB":980,"date":1675169820,"rateBuy":0,"rateCross":97.4067,"rateSell":0},{"currencyCodeA":604,"currencyCodeB":980,"date":1675172782,"rateBuy":0,"rateCross":9.815,"rateSell":0},{"currencyCodeA":608,"currencyCodeB":980,"date":1675174731,"rateBuy":0,"rateCross":0.6865,"rateSell":0},{"currencyCodeA":586,"currencyCodeB":980,"date":1675173121,"rateBuy":0,"rateCross":0.139,"rateSell":0},{"currencyCodeA":985,"currencyCodeB":980,"date":1675175427,"rateBuy":0,"rateCross":8.6795,"rateSell":0},{"currencyCodeA":600,"currencyCodeB":980,"date":1675170923,"rateBuy":0,"rateCross":0.0051,"rateSell":0},{"currencyCodeA":634,"currencyCodeB":980,"date":1675174960,"rateBuy":0,"rateCross":10.279,"rateSell":0},{"currencyCodeA":946,"currencyCodeB":980,"date":1675175420,"rateBuy":0,"rateCross":8.3479,"rateSell":0},{"currencyCodeA":941,"currencyCodeB":980,"date":1675175015,"rateBuy":0,"rateCross":0.3471,"rateSell":0},{"currencyCodeA":682,"currencyCodeB":980,"date":1675174496,"rateBuy":0,"rateCross":9.9626,"rateSell":0},{"currencyCodeA":690,"currencyCodeB":980,"date":1675174121,"rateBuy":0,"rateCross":2.7107,"rateSell":0},{"currencyCodeA":938,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.0627,"rateSell":0},{"currencyCodeA":752,"currencyCodeB":980,"date":1675175416,"rateBuy":0,"rateCross":3.6463,"rateSell":0},{"currencyCodeA":702,"currencyCodeB":980,"date":1675175224,"rateBuy":0,"rateCross":28.5636,"rateSell":0},{"currencyCodeA":694,"currencyCodeB":980,"date":1664217991,"rateBuy":0,"rateCross":0.0024,"rateSell":0},{"currencyCodeA":706,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.0647,"rateSell":0},{"currencyCodeA":968,"currencyCodeB":980,"date":1669419358,"rateBuy":0,"rateCross":1.223,"rateSell":0},{"currencyCodeA":760,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.0081,"rateSell":0},{"currencyCodeA":748,"currencyCodeB":980,"date":1668614714,"rateBuy":0,"rateCross":2.1898,"rateSell":0},{"currencyCodeA":764,"currencyCodeB":980,"date":1675175359,"rateBuy":0,"rateCross":1.1467,"rateSell":0},{"currencyCodeA":972,"currencyCodeB":980,"date":1675171585,"rateBuy":0,"rateCross":3.6375,"rateSell":0},{"currencyCodeA":795,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.0021,"rateSell":0},{"currencyCodeA":788,"currencyCodeB":980,"date":1675115834,"rateBuy":0,"rateCross":12.3553,"rateSell":0},{"currencyCodeA":949,"currencyCodeB":980,"date":1675175427,"rateBuy":0,"rateCross":2.011,"rateSell":0},{"currencyCodeA":901,"currencyCodeB":980,"date":1675173960,"rateBuy":0,"rateCross":1.2462,"rateSell":0},{"currencyCodeA":834,"currencyCodeB":980,"date":1675161871,"rateBuy":0,"rateCross":0.016,"rateSell":0},{"currencyCodeA":800,"currencyCodeB":980,"date":1675144477,"rateBuy":0,"rateCross":0.0101,"rateSell":0},{"currencyCodeA":858,"currencyCodeB":980,"date":1675169830,"rateBuy":0,"rateCross":0.9552,"rateSell":0},{"currencyCodeA":860,"currencyCodeB":980,"date":1675175406,"rateBuy":0,"rateCross":0.0033,"rateSell":0},{"currencyCodeA":937,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":1.6701,"rateSell":0},{"currencyCodeA":704,"currencyCodeB":980,"date":1675174868,"rateBuy":0,"rateCross":0.0015,"rateSell":0},{"currencyCodeA":950,"currencyCodeB":980,"date":1675108101,"rateBuy":0,"rateCross":0.062,"rateSell":0},{"currencyCodeA":952,"currencyCodeB":980,"date":1675173477,"rateBuy":0,"rateCross":0.0623,"rateSell":0},{"currencyCodeA":886,"currencyCodeB":980,"date":1543715495,"rateBuy":0,"rateCross":0.112,"rateSell":0},{"currencyCodeA":710,"currencyCodeB":980,"date":1675175067,"rateBuy":0,"rateCross":2.1798,"rateSell":0},{"currencyCodeA":894,"currencyCodeB":980,"date":1675116006,"rateBuy":0,"rateCross":0.0019,"rateSell":0}]

Here are screenshots of errors when trying IMPORTDATA or IMPORTXML:

It's logical that IMPORTXML doesn't work since it's not xml that target url returns. It's just plain json. But IMPORTDATA wouldn't give me data neither.

Welcome to Web Applications Stack Exchange. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. — doubleunary, Jan 26 at 11:55
I think you're overcomplicating but here is temporary table. docs.google.com/spreadsheets/d/…. You can simply open url api.monobank.ua/bank/currency in chrome and see json there. All I need is data from that json. I don't care in which format - I can parse it from there. I just need to get it with numbers and I want to use build in functionality only. I don't want custom scripts. That's it. All those regex, queries etc. have no point if there's nothing to use them on because import function doesn't work. — Constantine Ketskalo, Jan 29 at 17:06
While links to external resources might be helpful questions on this site should be self-contained. Considering this please add a decription of the linked resource conent and sample data directly into the question body. Also add a brief description of your search efforts for cotent from this site including links to the most relevant posts an a explanation about why those posts didn't help you as is suggested in How to Ask. — Rubén - People First, Jan 30 at 18:54
P.S. If you will post error messages and/or formulas consider to temporaly set Gooogle Sheets to use functions names in English, set the country / region setting to use United States (or any country using a dot a decimal separator, comma as parameter separator) and switch your Google account to use English US so the errors messages are shown in this language and avoid having translation errors. Note: Internally Google Sheets uses English US and America/Los_Angeles timezone if for any reason you don't want to follow this suggestions, you should specify the complete spreadsheet settings. — Rubén - People First, Jan 30 at 18:56

doubleunary · Accepted Answer · 2023-01-31 15:19:49Z

Use importdata() and extract numbers only by removing labels with regexreplace(), like this:

=lambda( 
  data, numRows, numColumns, 
  query( 
    makearray( 
      numRows, numColumns, 
      lambda( 
        row, column, 
        lambda( 
          val,
          ifs( 
            (column = 1) * regexmatch(val, "840"), "USD", 
            (column = 1) * regexmatch(val, "978"), "EUR", 
            (column = 1), "other", 
            true, value(regexreplace(val, "[^\d.]", "")) 
          )
        )( 
          index(data, 1, (row - 1) * numColumns + column) 
        )  
      ) 
    ), 
    "select Col1, Col4, Col6 
     where Col2 = 980 and (Col1 = 'USD' or Col1 = 'EUR') 
     label Col1 'currency code', Col4 'buy', Col6 'sell' 
    ", 0 
  ) 
)(
  importdata("https://api.monobank.ua/bank/currency"), 
  1000, 6 
)

The formula will only extract the rows where currencyCodeA corresponds with USD or EUR. To extract all data, use this:

=lambda( 
  data, numRows, numColumns, 
  query( 
    makearray( 
      numRows, numColumns, 
      lambda( 
        row, column, 
        value( regexreplace( 
          index(data, 1, (row - 1) * numColumns + column), 
          "[^\d.]", "" 
        ) ) 
      ) 
    ), 
    "where Col1 is not null 
     label Col1 'currencyCodeA', Col2 'currencyCodeB', Col3 'date', 
           Col4 'rateBuy', Col5 'rateCross', Col6 'rateSell'  
    ", 0 
  ) 
)(
  importdata("https://api.monobank.ua/bank/currency"), 
  1000, 6 
)

In the event your spreadsheet locale uses commas , as decimal separators, you will have to use semicolons ; as formula argument separators, like this:

=lambda( 
  data; numRows; numColumns; 
  query( 
    makearray( 
      numRows; numColumns; 
      lambda( 
        row; column; 
        value( regexreplace( 
          index(data; 1; (row - 1) * numColumns + column); 
          "[^\d.]"; "" 
        ) ) 
      ) 
    ); 
    "where Col1 is not null 
     label Col1 'currencyCodeA', Col2 'currencyCodeB', Col3 'date', 
           Col4 'rateBuy', Col5 'rateCross', Col6 'rateSell'  
    "; 0 
  ) 
)(
  importdata("https://api.monobank.ua/bank/currency"); 
  1000; 6 
)

Alternatively, use the ImportJSON() custom function.

Update: At first I thought that It requires 2 more parameters but they are optional. BUt both ways it gives same error: "#REF; Couldn't unfold resulta. Please enter additional columns (384)." (that's rough translation, English error text might be different). — Constantine Ketskalo, Jan 29 at 16:53
The formula given in the answer seems to work fine. Added a note regarding spreadsheet locale. — doubleunary, Jan 30 at 21:08
@BlindSpots, thanks for your comment. I didn't get it at first. I mean neither text of an error nor your comment. Being a software developer I thought that IMPORTDATA formula doesn't work with json and my input caused some error in the code behind that executes this formula and that number is the error code. But it turned out that it's just my spreadsheet physically didn't have enough space to display data and it told me "dude, add 384 columns manually to the spreadsheet and I'll be able to display the data". Just a note in case if someone needs it one day. :) — Constantine Ketskalo, Feb 18 at 0:14

Stack Exchange Network

How can I import json from url using only existing formulas of google sheets (without scripts or apps or whatever else additional there is)?

1 Answer 1

Your Answer

Not the answer you're looking for? Browse other questions tagged
google-sheets
importdata
json
or ask your own question.

Hot Network Questions

How can I import json from url using only existing formulas of google sheets (without scripts or apps or whatever else additional there is)?

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Not the answer you're looking for? Browse other questions tagged google-sheetsimportdatajson or ask your own question.

Related

Hot Network Questions

Not the answer you're looking for? Browse other questions tagged
google-sheets
importdata
json
or ask your own question.