Automation Part 1: INSERT_DATA, OVERWRITE_DATA, and UPDATE_DATA functions

This is part one of our tutorial series on automating actions in dashdash.

Overview

So there’s INSERT_DATA(), OVERWRITE_DATA(), and UDPATE_DATA() but what’s the difference?

Well, let’s say you’ve executed a function and want to display the JSON data in your spreadsheet. Of course, one option is to open the data explorer and parse the different retrieved values, copy, and then paste them into your individual rows and columns. But that task becomes quite manual when you have hundreds of responses.

Thankfully, you can use dashdash’s INSERT_DATA(), OVERWRITE_DATA(), and UPDATE_DATA() functions to automatically retrieve the data from the JSON response and import it into a table.

And the difference between them? Well, it all depends on what you’d like your table to behave in the end:

Do you want your table to… Then use…
Have data be continuously appended to the table, even if it’s a duplicate? INSERT_DATA()
Have the table rewritten every time you get new JSON data? OVERWRITE_DATA()
Have data be appended to the table, but if an entry already exists, updates that entry? UPDATE_DATA()

INSERT_DATA

INSERT_DATA() constantly appends your retrieved data to a table. Something like a running list, the function doesn’t check if an entry already exists. It just keeps adding to the table, which can end up in many duplicates. This method is useful if you want a historical log of the data.

Syntax

INSERT_DATA(data, destination, [auto])

Parameter Mandatory Description
data Yes The JSON object from which to get data.
destination Yes The range of cells where to insert the data.
auto No A TRUE OR FALSE statement that indicates whether to add new headers automatically. By default, this is set to TRUE.

:memo: Note: The auto parameter is used when different searches return different JSON objects. For example, if one returns four data points, and another returns five, you might not want to have empty fields in your table. In that case, set this parameter to FALSE. For more information, check out the Inserting data into pre-existing tables section below.

Example

Let’s say we want to use the SEARCH_COMPANIES_ADVANCED_CRUNCHBASE function to get companies that:

  • Have over 500 employees
  • Are located in Germany
  • Operate in the Internet industry
  • Have had a funding round since 2019

In cell A1, paste in the following the formula:

=SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">500",“Germany”,“Internet”,">2019")

From the JSON response, we know that the data we’d like to have put into our table comes from the organizations object.

Example screenshot

In cell B1, paste in the following PARSE() formula:

=PARSE(A1,"organizations")

And now, in order to get all this information in a table, we can use the INSERT_DATA() function. Paste the following function in cell A2:

=INSERT_DATA(B1,A3:3)

Great! Now, if we execute the function again, we’ll see that the same data is appended to our table, resulting in duplicates.

Duplicates example one

If we change it to >100 employees, and run the INSERT_DATA() function again, you’ll again see that the data is appended to our table, with duplicates.

Duplicates example two

INSERT_DATA() is great if you want a whole log of information. However, if you want to have unique entries every time you execute a function, then there’s OVERWRITE_DATA() and UPDATE_DATA().

OVERWRITE_DATA

OVERWRITE_DATA() constantly recreates your table, writing only the most recent data available according to your query. This is great if you want to constantly tweak the parameters in your data-returning function and have the table reflect only the most relevant information.

Syntax

OVERWRITE_DATA(data, destination, [auto])

Parameter Mandatory Description
data Yes The JSON object from which to get data.
destination Yes The range of cells where to insert the data.
auto No A TRUE OR FALSE statement that indicates whether to add new headers automatically. By default, this is set to TRUE.

:memo: Note: The auto parameter is used when different searches return different JSON objects. For example, if one returns four data points, and another returns five, you might not want to have empty fields in your table. In that case, set this parameter to FALSE. For more information, check out the Inserting data into pre-existing tables section below.

Examples

Let’s say we want to use the SEARCH_COMPANIES_ADVANCED_CRUNCHBASE function to get companies that:

  • Have less than 500 employees
  • Are located in Germany
  • Operate in the Internet industry
  • Have had a funding round since 2019

In cell A1, paste in the following the formula:

=SEARCH_COMPANIES_ADVANCED_CRUNCHBASE("<500",“Germany”,“Internet”,">2019")

Again, from the JSON response, we know that the data we’d like to have put into our table comes from the organizations object. In cell B1, paste in the following PARSE() formula:

=PARSE(A1,"organizations")

And now, in order to get all this information in a table, we can use the OVERWRITE_DATA()function. Paste the following function in cell A2:

=OVERWRITE_DATA(B1,A3:3)

Voila! Now, if we change it to France, and run the OVERWRITE_DATA() function again, you’ll see that you’ll only get companies from France, and the previous results are overwritten.

Overwriting table entries example

UPDATE_DATA

UPDATE_DATA() first checks whether or not an entry already exists in your table. If it does, it updates that entry with any new data. If it doesn’t, it appends the data in a new row. This is great if you want to have an up-to-date database.

:bulb: In order to identify whether or not an entry is unique, the UPDATE_DATA() function uses a keys parameter.

Parameter Mandatory Description
data Yes The JSON object from which to get data.
keys Yes The JSON key or keys used to determine if the data is unique (insert a new row) or not (update an existing entry). For example: "key1" or '["key1", "key2"]'.
destination Yes The range of cells where to insert the data.
auto No A TRUE OR FALSE statement that indicates whether to add new headers automatically. By default, this is set to TRUE.

:memo: Note: The auto parameter is used when different searches return different JSON objects. For example, if one returns four data points, and another returns five, you might not want to have empty fields in your table. In that case, set this parameter to FALSE. For more information, check out the Inserting data into pre-existing tables section below.

Example

Let’s say we want to use the SEARCH_COMPANIES_ADVANCED_CRUNCHBASE function to get companies that:

  • Have more than 500 employees
  • Are located in France
  • Operate in the Internet industry
  • Have had a funding round since 2019

In cell A1, paste in the following the formula:

=SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">500","France","Internet",">2019")

From the JSON response, we know that the data we’d like to have put into our table comes from the organizations object. In cell B1, paste in the following PARSE() formula:

=PARSE(A1,"organizations")

Now we’ll need to choose a unique identifier for each entry to use in our keys parameter. If we look at the JSON response, we can see that the combination of domain and foundedOn would ensure that each of our entries is unique.

Unique identifiers example

And now, in order to get all this information into a table, we can use the UPDATE_DATA()function. Paste the following function in cell A2:

=UPDATE_DATA(B1,'["domain", "foundedOn"]',A3:3)

Brilliant! Let’s change it now so that the country is Spain and execute the query again. We’ll see that the table will be updated with companies located in Spain. Then if we change it back to France, there shouldn’t be any change as those entries already exist in our table.

Updating search query example

Specifying fewer cells than data points

If in the destination parameter you specify fewer cells than the number of possible data points, you’ll only get data for that number of cells. In our examples, each organizations has seven data points. If our data functions had the following:

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

Then we’d only get the name, domain, and foundedOn` data points. Why these three? By default, the tables are filled in the order that the data points are listed. If you’d like to know how to change this, check out Specify the column order below.

If you’d like to get all the data points, then you can just specify the entire row as the destination.

Specifying a row as a destination

If you want to receive all the possible data points, just define the starting column as well as the cell, and then just the row number (as in all the examples above):

=INSERT_DATA(PARSE(A1,“organizations”),A3:3)

Specify the column order

By default, the columns are shown in the same order as the data points in the retrieved JSON. But you can specify the order in which you want the columns to appear. You’d just need to alter the JsonPath in the PARSE() function to retrieve the JSON in the order you’d like the columns to appear.

For example, to have the table created with the following header order: domain, foundedOn, country, you’d just need to alter your PARSE() function like so:

=PARSE(A1,"[‘organizations’][*].[‘domain’, ‘foundedOn’, 'country']")

Inserting data into pre-existing tables

You can use INSERT_DATA(), OVERWRITE_DATA(), and UPDATE_DATA() with existing tables as well. Just specify the existing header row in the function.

:memo: Note: The headers in your table need to exactly match the data points in the JSON object.

For example, if we have a pre-existing table like in the example below, and want to add data to it:
pre-existing-table-example-one

Then all we need to do is use our chosen data function with the auto parameter set to FALSE:

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

Prexisting table example

pre-existing-table-example-two

If we don’t set the auto parameter to FALSE, then the rest of the headers will be added to the table.

Prexisting table with added headers example

Challenge

Armed with the knowledge you’ve got from this tutorial, try the following challenges:

Challenge 1

Try to combine the Crunchbase, PARSE(), and UPDATE_DATA() functions all in one cell.
:mailbox_with_mail: Answer:

=UPDATE_DATA(PARSE(SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">500","Germany","Internet",">2019"),"organizations"),'["domain", "foundedOn"]',A3:3)


Challenge 2

Using the same formula from before, change the column order to be domain, country, foundedOn, and fundingTotalUsd.
:mailbox_with_mail: Answer:

=UPDATE_DATA(PARSE(SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">500","Germany","Internet",">2019"),"['organizations'][*].['domain', 'country', 'foundedOn', 'fundingTotalUsd']"),'["domain", "foundedOn"]',A3:3)


Challenge 3

Use UPDATE_DATA to update a previously created table that only has the following headers:

  • foundedOn
  • domain
  • fundingTotalUsd
  • city
    :mailbox_with_mail: Answer:

=UPDATE_DATA(PARSE(SEARCH_COMPANIES_ADVANCED_CRUNCHBASE(">500","Germany","Internet",">2019"),"organizations"),'["domain", "foundedOn"]',A3:3,false)


Summary

Not so hard now, is it :wink:! You’ve learned how to get data from a JSON response into three different types of tables, how to manipulate the order of the columns, and how to insert data into a pre-existing table. Not too shabby!

In part two of the series, we’ll learn how to automate these functions with REFRESH(), REPEAT(), and SCHEDULE. See you soon!

3 Likes