To learn more about how to change a column type, check out this tutorial article.
Below is a list of available column/data types.
- Text: a single line of text.
- LongText: one or more lines of text.
- Name: the name of a person or place.
Values of these types can be graphed and used in mathematical operations.
- Number: a whole number; an integer.
- Decimal: a real number; a number with a fractional component; a floating-point number.
- Price: a currency value. The TypeQualifier field can be used to indicate a currency symbol ($ is the default).
- Percent: a percentage.
These values are shown using the timezone and presentation format of the user's device.
- Date: a specific day, month, and year.
- DateTime: a specific hour, minute, and (optionally) second of a specific day.
- Time: a specific hour, minute, and (optionally) second without regard to the day.
- Duration: a period of time as a number of hours, minutes, and seconds.
In some apps, it's important to record a timestamp or increment a counter automatically in a row when changes are made to other columns, and even values within the columns. The change types provide this functionality. By default, change types automatically update when any other column value changes. However, they can be constrained to react to changes only on specific other columns. Change types can also be constrained to react to changes to certain values.
- ChangeCounter: shows how many times an entry has been edited. See how this works in the Interview Feedback sample.
- ChangeLocation: will automatically populate with the current GPS location (where the change was made).
- ChangeTimestamp: shows when an entry was last edited. See how this works constrained to certain columns in the Store Inventory sample.
Columns of these types are constrained to having one of a fixed list of allowed values.
- Enum: Allows the user to select a single value from a list of allowed values. See Dropdowns
- EnumList: EnumList is identical to Enum, except it allows the user to select multiple values from a list of allowed values. See Dropdowns
- Ref: are used to create relationships between one record and another. See References Between tables.
- Yes/No: also known as a Boolean type, these values display as Y or N.
- Color: color code entries in your app with a subset of standard colors: Black, White (which is shown as light grey), Green, Blue, Purple, Red, Orange, and Yellow. See how color coding works in the Project Plan sample.
- Progress: show the progress of an entry using Harvey balls. See how the progress function works in the Delivery Tracking app.
Values of these types can be tapped to launch communication.
- Email: an email address, giving you the ability to send emails by clicking the email address.
- Phone: a phone number, giving you the option to both call and SMS text through the app. At present, AppSheet only automatically recognizes phone numbers in North American format. We hope to improve this in the future to recognize international phone numbers as well. You can overcome this limitation by manually setting the column type to Phone. Once you do this, AppSheet will treat the column as a phone number, and the column type setting will be preserved across schema regeneration. When your application includes both a home phone number and a cell phone number, you can use format rules to distinguish between the two.
In the app, values of these types can be seen on a map.
- LatLong: a latitude and longitude (e.g., "48.5564, -122.3421"). Form fields for this data type can fill in the current location with a single click. See how location capture works in the Agriculture Inspection sample.
- XY: models a location inside an image, such as a location on a custom map or schematic. XY refers to an X axis value and a Y axis value defining a position within the selected image, where 0, 0 corresponds to the top-left corner and 100, 100 corresponds to the bottom-right corner. You choose the background image by providing a URL in the BaseImage field of the type qualifier, this image is then used as the background for the Map view when viewing XY column values. The image must be public (anyone with the link can view) for the map to be displayed.
- Address: a fully-specified postal or street address.
Simple Address Field
You can specify a complete address in a single worksheet column.
Include the word "Address" in the column name to help AppSheet identify the address column. For example, you could name the column Address or Customer Address.
Enter the complete address including the street, city, state, country and postal code values. This helps ensure the address geo-codes correctly.
You can include two or more addresses in the same worksheet. For example, your worksheet might contain a Billing Address in one column and a Shipping Address in another column.
Automatically Computed Address Field
You can store address information in AppSheet by creating adjacent columns in your worksheet and naming them appropriately. When you do this, AppSheet automatically recognizes the adjacent columns form an address.
For example, you can create adjacent columns in your worksheet, and name them Street, City, State, Country, Zip. In this case, AppSheet will recognize that, taken together, these columns represent an address and it will create a Computed Address column that concatenates the values in the component columns.
Do not specify a column name containing the word "Address" in place of "Street". We interpret any column name containing the word "Address" to mean the column contains an entire address. For example, a column name such as Street Address or Home Address is assumed to contain an entire address. This will prevent adjacent fields from being recognized as a multi-part address.
If you need two (or more) lines of street information, use Street1 and Street2 rather than just Street.
You can include an apartment, suite, or unit number in your address by adding a column between Street and City named Apartment, Apt, Suite, or Unit. If you do this, ensure that the data values you enter in this column are legitimate apartment, suite, or unit numbers. These values are included when computing the geo-coded address. If you enter illegitimate apartment, suite, or unit number values such as "around the back" this may reduce the accuracy of the geo-coded address.
If you need to store other information with the address, such as "around the back", create a separate column following the address to contain this information. For example, you might call this column "Delivery Notes".
You may use Province in place of State.
You can omit Country, but this may reduce the accuracy of the geo-coded address.
You may use PostalCode or ZipCode in place of Zip.
You can include two or more addresses in the same worksheet. For example, your worksheet might contain both a Home Address and a Work Address. AppSheet recognizes the two addresses based upon a combination of naming and adjacency. Keep the Home Address columns adjacent to one another and likewise for the Work Address columns. Then name the columns to help AppSheet group them appropriately. For example, you might name the adjacent home address columns Home Street, Home City, Home State, Home Country, Home Zip. You might name the adjacent work address columns Work Street1, Work Street2, Work City, Work State, Work Country, Work Zip.
You can use this approach to include three or more addresses in your worksheet.
Manually Computed Address Field
You can populate an address column by manually combining the values of two or more columns.
- In your worksheet create a column and include "Address" in the column's name. For example, you might call the column Customer Address.
- In the app editor, add the worksheet as a table.
- Go to the Data > Column Structure tab. Ensure the Customer Address column has a type of Address.
- 4. Assign the column an app formula such as
CONCATENATE([Calle], ",", [Ciudad], ",", [Estado], ",", [Pais], ",", [Postal]), where the column names in the CONCATENATE() function represent the names of the street, city, state, country, and postal code columns.
Values of these types are shown as inline content or open in an external content viewer.
- Drawing: creates a drawing pad in the app.
- Image: .jpg, .png and .gif images. The values may be image URLs or names of files in the source file system of the spreadsheet. Please reference the section describing how to use files as images. Images are captured on the device using the camera or from the local camera roll.
- Thumbnail: also models images, but instructs the app to expect small icons and thumbnails. Thumbnails are captured just like images.
- Signature: a user signature. These are captured using a touch-based signature pad and are stored as small inline images in the spreadsheet.
- File: any file content that can be viewed in a browser (typically used for PDF documents). Capture of files is only supported when running in a browser. In app environments, it is read-only.
- Video: .mpeg videos and YouTube videos. The values should be publicly accessible URLs of videos. The YouTube embed URL format is also supported. Capture of video is not supported.
Show Types - empty columns in your spreadsheet that serve the sole purpose of improving the presentation of data capture forms. Show type columns are only permitted in Form views. There are six categories of show types:
- Page_Header: used to create a new page within the form.
- Section_Header: used to create a new section within the same form page.
- Text: used to show some descriptive text.
- URL: used to show a clickable URL.
- Image: used to show a static image.
- Video: used to show an MP4 video.
- URL: a web address. There are two parts to a URL value: a hyperlink address like "http://en.wikipedia.org" and an optional hyperlink text like "Click here to visit Wikipedia." If you wish to specify the hyperlink text value of the URL field, set its Is hyperlink property to "checked" in the Data > Columns tab of the Editor.
Setting the Is hyperlink property can significantly reduce the performance of a table stored as a Google Sheet. Normally we read the contents of a Google Sheet using the Google Sheets API. Using the Google Sheets API is the fastest and most reliable way to read Google Sheets.
Unfortunately, the Google Sheets API is incapable of reading a URL containing both a hyperlink address and hyperlink text. The Google Sheets API only returns the hyperlink address value but not the hyperlink text value.
When you set the 'IsHyperlink' property to 'true', we read the Google Sheet using an entirely different mechanism. We ask Google Sheets to "export" the Google Sheet as a Microsoft .xlsx file. This translates the entire contents of the Google Worksheet to Microsoft Excel format. We then use an Excel library to read the exported .xlsx file. This tends to be slower and less reliable because the worksheet must be converted from Google Sheets to Microsoft Excel format, it must be exported in Excel .xlsx format, and the exported data must be read using the Excel library.
- App: navigate to another AppSheet app or to navigate to a different view in the current app
- MultiColumnKey: a composite field representing the combination of multiple fields for the purpose of a key.
Column Types Video Part 1: Text, Numeric, Temporal, Communication, and System Types
Column Types Video Part 2: Change Types
Column Types Video Part 3: Geographic Types
Column Types Video Part 4: Content Types (except 'Show')
Column Types Video Part 5: Enumerated (Enum Types)
Column Types Video Part 6: Show Types