Fixed
Status Update
Comments
am...@google.com <am...@google.com>
am...@google.com <am...@google.com> #2
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).
ca...@gmail.com <ca...@gmail.com> #3
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?
ki...@gmail.com <ki...@gmail.com> #4
@marc.art: The validation still validates within the UI itself; it's just that the results of the validation aren't available in code.
am...@google.com <am...@google.com> #5
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()!!
[Deleted User] <[Deleted User]> #6
This is a blocking feature for implementing a compliance changelog of only valid entries.
lo...@gmail.com <lo...@gmail.com> #7
Would definitely be useful.
t....@gmail.com <t....@gmail.com> #8
Can't see why this isn't already available. I hope it is added soon!
[Deleted User] <[Deleted User]> #9
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!
ad...@gmail.com <ad...@gmail.com> #10
I'm desperate for this method to be added! Please please please do it soonest possible! Thank you!
[Deleted User] <[Deleted User]> #11
Yes please implement this. I suppose the data could be checked against the data validation rules before setting the values, but that would really slow the write process down. Not to mention how much of a pain it would be to code all the validity checks to accommodate all the different possible data validation rules.
I've been starting to use Google Sheets to serve some of our company's data storage in place of data typically stored in a SQL database, but the hard data validation rules are an essential part to maintaining data integrity in the way that's possible with a SQL database.
I've been starting to use Google Sheets to serve some of our company's data storage in place of data typically stored in a SQL database, but the hard data validation rules are an essential part to maintaining data integrity in the way that's possible with a SQL database.
ch...@google.com <ch...@google.com> #12
Please add this. It will bring down so much bad code that I have put in place due to lack of this function.
[Deleted User] <[Deleted User]> #13
Please add this in asimple manner: isValid() true or false
ja...@gmail.com <ja...@gmail.com> #14
I would say the methis should be intiutive to read.
Range.dataValidate() :: [][]bool
Since we are doing DataValidations ...
Range.dataValidate() :: [][]bool
Since we are doing DataValidations ...
ch...@google.com <ch...@google.com> #15
I need it like a thirsty desert traveler needs a few drops of water.
[Deleted User] <[Deleted User]> #16
I need it like a few drops of water need hydrogen and oxygen
ch...@google.com <ch...@google.com> #17
Must I beg?
ar...@google.com <ar...@google.com> #19
Also require this, so i can track any validations fails centrally, without logging into each spreadsheet and sheet to check which is time consuming.
az...@gmail.com <az...@gmail.com> #20
OK Google... We know you can do it !
dg...@gmail.com <dg...@gmail.com> #21
Nearly 5 years later and still no change..
I don't think this will happen
I don't think this will happen
[Deleted User] <[Deleted User]> #22
+1
jo...@gmail.com <jo...@gmail.com> #23
It would be very cool
dr...@gmail.com <dr...@gmail.com> #24
+1
fa...@gmail.com <fa...@gmail.com> #25
+1
ps...@gmail.com <ps...@gmail.com> #26
+1
dr...@gmail.com <dr...@gmail.com> #27
+1
dr...@gmail.com <dr...@gmail.com> #28
+1
ps...@gmail.com <ps...@gmail.com> #31
+1
de...@gmail.com <de...@gmail.com> #32
+1
de...@gmail.com <de...@gmail.com> #33
+1
pr...@gmail.com <pr...@gmail.com> #34
+1 would be incredibly helpful to just have an ISVALID() formula that returns TRUE has a legit data validation value and FALSE if somebody entered a value into the cell that doesn't match a data validation value. Simple addition to Google Sheets that would make tons of people happy.
[Deleted User] <[Deleted User]> #35
+1 Hoping...
ay...@gmail.com <ay...@gmail.com> #36
This would be extremely helpful!
Strange that it is not yet implemented, or able to get error validation.
+1
Strange that it is not yet implemented, or able to get error validation.
+1
9g...@gmail.com <9g...@gmail.com> #37
+1
[Deleted User] <[Deleted User]> #38
Please add this
za...@gmail.com <za...@gmail.com> #39
+1
sh...@gmail.com <sh...@gmail.com> #40
+1
ja...@gmail.com <ja...@gmail.com> #41
+1
de...@gmail.com <de...@gmail.com> #42
+1
pe...@gmail.com <pe...@gmail.com> #43
+1
pe...@gmail.com <pe...@gmail.com> #44
+1
hu...@gmail.com <hu...@gmail.com> #45
+1
[Deleted User] <[Deleted User]> #46
+1
an...@gmail.com <an...@gmail.com> #47
+1
hi...@gmail.com <hi...@gmail.com> #48
+1
[Deleted User] <[Deleted User]> #49
+1
ha...@gmail.com <ha...@gmail.com> #50
+1
ri...@gmail.com <ri...@gmail.com> #51
+1
fa...@gmail.com <fa...@gmail.com> #52
Is somebody working on this one?
de...@gmail.com <de...@gmail.com> #53
I'm an experienced app developer and just starting w/ Appscript to customize my client's google sheets. I realized that this simple but critical function-- check if a cell value satisfies a data validation, or if satisfies the conditional formatting condition-- is missing!! Really surprised and disappointed. Tried to find a work-around but apparently I couldn't use some very useful built-in functions such as ISEMAIL within appscript. Lack of these features are quite discouraging to diving into Google Workspace Dev seriously. HOPEFULLY this feature will be RELEASE SOON!
sh...@google.com <sh...@google.com> #55
@co...@geeksconnect.com
" HOPEFULLY this feature will be RELEASE SOON!"
Been asked since 2016 with no significant progress.
I think there´s more chance of google redoing their whole platform services (which they do frequently) at this point that getting some of these basic stuff.
Would not hold my code waiting for it.
Best worst solution might be to write your validation as a conditional to a temporary (or permanent) cell and read the value of the result I guess...
Haven´t used this in a while just because it´s not practical.
" HOPEFULLY this feature will be RELEASE SOON!"
Been asked since 2016 with no significant progress.
I think there´s more chance of google redoing their whole platform services (which they do frequently) at this point that getting some of these basic stuff.
Would not hold my code waiting for it.
Best worst solution might be to write your validation as a conditional to a temporary (or permanent) cell and read the value of the result I guess...
Haven´t used this in a while just because it´s not practical.
za...@gmail.com <za...@gmail.com> #56
+1
dr...@gmail.com <dr...@gmail.com> #57
+1 & holding my breath.
sh...@google.com <sh...@google.com> #58
+1
sp...@gmail.com <sp...@gmail.com> #59
+!
sh...@google.com <sh...@google.com> #60
+1
ha...@gmail.com <ha...@gmail.com> #61
+1 as well!!
je...@gmail.com <je...@gmail.com> #62
+1
pr...@gmail.com <pr...@gmail.com> #63
+1
su...@gmail.com <su...@gmail.com> #64
+1
sh...@google.com <sh...@google.com> #65
+1
ny...@gmail.com <ny...@gmail.com> #66
+1
fa...@gmail.com <fa...@gmail.com> #67
+1
le...@gmail.com <le...@gmail.com> #68
+1
pi...@gmail.com <pi...@gmail.com> #69
+1
yi...@gmail.com <yi...@gmail.com> #70
+1
de...@gmail.com <de...@gmail.com> #71
+1
t....@gmail.com <t....@gmail.com> #72
+1
cn...@gmail.com <cn...@gmail.com> #73
za...@gmail.com <za...@gmail.com> #74
+1
da...@gmail.com <da...@gmail.com> #75
+1
[Deleted User] <[Deleted User]> #76
+1
ro...@gmail.com <ro...@gmail.com> #77
+1. Add isValid() method
an...@googlemail.com <an...@googlemail.com> #78
+1
Above suggested a good hack but "try{} catch(e){}" code section takes a long time ( >120 sec). If it processes 3 cells, then we get "Exceeded maximum execution time".
Need a solution from Google.
Above suggested a good hack but "try{} catch(e){}" code section takes a long time ( >120 sec). If it processes 3 cells, then we get "Exceeded maximum execution time".
Need a solution from Google.
sh...@google.com <sh...@google.com> #79
+1
fr...@gmail.com <fr...@gmail.com> #80
+1
It's unfortunate that after the better part of a decade since the original ask, it's pretty clear Google doesn't seem to have the time of day (or care?) to implement this pretty useful feature. Would enjoy having it nonetheless, so I'll continue the bump.
I checked out the Developer References for Apps Script, there is a DataValidation section that I thought would resolve my use-case, but unfortunately after some experimentation it failed to do the trick. I am using a Data Validation that rejects inputs that are not given in the dropdown menu, however I am using a query-based two-layer data validation dropdown system (in other words, my first drop down menu alters a query range that influences what options my second dropdown menu offers). When I change the selection of the first dropdown to something with new options in the second dropdown menu, the selected option remains even though it is invalid in the second dropdown menu. getAllowInvalid() returns "false" in that it will reject any input that fails validation, however when I use getCriteriaValues() to determine a true/false difference between the first menu (which should be valid and TRUE) and the second menu (which is invalid and should be FALSE) they both return TRUE.
Ergo, the currently implemented system is flawed and does not satisfy the simple requirements that my needs nor the needs of many on this 8-year old tracker. I will be looking into an onEdit script to blank out the second dropdown menu as a bandage fix, but it is exactly that: a bandage.
It's unfortunate that after the better part of a decade since the original ask, it's pretty clear Google doesn't seem to have the time of day (or care?) to implement this pretty useful feature. Would enjoy having it nonetheless, so I'll continue the bump.
I checked out the Developer References for Apps Script, there is a DataValidation section that I thought would resolve my use-case, but unfortunately after some experimentation it failed to do the trick. I am using a Data Validation that rejects inputs that are not given in the dropdown menu, however I am using a query-based two-layer data validation dropdown system (in other words, my first drop down menu alters a query range that influences what options my second dropdown menu offers). When I change the selection of the first dropdown to something with new options in the second dropdown menu, the selected option remains even though it is invalid in the second dropdown menu. getAllowInvalid() returns "false" in that it will reject any input that fails validation, however when I use getCriteriaValues() to determine a true/false difference between the first menu (which should be valid and TRUE) and the second menu (which is invalid and should be FALSE) they both return TRUE.
Ergo, the currently implemented system is flawed and does not satisfy the simple requirements that my needs nor the needs of many on this 8-year old tracker. I will be looking into an onEdit script to blank out the second dropdown menu as a bandage fix, but it is exactly that: a bandage.
sh...@google.com <sh...@google.com> #81
Google doesn't care. Share this on twitter... it wont matter. This project is dead. Do much potential lost. The API provided doesn't work as documented. Its broken and will never work. Stop using Google services.
zz...@gmail.com <zz...@gmail.com> #82
+1
sh...@gmail.com <sh...@gmail.com> #83
+1
why google doesn't care?
why google doesn't care?
sh...@gmail.com <sh...@gmail.com> #84
+1 cmon it has been almost 10 years
ss...@gmail.com <ss...@gmail.com> #85
+1
lo...@gmail.com <lo...@gmail.com> #86
+1...million
am...@gmail.com <am...@gmail.com> #87
+1
am...@google.com <am...@google.com>
pa...@gmail.com <pa...@gmail.com> #88
+100
we...@gmail.com <we...@gmail.com> #89
+1
kp...@gmail.com <kp...@gmail.com> #90
+100
sh...@google.com <sh...@google.com> #91
+++ would be useful. Want to make sure validation requirements are met before running
pa...@outlook.com <pa...@outlook.com> #93
+1
sh...@google.com <sh...@google.com> #94
We actually just noticed this issue ourselves and it is actually getting addressed right now in the BottomSheet code. Sorry about that. I have also communicated with the Android Material Design team to make sure other code doesn't need to be updated as well.
There isn't a public bug that I'm aware of that is tracking that precise work, but know that your specific issue will be fixed in an upcoming release of the Android Material Design library.
There isn't a public bug that I'm aware of that is tracking that precise work, but know that your specific issue will be fixed in an upcoming release of the Android Material Design library.
pa...@outlook.com <pa...@outlook.com> #95
I appreciate the update. Please let us know when it is fixed in this thread since there isn't a dedicated thread for that specific bug.
me...@google.com <me...@google.com> #96
Material Design Library is actively updating this for BSB, you may track the progress here: b/128698671
yq...@gmail.com <yq...@gmail.com> #97
I'm having the same problem with a RecyclerView in MotionLayout, so I created issue for it.
https://issuetracker.google.com/issues/128914828
pv...@gmail.com <pv...@gmail.com> #98
I'm using recycler view in bottom sheet and this issue is not fixed.
recyclerview-v7:28.0.0
recyclerview-v7:28.0.0
vz...@gmail.com <vz...@gmail.com> #99
we are using 28.0.0, the problem is still there.
sh...@google.com <sh...@google.com> #100
28.0 was the last release of RecyclerView in the support library packages and should be considered deprecated:
https://developer.android.com/reference/android/support/v7/widget/RecyclerView
The AndroidX version of RecyclerView picks up where that version left off and is currently supported:
https://developer.android.com/jetpack/androidx/releases/recyclerview
The AndroidX version of RecyclerView picks up where that version left off and is currently supported:
sh...@google.com <sh...@google.com> #101
See comment #92 .
sa...@gmail.com <sa...@gmail.com> #102
For those having problems with bottom sheet, you need to bump your material version to at least 1.1.0-alpha05
[Deleted User] <[Deleted User]> #103
I am getting same issue with androidx.recyclerview:recyclerview:1.1.0.
[Deleted User] <[Deleted User]> #104
Bump, same issue on androix.recyclerview:recyclerview:1.1.0.
sh...@google.com <sh...@google.com> #105
I will need a minimal repro case for me to be able to look into this any further. Thanks.
te...@gmail.com <te...@gmail.com> #106
One was given to you in the first post almost 2 and a half years ago. *facepalm*. I wonder why us developers even bother logging bugs with Google anymore...
sh...@google.com <sh...@google.com> #107
What I meant was that I believe this bug to be fixed given all of the changes that have been done to fix it. In making the changes, I looked for a holistic solution and thus think it was indeed holistic, but if some still find that it does not work, it would be very helpful for me to see a demonstration.
ma...@gmail.com <ma...@gmail.com> #108
Also reproed while using MotionLayout :(
ma...@gmail.com <ma...@gmail.com> #109
Also reproed while using MotionLayout :(
but it's working fine if I double tap it quickly
but it's working fine if I double tap it quickly
sh...@google.com <sh...@google.com> #110
I would need more information about an issue with MotionLayout. Can you please file a separate bug with details including a minimal repro case?
bo...@gmail.com <bo...@gmail.com> #111
Updated my AndroidX dependencies and fixed the issue with appbarlayout/bottomnav/recyclerview. Did not try out MotionLayout
Description
Version used: 26.0.2
Theme used: Theme.AppCompat.NoActionBar
Devices/Android versions reproduced on: AVD API 25
I just upgraded to API 26 and support library 26.0.2. But I found that my RecyclerView items is not clickable right after the scrolling. If you wait for a second, it will work. But if you click the item immediately, it won't. Even if the RecyclerView is not scrolling at all(e.g. has scrolled to the top).
When I downgraded to support library 25.4.0 everything goes fine again. The key point is that my RecyclerView is in a CoordinatorLayout and has a SCROLL_FLAG_SCROLL flag on my Toolbar of the AppBarLayout. If I don't use this flag, then this problem will disappear.
I've tried to add focusable="false" to the CoordinatorLayout but still had no luck.
Is there any way to disable this behavior? Because it's really annoying to click twice to trigger the click event.
I think the problem is the scrollState of the RecyclerView. When it's stopped scrolling, it's not changed to SCROLL_STATE_IDLE immediately. Looking into the source code of RecyclerView, I found there's a ViewFlinger controlling the scroll state. When I fling down to scroll to the top, it's not calling setScrollState(SCROLL_STATE_IDLE) immediately. Instead, it wait for a while to trigger this method. The more fast I fling, the more time I need to wait. It just like the RecyclerView is still scrolling in the background. Because the scroller.isFinished() doesn't return true right after the RecyclerView stop scrolling when it touched the top. Maybe it's a bug of the RecyclerView when it's in a CoordinatorLayout.
The attachment is a screen recording of this behavior.
<android.support.design.widget.CoordinatorLayout
android:id="@+id/coordinateLayout"
android:layout_width="match_parent"
android:layout_height="match_parent">
<android.support.design.widget.AppBarLayout
android:id="@+id/fragmentAppBar"
android:layout_width="match_parent"
android:layout_height="wrap_content"
app:elevation="0dp"
android:background="@null">
<include
android:id="@+id/dynamicActionBarHolder"
layout="@layout/dynamic_action_bar"/>
</android.support.design.widget.AppBarLayout>
<android.support.v4.widget.SwipeRefreshLayout
android:id="@+id/pullToRefreshMailRecycler"
android:layout_width="match_parent"
android:layout_height="match_parent"
app:layout_behavior="@string/appbar_scrolling_view_behavior">
<android.support.v7.widget.RecyclerView
android:id="@+id/mailRecyclerView"
android:layout_width="match_parent"
android:layout_height="match_parent"/>
</android.support.v4.widget.SwipeRefreshLayout>
</android.support.design.widget.CoordinatorLayout>
layout/dynamic_action_bar.xml
<FrameLayout xmlns:android="
xmlns:app="
android:layout_width="match_parent"
android:layout_height="?attr/actionBarSize"
app:layout_scrollFlags="scroll"
android:clickable="true"
android:background="?theme.dynamicActionBarBackground">
<ImageButton
android:id="@+id/dynamicAcbMenuIcon"
android:layout_width="?attr/actionBarSize"
android:layout_height="?attr/actionBarSize"
android:background="@drawable/article_explicit_button_background"
android:src="?theme.menuIcon"/>
<RelativeLayout
android:id="@+id/dynamicAcbTitleHolder"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginStart="?attr/actionBarSize"
android:paddingTop="5dp"
android:paddingBottom="5dp"
android:paddingEnd="5dp"
android:layout_gravity="center_vertical">
<TextView
android:id="@+id/dynamicAcbTitle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:singleLine="true"
android:textSize="@dimen/action_bar_title_portrait_size"
android:textColor="?theme.listItemTitleColor"
android:ellipsize="end"
android:text="ActionBar"/>
<TextView
android:id="@+id/dynamicAcbSubtitle"
android:layout_below="@+id/dynamicAcbTitle"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:singleLine="true"
android:textSize="@dimen/action_bar_subtitle_portrait_size"
android:textColor="?theme.listItemTitleColor"
android:ellipsize="end"
android:text="If you say so"/>
</RelativeLayout>
</FrameLayout>