How to Modify JSON Objects in dashdash

dashdash gives you access to data through APIs. That data is returned in the form of JSON objects. Sometimes you may need to make changes to the retrieved objects for different purposes. For example, changing specific values, key names, or even changing the structure of the object.

We will discuss how you can make those changes to a JSON object. We’ll start by taking a quick look at how JSON objects are structured to understand how we can manipulate them. Then, we’ll see how we can use functions in dashdash to modify them.

JSON Objects

JSON Syntax

JSON objects consist of key/value data pairs. They are written in double quotation marks ", separated by a colon :, and surrounded by curly braces {}. Keys must be strings, and values must be a valid JSON data type. This includes strings, numbers, objects, arrays, booleans, or null. A simple JSON object consisting of one key/value pair would look like this

{“companyName”:”dashdash”}

When the JSON object consists of more than one data pair, each key/value pair is separated by a comma.

{
  “companyName”:”dashdash”,
  ”domain”:”dashdash.com”
}

You can nest a JSON object inside another JSON object by setting it as a value.

{
  “companyName”:”dashdash”,
  ”domain”:”dashdash.com”,
  “offices”:{
    “office1”:”Porto”,
    “office2”:”Berlin”,
  }
}

You can also specify the values of an object property as an array by adding square brackets [].

{
  “companyName”:”dashdash”,
  ”domain”:”dashdash.com”,
  “offices”:[”Porto”,”Berlin”]
}

Similarly, values in an array can also be another array or even another JSON object:

{
  “companyName”:”dashdash”,
  ”domain”:”dashdash.com”,
  “offices”:[
      { “office”:”Porto”, “employees”:[“João”,”Pedro”] },
      { “office”:”Berlin”, “employees”:[“Hady”,”Nadja”] }
    ]
  }
}

JSON Objects in dashdash

When adding a JSON object to a cell in dashdash, it is automatically recognized and shown in the cell as {data}. The same happens when you make a function request, whether using custom dashdash functions or custom API functions like GET, POST, and PUT. In that case, the JSON object is either shown as {data} or other custom labels depending on the function (e.g. {companies}, {people}, {analytics}, etc.).

By clicking on the icon in a {data} cell, the Data Explorer is shown which allows you to easily explore the JSON object and parse different parts of it.

Modifying JSON Objects

Since JSON objects consist of text, we can use any of the text manipulation and classification functions on them (e.g., LEFT, RIGHT, LEN, SEARCH, etc.). One function that we can use to modify different strings or parts of JSON objects is SUBSTITUTE.

SUBSTITUTE

(original_text,search_for,replace_with)

The SUBSTITUTE function allows you to substitute all occurrences of a specified text in a string with new text. The function has three required parameters:

  • original_text: The string to change.
  • search_for: The text to be replaced.
  • replace_with: The new text to replace with.

SUBSTITUTE is case-sensitive and does not support wildcards.

For example, =SUBSTITUTE("212","2","1") returns “111” because the function replaces every instance of “2” with “1”.

Screenshot 2020-04-26 at 22.18.40

Modifying JSON Objects With SUBSTITUTE

Similarly, we can use SUBSTITUTE to modify JSON objects.

Changing Key Names and/or Values

We can use SUBSTITUTE to replace all instances of a key name and/or value with new ones. For example, if we want to change the key “employees” in the previous example to “team_members”, we can add the JSON object to A1 and execute this function.

A2: =SUBSTITUTE(A1,"employees","team_members")

This returns

{
  “companyName”:”dashdash”,
  ”domain”:”dashdash.com”,
  “offices”:[
      { “office”:”Porto”, “team_members”:[“João”,”Pedro”] },
      { “office”:”Berlin”, “team_members”:[“Hady”,”Nadja”] }
    ]
  }
}

Adding New Key/Value Pairs

We can also use SUBSTITUTE to add new key/value pairs. To do this, we need to reference a pre-existing key name and replace it with the new pair. Then, we need to add the replaced key name once again, keeping in mind the proper JSON object syntax discussed earlier.

For example, if we have a JSON object with the company domain.

{”domain”:”dashdash.com”}

And we want to add the company name so that it would look like this.

{“companyName”:”dashdash”,”domain”:”dashdash.com”}

What we need to do is use the SUBSTITUTE function to substitute 'domain' with 'companyName”:”dashdash”,”domain'

A1: {”domain”:”dashdash.com”}
A2: =SUBSTITUTE(A1,"domain",'companyName":"dashdash","domain')

Notice that we’re using single quotes for the “replace_with” parameter so as not to confuse them with the double quotes included in the JSON syntax.

So to add new key/value pairs to a JSON, the general formula is:

=SUBSTITUTE([JSON],"[pre-existing key]",'key1":"value1","pre-existing key')

You can add more than one key/pair value by adding more “key":"value" instances, separated by commas, before the pre-existing key.

=SUBSTITUTE([JSON],"[pre-existing key]",'key1":"value1",”key2”:”value2”,”key3”:”value3”,...,"pre-existing key')

Of course, you can always make it more dynamic by using cell references.

A1: {”domain”:”dashdash.com”}
A2: =SUBSTITUTE(A1,"domain",B1&'":"’&B2&’","domain')
B1: companyName
B2: dashdash

This would return the same result.

{“companyName”:”dashdash”,”domain”:”dashdash.com”}

Examples/Use Cases

While modifying JSON objects can be used for different purposes, the approach of adding new key/pair values is particularly useful when you want to display different parts of a JSON object in one table.

When using dashdash functions like INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA to display JSON data in a table, you can only display key/value pairs that are on the same level. For example, you can’t insert data at the top-level of the JSON object along with data in a nested JSON object simultaneously. One way we can achieve this is by adding different data to the same level to be able to insert it into the spreadsheet at once.

Make sure to check out our forum post on how to use INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA in dashdash for more information about using these functions to display JSON data in a spreadsheet.

Adding Company Names to LinkedIn

We’ll start by building a quick spreadsheet that retrieves people from LinkedIn using the company name and job title.

A1: Company name
A2: Job title
B1: Superhuman
B2: Sales Manager
D1: Get people
E1: =SEARCH_LINKEDIN_PERSON_GOOGLE(B1&" "&B2)

By checking out the JSON object in E1, we’ll notice that the returned items consist of page titles and links. If we execute multiple other searches, it might be harder to keep track of which items or people belong to which search or company.

To solve that, we will use SUBSTITUTE to add the company name to each of these items. That way, we can display all three data properties at once.

D2: Add company
E2: =SUBSTITUTE(E1,"title",'company":"'&B1&'","title')

Now, if we use INSERT_DATA to display the items part of the JSON object in E2, we will get the following table.

D3: Fill table
E3: =INSERT_DATA(PARSE(E2,"items"),A5:C5)

Now, if we execute another search by changing B1 to “Intercom”, we would be able to keep track of the different searches by having the company names in column A.

Combining Funding Rounds With Investors

Another popular use case is combining a list of company investors with the company name and the respective funding round. These three properties are retrieved by Crunchbase in different nested objects.

We’ll start by adding the company name to the funding rounds and exporting them using INSERT_DATA to a new view called “Funding rounds”.

A1: Company name
A2: dashdash
B1: Get company
B2: =GET_COMPANY_DETAIL_CRUNCHBASE(A2)
C1: Add company name
C2: =SUBSTITUTE(B2,"investmentType",'company":"'&A2&'","investmentType')
D1: Insert to funding rounds
D2: =INSERT_DATA(PARSE(C2,"organizations[*].fundingRounds[*]"),'Funding rounds'!$A$1:$G$1)

The INSERT_DATA function will insert the funding rounds to the new view along with the company name. Notice that because “investors” is a nested object inside the “fundingRounds” part, it’s inserted as a JSON object.

In the “Funding rounds” view, we will now add the necessary data to the investors JSON object and export them to the “Investors” sheet. For example, if we wanted to add the company name and investmentType to the investors.

H1: Add info
H2: =SUBSTITUTE(G2,"name",'company":"'&A2&'","investmentType":"'&B2&'","name')
I1: Insert to investors
I2: =INSERT_DATA(H2,'Investors'!$A$1:$E$1)

We now have a list of the investors in the first funding round along with the company name and investment type. To insert the investors in the other round, all we have to do is just copy the cells H2:I2 in the “Funding rounds” view and paste them in H3:I3.

We can also automate this process by using the FILL function to automatically copy those cells and paste them next to any new rows in the Funding rounds view.

J1: =FILL(H2:I2,COUNTA(A2:A)-1)

That way, if we head back to the“Companies” view and add new companies to column A. Then, copied the cells B2:D2 and pasted them next to the new rows, the funding rounds and investors list would be automatically updated.



Get Started Now!

Sign up to dashdash to get started generating lists of qualified leads, automating your marketing dashboards, and integrating with custom APIs. All in a spreadsheet.

2 Likes