Go Back   Access World Forums > Apps and Windows > Other Software

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-23-2018, 06:08 AM   #1
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Question Help with Custom Data Validation rule via app script in Google Sheets

Hi everyone,

Our school is moving to G Suite. I want to create/modify a script in one of my google sheets which will add a once-off validation rule to a column in one of my sheets.

For each cell in column C (starting with C2), return a range from the same row but using columns I2:AA2 as the dropdown list, then move on to the next row. the row number will be the same across the columns as you go down (C3 = I3:AA3, C4 = I4:AA4, etc down to 1000 (end of the sheet for now))

i have always been terrible at the loop codes, and google scripts are brand new to me, so was hoping someone could point me in the right direction.

i have found the google documentation regarding data validation scripts, just not sure how to customise it for my own sheet.

Most of the tutorials and scripts I've been able to find have been to run a datavalidation script onEdit of a cell, but i find they are very slow for me.

Any help appreciated.

Thx
Agnieszka

p.s. for those of you who have been wondering where i disappeared to, i have switched careers to teaching in a secondary school (science, physics, information and software technologies)!! it was supposed to improve my work-life balance but it is the hardest, most demanding, most time-sucking job that I have ever come across in my life!! So, many (all??) of my hobbies are on the backburner for the moment. I am enjoying the challenge, though, and the cliché that it is rewarding is true. I've been told things will settle down for me after about 3(!!!!) years. Currently doing an appalling job at not burning-out.

__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 06-26-2018, 11:08 AM   #2
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,007
Thanks: 461
Thanked 889 Times in 844 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Help with Custom Data Validation rule via app script in Google Sheets

Very nice to hear about your new venture! I hope the 3 years goes nice and quickly and you can settle down! I have dabbled a bit with Google code, but I'm very much a beginner so I doubt if I can be of much help. However Bruce McPherson has a Google group which is excellent here:- https://plus.google.com/communities/...7-af71a8e08ac9 although I'm not too sure that there are very many experts answering questions there yet.

There's also Ben Collins who has done some excellent videos on Google sheets and he does the 30 day challenge course which is free and very very good!

See Here:-
https://www.benlcollins.com/

Free 30 Day Course HERE:- https://courses.benlcollins.com/p/advanced30

I personally would like to see a nice Google App Script Forum within Access World Forums because I think it will help extend the life of the site. But you never know Microsoft Access may well Out Live Google app scripts! They all seem to keep dropping stuff and starting new stuff willy-nilly with little regard to the people that put the time and effort into learning it...
__________________
Code:
                 |||||
               @(~Ô^Ô~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)

Last edited by Uncle Gizmo; 06-26-2018 at 11:14 AM. Reason: added url
Uncle Gizmo is offline   Reply With Quote
The Following User Says Thank You to Uncle Gizmo For This Useful Post:
wiklendt (06-26-2018)
Old 06-26-2018, 01:34 PM   #3
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Help with Custom Data Validation rule via app script in Google Sheets

Awesome suggestions,thanks Uncle Gizmo!

Yes i agree, i actually expected to see some google stuff here on AWF especially as apps script seems googles equivalent of VBA, but, of course, it's not Access.

__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Old 06-26-2018, 01:56 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,293
Thanks: 105
Thanked 2,492 Times in 2,288 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Help with Custom Data Validation rule via app script in Google Sheets

Hi

As an ex-teacher, I can confirm from personal experience that the first 37 years were the hardest ....after which I retired!

Good luck with your new career and I hope you manage to avoid getting ill in each and every school holiday.
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
isladogs is offline   Reply With Quote
Old 06-26-2018, 06:40 PM   #5
wiklendt
i recommend chocolate
 
wiklendt's Avatar
 
Join Date: Mar 2008
Location: Cobar, Australia
Posts: 1,746
Thanks: 13
Thanked 20 Times in 18 Posts
wiklendt is a jewel in the rough wiklendt is a jewel in the rough wiklendt is a jewel in the rough
Re: Help with Custom Data Validation rule via app script in Google Sheets

Quote:
Originally Posted by ridders View Post
Hi

As an ex-teacher, I can confirm from personal experience that the first 37 years were the hardest ....after which I retired!

Good luck with your new career and I hope you manage to avoid getting ill in each and every school holiday.
ha ha ha, i'm doing all right so far

__________________

Agnieszka


Access 2016 x32 | Win10 x64 Home
_________________________________
! a tutorial on
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
wiklendt is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Interaction with google sheets eatraas Modules & VBA 3 05-13-2018 12:14 PM
Data validation rule in VBA vinwin06 Modules & VBA 1 11-28-2013 03:05 AM
Validation Rule for data entry james_halliwell General 5 11-17-2011 04:57 AM
Validation rule - data must be 'not' be between two dates Flint Forms 1 03-25-2004 12:14 PM
Combo box - custom validation rule JiTS Forms 8 01-09-2004 05:02 PM




All times are GMT -8. The time now is 10:09 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World