JSON: using dashdash's Data Explorer, PAIR2JSON, and RANGE2JSON

Overview

So in our last article, we spoke a bit about what JSON is so that we’d all have a common starting point. But now it’s time to move on to some more practical elements (and also to show off just why dashdash is more awesome). In this article, we’ll cover:

  • Using the data explorer to understand just what’s in your JSON.
  • How to build JSON objects using PAIR2JSON
  • How to build arrays of JSON objects using RANGE2JSON

Data Explorer

Whenever you use an integration function, 90% of the time you’ll see a cell like this:

image

The curly brackets and Data Explorer icon Data Icon icon let you know that this cell contains JSON. If you click on the icon, our Data Explorer side panel will pop out, showing you the entire JSON (but without all that distracting punctuation).

You’ll use the Data Explorer in dashdash a lot, so it’s definitely a good idea to understand how to use it. Let’s have a look at the screenshot a bit more closely :microscope: .

Data Explorer: Arrays and Objects

At the beginning, we see that we have a JSON array that has 947 JSON objects (items). How do we know this?

Well, next to data we have square brackets with the number of objects, [947] (in green), and then each item in the array has curly brackets with a number (indicating how many key-value pairs are in that object) , such as {8} (in blue).

So now you know how to easily identify JSON arrays and objects using the Data Explorer! And if you click on the expand arrow next to any object or array, you’ll see all the contents stored there :).

Data Explorer: Getting JSON values

The Data Explorer also lets you easily access any object or value so that you can then just paste it into your spreadsheet. All you need to do is hover over the object or value that you want, click the Copy button, and paste it into your spreadsheet.

Copying objects example

Copying values example

When you paste it into your spreadsheet, in the function bar you’ll see a formula like this:

=PARSE(A1,"[0].['name']")

This is something called JsonPath, and it’s a very handy way of getting all the data you want from a JSON response without needing to copy and paste all the time. However, we’ll give you an in-depth walkthrough on how to use it in a later article (or, if you’re feeling adventurous, you can check out this article we’ve already written - but be warned, it’s not for the faint of heart! :upside_down_face:).

Building JSON objects

So we know that a JSON objects need a key and a value, like this:


{
    "key":"value"
}

But trust us when we say this: you’re going to make typos with all the punctuation while creating these. That’s why dashdash has an awesome function to do that all for you: PAIR2JSON.

What it does is simple: it’ll take whatever you give it, and create a JSON object for you, perfectly formatted.

PAIR2JSON(string1, value1, [string2], [value2], [...], [...])

Parameter Mandatory Description
string1 Yes The key of your first JSON pair.
value1 Yes The value of your first JSON pair.
string2 No The key of your second JSON pair.
value2 No The value of your second JSON pair.
PAIR2JSON example

When will you mostly use PAIR2JSON? Most of the time, it’ll be necessary when you want to create headers for Custom APIs (check out the DadJoke Translation Project to see it in action, or our Airtable API series).

Building JSON arrays

And again, you’ve already seen how to create JSON arrays:


{
   "companies":[ /* The company key has an array of companies */
      {
         "Company":"Apple", /* The first item in the array*/
         "Domain":"apple.com",
         "Address":{
            "street address":"1 Infinite Loop",
            "city":"Cupertino",
            "state":"California",
            "country":"United States"
         }
      }, /* The comma separats the first item from the second one. */
      {
         "Company":"dashdash", 
         "Domain":"dashdash.com",
         "Address":{
            "street address":"Am Märchenbrunnen 5",
            "city":"Berlin",
            "state":"Berlin",
            "country":"Germany"
         }
      }
   ]
}

But once again, and if you remember the teaser example from our previous article in this series, dashdash has an easy way for you to do all of this: RANGE2JSON.

RANGE2JSON(header, data,)

Parameter Mandatory Description
header Yes The header row of your table. These will be the keys of each JSON pair.
data Yes The range below your header row where you have values. For each row we’ll create a separate JSON object, with each cell of that row becoming the value of header row key.
RANGE2JSON example

The keys are the table headers, and the values are each individual cell in a row. In one of the cells we have an array (as you can see in the data explorer side panel).

As you can see, each object in the array automatically has the same keys (the table headers), and it’s just the values that change (the individual cells of a row).

What if I need to send the JSON array as an object?

Say you have one header row and one row with values. You can of course just use PAIR2JSON and individually add all those keys and values. But that’s not we’re about at dashdash - we want to make things simple. So, instead, we’ll use RANGE2JSON, and use some magic.

  1. Create your JSON array using RANGE2JSON.
  2. Use this formula to get rid of the square brackets:

=SUBSTITUTE(SUBSTITUTE(cell_that_has_the_JSON,"[",""),"]","")

  1. Copy and paste the values (Cmd + Shift + V or Ctrl + Shift + V) into another cell.

And there you go: a perfectly formatted JSON object :muscle: .

Array to object example

And what if I want to have an object that has a JSON array as it’s value?

Ah, that couldn’t be easier!

  1. Create your JSON array using RANGE2JSON.
  2. Use PAIR2JSON with the string1 parameter as your key, and value1 as the cell reference to your RANGE2JSON function.
JSON object with array value example

OK, but now, I want that array to actually be a nested object…

Too easy:

  1. Create your JSON array using RANGE2JSON.
  2. Use this formula to get rid of the square brackets:

=SUBSTITUTE(SUBSTITUTE(cell_that_has_the_JSON,"[",""),"]","")

  1. Use PAIR2JSON with the string1 parameter as your key, and value1 as the cell reference to your RANGE2JSON function.
Nested object example

Summary

Well then, that was fun right? You now know how to use dashdash’s Data Explorer, PAIR2JSON, and RANGE2JSON functions, as well as how to modify them! Nice!

Next time, we’ll talk a little bit more about how to use JsonPath (nice and gentle - we promise!).

Till next time, build you amazing folk, build :rocket: !