How to Use INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA in dashdash

You’ve executed a function and want to display the JSON data in the spreadsheet. Of course, one option is to open the data explorer and parse the different retrieved values in cells.

However, when retrieving larger sets of data, this might be almost impossible. Imagine having an array of a hundred items, each with ten data points, and having to parse all one thousand values one by one.

That’s why dashdash offers three functions that allow you to display JSON data in table form easily. These functions are INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA.

In this post, we will discuss how each function operates and give you some tips and tricks that will help you make the most out of them.

INSERT_DATA

(data, destination, [auto])

INSERT_DATA adds the retrieved data to a table. It will keep inserting the data with every function execution, creating a data log. For example, if we want to retrieve a list of companies in New York with over 1,000 employees in the internet industry that had a funding round since 2019.

  1. We’ll use the SEARCH_COMPANIES_ADVANCED_CRUNCHBASE function to retrieve the list of the companies.
  2. We’ll use INSERT_DATA to display the companies in the spreadsheet.
    • For the data parameter, we need to define where the list of companies is located, which is the item called organizations in the data object A1.
      PARSE(A1, “organizations”)
    • The destination parameter is where we want the table to be displayed. We’ll be displaying the table at A3:G3.

A1: =SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">1000",“New York”,“Internet”,">2019")
A2: =INSERT_DATA(PARSE(A1,“organizations”),A3:G3)

The data will be displayed as expected. If we want to retrieve more companies, we can make the search criteria broader by changing the employees parameter in A1 to “>500” instead of “>1000”. By executing the function, the INSERT_DATA function will automatically execute and insert the new list under the pre-existing list.

You will notice that we now have duplicate entries from the first and second executions because, as previously mentioned, INSERT_DATA inserts all the retrieved data with every function execution. If we don’t want to have duplicates in our table, we’ll need to use either OVERWRITE_DATA or UPDATE_DATA.

OVERWRITE_DATA

(data, destination, [auto])

OVERWRITE_DATA overwrites any pre-existing table data, creating a new table with every new function execution.
If we repeat the previous example with OVERWRITE_DATA instead of INSERT_DATA.

A1: =SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">1000",“New York”,“Internet”,">2019")
A2: =OVERWRITE_DATA(PARSE(A1,“organizations”),A3:G3)

The data will be displayed normally, the same way as INSERT_DATA. If we change the employees parameter in A1 to “>500” and execute the function, the table will be updated.

You will notice that this time there are no duplicates since the table was completely overwritten with the data retrieved from the second execution. Also, notice that the three companies retrieved by the first execution didn’t remain in their original rows as they were inserted once again in the order returned by the Crunchbase function.

Assume that we want to retrieve the companies with the same criteria that are located in San Diego as well. By changing the location parameter in A1 from “New York” to “San Diego” and executing the function, the table will be updated as follows.

As you can see, the table data was overwritten once again, and only the data retrieved from the most recent function execution is shown. But what if we want to keep adding the newly retrieved companies to the same table while avoiding duplicates? That’s where UPDATE_DATA comes in.

UPDATE_DATA

(data, keys, destination, [auto])

UPDATE_DATA takes an extra keys parameter, which are usually unique identifiers. It checks the defined key(s) to check if the retrieved data items are unique (don’t match the keys) or not.

  • If an item is unique, it’s inserted normally in the table in a new row, acting like INSERT_DATA.
  • If an item is not unique, it doesn’t insert it in a new row and only updates its other data points, if any changes occurred to them.

We’ll be repeating the same example with UPDATE_DATA instead of INSERT_DATA or OVERWRITE_DATA. This time, we’ll need to define the keys parameter as well, which we’ll set to “domain”.

A1: =SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">1000",“New York”,“Internet”,">2019")
A2: =UPDATE_DATA(PARSE(A1,“organizations”),“domain”,A3:G3)

The first execution will display the data normally as an INSERT_DATA or OVERWRITE_DATA would. By changing the employees parameter in A1 to “>500” and executing the function, the table will be updated as follows.

The table might seem similar to the one returned by OVERWRITE_DATA. However, the way it was displayed is different. First, the function checks the defined keys, which in this case is the company domain, to determine whether to insert the company or not.

  • If the company domain doesn’t match any previously inserted ones, it inserts it in a new row.
  • If the company domain matches any pre-existing rows, it doesn’t insert it in a new row, and it only updates its other values, if any changes occurred. For example, if a previously retrieved company was retrieved again, and its funding total changed, it would be updated.

That’s why you will notice that the previously retrieved companies remained at the top rows. Then, the new entries were inserted below.

Similarly, if we change the location parameter in A1 to from “New York” to “San Diego”, the new company will only be added to the table without affecting any of the previously retrieved companies.

You can also specify multiple keys if you want the function to check multiple values to determine whether the data is unique or not. For example, by checking both the company domain and name.

=UPDATE_DATA(PARSE(A1,“organizations”),’[“domain”,“name”]’,A3:G3)

Tips and Tricks

Parsing the Data

You might have already been wondering why we’ve been passing PARSE(A1, “organizations”) as the data parameter and not just A1. That’s because when displaying a list of items, you have to pass the specific part of the JSON object that you want to display.

In the previous examples, the Crunchbase function returns the companies inside an item called organizations. So we’re using the PARSE function to extract the “organizations” part from the JSON object in A1, which is then passed to INSERT_DATA, OVERWRITE_DATA, or UPDATE_DATA to display it.

An easy way to parse the data without having to worry about the formula is to parse it through the Data Explorer.

  1. Click on the icon next to the {companies} object in A1.
  2. Click on “All data”
  3. Navigate the data to find the part with the items that you want to display.
  4. Click on the “Copy” button next to the item you want to parse to copy it to the clipboard.
  5. Paste it in a cell to automatically generate the needed PARSE formula.

You can then copy the generated PARSE formula (without the equals sign) and paste it in the data parameter for INSERT_DATA, OVERWRITE_DATA, or UPDATE_DATA.

A2: =PARSE(A1,"[‘organizations’]")

Parsing Selected Items

You can use JsonPath to filter the list of items so that you can only display specific ones. For example, if you want to skip the first item, only insert the last two items, only insert companies that have “GmbH” in their name, etc.

To do so, you’ll need to add the filtering criteria in square brackets at the end of the PARSE formula retrieved in the last section. Here are some examples to get you started.

JsonPath Result
organizations[*] All companies
organizations[2] The third company
organizations[-2] The second to last company
organizations[0,1] The first two companies
organizations[:2] All companies from index 0 (inclusive) until index 2 (exclusive)
organizations[1:2] All companies from index 1 (inclusive) until index 2 (exclusive)
organizations[-2:] Last two companies
organizations[2:] Company number two from tail
organizations[?(@.fundingTotalUsd] All companies with a funding total value
organizations[?(@.fundingTotalUsd > 10000000)] All companies with a funding total value more than $10 million
organizations[?(@.name =~ /.*GOOGLE/i)] All companies with Google in their name (ignore case)

For example, to insert the last company you would enter the following.

=INSERT_DATA(PARSE(A1,“organizations[-1]”),A3:G3)

Parsing Selected Values

To only display specific data points and their values, we’ll once again be using JsonPath to do so. All you have to do is retrieve the PARSE formula and use the method from the last section to indicate the items you want to select (e.g. [*] for all items). Then, you need to add in square brackets a list of the values you wish to display, each one between single quotes and separated by commas.

For example, to display only the names and domains of the companies, we’ll be using this formula.

=PARSE(A1,"[‘organizations’][*][‘name’,’domain’]")

By adding it to the data parameter of INSERT_DATA, it would look like this.

=INSERT_DATA(PARSE(A1,"[‘organizations’][*][‘name’,’domain’]"),A3:B3)

Specifying the Columns’ Order

By default, the columns are shown in the same order as the data points in the retrieved JSON. However, you can specify the order in which the columns are shown by using the same method shown in the previous section. All you have to do is list down the data points you wish to display in the order you want.

For example, to display the company domain first followed by the company name, you would need to change their order.

=INSERT_DATA(PARSE(A1,"[‘organizations’][*][‘domain’,‘name’]"),A3:B3)

Specifying a Row as a Destination

So far, for the destination parameter, we’ve been defining the exact number of cells to input the table at depending on the number of columns we’re inserting. However, if you want to display all the data points and we don’t know, or we just don’t want to bother checking how many there are, we can just select the row to insert the table at.

To do so, we will need to define the cell where the table starts (e.g., B3) then add the same row number without the column (:3).
Adding it to the previous example, it would look like this.

=INSERT_DATA(PARSE(A1,"[‘organizations’][*][‘domain’,‘name’]"),B3:3)

Specifying Fewer Cells

If you specify a number of cells that is less than the existing number of data points as the destination, the functions will only retrieve a number of data points equal to the number of specified cells. The selected data points are based on the order retrieved by the function unless specified otherwise in the PARSE formula.

For example, if we select A3:C3 as the destination, we would only get the first three data points, which are company name, domain, and founding date.

=INSERT_DATA(PARSE(A1,”organizations”),A3:C3)

Inserting to a Pre-Existing Table

You don’t need to always create your tables from scratch using INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA. You can use them to add data to a pre-existing table by selecting the table header as the destination. You can also have multiple different functions inserting data to the same table. However, the headers need to match the names of the data points.

For example, if we have a pre-existing table with the headers “name”, “category”, and “domain” in A3:C3 and we use it as the destination.

=INSERT_DATA(PARSE(A1,"[‘organizations’]"),A3:C3)

You will notice that the function inserted the data under “name” and “domain” but not “category” because it couldn’t find a matching data point.

The “auto” Parameter

The auto parameter is an optional boolean parameter that allows you to determine whether missing headers should be automatically inserted or not. By default, it’s set to true.

If we were to use the previous example but change the destination from A3:C3 to A3:3 to select the row.

=INSERT_DATA(PARSE(A1,"[‘organizations’]"),A3:3)

You’ll notice that the function matches any pre-existing columns and, by default, creates new ones for any missing ones.

However, if we don’t want the function to create new headers for missing data points automatically, we can do so by setting the auto parameter to false.

=INSERT_DATA(PARSE(A1,"[‘organizations’]"),A3:3,false)

Recommended Tables

For some functions, dashdash offers recommended tables that allow you to create the tables and display the data with just a few clicks. All you need to do is.

  1. Select the desired table.

  1. Select the function you want to use:
    • UPDATE + INSERT: To use UPDATE_DATA.
    • INSERT: To use INSERT_DATA.
    • OVERWRITE: To use OVERWRITE_DATA.

  1. Select where you want to insert the table and press Enter.

This will automatically create the necessary function for you and display the data accordingly.

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