How to Handle JSON Data with Repeated and Record Fields in Google Sheets

Google Sheets does not natively support repeated fields or record fields that often appear in structured data such as BigQuery exports. As a result, when data containing these fields is imported into Google Sheets, it will be presented in JSON format.

This article explains how to handle and manipulate this JSON data, including flattening it for easier use in Google Sheets and processing it using Google Sheets functions like SPLIT and REGEXEXTRACT.

Why Do You See JSON in Your Google Sheets?

The JSON format appears in your Google Sheets because the imported data includes fields with multiple values (repeated fields) or nested structures (record fields). Google Sheets cannot fully process these fields as individual columns, representing them as raw JSON text.

Example: Repeated Fields

  {
    "id": 1,
    "tags": ["marketing", "sales", "analytics"]
  }

When this data is imported into Google Sheets, the field tags will appear as a JSON array in the corresponding cell.

Example: Record Fields

  {
    "id": 2,
    "customer": {
      "name": "John Doe",
      "email": "johndoe@example.com"
    }
  }

This record will appear as a nested JSON object in Google Sheets.

How to Work with JSON Data in Google Sheets

To handle this data effectively in Google Sheets, you can take two approaches: flattening the data or using Google Sheets functions to process it.

Option 1. Flattening Data in DataMart (SQL Query Approach)

If you want to make the data more manageable before it reaches Google Sheets, you can modify your SQL query in DataMart to flatten the data. Flattening transforms nested data structures into a table format, making them easier to analyze in Google Sheets.

Example SQL for Flattening Data:

  SELECT
    id,
    tag
  FROM
    `project.dataset.table`,
  UNNEST(tags) AS tag

In this example, the repeated field tags is flattened into individual rows, which are easier to work with in Google Sheets.

Option 2. Handling JSON Arrays with Repeated Fields

If the JSON data contains an array of values, like a list of categories or tags, you may want to extract and display these values separately in Google Sheets.

To do this, use the following formula:

=ARRAYFORMULA(TRIM(SUBSTITUTE(TRANSPOSE(SPLIT(REGEXEXTRACT(A1, "\[([^\]]+)\]"), ",")), """", "")))

This formula performs the following steps:

1. REGEXEXTRACT: Extracts the values inside the square brackets [] (i.e., the array).
2. SPLIT: Splits the extracted values by commas ,.
3. TRANSPOSE: Converts the data from a horizontal to a vertical format, displaying each item in a separate row.
4. SUBSTITUTE: Removes the double quotes around each value.
5. TRIM: Removes any leading or trailing spaces from the extracted values.

Example

Given the following JSON data in a cell:

["marketing", "sales", "analytics"]

After applying the formula, the values will be displayed in separate rows without quotes or extra spaces:

marketing
sales
analytics

Handling Errors with Malformed JSON

If the JSON data is not formatted correctly, you may encounter errors when trying to extract and process the data in Google Sheets. A common issue arises when the data is missing the expected structure, such as missing square brackets [] or containing extra commas.

For example, if a cell contains a malformed JSON string like this:

"marketing", "sales", "analytics"

The formula will return an error because it doesn't recognize the data as a valid array. To handle such cases, you can use the IFERROR function to prevent errors and display a fallback value instead.

Example

Here’s how you can modify the formula to handle malformed JSON data gracefully:

=IFERROR(ARRAYFORMULA(TRIM(SUBSTITUTE(TRANSPOSE(SPLIT(REGEXEXTRACT(A1, "\[([^\]]+)\]"), ",")), """", ""))), "Invalid JSON format")

Explanation:

• The IFERROR function checks if the formula results in an error (e.g., when the JSON is malformed).
• If the formula encounters an error, it returns the text "Invalid JSON format".
• If the formula works correctly, it processes the data as usual, removing quotes and extra spaces.

For instance, if the input is:

"marketing", "sales", "analytics"

But if the data is correctly formatted like this:

["marketing", "sales", "analytics"]

The formula will return:

  marketing
  sales
  analytics

Using IFERROR allows you to handle malformed JSON cases more gracefully, providing a fallback message instead of an error.

 

Handling JSON data with repeated and record fields in Google Sheets can be a bit tricky, but with the right approach, you can easily manage and process this data. By flattening nested structures or using Google Sheets functions like SPLIT or REGEXEXTRACT, you can make the data more accessible and easier to work with.

If you need further assistance, feel free to reach out to our Support Team at bi@owox.com.