JSON to Google Sheets

/**
 * Run this once from the editor (select authorize, click Run) and approve
 * access. Formulas in cells can't ask for permission on their own.
 * It only builds a request; nothing is fetched.
 */
function authorize() {
  UrlFetchApp.getRequest('https://example.com');
}

/**
 * Imports JSON from a URL into your sheet as a table: one row per
 * record, nested fields as dot-notation columns (Address.City).
 * From jsonsupport.com.
 *
 * @param {string} url      The URL that returns JSON.
 * @param {string} path     Optional. Where the records are: "data.items".
 * @param {any}    refresh  Optional. Point this at any cell; changing
 *                          that cell re-fetches the data.
 * @return The table, header row first.
 * @customfunction
 */
function IMPORTJSON(url, path, refresh) {
  if (!url) {
    throw new Error('Give IMPORTJSON a URL: =IMPORTJSON("https://...")');
  }

  const response = UrlFetchApp.fetch(url, {
    muteHttpExceptions: true,
    headers: { Accept: 'application/json' },
  });
  const status = response.getResponseCode();
  if (status >= 400) throw new Error('The URL returned HTTP ' + status + '.');

  let data;
  try {
    data = JSON.parse(response.getContentText());
  } catch (e) {
    throw new Error('The URL did not return valid JSON.');
  }

  if (path) {
    for (const key of String(path).split('.')) {
      if (data === null || typeof data !== 'object' || !(key in data)) {
        throw new Error('"' + path + '" was not found in the response.');
      }
      data = data[key];
    }
  } else if (isObject_(data) && !Array.isArray(data)) {
    // No path given: APIs often wrap the records, e.g. {"data": [...]}.
    const lists = Object.keys(data).filter(key =>
      Array.isArray(data[key]) && data[key].some(isObject_));
    if (lists.length) {
      lists.sort((a, b) => data[b].length - data[a].length);
      data = data[lists[0]];
    }
  }

  const records = Array.isArray(data) ? data : [data];
  if (records.length === 0) return [['No records']];

  const flat = records.map(record => flatten_(record, ''));
  const keys = [];
  const seen = {};
  flat.forEach(row => Object.keys(row).forEach(key => {
    if (!seen[key]) { seen[key] = true; keys.push(key); }
  }));

  const header = keys.map(key => key.split('.')
    .map(part => part.charAt(0).toUpperCase() + part.slice(1))
    .join('.'));
  const rows = flat.map(row => keys.map(key =>
    (row[key] === undefined || row[key] === null ? '' : row[key])));
  return [header].concat(rows);
}

function isObject_(value) {
  return value !== null && typeof value === 'object';
}

function flatten_(value, prefix) {
  if (!isObject_(value)) {
    const single = {};
    single[prefix || 'value'] = value;
    return single;
  }
  const out = {};
  Object.keys(value).forEach(key => {
    const flatKey = prefix ? prefix + '.' + key : key;
    const child = value[key];
    if (Array.isArray(child)) {
      if (!child.some(isObject_)) {
        // A simple list (tags, ids) becomes one comma-separated cell.
        out[flatKey] = child
          .map(item => (item === null ? '' : String(item)))
          .join(', ');
      } else {
        child.forEach((item, i) =>
          Object.assign(out, flatten_(item, flatKey + '.' + i)));
      }
    } else if (isObject_(child)) {
      Object.assign(out, flatten_(child, flatKey));
    } else {
      out[flatKey] = child === null ? '' : child;
    }
  });
  return out;
}