Lido's IMPORTJSON formula takes the link to any JSON file and places its contents into the spreadsheet, so you can read and process the data in a powerful, usable editor. Plus, preprocess the data before you even look at it using the JQ argument.

=IMPORTJSON(url, output_cell, [jq_command])

How to use IMPORTJSON

IMPORTJSON only needs two arguments: the URL to your JSON file, and an output cell that specifies the top-left corner of the output. For our example, we will use a JSON file at "https://lido.app/example.json", which we want to output to D5. Our file looks like this:

[
    { "name": "Alice", "email": "[email protected]" },
    { "name": "Bob", "email": "[email protected]" },
    { "name": "Carol", "email": "[email protected]" }
]

For ease of reading, we'll place the link to our file in its own cell, but it could just as easily be written directly into the first argument.

Then, we can write our IMPORTJSON formula, which we'll place in A5. The first argument should be the URL, and the second should be where we want the top-left of our output to go.

Now, we can run the IMPORTJSON Action! Just right-click on the IMPORTJSON cell and click "Run action" to import your JSON data into Lido.

If your data is already in the format you want, then you're done! Otherwise, keep reading, and we'll show you how to preprocess your data into the format best suited for your task.

How to use the JQ argument

JQ is a powerful tool to reformat and query JSON objects. The full documentation can be found on the project's website, but here we'll go over the basics and a few useful commands.

I have a JSON Object

IMPORTJSON can output purely-object JSON data without any preprocessing by printing out the paths to all the leaf values in the object tree in a two-column format. In many cases, this is sufficient to inspect the structure of your data, however if you want to analyze it more automatically, such as with tables, you may want to transform it into something more tabular. IMPORTJSON's JQ argument is perfect for translating a JSON object into a table, or extracting only the data you need.

For this example, we'll use the following JSON file:

{
    "name": "Alice",
    "email": "[email protected]",
    "addresses": [
        "1 Lido Rd.",
        "2 Lido Rd.",
        "3 Lido Rd."
    ]
}

Running IMPORTJSON on this input with no JQ gives this output:

Suppose we just want the name and email values. To select some of the output values, we can just write the new name of the key, followed by the name of the key in our object, preceded by a period. To tell JQ that we want the output to be an object, we also surround it with curly braces. Like this:

`{ Name: .name, Email: .email }`

Then we can provide this string as a third argument to IMPORTJSON, and run the action:

Now suppose we only want Alice's first address in the output. We can index arrays by using square brackets, and just picking the number in the array that we want. Note that arrays are zero-indexed; if we want the first element, we use [0], if we want the second element, we use [1], etc..

`{ Name: .name, Email: .email, Address: .addresses[0] }`

This gives us the output

We can also string these references together as much as we want. Suppose we have a more complicated object, like this:

{
    "name": "Alice",
    "email": "[email protected]",
    "addresses": [
        { "address": "1 Lido Rd.", "colors": ["red", "orange"] },
        { "address": "2 Lido Rd.", "colors": ["green", "teal"] },
        { "address": "3 Lido Rd.", "colors": ["blue", "indigo"] }
    ]
}

If we want the second color associated with each address, we can use a command like this:

`{ Name: .name, Email: .email, ColorOne: .addresses[0].colors[1], ColorTwo: .addresses[1].colors[1], ColorThree: .addresses[2].colors[1] }`

This gives us the output

That's all you need to know to get started working with JSON Objects!

I have an array of JSON data

Data formatted in an array is perfect for analysis inside of a spreadsheet. Unfortunately, the information we want is often hidden deep inside complicated JSON structures. With IMPORTJSON and JQ, we can quickly extract what we want from each array element, formatted in an easy to read, spreadsheet-compatible output.

Suppose we have the following JSON file:

[
    {
        "name": "Alice",
        "email": "[email protected]",
        "addresses": [
            "1 Lido Rd.",
            "2 Lido Rd.",
            "3 Lido Rd."
        ]
    },
    {
        "name": "Bob",
        "email": "[email protected]",
        "addresses": [
            "4 Lido Rd.",
            "5 Lido Rd."
        ]
    },
    {
        "name": "Carol",
        "email": "[email protected]",
        "addresses": [
            "6 Lido Rd."
        ]
    }
]

Running IMPORTJSON with no JQ on this file gives the output

Now we want to pull out the name, email, and first address of each object. Everything we looked at in the last section on objects is applicable here. Now, how can we apply these object operations to every value in an array? The answer is map!

map will simply apply a JQ command to every element of an array. So we can just use the same command as before, but wrap it in a map function. Very similar to Lido formulas!

`map({ Name: .name, Email: .email, Address: .addresses[0] })`

This gives us nicely-formatted tabular output:

Now you're ready to format your JSON data with Lido! Don't forget to check out the JQ Documentation if you want to see more of what this powerful tool can do.