Inserting data automatically

Learn how to automatically insert data from JSON or other tables using OVERWRITE(), INSERT(), and UPDATE().

Intro

So, you can get one or two points manually, either by using the Data Explorer or using PARSE(). But what if you want to get all that data into a table automatically? Well, that’s the topic of this lesson, where we’ll:

  • See how to get multiple data points using JsonPath
  • Use our OVERWRITE(), INSERT(), and UPDATE() to automatically create tables

Getting the data we want

Based on the spreadsheet that we previously created, say we want to get all the names, domains, and fundingTotalUsd for the companies at an event. Then we go to our trusty old PARSE() function like so:

kb-int-jsonpath-three-values

Type that function into cell E3. Then, open up the Data Explorer to make sure that you have an array of objects. Each object will have the name, domain, and fundingTotalUsd.

🤓 How about we get this data into a table automatically?

Creating tables automatically

When you have the parsed JSON data you want to put in the table, you need to decide how you want that table to behave.

Do you want your table to…Then use…
Rewrite the entire table every time you get new data?OVERWRITE()
Continuously append data to the table, even if it’s a duplicate?INSERT()
Append data to the table, but if an entry already exists, updates that entry?UPDATE()

Let’s have a quick look at how these functions work.

OVERWRITE

OVERWRITE() 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 and have the table reflect only the most relevant information.

kn-int-overwrite

Syntax
OVERWRITE(data, destination)

ParameterMandatoryDescription
dataYesThe data you want to get into a table. For example a JSON cell or another table.
destinationYesThe range of cells where to insert the data.

Test it out!

In cell E3, paste in this function: =OVERWRITE(E2,A4:4).

INSERT

INSERT() 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 means you will get duplicates. This method is useful if you want a full historical log of the data.

kb-int-insert

Syntax
INSERT(data, destination)

ParameterMandatoryDescription
dataYesThe data you want to get into a table. For example a JSON cell or another table.
destinationYesThe range of cells where to insert the data.

Test it out!

In cell E3, paste in this function: =INSERT(E2,A4:4).

UPDATE

UPDATE() 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 without any duplicates.

In order to identify whether or not an entry is unique, the UPDATE() function uses a keys parameter. You need to choose something that uniquely identifies each entry. In our case, we'll choose name and domain.

kb-int-update

Syntax
UPDATE(data, keys, destination)

ParameterMandatoryDescription
dataYesThe data you want to get into a table. For example a JSON cell or another table.
keysYesThe columns or JSON keys used to determine if the data is unique (insert a new row) or not (update an existing entry).
destinationYesThe range of cells where to insert the data.

Test it out!

In cell E3, paste in this function: =UPDATE(E2,'["name", "domain"]',A4:4).

So in short, to get the data you want into tables automatically:

  1. Figure out what data you want and use PARSE() to get it.
  2. Use one of the functions to insert the data into a table.
  3. Enjoy!

Spreadsheet example

In our spreadsheet, since this is a little calculator for users to search for data, we’re going to use OVERWRITE(). And as we want it all to happen with one click of a button, we need to put it all into our formula in cell E2 (you don’t have to - but we want to show you how to do it).

kb-academy-overwrite-data-example

Adding data from one table to another
You don't always need to create tables from JSON - you can also create them from other tables! To show you how, let's add a little log sheet so that we can register what people search for.

  1. Create a new table and rename it to App Log.
  2. Set the table mode to Table. We want to see all the queries from all our users ;).
  3. In cell E3 of Event App, enter the following formula:

kb-int-insert-app-log

❇️ Done! Whenever someone clicks the search button, we’ll log what they searched for. Brilliant! And you learned another cool skill: inserting data from one table into another 💪.

Before we go on, just make sure that your spreadsheet looks more or less like this:

kb-eventApp-appLog-newUI

Up next

As you can see - it’s really easy to create custom tables from the data that you get back from an integration function. All you really need to know is what data you want to get and how you’d like your table to display that info.

Now - what if we wanted to have an email sent to us every day with a log of our user’s searches? Well, that’s what we’ll be talking about next in scheduling and repeating actions.