There are some shorter ways to do the same by using the SheetJS utils functions to convert directly the sheet content to another format. Here is a codepen where you can test this method. If you want to use this import statement in an html page you'll need to do it inside those scripts tags. If you want more informations you could check the SheetJS documentation and there is a more detailed example here : How to read an excel file contents on client side? Like this 'A1:J215' from it you could get the amount of lines or the char of the last column. '!ref' is more interesting as it contains the plage of cells containing data wich is a string '!margin' refers to cells margins so it may not represent any interest. JSON data is not transforming into proper table. But after getting data in excel, trying to transform that JSON data into Table by using power query. This array also have two more entries with those keys '!margin' and '!ref': Excel can Convert JSON data to Table in Excel Hi, Actually i am getting data from external source through API by using 'From Web' option in excel & getting JSON data.

The content of a sheet accessed this way is a single dimension object array wich contains all the cells of the sheet starting from the first cell of the header to the last cell wich contains data.Įach of those cells has a key like this 'A1', 'B250', 'J3'

Excel parse json