Effective use of column headers

Locating Column Headers

AppSheet uses the following heuristics to locate your spreadsheet's header row. 

  1. We assume that rows containing bold text are more likely header rows. When all of your data values are text, this can mislead AppSheet header detection. In this case, making your header row bold, helps AppSheet find your header row. 
  2. We examine the data value in each cell of the row and try to determine each cell's data type based on the cell's value. If we determine that the cell's value is one of the known data types, then we can be fairly confident the row contains data and is not a header.
  3. We examine the format value in each cell of the row. If the cell's format matches one of the known data types, then we can be fairly confident the row contains data and is not a header.
  4. We examine the text value in each cell of the row. If the cell's text value is a frequently occurring header word like "Name", "Address", "Amount", "Total", and so forth, we can be fairly confident the row is a header.
  5. We assume rows that appear closer to the top of the worksheet are more likely to be header rows. However, people sometimes create multi-row headers where the last header row is actually the most meaningful (or the most specific) header row, so we attempt to recognize multi-row headers.

  

Inferring Column Type's from the Header

AppSheet infers the types of columns from the column header names as well as from the content of the rows. Especially in cases where there are no existing data rows, it is important to pay attention to the column header names.

There are special words in column headers that 'trigger' AppSheet to infer specific column types. For example, a column header name 'Web Site' suggests that the data is of the URL type. Currently, this only works with English but we intend to add similar capabilities for other languages in the future.

There are a few special cases to be aware of:

  • A column header ending with a question mark is inferred as a Yes/No data type. 
  • A column header ending with an exclamation mark is inferred as an action Url
  • A column header whose name is similar to another table already in the app may be inferred as a table Ref type. For example, if there is a table called Products and a new spreadsheet is added with a column called 'Product' or 'Products', AppSheet will infer that it is a Ref type.

If you are building an app from a Google Form and your question is marked as Text, a similar inference process occurs on the question title.

Here is a complete list of field types, what they do, and the trigger words that activate them:

  • Address 

What it does: An Address field contains a full postal address that can be displayed as a map pin on a Google map. The completeness of the address will increase the accuracy of the map pin location.
Trigger words: "address", "where"

  • ChangeCounter

What it does: A ChangeCounter field contains a numeric counter value reflecting how many times an entry has been changed. 
Trigger words: No trigger words. Must be selected in the Editor.

  • ChangeLocation

What it does: A ChangeLocation field contains a latitude and longitude value reflecting the current GPS location where an entry has been changed.
Trigger words: No trigger words. Must be selected in the Editor.

  • ChangeTimestamp

What it does: A ChangeCounter field contains a date and time value reflecting when an entry has been changed.
Trigger words: No trigger words. Must be selected in the Editor.

  • Color

What it does: A Color field contains a color value. By default the value can be any one of the standard color values "blue", "green", "orange", "purple", "red", or "yellow". You can specify other color values when defining the field. You can populate the color field using an expression in the InitialValue or AppFormula field. That expression can compute the color value based on the value of other fields in the record.
Trigger words: "blue", "green", "orange", "purple", "red", "yellow"

  • Date

What it does: A Date field contains a date value that specifies a year, month, and day.
Trigger words: "birthday", "dob", "day", "month", "year"

  • DateTime 

What it does: A DateTime field contains a date and time value that specifies a year, month, day, hour, minute, and second.
Trigger words: "date", "dates", "day", "days", "month", "months", "year", "years", "timestamp"

  • Decimal 

What it does: A Decimal field contains a decimal number with a specified decimal precision.
Trigger words: "altitude", "altitudes", "amount", "amounts", "amt", "amts", "age", "ages", "capacity", "capacities", "depth", "depths",  "displacement", "displacements", "height", "heights", "hours", "latitude", "latitudes", "length", "lengths", "longitude", "longitudes",  "magnitude", "magnitudes", "mass", "masses", "population", "populations", "pop", "qty", "qtys", "quantity", "quantities", "size", "sizes", "sum", "sums", "total", "totals", "units", "weight", "weights", "width", "widths", "volume", "volumes"

  • Drawing

What it does: A Drawing field contains a drawing or notes that you can enter through your application. You can choose from a set of seven colors when entering the drawing. 
Trigger words: "depiction", "diagram", "drawing", "illustration", "layout", "likeness", "rendering", "sketch"

  • Duration

What it does: A Duration field contains a time duration specified in hours, minutes, and seconds. 
Trigger words: "duration", "timespan", "period", "elapsed"

  • Email

What it does: An Email field contains a email address. You can use the email address to send an email.
Trigger words: "email", "e-mail"

  • Enum

What it does: An Enum field contains one value from a specified list of values. The list of values can be obtained in several ways.

  1. You can enter the list values manually in the AppSheet Editor when you define the field.
  2. The list values can be obtained from a Data Validation rule you specify in your worksheet.
  3. The list values can be obtained from another table in your AppSheet application.

Trigger words: No trigger words. The field type is determined automatic if a Data Validation rule was specify in your worksheet; otherwise, you must specify the field type in the Editor.

  • EnumList

What it does: An EnumList field contains one or more values from a specifies list of value. You specify the list of values from which the user can pick as described for Enum above.
Trigger words: No trigger words. Must be selected in the Editor.

  • File

What it does: A File field contains a reference to a data file in your cloud drive, such as a PDFs or document file. By adding a string indicating the location of the file in relation to the spreadsheet. For example. Reference/guide.pdf ß AppSheet will locate the file and add a link to it from the app.
Trigger words: "file", "files"

  • Image

What it does: An Image field contains a reference  to an image in .jpg, .png, or .gif format. It allows you to capture images from your mobile device's camera or camera roll.
Trigger words: "image", "images", "picture", "pictures", "photograph", "photographs", "photo", "photos", "figure", "figures", "fig", "figs", "icon", "icons", "illustration", "illustrations", "snapshot", "snapshots"

  • LatLong

What it does: A LatLong field contains a latitude and longitude value as as 48.5564, -122.3421. It allows you to capture the current location from your mobile device's GPS. The field value can be displayed as a map pin on Google Maps. 
Trigger words: "latlong", "geolocation"

  • LongText

What it does: A LongText field contains long multi-line text values.
Trigger words: "notation", "notations", "note", "notes", "desc", "description", "descriptions", "comment", "comments"

  • Name

What it does: A Name field contains the name of a person, place, or thing.
Trigger words: "first", "givenname", "given-name", "last", "middle", "mi", "name", "names", "surname", "surnames"

  • Number

What it does: A Number field contains an positive or negative integer value.
Trigger words: "altitude", "altitudes", "amount", "amounts", "amt", "amts", "age", "ages", "capacity", "capacities", "depth", "depths", "displacement", "displacements", "height", "heights", "hours", "latitude", "latitudes", "length", "lengths", "longitude", "longitudes",  "magnitude", "magnitudes", "mass", "masses", "population", "populations", "pop", "qty", "qtys", "quantity", "quantities", "size", "sizes", "sum", "sums", "total", "totals", "units", "weight", "weights", "width", "widths", "volume", "volumes"

  • Percent

What it does: A Percent field contains a percent value with a specified decimal precision.
Trigger words: "%", "discount", "discounts", "interest", "percent", "percentage", "chance", "percentages", "probability", "probabilities", "quota", "quotas", "split"

  • Phone 

What it does: A Phone field contains a telephone number value. Your application can call or text this number.
Trigger words: "cell", "cellphone", "fax", "mobile", "pager", "phone", "phonenumber", "tdd", "tel", "telephone", "telex", "tty"

  • Postal Code

What it does: A Postal Code field contains a ZIP code or postal code value. Typically, the postal code is part of a multi-column field consisting of a "Street" column, "City" column, "State/Province" column, optional "Country" column, and a "Postal Code" column. AppSheet creates a multi-column Address field that combines the values from these columns.
Trigger words: "zip", "zipcode", "postalcode"

  • Price

What it does: A Price field contains a decimal number with a specified decimal precision and currency symbol.
Trigger words: "amount", "amounts", "amt", "amts", "balance", "balances", "bonus", "bonuses", "cash", "commission", "commissions", "compensation", "contribution", "contributions", "cost", "costs",  "discount", "discounts", "dividend", "dividends", "donation", "donations", "dues", "duty", "duties", "earnings", "excise", "expense", "expenses", "fee", "fees", "fine", "fines",   "gain", "gains", "gross", "indemnity", "income", "levy", "levies", "loss", "losses", "pay", "payment", "payments", "payoff", "pension", "pledge", "pledges", "premium", "premiums", "price", "prices",  "proceeds", "profit", "profits","receipts", "reimbursement", "reimbursements", "remittance", "remittances", "remuneration", "remunerations", "rent", "rents", "revenue", "revenues", "royality", "royalities", "salary", "salaries", "sales", "stipend", "stipends", "subtotal", "subtotals", "tariff", "tariffs", "tax", "taxes", "tip", "tips", "tithe", "tithes", "toll", "tolls", "total", "totals","value", "values", "wage", "wages", "winnings"

  • Ref

What it does: A Ref field contains a reference to a specific record in another table. When you define the Ref field, you specify the name of the target table. Each Ref field value is the key value of a specific record in the target table. A Ref field is the equivalent of a foreign key field in a relational database.
Trigger words: No trigger words. Must be selected in the Editor.

  • Signature 

What it does: A Signature field contains a signature. It allows you to capture a signature on your mobile device. Each captured signature is saved to its own file on the app creator’s Google Drive. The name of this file is stored in the Signature field.
Trigger words: "signature", "signatures"

  • Thumbnail

What it does: A Thumbnail field contains a small image. It is exactly like an Image field except the captured images are expected to be smaller.
Trigger words: "thumbnail", "thumbnails", "logo", "logos"

  • Time

What it does: A Time field contains a time value that specifies an hour, minute, and second.
Trigger words: "hour", "hours", "mins", "minute", "minutes", "second", "seconds", "secs", "time", "times"

  • URL

What it does: A URL field contains a URL value. When an application user clicks the field value, the default device browser will load the page.
Trigger words: "site", "sites", "url", "urls", "web", "website", "websites"

  • Yes/No

What it does: A Yes/No field contains a value of True, False, or Not specified. The field value is displayed as Y (True), N (False), or N/A (Not specified). A Yes/No field is the equivalent of a Boolean field in a relational database.
Trigger words: Starts with "has" or "is" or ends with "?".

 

Displaying column headers

You can choose to display your table's column names in your app. To enable column name display, open your application in the Editor, go to the UX>Options tab, and set the 'Explicit column headers' check box. When you chose this option, your column widths will be expanded to accommodate the column name.

The 'Explicit column headers' option is ignored for columns of type:

  • Address
  • ChangeLocation
  • Color
  • Email
  • File
  • LatLong
  • Phone
  • Progress
  • Url
  • Yes/No

 

 

Have more questions? Submit a request

0 Comments

Article is closed for comments.