Parse JSON Google Apps Script

PARSEJSON.gs

/**
 * Fetches JSON from a URL and returns it as a table, with optional child array expansion.
 *
 * @param {string} url URL returning JSON.
 * @param {string=} path Dot path to target the base array, e.g. "planets" or "" if root is the array.
 * @param {string=} expand Relational child array property to expand, e.g. "Inventory" or "WorkforceConsumption".
 * @param {Array<Array>=} headerRange Optional 2-column range containing keys and values.
 * @return {Array}
 * @customfunction
 */
function PARSEJSON(url, path = '', expand = '', headerRange = null) {
  const options = {};
  
  // Parse headers from a 2-column spreadsheet range if provided
  if (headerRange && Array.isArray(headerRange)) {
    options.headers = {};
    headerRange.forEach(row => {
      const key = row[0];
      const value = row[1];
      if (key !== undefined && key !== '' && value !== undefined && value !== '') {
        options.headers[key.toString().trim()] = value.toString().trim();
      }
    });
  }

  const response = UrlFetchApp.fetch(url, options);
  const json = JSON.parse(response.getContentText());

  // Format bracket syntax (e.g. [0]) to standard dot notation paths
  const cleanPath = path ? path.replace(/\[(\d+)\]/g, '.$1').replace(/^\.|\.$/g, '') : '';
  const cleanExpand = expand ? expand.replace(/\[(\d+)\]/g, '.$1').replace(/^\.|\.$/g, '') : '';

  // Extract base data array using the path parameter
  const baseData = cleanPath
    ? cleanPath.split('.').reduce((obj, key) => obj?.[key], json)
    : json;

  const parentRows = Array.isArray(baseData) ? baseData : [baseData];
  let finalRecords = [];

  // If expand parameter is specified, execute the cross-join row explosion
  if (cleanExpand) {
    parentRows.forEach(parent => {
      if (!parent || typeof parent !== 'object') {
        finalRecords.push(parent);
        return;
      }

      // Resolve the nested child array inside this specific parent row
      const childData = cleanExpand.split('.').reduce((obj, key) => obj?.[key], parent);
      const childArray = Array.isArray(childData) ? childData : (childData ? [childData] : []);

      if (childArray.length === 0) {
        // Keep the parent row even if its target child array is empty
        finalRecords.push(parent);
      } else {
        // Duplicate parent attributes for each child item
        childArray.forEach(childItem => {
          const baseParentCopy = { ...parent };
          
          // Remove all heavy child arrays from the root parent copy to clean up columns
          Object.keys(baseParentCopy).forEach(k => {
            if (Array.isArray(baseParentCopy[k])) {
              delete baseParentCopy[k];
            }
          });

          let integratedRecord;
          if (childItem && typeof childItem !== 'object') {
            // Fallback for primitive arrays (strings/numbers)
            integratedRecord = { ...baseParentCopy, [cleanExpand]: childItem };
          } else if (childItem) {
            // Flatten child object properties inline, prefixed with the array's name
            const prefixedChild = {};
            Object.entries(childItem).forEach(([k, v]) => {
              prefixedChild[`${cleanExpand}.${k}`] = v;
            });
            integratedRecord = { ...baseParentCopy, ...prefixedChild };
          } else {
            integratedRecord = baseParentCopy;
          }
          finalRecords.push(integratedRecord);
        });
      }
    });
  } else {
    finalRecords = parentRows;
  }

  // Flatten any remaining nested properties down to flat dot notation keys
  const flatRows = finalRecords.map(row =>
    row && typeof row === 'object' ? flattenObject(row) : { value: row }
  );

  const headers = [...new Set(flatRows.flatMap(row => Object.keys(row)))];

  return [
    headers,
    ...flatRows.map(row => headers.map(header => row[header] ?? ''))
  ];
}

/**
 * Helper function to flatten nested objects into dot-notated keys.
 * Safely stringifies complex nested arrays to prevent "[object Object]" errors.
 */
function flattenObject(obj, prefix = '') {
  const out = {};
  
  Object.entries(obj).forEach(([key, value]) => {
    const path = prefix ? `${prefix}.${key}` : key;
    
    if (value && typeof value === 'object' && !Array.isArray(value)) {
      // Recursively flatten nested sub-objects
      Object.assign(out, flattenObject(value, path));
    } else if (Array.isArray(value)) {
      // Check if the array contains objects inside it
      const hasObjects = value.some(item => item && typeof item === 'object');
      
      if (hasObjects) {
        // Fall back to clean JSON string representation if complex types are found
        out[path] = JSON.stringify(value);
      } else {
        // Clean comma separation for flat primitive arrays (e.g. ["Tag1", "Tag2"])
        out[path] = value.join(', ');
      }
    } else {
      out[path] = value;
    }
  });
  
  return out;
}