Its complex!
JSON and spreadsheet-like data don't go together very easily because JSONs are inherently nested, which is not easy to represent in a tabular way. Often the keys will appear in a different order each time, which is why you may be getting inconsistent results.
For this reason, I suggest you get familiar with Apps Script because it is a much friendlier way to interact with JSON than via in sheet functions. This way you can easily call values by name because JSON, after all, is JavaScript Object notation, and Apps Script is a type of JavaScript!
Sample Apps Script
To illustrate this, here is a script I just wrote to extract data from the particular JSON you posted. It can work as a function in sheet too. Below is demonstrated how to use it:
/**
* Returns 2D array of values. i.e.
* Value, Raw, Fmt, LongFmt
* sharesShort, 9890638, 9.89M, 9,890,638
*/
function getJsonValue(url) {
// Fetch the JSON
let response = UrlFetchApp.fetch(url);
let text = response.getContentText();
let json = JSON.parse(text);
// Initialize the output array
let output = [];
// Navigate to where the main data is
let stats = json.quoteSummary.result[0].defaultKeyStatistics
// Adding headers to the output
let header = ["Value", "Raw", "Fmt", "LongFmt"]
output.push(header)
// For each key in stats, add:
// Value, Raw, Fmt, LongFmt
for (let attribute in stats) {
// initialize row array
let row = [];
// Add Value to array
row.push(attribute);
// Check if the contents of the attribute is an object, else add blank row
if (typeof(stats[attribute]) == "object" && stats[attribute] != null) {
// if the object contains raw, fmt, longfmt, then add to row, else add null
"raw" in stats[attribute] ? row.push(stats[attribute].raw) : row.push(null)
"fmt" in stats[attribute] ? row.push(stats[attribute].fmt) : row.push(null)
"longFmt" in stats[attribute] ? row.push(stats[attribute].longFmt) : row.push(null)
} else {
row.push(null);
row.push(null);
row.push(null);
}
// Add row to output
output.push(row)
}
// Return 2D array
return output
}
You can use it like this:
Or in code:
function test() {
let file = SpreadsheetApp.getActive();
let sheet = file.getSheetByName("Sheet1");
let output = getJsonValue("https://query2.finance.yahoo.com/v10/finance/quoteSummary/EDIT?modules=defaultKeyStatistics")
let rows = output.length
let cols = output[0].length
let range = sheet.getRange(1,1,rows, cols)
range.setValues(output)
}
This script is mainly for you to adapt yourself, depending on the structure of the JSON that you are using.
References
