Automation Part 3: INSERT(), OVERWRITE(), and UPDATE()

Overview

So, we’ve already covered how to update tables based on the JSON that you get. But what about if you have some data in your views and you simply want to “push” it to another table or view? Yeah, you can just copy and paste that row, but that’s awfully manual. Plus, if say you have a published Form, you can’t really ask your users to cut and copy their results into another table. It’s much cooler if all they need to to do is press a button, and then the data is automatically inserted to another view. And that’s what we’re going to learn today with INSERT(), OVERWRITE(), and UPDATE().

And the difference? Well, it’s just like their _DATA() sibilings:

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

If you’re still not sure - check out this simple app we created to illustrate the difference. Pay attention to the timestamps.

To make full use of the examples, we’ll be creating this little app ourselves in this tutorial. To get your set up, click on the “expand” section below.

I/O/U Tutorial App Setup

In a new spreadsheet, create the following views and content:

Form

Cell Content
A1 Name
B1 Profession
C1 DOB
D2 “Submit” button

Insert, Overwrite, Update (three views)

Cell Content
A1 Name
B1 Profession
C1 DOB
D1 Timestamp
D2 =IF(C2="","",NOW()) and drag this formula down to cell D5

And that’s it! Let’s get cracking!

INSERT()

INSERT()constantly appends information to your table, which allows you to have a full historical log of actions or data (thought with duplicates). In the example app, if you click Submit three times, we’ll have three entries in the Insert view.

See INSERT in action

Syntax

INSERT(source_header, data, destination, [auto])

Parameter Mandatory Description
source_header Yes The header row from where to you will take information from. For example: A1:D1.
data Yes The cell or range below the source_header that actually has the information you want to insert into another table. For example: A2:D2.
destination Yes The header row under which you want the data to be inserted. For example: 'view2'!A1:D1.
auto No Indicates whether or not to add additional headers to the destination table.

Example

In the I/O/U Tutorial App spreadsheet you’ve created, add the following:

Cell Formula
E2 =IF(D2="Submit",INSERT(A1:C1,A2:C2,'Insert'!A1:C1),"")

Now, whenever someone clicks the Submit button, their name, profession, and date of birth will be added to the Insert view as a log.

OVERWRITE()

OVERWRITE()recreates the table each time it is called, overwriting any pre-existing data. In the example app, if you click Submit three times, we’ll have only one entry in the Overwrite view. But if we check the view after each time, we’ll see that the timestamp changes.

See OVERWRITE in action

Syntax

OVERWRITE(source_header, data, destination, [auto])

Parameter Mandatory Description
source_header Yes The header row from where to you will “take” information from. For example: A1:D1.
data Yes The cell or range below the header row that actually has the information you want to insert into another table. For example: A2:D2.
destination Yes The header row under which you want the data to be overwritten and inserted. For example: 'view2'!A1:D1.
auto No Indicates whether or not to add additional headers if they appear.

Example

In the I/O/U Tutorial App spreadsheet you’ve created, add the following:

Cell Formula
E2 =IF(D2="OVERWRITE",INSERT(A1:C1,A2:C2,'Overwrite'!A1:C1),"")

Now, whenever someone clicks the Submit button, their name, profession, and date of birth will be added to the Overwrite view. But each time you click it, the entire table will be overwritten - so just the data from the most recent click will be visible there.

UPDATE()

UPDATE()is used to either update an existing entry with some new data, or insert a new row. It uses the keys parameter to determine whether or not an entry is unique (add a new row) or not (update an existing row). In the example app, you’ll notice that if you submit the same name several times, the Update view will only register the first entry. But if you change the name, a new row will be inserted.

See UPDATE in action

Syntax

UPDATE(source_header,keys, data, destination, [auto])

Parameter Mandatory Description
source_header Yes The header row from where to you will “take” information from. For example: A1:D1.
keys Yes The number of columns or rows, starting from the left, to use in order to identify the “uniqueness” of the entry. For example: 1(one column), 3 (use three columns). The number of keys cannot be large than the number of columns or rows in the table.
data Yes The cell or range below the header row that actually has the information you want to insert into another table. For example: A2:D2.
destination Yes The header row under which you want the data to be updated or inserted. For example: 'view2'!A1:D1.
auto No Indicates whether or not to add additional headers if they appear.

Example

In the I/O/U Tutorial App spreadsheet you’ve created, add the following:

Cell Formula
E2 =IF(D2="Submit",UPDATE(A1:C1,3,A2:C2,'Update'!A1:C1),"")

This time, whenever someone clicks the Submit button, we’ll check if the name, profession, and DOB exist together in one row in our Update view (the keys parameter looks at all three columns), and if it doesn’t it’ll insert a new row with those parameters. If it does, well, it’ll update that row. Though in our case, that won’t be possible.

Challenge

And now that you’ve built the app, it’s time to get your creative juices flowing a little bit with a challenge.

  1. Change the UPDATE() formula in such a way that if someone has the same name and profession, but a different DOB, it’ll update the entry to have the new DOB.
  2. Recreate the I/O/U app, transpose everything vertically, and publish it.
  3. Using our Instagram integration, create a spreadsheet that gets the number of Instagram followers for two different accounts every Monday, Wednesday, and Friday at 10 am and inserts them into a table, along with a timestamp.

Summary

Nicely done! So now you know how to use our INSERT(), OVERWRITE(), and UPDATE() functions to take data from one table, and place it in another. Not too shabby right? And, if you did our challenges, you’ve also seen how you can combine an automation function from part 2 of this series to really step it up.

Also, if you want to some more “real-life” examples, check out our:

In the last part of the series, we’ll show you a few more tricks that dashdash has to make your spreadsheets even more automated with CLEAR(), FILL(), and SUBSTITUTE(). Stay tuned :sunglasses:!

3 Likes