New Excel Function ImportJson()

[bitv...@gmail.com]

Hello, I know there are tons of guides of how to import json into excel and this may be a little bit spoon feedish but I've spent hours scratching my head as far as these VBA scripts go. So what I'm trying to do is import json from the Api on multiple websites and have it fetch updated data automatically.


I would really like to be able to just use a function inside Excel similar to this with google spreadsheets.


https://docs.google.com/spreadsheets/d/1NSdHqIueq6Ie1zU0atK4hHvqqqoxu7lW7eTMTRx6--4/edit#gid=0

The above allows me to just say =importjson(https://btc-e.com/api/2/btc_usd/ticker) and it would dump the entire api.


Any help would be greatly appreciated.

[bruce]

Excel doesn't work the same way as google spreadsheets. You can't update cells other than the one the formula is on from a custom formula. Its the same thing in spreadsheets, but the crucial difference with sheets is that you don't need to know in advance the size and shape of arrayformula results, whereas in excel you do. 

The custom function to do this itself is very straightforward, using the libraries in cDataSet.xlsm - here's an example of what it would look like, along with calling it below. 

.. example of importinging

Private Sub testImportRestAny()

    importRestAny "https://btc-e.com/api/2/btc_usd/ticker", "Sheet2"

End Sub

In theory you could amend the below to return an array of results, and invoke it like an array formula. However, I recommend that you create a table of queries you want to do along with places to put the results, and execute then as below via a button on the page

.. import function

Public Function importRestAny(url As String, workSheetName As String) As Long

    Dim cr As cRest

    ' will clear a sheet and do a rest query

    wholeSheet(workSheetName).Cells.clear

    

    ' get that data and deduce the headings

    Set cr = restQuery(workSheetName, , , , url, , , False, , , True, , , , , , , False)

    makeHeadingsFromData cr

    

    ' populate, avoiding doing the query again

    With restQuery(workSheetName, , , , url, , , , , , True, cr.jObject.jString, , , , , , False)

        ' return the number of rows populated

        importRestAny = .dset.rows.count

        .tearDown

    End With

    

    cr.tearDown

End Function

[ivan...@gmail.com]

Use Power Query!