Application programming interface (API) related issue:
Is it possible to script JSON calls in a macro?
I want to get a JSON string through an API connection. It looks like the problem is Excel expects the parameters to be passed in the HTML-string, but JSON passes parameters in the HTML body. Any ideas?
Import JSON Data Into Excel 2016 Using a Get & Transform Query Step 1: Open The Data in the Query Editor When clicking “From JSON”, you will be presented with a file browser. Find the file on your disk and click Import.
How to solve this API issue?
- Question: Q: Import Json into Numbers Last year I was guided fromsome awesome ppl here into an AppleScript that helped me paste information from an XML into my Numbers document. Unfortunatly, the xml is no longer pulling new data.
- Import & Export. Import MS Project; Import Excel (or any other spreadsheet application) Import tables from Word documents or Web Pages; Import CSV files; Import Google Calendar; Import Outlook or Calendar(mac) Import/Export JSON; Export to Excel & CSV; Export to image or pdf; Account & login; Ipad, Iphone and Android; Frequently Asked Question.
- Paste the JSON into the content area Pres the 'Validate' button. If the JSON is good, you will see a 'Valid JSON' remark in the Results section below; if not, it will tell you where the problem s lie so that you can fix it/them.
- To import data in Excel, the steps are the same for Rest API that for a physical json file. The only difference is that a local path is used instead of a URL. In order to get JSON data into Excel, you need to repeat the steps to add the ZappySys ODBC Driver in the ODBC Data source, but instead of specifying a URL, we just need to specify the.
Solution no. 1:
Since this is VBA, I’d use COM to call xmlhttprequest but use it in synchronous manner as not to upset VBA’s single threaded execution environment, A sample class that illustrates a post and get request in this manner follows:
So now you can call the above to return you the server’s response:
The problem here is we want to be able to read the data returned from the server in some way, more so than manipulating the JSON string directly. What’s worked for me is using the VBA-JSON (google code export here) COM type Collection to handle JSON arrays and Dictionary to handle members and their declarations, with a parser factory method Parse that basically makes creating these collections of dictionaries much simpler.
So now we can parse the JSON:
into something like the following:
That’s nice but what about stuff like being able to edit and post back the data? Well there’s also a method toString to create a JSON string from the above [Collection/Dictionary] JSON data, assuming the server accepts JSON back.
Solution no. 2:
I wrote a .NET Excel-Addin for this. It’s a generic Excel JSON client that streams any JSON object straight into Excel via http.
Docs and installation instructions can be found here:
http://excel-requests.pathio.com/en/master/
And here’s the GitHub link:
https://github.com/ZoomerAnalytics/excel-requests