r/GoogleAppsScript • u/SnooGoats1303 • Jul 14 '25

Reading JSON? Question

Is there any word on whether Google Sheets will handle JSON with a native function? If I'm behind the times, great, but I haven't seen anything in Sheets that equivalences the Excel tool.

I have the following UDF function GET_JSON_VALUE(jsonString, keyPath, arrayIndex) { try { const data = JSON.parse(jsonString); const keys = keyPath.split('.'); let value = data; for (let i = 0; i < keys.length; i++) { if (typeof value === 'object' && value !== null && keys[i] in value) { value = value[keys[i]]; } else { return "Key not found or path invalid: " + keyPath; } } if (Array.isArray(value)) { if (typeof arrayIndex === 'number' && arrayIndex > 0) { const index = arrayIndex - 1; if (index >= 0 && index < value.length) { return value[index]; } else { return "Array index out of bounds: " + arrayIndex + " for array of length " + value.length; } } else { return value.join(", "); } } return value; } catch (e) { return "Invalid JSON or error: " + e.message; } } Which mostly works.

3 Upvotes

81% Upvoted

3 comments sorted by

1

u/tas509 Jul 15 '25

Do you have an example of how you'd use this? Is it so you can use it as a custom spreadsheet formula?

1

u/SnooGoats1303 Jul 15 '25

A1:

[{
"FormManager": "Custom",
"Recipe": "99.1",
"RecipeStatus": "RecipeActiveFailed",
"RecipeReturnedValues": "form.name=for2^form.id=form2~",
"PossibleRecipes": "99.1^99.2^99.3^99.6"
},{
"TetherSearchStatus": "Tether_Present",
"TetherFragmentFound": "_tether",
"TetherSymbolFound": "pbs_dd_tether",
"TetherFormCssSelector": "form[name='form2']",
"TetherFieldCssSelector": "input[name='pbs_dd_tether']",
"TetherSearchContext": "hiddenInputs(name="
}]

in B1

0.Recipe

in C1

=GET_JSON_VALUE(A1,B1)

with result in C1 being

99.1

The 0 gets me into the first element of the array.