Skip to content

Instantly share code, notes, and snippets.

@pfelipm
Last active August 13, 2023 16:12
Show Gist options
  • Save pfelipm/f8a0581e9a038850ad5ae501bb5ff6db to your computer and use it in GitHub Desktop.
Save pfelipm/f8a0581e9a038850ad5ae501bb5ff6db to your computer and use it in GitHub Desktop.
PARSEJSON Apps Script custom function for Google Sheets
/**
* Parses a JSON string and returns a single element designated by its full path.
* Mimics Coda's ParseJSON (https://coda.io/formulas#ParseJSON) formula.
* @param {A2:A3} jsonData Source JSON string or data interval containing JSON strings to parse.
* @param {A5:A6} fullPath Path string or data interval of paths to use for extraction, use ".number" for array elements.
* @param {false} stringify Optional, false (default) if result should be stringified only if it is an object.
* @return Extracted primitive value or stringified representation, if array or object.
*
* @customfunction
*
* Demo: https://docs.google.com/spreadsheets/d/1V_Vz8cBXckzJqtt1Bv_wYeeqrb154AuZb317jX7DY9E
*
* Pablo Felip (@pfelipm) | MIT License
*/
function PARSEJSON(jsonData, fullPath, stringify = false) {
if (
(typeof fullPath != 'string' && !Array.isArray(fullPath)) ||
(typeof jsonData != 'string' && !Array.isArray(jsonData)) ||
typeof stringify != 'boolean'
) throw 'Missing or bad parameters.';
// Helper JSON parser function
function parseJsonString(jsonString, fullPath, stringify) {
if (jsonString && fullPath && typeof jsonString == 'string' && typeof fullPath == 'string') {
const result = fullPath.split('.').reduce((result, segment ) => result?.[segment], JSON.parse(jsonString));
return typeof result == 'object' || stringify
? JSON.stringify(result)
: result;
}
}
// Single JSON value to parse, single path
if (typeof jsonData == 'string' && typeof fullPath == 'string')
return parseJsonString(jsonData, fullPath, stringify);
// Single JSON value to parse, multiple paths
else if (typeof jsonData == 'string' && Array.isArray(fullPath))
return fullPath.map(rowInterval => rowInterval.map(fullPathString => parseJsonString(
jsonData,
fullPathString,
stringify
)));
// Data interval with multiple JSON values to parse
else return jsonData.map((rowInterval, row) => rowInterval.map((jsonString, col) => {
// Single path
if (typeof fullPath == 'string') return parseJsonString(jsonString, fullPath, stringify);
// Different paths for each JSON value
else return parseJsonString(
jsonString,
// Don't parse element if jsonData and fullPath arrays are not of the same size
row >= fullPath.length || col >= fullPath[0].length ? '' : fullPath[row][col],
stringify);
}));
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment