How do I import JSON from a URL in Google Sheets?

I am trying to import JSON data from a public API into Google Sheets. (For the sake of an example, I'll be using data from Json Placeholder)

{"id":1,"name":"Leanne Graham","username":"Bret","email":"Sincere@april.biz","address":{"street":"Kulas Light","suite":"Apt. 556","city":"Gwenborough","zipcode":"92998-3874","geo":{"lat":"-37.3159","lng":"81.1496"}},"phone":"1-770-736-8031 x56442","website":"hildegard.org","company":{"name":"Romaguera-Crona","catchPhrase":"Multi-layered client-server neural-net","bs":"harness real-time e-markets"}}

I first tried to pull in the JSON data from the URL using IMPORTDATA, but this removes commas (treated as delimiter characters) and the quotation marks that surround the attribute names.

=IMPORTDATA("https://jsonplaceholder.typicode.com/users/1")

I tried joining each part and readding the commas with TEXTJOIN. However, excess commas get added to the data, seemingly after curly brackets in the original JSON.

=TEXTJOIN(",", TRUE, IMPORTDATA("https://jsonplaceholder.typicode.com/users/1"))

My current workaround is to specify an dummy character (^) for IMPORTDATA's delimiter.

=IMPORTDATA("https://jsonplaceholder.typicode.com/users/1", "^")

However, this approach will fail if that placeholder delimiter ever appears in the JSON data. It also doesn't account for quotation marks getting removed from the attribute names. My end goal is to fetch specific attributes from the JSON, (for example, the name field), but I need to get the raw JSON into Google Sheets before I can start parsing it.

Is there a way I can import the JSON data directly from the URL into Google Sheets?

Create a custom function with Google Apps Script to fetch the data from a URL.

/**
 * Fetches data from a url.
 * @param url Url with data
 * @customfunction
 */
function FetchURLContents(url) {
    return UrlFetchApp.fetch(url).getContentText();
}

To add the custom function:

• Open your Google Sheet
• Select Extensions > App Script
• Overwrite the contents of Code.gs with the code above
• Click the save icon
• Return to your Google Doc
• Call the function with =FetchURLContents(your_url_here)

Example:

=FetchURLContents("https://jsonplaceholder.typicode.com/users/1")

Note that a single cell in Google Sheets is limited to 50,000 characters. If you only want to fetch a specific part of the JSON, you can work around this by narrowing down the specific part of the JSON you want within the same cell formula.

To fetch a specific JSON property from a URL, you can use the following function:

/**
 * Fetches JSON data from a url. Optionally filterable by property.
 * @param url Url with data
 * @param propertyPath (optional) The property you want to retrieve. Nested properties should be specified as 'parentProperty.childProperty'.
 * @customfunction
 */
function GetJsonFromURL(url, propertyPath = "") {
  const response = UrlFetchApp.fetch(url);
  const jsonData = JSON.parse(response.getContentText());
  const pathParts = propertyPath.split('.');
  let currentJson = jsonData;
  console.log(`json data ${jsonData}`)

  // Filter by propertyPath if provided
  if (propertyPath)  {
    for (let property of pathParts) {
      if (currentJson && property in currentJson) {
        currentJson = currentJson[property];
      } else {
        throw new Error(`Property ${propertyPath} not found`);
      }
    }
  }
  return currentJson;
}

Example:

=GetJsonFromURL("https://jsonplaceholder.typicode.com/users/1", "address.street")

_{Note: You may want to keep URL fetching split into its own function to handle things like HTTP Status error codes and connection issues.}