Data Validation Errors for Certain Options (Percentages)

[Keith_Fairbairn]

Hi,

How do I stop validation errors from appearing in Google sheets and then causing errors in Appsheet for single digit % options from a data validation drop down list?

I use a data validation for a drop-down in Google Sheet, the options are 0%, 0-25%, 50-75% and 100%. The problem: for 0% and 100% (or any digit only option that does not use "-" for a range), I get a red mark in the corner of the cell on the google sheet saying "Input must fall within specified range". The input does fall within the exact range since it's only selected from a drop-down and can't pick anything else.

On top of this, the options for this question in Appsheet convert the 0% to 0 and 100% to 1. I've tried both Enum and Enum list in Appsheet for data type but the error/issue is the same.

Any ideas on how to fix this?

Keith

[Suvrutt_Gurjar · Accepted solution]

Appsheet Percent column inherently stores  0% as 0.0 and 100 % as 1.00 . Please refer below excerpt from the help article.

https://support.google.com/appsheet/answer/10106435?hl=en&sjid=17102721186839271485-AP

So you could use few options.

1. If you wish to preserve the Google Sheet validation, you could make the said column as enum with base type as text in AppSheet. Then in the backend Google sheet the data will be captured in the format (0%, 0-25%, 50-75% and 100%) you deisre. However within AppSheet you will not be directly able to do any calculations with this column unless you convert it to decimal type or number type.

2. You could use AppSheet percent column type and validation within AppSheet itself. However in this case you may not have a range denoted as a dropdown for the user as a percentage column will not store range values such as (0-0.25 etc.) 

In general when using AppSheet app, it may be a better idea ( but not a must) to use backend sheet for purely storing data as text rather than applying additional (dual)  formatting - AppSheet column types as well as  backend based.

@Steve 's following tip is a very good reference on spreadsheet formatting

Spreadsheet formatting tips - Google Cloud Community

[Keith_Fairbairn · Accepted solution]

Thank you Suvrutt,

I did a version of #1 that you suggested above and now things are working as I would like at both ends. For Google sheet, I formatted the column with the drop down list options as Plain Text and in AppSheet, I used the Enumblist for the Type. Works perfect 🙂

I have 2 additional follow-ups.

Now that I've done the above, how do I get Appsheet to allow only 1 selection from the drop-down options presented in the app (they're presented as check-boxes)?

Also with the options presented as checkboxes, they're out of order, not descending as I'd expect them to be like they appear in the Google sheet. Do you know how I might fix this?

Keith

[Suvrutt_Gurjar · Accepted solution]

Appsheet Percent column inherently stores  0% as 0.0 and 100 % as 1.00 . Please refer below excerpt from the help article.

https://support.google.com/appsheet/answer/10106435?hl=en&sjid=17102721186839271485-AP

So you could use few options.

1. If you wish to preserve the Google Sheet validation, you could make the said column as enum with base type as text in AppSheet. Then in the backend Google sheet the data will be captured in the format (0%, 0-25%, 50-75% and 100%) you deisre. However within AppSheet you will not be directly able to do any calculations with this column unless you convert it to decimal type or number type.

2. You could use AppSheet percent column type and validation within AppSheet itself. However in this case you may not have a range denoted as a dropdown for the user as a percentage column will not store range values such as (0-0.25 etc.) 

In general when using AppSheet app, it may be a better idea ( but not a must) to use backend sheet for purely storing data as text rather than applying additional (dual)  formatting - AppSheet column types as well as  backend based.

@Steve 's following tip is a very good reference on spreadsheet formatting

Spreadsheet formatting tips - Google Cloud Community

[Keith_Fairbairn · Accepted solution]

Thank you Suvrutt,

I did a version of #1 that you suggested above and now things are working as I would like at both ends. For Google sheet, I formatted the column with the drop down list options as Plain Text and in AppSheet, I used the Enumblist for the Type. Works perfect 🙂

I have 2 additional follow-ups.

Now that I've done the above, how do I get Appsheet to allow only 1 selection from the drop-down options presented in the app (they're presented as check-boxes)?

Also with the options presented as checkboxes, they're out of order, not descending as I'd expect them to be like they appear in the Google sheet. Do you know how I might fix this?

Keith

[Suvrutt_Gurjar · Accepted solution]

---

@Keith_Fairbairn wrote:

Now that I've done the above, how do I get Appsheet to allow only 1 selection from the drop-down options presented in the app (they're presented as check-boxes)?

---

Please make the column type as enum and not as enumlist.

---

@Keith_Fairbairn wrote:

Also with the options presented as checkboxes, they're out of order, not descending as I'd expect them to be like they appear in the Google sheet. Do you know how I might fix this?

---

In the said column, please add those options as shown below

[Keith_Fairbairn]

Brilliant!

Thank you @Suvrutt_Gurjar