Both Google Sheets and Microsoft Excel support a built-in mechanism called data validation rules. Data validation rules allow you to constrain the values that can be entered into a worksheet cell. You can define one or more data validation rules for your worksheet. Typically, you define a separate data validation rule for each column in your worksheet where you need to constrain user entered values.
When you create an AppSheet application from a worksheet having data validation rules, AppSheet will automatically detect these data validation rules and apply them to your AppSheet application. This ensures that data values entered through your AppSheet application conform to the same rules as data values entered directly into your worksheet.
The drop downs that AppSheet automatically creates from worksheet data validation rules, have one significant limitation. AppSheet does not automatically detect changes you make to your worksheet data validation rules. Instead, each time you change a worksheet data validation rule, you must manually regenerate the corresponding AppSheet table. When you do the regenerate, AppSheet will detect the worksheet data validation rule change and update the AppSheet application to match.
Defining a Google Sheets Data Validation Rule
You can define a data validation rule in Google sheets in two ways.
- You can manually type in the allowed values into the validation list.
- You can create a new worksheet containing a predefined set of cells that contain the allowed values.
When you have lots of allowed values, we recommend the second approach. The first approach, manually typing the allowed values, imposes a limit of 256 characters for the entire list.
You can follow the steps listed below to define a data validation rule for your Google worksheet. We refer to the original worksheet containing your application data as the “DataSheet”. We refer to the new worksheet contained the allowed values as the “EnumSheet”.
- Add a new worksheet to your Google workbook to contain your allowed values.
- Open the “EnumSheet” and allocate a column for your first set of allowed values.
- Enter a column header value. In my case I entered “Fruits” in cell A1.
- Enter all of the allowed values into this column. In my case I entered “Apple” in cell A2, “Apricot” in cell A3, “Avacado” in cell A4, and so forth.
- In the “DataSheet” go the column that will contain the data values and select all of the cells in the column.
- Go to the “Data” menu and select “Validation”.
- The “Data validation” dialog window will appear. In the “Criteria” row select “List from a range” and then click the “Select data range” button just to the right of the drop down list.
- The “What data?” dialog window will appear.
- With the “What data?” dialog window still open, click the “EnumSheet” tab to open the “EnumSheet”.
- Select the entire range of cells in the column containing your allowed values. In my case, I selected the cells containing “Apple”, “Apricot”, “Avacado”, etc.
- The “What data?” dialog should now contain something like “EnumSheet!A2:A15”. The “EnumSheet” name appears to the left of the “!” and the selected allowed values cell range appears to the right.
- Click “OK” in the “What data?” dialog.
- The “Data validation” window will now reflect the “EnumSheet” name and cell range you just selected above. These are the allowed values that will be used for validation.
- Hit Save in the Data validation dialog.
- If you have multiple sets of allowed values, you can add a column in the "EnumSheet" for each set of allowed values. For example, you might add a column for "Vegetable" allowed values, another for "Animal" allowed values, and so forth. Repeat steps 2 through 14 for each of your allowed values.
- Ensure that at least one row in the "DataSheet" contains data values. For each data column, choose one of the legal allowed values as the data value.
- Generate your AppSheet application from the workbook (use the add-on). AppSheet will read the "DataSheet" to get your data values. It will read the “Enum Sheet” to get your allowed values.
- Once you have created your app, open the Data>Column Structure tab to ensure all of the allowed values have been picked up by AppSheet. They should appear in the “Type Qualifier” column for each of your data fields having a data validation rule.
Defining a Microsoft Excel Data Validation Rule
You can specify enum values in another sheet when working with Excel worksheets. However, one additional step is required to do this. You must use the Excel "Define Name" feature to name the enum values contained in the other worksheet. Do this as follows:
- Enter the enum values in a dedicated column on a separate Excel worksheet as described in steps 1 through 4 above.
- On the Excel "Formulas" tab click "Define Name" to define a new Name that refers to the worksheet and cells you created in step 1. When the "New Name" dialgo is displayed enter a value such as "FruitEnum" in the "Name" attribute. Click the icon to the right of the "Refers to" attribute. Select the worksheet containing the enum values, and select the range of cells containing the enum values. Click "OK"
- In the Excel “DataSheet” go to the column that will contain the enum values and select all of the cells in the column. On the Excel "Data" tab click "Data Validation" and select "Data Validation" from the drop down list. In the "Data Validation" dialog select "List" from the "Allow" drop down. In the "Source" field enter "=" followed by the Name you defined in the previous step. Click "OK".
- Ensure that at least one row in the "DataSheet" contains data values. For each enum column, choose one of the legal enum values as the data value.
- Regenerate the AppSheet application and it will extract the enum values from the enum values column you created in step 1.
- Once you have created your app, open the Data>Column Structure tab to ensure all of the enum values have been picked up by AppSheet. They should appear in the “Type Qualifier” column for your “Enum” field.