Objectsheet Tutorial Page 1: Table Basics

These tutorial pages introduce some of the basic capabilities of the Objectsheet. Each of the examples below is live and fully functional; please experiment with them to help understand how they work. We'll introduce the Table first. These examples work with Internet Explorer and Mozilla.

This page covers basic navigation, formulas, values, and cell referencing. The next page covers formats, styles, and display types, while the third page covers scratch sections, introspection, and cross-table referencing. The last page introduces HTML sections.

Note: This tutorial and the next one involve single isolated tables. The actual Objectsheet application can organize multiple Sections that can all refer to each others' data. This is introduced in the 3rd and 4th tutorials.

Page Contents:

1. Entering values and formulas, adding rows and columns
2. Referring to other cells, sorting
3. Template Formulas
4. Renaming Columns

1. Entering values and formulas, adding rows and columns

The table below contains some simple values and formulas. Formulas use Javascript (see the Javascript Reference for details). As with traditional spreadsheets, formulas are preceeded with an equals sign. In the example below, when you click on a cell that has a formula, the formula is shown. When you leave that cell, the formula is calculated and displayed*.

Go ahead and modify values and formulas in table1 (we'll cover cell-to-cell referencing below). The entire Objectsheet automatically recalculates when you modify a cell then tab or click out of it. You can always reset to the default values by reloading this page.

* The rest of the tutorial sections below will show both the formula and the result at the same time. This is controlled by a simple "display" formula.

Things to try:

• Click on a cell to edit its formula or value (the entries in columns b all have formulas, as does row 1 column c).
• When you leave the cell, the table recalculates. Alternatively, hit Control-Enter to calculate without leaving the cell.
• Formulas start with an equals sign ("=").
• Formulas use Javascript expressions.
• If your formula generates an error, an message is shown in the cell. In the full Objectsheet application you can also see the error message in the application's status bar.
• Note that row numbering starts at zero.

There are several ways to add and remove rows and columns from a table:

• Drag the gray border along the bottom or the right side of the table
• Click gray borders to add single row or column (Shift-click to delete)
• Control-Click bottom gray border to add 5 rows (Shift-Control-Click to delete)
• To insert or remove a row in the middle, highlight a row first by clicking on the row's label on the left; the new row will be added above the highlighted row.
• To insert or remove a column in the middle, highlight the column first by clicking on the horizontal grey bar just below a column name; the new column will be added its left.
• Click in a cell in the bottom row of the table and hit the Enter key to add a new row at the bottom.
• You can add and remove rows and columns or directly adjust the size of a Table using formulas.

Notes:

• When you click on a cell, it automatically widens to show you all the content within it.
• You can't delete a row if it has values in it; you must delete the values first. This is a safety feature until the undo feature is completed.
• (Javascript experts) The Math object is automatically exposed in Objectsheets so you don't need the prefix within math functions (log(), exp(), pow(), random(), etc.)

2. Referring to other cells, sorting

Referring between cells in an Objectsheet table is a little different than in a spreadsheet. Within a given row, you refer to values from other columns simply by using the name of the column.

You can refer to cells in other rows in several ways:

• Using the "us" array: us[row#].columnname. The us[] array contains each row (indexed by the number in the leftmost column).
• Using the Column Function: this.columname(row#)< (note the parentheses around row#). This function is automatically created for each column.
• Using the "prev" object to refer to the row immediately above: prev.columnname

The table below shows some examples of referring to other cells. You have the full Javascript language and functions at your disposal anywhere in the Objectsheet (for example below, getting the length of a string in row 3, column d).

With the Objectsheet, refer to the current row and column in formulas using the variables "row" and "col" (see the formulas in row 1 column c and row 3 column b, below). Also, note the use of an in-cell comment in row 3 column d. In Javascript, simple comments start from '//' and continue to the end of the line.

Finally, you can sort Objectsheet Tables just by clicking on the grey vertical bar just right of the column name. More complex ways to sort rows are also available. While sorting, the index column on the left shows both the current and original (pre-sorted) indices for each row.

For this and all following examples, we've changed the display in the cells to show both the formula and the result at the same time and widened the cells for easier viewing.

Things to try:

• Create new formulas in blank cells. Text or numbers can be entered directly; prefix formulas with "=".
• Modify formulas
• In formulas, only modify the part to the right of :=
• Refer to other cells on the same row using the appropriate column name
• Refer to cells on the previous row by prefixing column name with "prev."
• Refer to cells on any row by prefixing column name with "us[row#]."
• Refer to the current row and column number using row and col, respectively
• Modify values that feed formulas in other cells (e.g., row 0, column a) and watch the change propagate to those other cells.
• Resize columns by dragging the grey vertical bar just right of the column name
• Sort on columns by clicking the grey vertical bar just right of the column name.
  • Successive clicks cycle the sort between ascending ("+" prefix), decending ("-" prefix), and no-sort (no prefix). Shift-click cycles backwards.
  • When sorting, the original row numbers are shown on the left, followed by a backslash ("\")

Notes:

• We've changed the display in the cells to show both the formula and the result at the same time and widened the cells for easier viewing. We'll describe how that works later.
• References to other rows may appear a little more messy than a simple $a$4 reference you'd find in a spreadsheet. But as we will see later, the need to directly reference other rows will be rare.

3. Template formulas

In the table below, we've unhidden a new row, the formula row. You can hide and show the formula row by clicking on the "f()" button at the upper right of the Table. The formula row is part of the Template, where all the "rules" go that govern a specific Objectsheet Table. Formulas in the formula row can use the same functions and references as the cells in the examples above.

There are a variety of ways to build values down a column. Several of these are illustrated in the example below.

• The term row in formulas builds values according to the row number (see column 'a')
• Use prev. to build values sequentially, row by row (see columns c, d, and e). When using prev, make sure the top row has some other value, as prev has no value in row 0.
• Another approach to make sure the top cell has a value is to use the Javascript "or" operator: in column d (prev.d||0) results in 0 for row zero.
• The range() formula forces specific values at the top and bottom of a column, with the values in between equally spaced between them. As you grow and shrink the table, the end values remain fixed and the numbers in the middle adjust accordingly.

Cells calculated using a template formula have cell color (grey) distinct from manually entered values. If you manually type a value (or formula) into one of these cells, the cell changes back to white, indicating a manually entered value. This makes it clear how a cell was calculated. In the example below, column c is calculated by the formula prev.c*2, but the value row 0, column c was entered directly and thus overrides the formula. If you remove a manually-entered value, the original template formula is restored.

Things to try:

• Modify formulas in the formula row
• Override a value in the body of the table by clicking in a cell and typing a new value or formula. Erase the new value and tab out of the cell to revert back to the original value.
• See what happens when you remove the value 1 in row 0 column "c". The table will indicate that prev has no value in row zero.
• Remove an override value by clicking in a cell, clearing its contents, then tabbing or clicking outside the cell.
• Erase a formula in the formula row completely-- the calculated values disappear, turning the cell back to white. Any manually entered values remain.
• Add more objects to the table by clicking or dragging the horizontal bar at the bottom of the table and see how new values are automatically calculated
• As you change the table height, see how the range() function forces the endpoints to be the same.
• Hide and show the formula row by clicking on the "f()" button on the upper right of the table

4. Renaming columns

In the Objectsheet, you can rename the columns; any existing reference to that column in a formula will automatically change as well. Objectsheet column names are case-sensitive: "hour" is different from "Hour".

Things to try:

• Modify column names (e.g., turn "day" into "dy" and "hour" into "h"); see how the change propagates in the formulas.
• Try and rename a column so that it's the same as an existing column. The table won't allow that. The special Objectsheet terms, "us", "this", "prev" also cannot be used for column names.
• Add a space to a column name (e.g., change "day" to "full day"). The column name appears with the space, but underscores ("full_day") are used within formulas.
• You can delete a column name, leaving it blank. The column name used in formulas changes to an internal representation. Then change the column name back to a regular value.
• Add comments or units to the column name, separated by a comma or colon, or in parentheses. Comments or units are displayed but not part of the column name. For instance, change column "hr" to "hr, PDT" or "hr (PDT)"

This completes the basics for working with on Objectsheet table. The next tutorial covers cell formatting, styling, and display.