I don't know if I ever added a comment but in case I didn't I want to say this is by far the best script for "Dependent Dropdowns". I am a Top Contributor for Sheets at the Google Product Forums and I have helped many users adapt their sheets to use this script and also point them to these videos showing them where I got the script as it is a really good source to learn GAS. Thanks ever so much for providing this and I know it has (and will) helped a lot of people. James/mreighties :)
Correction: as mentioned in one of the comments by Derek Morgan it's also necessary to make sure the script runs only on the Main tab, so if statement needs to be modified to if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getName() == "Main"){
This helped me out a ton. I was able to use this script for a personal training program. Where you select the body part you want to exercise and then the second drop-down menu is exercises that use that body part.
Bhai, Luv 4m India. May God bless u. I am a Govt. Servant and ur tips helped me a lot for real-time information tracking/gathering during COVID-19. Keep it up. Thank You very much.
Wow! Problem solved so elegantly. I have little to no App Script experience and was able to apply this solution swiftly. And I love the edit recommended to apply across multiple sheets, which worked great. I'm no a convert
Programming strategies such as this contribute to changing the world in Newton style, Nikola Tesla and even Albert Einsten. An effort like his is equivalent to an increase in his genius. Thank you for giving me the opportunity to learn from your wonderful work.
One thing to notice is when you clear couple cells at once (i.e. by dragging or selecting with shift) only the first dependent dropdown list is cleared. Excellent video! Thank you so much! This video solved my problem!
4 years too late for you but for anyone else who may come across this: Change onEdit to onEdit(e) to get the event object that triggered it use e.range to get the full range that was edited use a for loop and loop from i = 0 to e.range.getNumRows() apply the validation to a new getRange(e.range.getRow() + i , dropdown list column number) will obviously need to do checks to make sure edits are within your preferred row range, correct column etc etc. If you have a good grasp of programming then you can go one step further for efficiency and copy the edited range into a 2d array using .getValues(), apply the validation to each appropriate index in the 2d array then set the edited range to the 2d array validations created using .setValues() as it greatly reduces the number of api calls.
I had a couple little snags but totally got this working for a budget sheet I made and it's killer. Thank you so much for putting this together. I am a complete N00B at Scripts but this was still easy enough to follow. Great work.
This will help me a ton once I go back and do basic scripts. LOL A friend said it's easy, but not yet. :) I like the way you explain things, btw; it's clear and obvious, building step by step. I can easily see what you're doing, even not knowing scripts yet.
You are the best teacher I ever had even in spanish, God bless you for your amazing job...
6 років тому
Excellent tutorial! I would be super excited about a followup video about how to deal with another column/criteria - ie Brand>Model>Accessorypackage - assuming each car model comes with a few different accessory set options. I have done this successfully with formulas, but ends up being really complex - keen to see how it can be solved with apps script.
brother thank you for superb knowledge. but i got an issue from this one. if we wanna input more than 15 rows (total 14 contents a row) on lists sheet, that doesn't exist on dropdown. would you explain this? thank youuu
This is an excellent tutorial, thank you. What if my validation data is not in a consolidated table on a single page but rather dispersed thorughout several sheets in my document? I have a master category list and next to every cateogry name I have entered a range name where the data for that category lives. I imagine it must be possible to use indirects or lookups in GAS but I'm not sure how I'd go about it.
Hello! Thank you for this tutorial! It was truly a great help and you have explained it so well. On a side note, I think, having set the data validation of the first column to "Reject Input" would be much easier, as it will prevent someone on typing that's not on the list. hehe. But yeah, just a suggestion. Thanks again! ^^
Thanks a ton for these!!! I followed both parts carefully and was able to apply to my cases with different tab names, columns and rows. Specifically, I used it for an event budget calculation with hotels and hotel room types. How would I go about implementing a third row with prices that get filled in automatically based on the first two choices? For example, if Column A (Hotel) is X and Colum B (Room Type) is Y, use a specific price.
One thing to notice is when you clear couple cells at once (i.e. by dragging or selecting with shift) only the first dependent dropdown list is cleared. Is there a work around for it?
First thanks! How can I add protection that they can not edit the second cell before the first one (Meaning that you can not write in column B before you fill column A)
Thank you so much sir, your videos are very, VERY much appreciated. Excellent quality in the contents, the explanations, the video. I'm learning a lot.
How can you modify this to produce the "Model" in two columns side by side? I want the same exact thing just two columns of the options next to eachother
Is there any way to combine this script with one for automatic sorting by the Statuses? So for example, say we want BMW to be first, then Audi etc.? Otherwise, this works amazing, thank you so much for this video!
Hi, thanks for sharing this! Extremely useful. I am new to script editor. Could you let me what would be the process to create similar thing on another sheet in the same google sheet.
hello good afternoon, how to avoid that the list is limited to the first column, if I want to implement it in different sheets and different column as it would be.
i was having trouble getting this to work, so i thought maybe i messed up. but i checked my code against yours, and they were the same. so i thought I'm just missing something, so i copied and pasted your code to replace mine. still didn't work. so i went back through and reread it like 5 times, then i finally realized that i didn't name my main sheet the same as you did, but while listening to you i put it as Main in the code. i feel like such an idiot right now lol. but at least i got it working. thanks a lot for these vids, they really helped me out.
What if we use 'if else' statement for checking activeCell: if(makeIndex != 0){ var validationRange = datass.getRange(3, makeIndex, datass.getLastRow()); var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build(); activeCell.offset(0, 1).setDataValidation(validationRule); } else { activeCell.clearContent(); }
I used to have problem with loading the dependent list for dropdown (second column dropdown of Main) which it could not load the list and just showed "loading". It was happenned when I deleted unnecessary empty columns and rows for reducing the file size purpose. After several trials, I found that we should put one empty row in the dependent list after the column list with the most items. Let say the column 'E' has the most row items (from 'E3:E10'), then add single empty row after it (row 11).
@@ExcelGoogleSheets What if I wanna make the "makes" in rows, not in column? My data is presented in column 1 as the main and depended in the next columns: Province > City Jatim > Sby, Mlg, Mdn Jateng > Smg Jabar > Bdg, Crb Because if I changed this line: makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues() into: makes = datass.getRange(1, 1, datass.getLastRow(), 1).getValues() it show array values "[[Jatim],[Jateng],[Jabar]]". And as I used "makes[0]" than only showed [Jatim] instead of the values "[Jatim, Jateng, Jabar]"
Hello Thanks for your efforts I just need your help I have already make the dynamic drop list and it’s working fine but now the problem and I need to hand another drop list in the same sheet and I don’t know how to edit the script to make it read the data from a different source Thanks again
Hi :) great video, thanks. Is it possible to have the options of the column B (Model) faster? not having to wait those seconds after choosing the "Make" in column A?
Thank you very much for your great videos ! I have a question : Can we update a cell automatically (A3 for example) in Model column when we replace the A3 model by a new Audi model in the lists tab ? I hope I was clear. Thank you for advance for your response
This tutorial is awesome and very useful for a beginner like me who has zero knowledge on programming and scripts. But when I save the sheets in excel format, the items on the drop down list on the first column remains but when changed, the items on the second column stays the same. Is there anything I can do about this?
It can be half managed by sheet.getActiveRange and loop the main solution for each cell in active range, but it will not work if you paste without preselect all affected cells, but if we will use event.range it will work.
when I try to rewrite the script and use another drop-down cell in the same sheet, why am i getting error to the first data validations.? can you help me ?
I have to correlate 5 column data in a selective drop down sheet. But i couldn't get a answer..
4 роки тому
Hi, Thanks a lot for this, really well structured and explained, save me a lot of time! Question: How can I block user from typing something in column B of main tab? By the way, why did you allow invalid data in column A, why didnt you set this to reject input? Just wondering... Thanks
Dear Sir, Firstly,I thank you for the remarkable videos you have made. Everybody is learning a lot from them. I have one query, I hope you will give me a solution for that. In the" Dependent Dropdown List for Entire Column - Apps Script " I need to make a workbook that contains more than 1 main sheet, which linked with the Lists sheet. E.g I like to make 15 main sheets linked with one list sheet. is it possible?
Thank you so much for this video! I followed the steps and my google sheet drop down menus are working great. Just one question: Is it possible to reject input that doesn't fall within the range, instead of just showing a warning message?
I figured it out adding .setAllowInvalid(false) to the validation rule. The validation rule will look like this: var validationRule = SpreadsheetApp.newDataValidation() .setAllowInvalid(false) .requireValueInRange(validationRange).build(); activeCell.offset(0, 1).setDataValidation(validationRule);
This video is just perfect for what I really need. I only have a problem on how I can add another dependent column. I am currently using named ranges which does not allow spaced between the words/values I have. Can anyone help me how to add another column. I've been trying to play with the codes since yesterday but I'm just not getting it. Thank you in advance!
What if we type wrong value in column 1, it clears the data validation rule and value. But what happens when we do undo. Will it get the previous value. Hope you understand my question
Probably I am very late to comment and had go through few comments and it seems this question was not raised. But if it is already answered. Could you be kind to reiterate here. Thanks
This is beautiful. Thank you. Can this be done in Excel? (I assume no). Also, if possible, please add comments as you go along in your thinking-out-loud process. Your thinking-out-loud is very helpful in understanding what is going on. It is just so that the code becomes hazy for those of us who are watching, after you move forward from one line to another, or as you go back and forth in logic-building. I think your comments written out along-the-way, could be helpful.
Logging does not work for me, in addition, the script suddenly stopped producing the 999 in my selected cell with no changes. Have no idea WTH is going on
How can the script be adjusted to create 2 or more columns of drop-downs? Right now if I make a choice in column A then script will create a drop-down in column B populated with items based on choice made in column A. I would like to instead create 2 or even more drop-down columns based on drop-down in A. Like if I make a choice in column A then the script creates 2 drop-downs in columns B and C populated with same choices. This is for a user I am helping who wants to choose a league in column A and then choose 2 teams from the drop-downs in B and C who are in the same league. Thanks, James :)
Thank you soo much! The clearDataValidation() is not working for me... I mean... it's working, remove the validation, but a few moment latter put it an emtpty select. I don't know why.
Thank you for the tutorial! It is great. However, my clearDataValidations() is not working. clearContent() is working fine but not the clearDataValidations(). Please help! T.T
Excelent, There is a way to modify/update information when you use a query or vlookup? Example The Doctor have a database from his patients. When the patient come back for a checkup he pulls the information just with the patient name in a nice dashboard. How can I add or modify information from the dashboard?? Dashboard = just a nice sheet I google sheets I was thinking to put aside a data validation list of the category (age, weight, comments....) Then use two scripts one for adding information to the cell (like concatenating ) and another for re-write the content of a cell. I have no idea how to go backwards to the dashboard to the database
You can't use QUERY or VLOOKUP for this. Unless there is an addon you can use, your only option would be Apps Script and quite a bit of programming work.
Learn Google Spreadsheets thanks, do you have a video where you can find an specific cell in different sheet, like index and math but in script. If I have the name of the patient (row) and the category that I want to modify (Column) . Then I use get value from the dashboard comentes and then pass that value to the row,column in the database I have been watching a lot of your videos but I don’t remember watching something like that
I don't know if I ever added a comment but in case I didn't I want to say this is by far the best script for "Dependent Dropdowns". I am a Top Contributor for Sheets at the Google Product Forums and I have helped many users adapt their sheets to use this script and also point them to these videos showing them where I got the script as it is a really good source to learn GAS.
Thanks ever so much for providing this and I know it has (and will) helped a lot of people.
James/mreighties :)
Hi James,
Could you help me)
I would like to ad two dependent dropdown lists in one sheet, how could i modify this script to get that?
Thanks a lot
Alexandru Pușcaș
Hello
I have the same problem
Did you solve it ?
Correction: as mentioned in one of the comments by Derek Morgan it's also necessary to make sure the script runs only on the Main tab, so if statement needs to be modified to
if(activeCell.getColumn() == 1 && activeCell.getRow() > 1 && ss.getName() == "Main"){
So can we have the same script used for different tabs?
This helped me out a ton. I was able to use this script for a personal training program. Where you select the body part you want to exercise and then the second drop-down menu is exercises that use that body part.
Bhai, Luv 4m India. May God bless u. I am a Govt. Servant and ur tips helped me a lot for real-time information tracking/gathering during COVID-19. Keep it up. Thank You very much.
Wow! Problem solved so elegantly. I have little to no App Script experience and was able to apply this solution swiftly. And I love the edit recommended to apply across multiple sheets, which worked great. I'm no a convert
Programming strategies such as this contribute to changing the world in Newton style, Nikola Tesla and even Albert Einsten. An effort like his is equivalent to an increase in his genius. Thank you for giving me the opportunity to learn from your wonderful work.
Thank you for listening to your viewers and followers. You are awesome!
I'll watch out for your future tutorials. More power to you!
Best google sheet editor You have all our love and encouragement
Though this is a kind of an old video, but yet it is the best practice I ever had for dependent drop-down menus. Love it.
One thing to notice is when you clear couple cells at once (i.e. by dragging or selecting with shift) only the first dependent dropdown list is cleared. Excellent video! Thank you so much! This video solved my problem!
4 years too late for you but for anyone else who may come across this:
Change onEdit to onEdit(e) to get the event object that triggered it
use e.range to get the full range that was edited
use a for loop and loop from i = 0 to e.range.getNumRows()
apply the validation to a new getRange(e.range.getRow() + i , dropdown list column number)
will obviously need to do checks to make sure edits are within your preferred row range, correct column etc etc.
If you have a good grasp of programming then you can go one step further for efficiency and copy the edited range into a 2d array using .getValues(), apply the validation to each appropriate index in the 2d array then set the edited range to the 2d array validations created using .setValues() as it greatly reduces the number of api calls.
This Script is great as this covers what users would like to do as far as multiple options down a column. Thanks for covering this option.
I had a couple little snags but totally got this working for a budget sheet I made and it's killer. Thank you so much for putting this together. I am a complete N00B at Scripts but this was still easy enough to follow. Great work.
THANKS!! ,
2 days searching the internet. I finally found the solution
lol, you found it!
This will help me a ton once I go back and do basic scripts. LOL A friend said it's easy, but not yet. :) I like the way you explain things, btw; it's clear and obvious, building step by step. I can easily see what you're doing, even not knowing scripts yet.
You are the best teacher I ever had even in spanish, God bless you for your amazing job...
Excellent tutorial! I would be super excited about a followup video about how to deal with another column/criteria - ie Brand>Model>Accessorypackage - assuming each car model comes with a few different accessory set options. I have done this successfully with formulas, but ends up being really complex - keen to see how it can be solved with apps script.
THE BEST DEPENDENT DROPDOWN LIST
brother thank you for superb knowledge.
but i got an issue from this one.
if we wanna input more than 15 rows (total 14 contents a row) on lists sheet, that doesn't exist on dropdown.
would you explain this? thank youuu
Another awesome lesson that solves a very practical problem. Thanks for sharing, and please keep them coming!
Hey sir, I love your tutorials they are detailed and accurate. However, "I AM GETTING LOADING" for my second column. Any assistance?
This is an excellent tutorial, thank you. What if my validation data is not in a consolidated table on a single page but rather dispersed thorughout several sheets in my document? I have a master category list and next to every cateogry name I have entered a range name where the data for that category lives. I imagine it must be possible to use indirects or lookups in GAS but I'm not sure how I'd go about it.
Hello! Thank you for this tutorial! It was truly a great help and you have explained it so well. On a side note, I think, having set the data validation of the first column to "Reject Input" would be much easier, as it will prevent someone on typing that's not on the list. hehe. But yeah, just a suggestion. Thanks again! ^^
Good point.
Still an awesome video but agreed... Video ends at about 13 minutes if you do.
Thanks man... Thanks for making my work more Better
datass.
My holiday's Off is worth it because of this. Thank you. and I'm ready to explore more in your channel. #2021
Thank You!
Thanks a ton for these!!! I followed both parts carefully and was able to apply to my cases with different tab names, columns and rows.
Specifically, I used it for an event budget calculation with hotels and hotel room types.
How would I go about implementing a third row with prices that get filled in automatically based on the first two choices?
For example, if Column A (Hotel) is X and Colum B (Room Type) is Y, use a specific price.
Perfect! That's exactly what I was working on.
Great!
One thing to notice is when you clear couple cells at once (i.e. by dragging or selecting with shift) only the first dependent dropdown list is cleared. Is there a work around for it?
Thanks a lot .. you have the logic and capability .. it was so useful
Believe it or not, right now im typing with my feet, bc im using my hands to clap! You deserve my sister bro!
This only took 3 tries and 6 hours but I figured it out!!!
THANK YOU this worked, and I learned a ton!
Thank-You - My very First Script! Much Appreciated.
First thanks!
How can I add protection that they can not edit the second cell before the first one
(Meaning that you can not write in column B before you fill column A)
Awesome! I followed all 3 of your instruction video's:
And I have it all working :)
Great video. Helped me a lot.
Thank you so much sir, your videos are very, VERY much appreciated. Excellent quality in the contents, the explanations, the video. I'm learning a lot.
It is perfect! How to do the same for 3 dependent lists and more?
How can you modify this to produce the "Model" in two columns side by side? I want the same exact thing just two columns of the options next to eachother
You are a brilliant teacher !! Many thanks cool way to help me with Apps Script
Outstanding! You really helped us out
Great!
Thanks a lot!! This is super helpful
Thanks dude, you are great, from India
👍
Sir can you please help me to create google form with dependat drop down list for Country, State, District, Taluka etc.
Unable to find the solution
Is there any way to combine this script with one for automatic sorting by the Statuses? So for example, say we want BMW to be first, then Audi etc.? Otherwise, this works amazing, thank you so much for this video!
How would you hide a value, in the Lists tab, from the model dropdown, if you select it within the Main tab? Thanks.
That was amazing bro, you got it
Hi, thanks for sharing this! Extremely useful. I am new to script editor. Could you let me what would be the process to create similar thing on another sheet in the same google sheet.
hello good afternoon, how to avoid that the list is limited to the first column, if I want to implement it in different sheets and different column as it would be.
i was having trouble getting this to work, so i thought maybe i messed up. but i checked my code against yours, and they were the same. so i thought I'm just missing something, so i copied and pasted your code to replace mine. still didn't work. so i went back through and reread it like 5 times, then i finally realized that i didn't name my main sheet the same as you did, but while listening to you i put it as Main in the code. i feel like such an idiot right now lol. but at least i got it working. thanks a lot for these vids, they really helped me out.
Could you please make a video on how to run two or multiple scripts in one Google sheet. I could not find any video on UA-cam. Thanks in advance
You don't have to write your code in a new line to popup the auto-completion list, just press: Ctrl + space
I know, but I can't help it :)
What if we use 'if else' statement for checking activeCell:
if(makeIndex != 0){
var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
activeCell.offset(0, 1).setDataValidation(validationRule);
} else {
activeCell.clearContent();
}
I used to have problem with loading the dependent list for dropdown (second column dropdown of Main) which it could not load the list and just showed "loading". It was happenned when I deleted unnecessary empty columns and rows for reducing the file size purpose.
After several trials, I found that we should put one empty row in the dependent list after the column list with the most items. Let say the column 'E' has the most row items (from 'E3:E10'), then add single empty row after it (row 11).
Instead of makes[0] convert array of arrays to a regular array makes.map(function(r){ return r[0] });
Also I have improved version of this uploaded ua-cam.com/video/s-I8Z4nTDak/v-deo.html
I love this brilliant script and the easily-understanding presentation method of yours.
By the way, what does square brackets in "makes[0]" is for?
It get the first element in the array which is the essentially the first row.
@@ExcelGoogleSheets What if I wanna make the "makes" in rows, not in column?
My data is presented in column 1 as the main and depended in the next columns:
Province > City
Jatim > Sby, Mlg, Mdn
Jateng > Smg
Jabar > Bdg, Crb
Because if I changed this line:
makes = datass.getRange(1, 1, 1, datass.getLastColumn()).getValues()
into:
makes = datass.getRange(1, 1, datass.getLastRow(), 1).getValues()
it show array values "[[Jatim],[Jateng],[Jabar]]".
And as I used "makes[0]" than only showed [Jatim] instead of the values "[Jatim, Jateng, Jabar]"
You're a life saver. Thank you!!
Hello
Thanks for your efforts
I just need your help I have already make the dynamic drop list and it’s working fine but now the problem and I need to hand another drop list in the same sheet and I don’t know how to edit the script to make it read the data from a different source
Thanks again
Hi :) great video, thanks. Is it possible to have the options of the column B (Model) faster? not having to wait those seconds after choosing the "Make" in column A?
How can I edit the columns places in the script? Lets say the drop list is in Column I and the values should appear in column O
have any video of Dependent Dropdown List for Apps Script form- page.html.
Thanks for this! Any idea how to get rid of the drop down arrow? I'm using clearDataValidations and it removes the options but not the arrow.
Can this be adapted to be used for a google form getting its data values from a google sheet?
Thank you very much for your great videos !
I have a question :
Can we update a cell automatically (A3 for example) in Model column when we replace the A3 model by a new Audi model in the lists tab ?
I hope I was clear.
Thank you for advance for your response
Great work sir
how to import this google spreadsheet data in google form?
I just wanted to know if there is a way to permanently remove the dropdown arrow from the cells on column B. Thanks for the tutorial!
This tutorial is awesome and very useful for a beginner like me who has zero knowledge on programming and scripts.
But when I save the sheets in excel format, the items on the drop down list on the first column remains but when changed, the items on the second column stays the same. Is there anything I can do about this?
I need to add 20 main sheets linked with the lists sheet. is it possible?
Hello! Thank you for an example! But how would you implement such thing in case you edit(copy-paste) one or multiple cells?
It can be half managed by sheet.getActiveRange and loop the main solution for each cell in active range, but it will not work if you paste without preselect all affected cells, but if we will use event.range it will work.
I used this script (and it works!) but now it's affecting all my other sheets, how can I use this for one specific sheet?
add to your if statement
&& SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getSheetName() == "yousheetname"
How do you add rows from here? I can't do it because it says it's protected. Help please
Awasome tutorial! Auth thanks for ur job!
👍
when I try to rewrite the script and use another drop-down cell in the same sheet, why am i getting error to the first data validations.? can you help me ?
What about having dropdown menus with multiple selection choices?
Amazing. This was so helpful
Thanks man, this video is great! Well done!
Thanks, it is what i needed. Only one thing in this example is missing: link to live-demo (With locked edition, available to copy);
I have to correlate 5 column data in a selective drop down sheet. But i couldn't get a answer..
Hi,
Thanks a lot for this, really well structured and explained, save me a lot of time!
Question: How can I block user from typing something in column B of main tab?
By the way, why did you allow invalid data in column A, why didnt you set this to reject input? Just wondering...
Thanks
And thank you so much!!! Your script is so helpful!
Gotta love the variable name... datass:D great video btw
Pretty Cool Stuff there Guy!
Question though no related to thus Video.
How would you tackle Auto Extending a Form?
How to do this if I add one more column on the "Main Tab" i want to add a "Status" header on C Column
Would you know of how to duplicate this whole thing? That I can have two more columns with different categories and subcategories?
Dear Sir,
Firstly,I thank you for the remarkable videos you have made. Everybody is learning a lot from them. I have one query, I hope you will give me a solution for that. In the" Dependent Dropdown List for Entire Column - Apps Script " I need to make a workbook that contains more than 1 main sheet, which linked with the Lists sheet. E.g I like to make 15 main sheets linked with one list sheet. is it possible?
Thank you so much for this video! I followed the steps and my google sheet drop down menus are working great. Just one question: Is it possible to reject input that doesn't fall within the range, instead of just showing a warning message?
I figured it out adding .setAllowInvalid(false) to the validation rule. The validation rule will look like this:
var validationRule = SpreadsheetApp.newDataValidation()
.setAllowInvalid(false)
.requireValueInRange(validationRange).build();
activeCell.offset(0, 1).setDataValidation(validationRule);
This video is just perfect for what I really need. I only have a problem on how I can add another dependent column. I am currently using named ranges which does not allow spaced between the words/values I have. Can anyone help me how to add another column. I've been trying to play with the codes since yesterday but I'm just not getting it. Thank you in advance!
why not just reject value with the data validation rule setting instead???
hi, thank for the video, do you have a script for 4 dependent dropdown column ?
where is the part 1?
What if we type wrong value in column 1, it clears the data validation rule and value. But what happens when we do undo. Will it get the previous value. Hope you understand my question
Probably I am very late to comment and had go through few comments and it seems this question was not raised. But if it is already answered. Could you be kind to reiterate here. Thanks
Hello, can you make some video about the html service (html template for example with variable from a sheet and then send an email)?
Are you able to share this script in a file with me?
This is beautiful. Thank you. Can this be done in Excel? (I assume no).
Also, if possible, please add comments as you go along in your thinking-out-loud process. Your thinking-out-loud is very helpful in understanding what is going on. It is just so that the code becomes hazy for those of us who are watching, after you move forward from one line to another, or as you go back and forth in logic-building. I think your comments written out along-the-way, could be helpful.
Yes, but you don't need scripts in Excel. You just use Indirect function right inside of data validation range box.
Logging does not work for me, in addition, the script suddenly stopped producing the 999 in my selected cell with no changes. Have no idea WTH is going on
How can the script be adjusted to create 2 or more columns of drop-downs? Right now if I make a choice in column A then script will create a drop-down in column B populated with items based on choice made in column A. I would like to instead create 2 or even more drop-down columns based on drop-down in A. Like if I make a choice in column A then the script creates 2 drop-downs in columns B and C populated with same choices. This is for a user I am helping who wants to choose a league in column A and then choose 2 teams from the drop-downs in B and C who are in the same league.
Thanks,
James :)
Hi James; sorry, but this is not something I can explain in a comment.
Well done tutorial - much appreciated
Thank you soo much! The clearDataValidation() is not working for me... I mean... it's working, remove the validation, but a few moment latter put it an emtpty select. I don't know why.
I find the solution, it's my fault! :)
Thank you for the tutorial! It is great. However, my clearDataValidations() is not working. clearContent() is working fine but not the clearDataValidations(). Please help! T.T
Excelent,
There is a way to modify/update information when you use a query or vlookup?
Example
The Doctor have a database from his patients. When the patient come back for a checkup he pulls the information just with the patient name in a nice dashboard.
How can I add or modify information from the dashboard??
Dashboard = just a nice sheet I google sheets
I was thinking to put aside a data validation list of the category (age, weight, comments....)
Then use two scripts one for adding information to the cell (like concatenating ) and another for re-write the content of a cell.
I have no idea how to go backwards to the dashboard to the database
You can't use QUERY or VLOOKUP for this. Unless there is an addon you can use, your only option would be Apps Script and quite a bit of programming work.
Learn Google Spreadsheets thanks, do you have a video where you can find an specific cell in different sheet, like index and math but in script. If I have the name of the patient (row) and the category that I want to modify (Column) .
Then I use get value from the dashboard comentes and then pass that value to the row,column in the database
I have been watching a lot of your videos but I don’t remember watching something like that