WAI
Status Update
No update yet.
Comments
am...@google.com
I'd also love to see this as a formula, in order to apply conditional formatting based on the whether the content of a cell passes the data validation rule or not. Something like ISVALID(value).
am...@google.com
I'm completely baffled: what's the point of "Data Validation" if there's no way of checking whether the data satisfies the condition? Could we all be missing something?
sa...@google.com
sa...@google.com
@marc.art: The validation still validates within the UI itself; it's just that the results of the validation aren't available in code.
ro...@google.com
This would be extremely helpful! Im looking to optimize my team's workflow and it is counterintuitive and wasteful on resources to reverse engineer the validation just to check if it is valid when that information is already shown in the UI! Please add .isValid()!!
sa...@google.com
This is a blocking feature for implementing a compliance changelog of only valid entries.
si...@digibites.nl
Would definitely be useful.
ro...@google.com
Can't see why this isn't already available. I hope it is added soon!
wa...@gmail.com
I've created a workaround for this issue that works in a very ugly -technically said- and slightly undetermined way.
My problem is that the validation is already done by the SpreadsheetApp, but only visually accessible by the user. It feels like that "you can see but cannot touch" :) It's against the whole idea of GAS.
I'd really love to have this result accessible (the feature implemented).
About the workaround:
It works based on the experience that the web browser implementation of catch() function allows to access thrown errors by the script editor JS code parts.
In case an invalid input into a cell is rejected by a validation rule then the system will display an error message that is catchable by the user written GAS. In order to make it work first the reject value has to be set on the specified cell then its vale has to be re-entered (modified) then -right after this- calling the getDataValidation() built in function allows us to catch the necessary error.
Only single cells can be tested with this method as setCellValues() ignores any data validation restriction (as of today).
Disadvantages:
- The validity won't be necessarily re-checked for this function: checking cell validity right after the value is inserted into it. Therefore the result of this function might be faulty.
- The code messes up the history as cells will be changed - in case they are valid.
I've tested it successfully on both Firefox and Chromium.
function getCellValidity(cell) {
var origValidRule = cell.getDataValidation();
if (origValidRule == null || ! (cell.getNumRows() == cell.getNumColumns() == 1)) {
return null;
}
var cell_value = cell.getValue();
if (cell_value === '') return true; // empty cell is always valid
var is_valid = true;
var cell_formula = cell.getFormula();
// Storing and checking if cell validation is set to allow invalid input with a warning or reject it
var reject_invalid = ! origValidRule.getAllowInvalid();
// If invalid value is allowed (just warning), then changing validation to reject it
// IMPORTANT: this will not throw an error!
if (! reject_invalid) {
var rejectValidRule = origValidRule.copy().setAllowInvalid(false).build();
cell.setDataValidation(rejectValidRule);
}
// Re-entering value or formula into the cell itself
var cell_formula = cell.getFormula();
if (cell_formula !== '') {
cell.setFormula(cell_formula);
} else {
cell.setValue(cell_value);
}
try {
var tempValidRule = cell.getDataValidation();
} catch(e) {
// Exception: The data that you entered in cell XY violates the data validation rules set on this cell.
// where XY is the A1 style address of the cell
is_valid = false;
}
// Restoring original rule
if (rejectValidRule != null) {
cell.setDataValidation(origValidRule.copy().setAllowInvalid(true).build());
}
return is_valid;
}
Finally let me express my gratitude for all the great works of the developers! With their shared tools/system I can solve many logistical problems of my and others' lives... Cheers!
On this issue: Hurry up guys!
My problem is that the validation is already done by the SpreadsheetApp, but only visually accessible by the user. It feels like that "you can see but cannot touch" :) It's against the whole idea of GAS.
I'd really love to have this result accessible (the feature implemented).
About the workaround:
It works based on the experience that the web browser implementation of catch() function allows to access thrown errors by the script editor JS code parts.
In case an invalid input into a cell is rejected by a validation rule then the system will display an error message that is catchable by the user written GAS. In order to make it work first the reject value has to be set on the specified cell then its vale has to be re-entered (modified) then -right after this- calling the getDataValidation() built in function allows us to catch the necessary error.
Only single cells can be tested with this method as setCellValues() ignores any data validation restriction (as of today).
Disadvantages:
- The validity won't be necessarily re-checked for this function: checking cell validity right after the value is inserted into it. Therefore the result of this function might be faulty.
- The code messes up the history as cells will be changed - in case they are valid.
I've tested it successfully on both Firefox and Chromium.
function getCellValidity(cell) {
var origValidRule = cell.getDataValidation();
if (origValidRule == null || ! (cell.getNumRows() == cell.getNumColumns() == 1)) {
return null;
}
var cell_value = cell.getValue();
if (cell_value === '') return true; // empty cell is always valid
var is_valid = true;
var cell_formula = cell.getFormula();
// Storing and checking if cell validation is set to allow invalid input with a warning or reject it
var reject_invalid = ! origValidRule.getAllowInvalid();
// If invalid value is allowed (just warning), then changing validation to reject it
// IMPORTANT: this will not throw an error!
if (! reject_invalid) {
var rejectValidRule = origValidRule.copy().setAllowInvalid(false).build();
cell.setDataValidation(rejectValidRule);
}
// Re-entering value or formula into the cell itself
var cell_formula = cell.getFormula();
if (cell_formula !== '') {
cell.setFormula(cell_formula);
} else {
cell.setValue(cell_value);
}
try {
var tempValidRule = cell.getDataValidation();
} catch(e) {
// Exception: The data that you entered in cell XY violates the data validation rules set on this cell.
// where XY is the A1 style address of the cell
is_valid = false;
}
// Restoring original rule
if (rejectValidRule != null) {
cell.setDataValidation(origValidRule.copy().setAllowInvalid(true).build());
}
return is_valid;
}
Finally let me express my gratitude for all the great works of the developers! With their shared tools/system I can solve many logistical problems of my and others' lives... Cheers!
On this issue: Hurry up guys!
Description
We publish an app that provides localized date/time formats. The default configuration is localized for international english. Overrides for US English are present in /values-en-rUS/ string files. Before Android N, this ensures that users with an non-US English locale see international formats. This includes both en-GB users as well as users with a custom locale such as en-DE. However, on Android N all users with an en-* locale see US English values.
Example:
res/values/strings.xml: @string/dateformat = dd-mm-yyyy
res/values-en-rUS/strings.xml: @string/dateformat = mm/dd/yyyy
Before N:
- User with en-GB sees dd-mm-yyyy.
- User with en-US sees mm/dd/yyyy.
On Android N:
- Both users see mm/dd/yyyy.
A second case, with a much greater impact, concerns localization for Chinese. Nearly all zh-* locales except zh-CN use Traditional Chinese. If I write an app for the Chinese market, my default /values/ folder contains resources localized in Traditional Chinese. Overrides for Simplified are then present in the /values-zh-rCN/ folder.
This case would also break on Android N and all my users would see Simplified Chinese (values-zh-rCN) values.
The current change in the resource resolution algorithm impacts all apps that
- provide strings for their default language in res/values
- have any override for their default language with a region qualifier
It is also not possible to provide resources for the default language only in the values-xx folder (where 'xx' is the language code) since all resources must have a (fallback) value in the default configuration.
Examples:
res/values contains English strings (en)
res/values-en-rGB contains British English strings (en-GB) like "Colour"
workaround in N: duplicate all res/values resources into res/values-en
res/values contains Traditional Chinese strings (zh)
res/values-zh-rCN contains Simplified Chinese strings (zh-CN)
workaround in N: duplicate all res/values resources into res/values-zh
The main issue is that you currently cannot specify for which locale res/values/ is localized. If res/values could be configured to match a certain locale, like "en", you don't need to duplicate resources.
The attached sample project, APK and screenshots showcase the expected and actual behavior.
* Which Developer Preview (or Android Beta) build are you using? (for
example, NPC91K). See Settings > About phone > Build number
Android N Developer Preview 3 (NPD35K)
* What device are you using? (for example, Nexus 6P)
Emulator & Nexus 9 tablet
* What are the steps to reproduce the problem? (Please provide the
minimal reproducible test case.)
1. Have a resource with override value in eu-US configuration.
2. Set locale to en-GB (done programmatically in sample).
3. Resolve resource (@string/resolved_locale in sample).
* What was the expected result?
Expected resource to resolve to value in default configuration.
* What was the actual result?
Resource resolved to value in en-US configuration.
* Relevant logcat output.
(none)