Import Json data in Excel in a tabular format

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.