This is still a work in progress, but I’ve been eating my own dog food and liking it: Win Inet for Excel. Let me know if you find something that breaks and open an issue on GitHub so I can fix it.

This add-in lets you grab a URL and put the characters returned in memory. Parse them as XML or HTML using the battle-tested libxml2 library used by etree in Python. Extract anything you want by manually traversing nodes or use XPath to zero in on exactly what you are after. It also has functions for parsing CSV and JSON into an Excel range.

JSON maps quite neatly to Excel. A JSON object is a two row range with keys in the first row and values in the second. A JSON array is a one row range of values. JSON primitive types number, string, boolean, and null correspond to Excel types xltypeNum, xltypeStr, xltypeBool, and xltypeErr with val.err = xlerrNull. Excel needs a little help to play nice with ranges that are recursive. You can use HLOOKUP to return a primitive type using the key and returning the second row but if the value is an object or array you will get a #VALUE!. Use JSON.VALUE(val, key) instead. The key can be an array of keys to recurse through or a jq-like dotted index. JSON arrays are indexed by 0-based numbers. Unlike jq, they should not be enclosed in square brackets.