PARSEJSON Apps Script custom function for Google Sheets

fxParseJson.gs

/**
 * 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);
  }));
  
}