Expanding data cells and Insert to other tables

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:

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.

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.

Syntax
OVERWRITE(data, destination)

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.

Syntax
INSERT(data, destination)

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.

Syntax
UPDATE(data, keys, destination)

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).

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:

❇️ 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:

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.