Spreadsheet formatting tips

The format of a cell in a spreadsheet used as a data source for an app can affect how AppSheet uses the cell’s content. It is therefore important to take care to choose cell formatting carefully.

Automatic format is okay for most data, but can cause problems in some circumstances.

The safest cell format you can use for everything is plain text. The downside to formatting the spreadsheet as plain text is the spreadsheet becomes less legible to its human users.

Here are my suggestions:

  • Format the entire worksheet as plain text.

  • Worksheet columns that correspond to app Number columns may be set to automatic, or to a desired numeric format.

  • Worksheet columns that correspond to app Decimal or Price columns may be set to automatic, or to a desired numeric format. I set the number of decimal places in the worksheet column to match that in the app column.

  • Worksheet columns that correspond to app Date, DateTime, Duration, or Time columns may be set to automatic, or to the corresponding built-in equivalent format. I haven’t tried using a custom format.

  • Worksheet columns that correspond to app Lat or Long (but not LatLong) columns may be set to plain text, automatic, or to a numeric format that allows a lot of decimal places (10?).

  • Worksheet columns that correspond to app EnumList, LatLong, List, or XY columns should be set to plain text.

  • All other worksheet columns should be plain text.

AppSheet doesn’t pay any attention to most other cell formatting. Generally, I’d consider it safe to use any of the following spreadsheet cell formatting features without any concern for app behavior:

  • Underline, italics, strikethrough, and bold. Note that bold is expected by AppSheet in the header row of the workbook.
  • Font/typeface.
  • Text or background color
  • Text size
  • Text wrapping or rotation
  • Horizontal or vertical alignment
  • Cell borders
  • Conditional formatting
  • Frozen rows or columns

Make sure your spreadsheet adheres to the guidelines given here:

10 Likes

When you add comments to the column header cells, this is also respected by AppSheet.

Column Description: The description, if provided, is shown to the user instead of the field name in forms. It is automatically populated from comments on the column header cells in the spreadsheet.

2 Likes