Sometimes you want calculations to run in the app while the user is interacting with the data in a form. To help you achieve that goal, you can specify an App Formula for specific columns. An App Formula is an expression AppSheet evaluates whenever a user changes a value in a form (while creating a new record or editing an existing record).

Each time such a change occurs in the form, the App Formula of every affected column in the record is evaluated and the column is assigned the result of the formula evaluation. For example, if a record has a Price column and a Tax column, the Tax column might have an App Formula [Price]*0.05 to compute a 5% tax automatically.

In the Editor, you can add App Formulas to any column when you go to Data > Column Structure.

Click on the edit icon (above, circled in red) and navigate to the App Formula field to enter the formula you need for your app. The formula itself is any valid AppSheet expression that matches the type of the column. You can learn more about the different kinds of expressions supported by AppSheet.

App Formulas vs. Initial Values

You can also specify an Initial Value for every column. The difference between an initial value and an app formula is that an initial value expression is used to assign initial or default values for a column in a new entry. If the expression is not a constant, it may be a formula that depends on other columns in the new entry. Its value will be recomputed as the new entry is filled in until the user explicitly assigns/overrides the value of the column. At that point, a value has been assigned and the "Initial Value" definition is no longer relevant for this entry.

In other words, an Initial Value expression acts just like an App Formula, but it has two differences: (a) the column is still editable by the user, and (b) once edited, the column is no longer automatically updated with continued re-computation of the expression.

App Formulas for Virtual Columns

App Formulas are also used to define Virtual Columns. These columns do not actually exist in the spreadsheet but are only computed within the context of the app itself.

App Formulas vs. Spreadsheet Formulas

App Formulas are distinct from Spreadsheet Formulas. The two mechanisms are complementary and both add value to your app. Your spreadsheet is a very powerful tool to run calculations. Those calculations run only when data syncs back to the spreadsheet.

App Formulas are commonly used to calculate the value of a column when the user makes updates to the app. When the changes are synced back to the spreadsheet, the computed value is saved to the corresponding spreadsheet cell.

Did this answer your question?