Help with Custom Data Validation rule via app script in Google Sheets

[wiklendt]

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.

 

[Uncle Gizmo]

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...9/stream/d873e8a3-d535-4209-89f7-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...

 

[wiklendt]

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.

 

[isladogs]

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.

 

[wiklendt]

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