Import Json data in Excel in a tabular format

[sabha]

Dear Friends,

cricapi.com delivers an api service where they give out data in json format that can be read through ASP/ASP.NET, PHP, CURL PHP, Node.JS & Javascript for which they have given sample source codes on how to retrieve the data. I was trying to figure out how to get the data in google sheets. I tried working with scripts in google sheets to import the data but it doesn't get imported in a proper format. I then contacted support team of cricapi but they are not willing to give support for google sheets and sent me the mail given below:

You'll need to work with someone who has experience with Google Sheets. We can't support you on this I'm afraid.

However the output of CricAPI is standard JSON format so if you have a person who knows how to program Google Sheets they will likely be able to fetch the data you need.


This is why I came here to check if it can be done in Excel using vba/macros/formulas. Please let me know if this can be done in MS Excel or google sheets (preferred).

Here is a link from where the json data can be fetched. (There is a limit to the api key and the link may not open after few hits and therefore I am also attaching output of the link in a text file). I am also attaching an excel file on the desired tabular format. Kindly have a look at it and let me know if there is a way to do it and if there is worth spending time on it. Thanks.
https://cricapi.com/api/fantasySumma...que_id=1152843

PS: I have not captured pid (player ids) in the attached excel file) which I should have done it. Presently I am doing it manually and want to know if this can be automated using MS Excel or preferably in google sheets.

Thanks for looking into it.

[Marc L]

Hi !

If the text file respects JSON format so it can be loaded via VBA (so under Excel) as you can see in some threads of this forum …

[Norie]

You could have a look here https://github.com/VBA-tools/VBA-JSON.

PS If you do use Google sheets then you could use JavaScript to handle the JSON data.

[sabha]

Thank you for your reply @Marc & @Norie. I do not know how to integrate javascript and google sheets. As far as Excel is concerned, I thought there would be a tool or a macro that can read the entire json data and put it in a worksheet automatically in a tabular format. The VBA-JSON tool requires parameters to be passed to get the desired value and I am looking to translate/decode the entire json data in excel tables that can be read properly. There is some kind of disconnect here or I may not be able to understand how to do this. Thanks for the start. I may have to look for some json to excel or json to google sheets converter on google.

[Norie]

Not sure what you mean by 'disconnect'.

That link i posted should help you translate/decode the JSON data you have but it's not going to do it automatically.

Try using this to load in your downloaded files.

Please Login or Register  to view this content.

[sabha]

Thank you for your reply.

By disconnect I meant some kind of confusion (either I am not explaining properly and I am unable to understand the message conveyed to me). I tried the code you sent and added microsoft scripting in references but I errors out at the line Set Parsed = JsonConverter.ParseJson(JsonText). I though there may be some error in my file so I downloaded a simple sample json file from internet but still the same error.

Alternatively based on my search and research, I found a service https://json-csv.com/ which translates the json output in tabular format very neatly but does not match the desired result I mentioned in my attached excel file. May be I will have to experiment on that a bit.

[Marc L]

The web service reflects exactly & only the data present within the JSON file so even if I have time to waste
I can not fill the holes of what you are expecting for non existing data !

[Norie]

What is the error?

Did you download the files/code from the link I posted?

Did you add the JsonConverter module from that link to the file where you tried the code I posted?

[sabha]

I didn't get you ! Didn't understand what exactly you are referring to as non existing data.

The web service data and the notepad file I attached both are same. In fact I copy-pasted data from web service and put it into a notepad file for display purpose on this forum as the link may not work if my daily api limit of hits are exhausted. The excel file I attached is what I am expecting (my desired output after reading json data from that link and its conversion). Presently I fetch the data for every cricket match and manually enter it in Excel in the format I have attached and was wondering if this task can be automated so that a vba or a macro or a google sheet srcipt can read the given url, read and convert it in my desired format. Hope I have explained it properly.

Thanks for taking time to read my question and reply.

[sabha]

What is the error?

Did you download the files/code from the link I posted?

Did you add the JsonConverter module from that link to the file where you tried the code I posted?

May be I am doing something wrong. I am reading your first two posts and the link you referred to once again and try it. Will get back if I run into problems. Thanks

[Marc L]

If I import your json text file attachment, the Excel worksheet result will be the same as the web servie you have found.
No matters if you are able to mod the layout to fit your convenience (at beginner level) …
Or you can think about moving to the commercial services.

For Google sheets, as here it's a Microsoft forum, you should better ask on an appropriate Google forum …

[CK76]

For google sheet, have a read of link.
https://medium.com/@paulgambill/how-...s-a3fede1a014a

[Norie]

Have a look a this link Installation.

[sabha]

Have a look a this link Installation.

Thanks. I understood.

Here is how I did it looking at the tutorial video. It works... and gives me my credits left and if I change that to something else, I get results.

Please Login or Register  to view this content.

The issue is... I do not know how to get the entire data in my desired format. May be I need to talk to someone who knows vba well and get this done.

Thanks for your time

[sabha]

For google sheet, have a read of link.
https://medium.com/@paulgambill/how-...s-a3fede1a014a

Thanks... I tried this but it does not get the entire data.

[sabha]

If I import your json text file attachment, the Excel worksheet result will be the same as the web servie you have found.
No matters if you are able to mod the layout to fit your convenience (at beginner level) …
Or you can think about moving to the commercial services.

For Google sheets, as here it's a Microsoft forum, you should better ask on an appropriate Google forum …

Yes.. the excel result will be same as the text file was made out of the web service output. Yes, you are right ! Me at a beginner level would definitely have issues modding the layout to my convenience. I thought it may not be time consuming and would have been easier getting things done. That is why I posted here.

I also tried the https://json-csv.com/ online service and it converts the whole data properly. May be I need to keep trying to work on that converted data to fit to my layout. Thank you very much for looking into it.

[sabha]

Just a small request. For an example sake, appreciate if someone can help me in drawing a small table like the one attached below using the below api link. All data in the table (picture attached) is available in the below link. The heading of that table is the path where the values are.
https://cricapi.com/api/fantasySumma...que_id=1152843

I will then be able to understand the code/syntax better and complete the rest part of it. Thank you very much in anticipation.

[sabha]

I was looking at power query option in Excel 2016 in the past couple of hours. It looks like it may do the job but I am not getting how to do it. Request some help either on power query or on my last post. Thanks