Home

Getting API into Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-114866%22%20slang%3D%22en-US%22%3EGetting%20API%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-114866%22%20slang%3D%22en-US%22%3EI'm%20not%20a%20developer%20so%20please%20don't%20get%20too%20technical.%20I've%20been%20trying%20to%20get%20the%20results%20from%20an%20API%20for%20a%20crypto%20website%20into%20Excel%2C%20but%20not%20all%20in%20one%20cell.%20It%20displays%20on%20the%20web%20like%20a%20table%20which%20is%20what%20I%20want.%20I've%20tried%20the%20external%20query%20feature%20but%20there's%20only%20one%20websites%20results%20that%20seem%20to%20work%20for%20me%20and%20of%20course%20one%20that%20I%20can't%20use.%20The%20one%20I%20want%20to%20use%20only%20returns%20data%2C%20in%20quotes%20with%20commas%20but%20there%20are%20no%20headers%20or%20names%20that%20other%20apps%20use.%20I'm%20having%20a%20hard%20time%20believing%20that%20there's%20not%20a%20relatively%20easy%20way%20to%20get%20the%20data%20into%20Excel%20in%20columns.%20Am%20I%20missing%20something%3F%20Any%20help%20greatly%20appreciated.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-114866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%20and%20importing%20API%20data%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-116875%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20API%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116875%22%20slang%3D%22en-US%22%3E%3CP%3ENot%20very%20hard%20to%20do%20with%20PowerQuery%2C%20see%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-116857%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20API%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-116857%22%20slang%3D%22en-US%22%3E%3CP%3ESure%20thanks%20for%20responding.%20%26nbsp%3B%20Here's%20a%20'snip'%20showing%20the%20data%20and%20site.%20%26nbsp%3B%20I%20think%20my%20question%20was%20poorly%20worded%2C%20but%20I%20just%20want%20to%20get%20data%20like%20this%20into%20excel.%20%26nbsp%3BIt's%20supposed%20to%20be%20a%20JSON%20api%2C%20but%20I%20have%20not%20been%20able%20to%20get%20the%20external%20query%20feature%20to%20work%20with%20this%20or%20similar%20data.%20%26nbsp%3B%20Not%20sure%20what%20else%20to%20try.%20%26nbsp%3BThanks%20in%20advance.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ELarry%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-115353%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20API%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-115353%22%20slang%3D%22en-US%22%3ECan%20you%20share%20what%20you%20have%3F%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-652068%22%20slang%3D%22en-US%22%3ERe%3A%20Getting%20API%20into%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-652068%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F86046%22%20target%3D%22_blank%22%3E%40larry%20rubin%3C%2FA%3E%26nbsp%3Bhope%20it's%20not%20too%20late.%20There%20is%20excel%20inbuilt%20function%20called%20WEBSERVICE()%20to%20get%20api%20data%20into%20excel.%20Hope%20it%20helps%20other%20who%20are%20in%20need.%3C%2FP%3E%3C%2FLINGO-BODY%3E
larry rubin
New Contributor
I'm not a developer so please don't get too technical. I've been trying to get the results from an API for a crypto website into Excel, but not all in one cell. It displays on the web like a table which is what I want. I've tried the external query feature but there's only one websites results that seem to work for me and of course one that I can't use. The one I want to use only returns data, in quotes with commas but there are no headers or names that other apps use. I'm having a hard time believing that there's not a relatively easy way to get the data into Excel in columns. Am I missing something? Any help greatly appreciated.
4 Replies
Can you share what you have?

Sure thanks for responding.   Here's a 'snip' showing the data and site.   I think my question was poorly worded, but I just want to get data like this into excel.  It's supposed to be a JSON api, but I have not been able to get the external query feature to work with this or similar data.   Not sure what else to try.  Thanks in advance.

 

Larry

Not very hard to do with PowerQuery, see attached.

@larry rubin hope it's not too late. There is excel inbuilt function called WEBSERVICE() to get api data into excel. Hope it helps other who are in need.