UPDATE, INSERT, FILL & CLEAR: Automate tables and databases!

Why?
You want to automate your database in a spreadsheet, e.g. because you want to update the information you get from a different service, or you want to create an automatic log of it?

With dashdash, you can. UPDATE() updates or inserts a row’s values on a table. INSERT() inserts a row’s values on a table even if there’s values with similar keys. CLEAR() clears the contents of a row. FILL() fills down a range by a number of rows.

UPDATE()
How?
Syntax: =UPDATE(source_range, key, data, target_range).

  • Source_range - The header range that locates the table where data will be updated or inserted from.
  • Key - The number of input data cells, counting from data1, to be used as unique keys when searching for unique rows.
  • Data - Cell or range with data to be updated or inserted.
  • Target_range - The header range that locates the table where data will be updated or inserted to.

Example: =UPDATE(A2:C2, 1, A3:C4, A5:C5).

Returns value: “Update. 2018-11-22” , and two rows are inserted below row 5, one with A3:C3, and the other with A4:C4.

INSERT()
How?
Syntax: =INSERT(source_header, data, target_header).

  • Source_header - The top of the table where data will be inserted from.
  • Data - The cell or range with data to be inserted.
  • Target_header - The top of the table where data will be inserted to.

Example: =INSERT(A2:C2, A3:C3, A5:C5).

Returns value: "Insert… ". A3, B3, C3 get inserted in the next available row below row 5, matching the names of columns in row 2.

FILL()
How?
Syntax: =FILL(range, [rows], [overwrite]).

  • Range - the cell or range to be extended down.
  • Rows [optional, 1 by default] - the number of rows to extend the original range by.
  • Overwrite [optional, 1 by default] - the method to overwrite cells. 0 means it doesn’t overwrite any cells that already have any content, but only writes on blank cells. 1 means it overwrites formulas that are different from what we want to write. 2 means it overwrites everything.

Example: =FILL(A1:D10, 4).

Returns value: returns “Fill. 2017-07-22 14:00:05” -
cells A2:D2, A3:D3, A4:D4, A5:D5 all have the same formulas as A1:D1, with relative references shifted by the number of rows from the original formula.

Good to know:

  • You can use FILL to add formulas dynamically to a log, e.g. with =FILL(B2:D2, COUNTIFS(A2:A,">0")-1). This will FILL the formulas in cells B2:D2 in an additional row whenever a new value is added to column A.
  • Beware! You can easily destroy your app with FILL. For example, if you want to FILL a data log with formulas, make sure you reference the first row or formulas of the table, not the column title row above, in the first argument. Otherwise you will copy the column titles across the formulas. And there is currently no version control yet.

CLEAR()
How?
Syntax: =CLEAR(row).

  • Row - the number of the row to be cleared.

Example: =CLEAR(10).

Returns value: returns “Clear. 2017-07-20” - clears all the cells in the whole row 10.

Related?