So in the script, I use this line to add the dropdown: sheet.getRange(row,col+1).setDataValidation(rule); You would modify the "+1" to get to the column you need. So for example, if your main option is in column A, and you want the conditional dropdown in E, then you would do this: sheet.getRange(row,col+4).setDataValidation(rule); Or if the dependent dropdown was actually before the main column, you can do that as well, just flip the + to a - and you can do it that way as well.
@@SheetsNinja I apologize for not being clear enough. I meant that the main option would be in column E, and the conditional dropdown would be in column F (or whatever column you choose).
@@darroldwilliams5867 So in the first version, we have a line like this: if (source.getName() == 'DATA' && val != '' && col ==1) { You would just need to change the "col == 1" to be whatever column you want to watch (e.g. E in your case), so that would mean it would then look like: if (source.getName() == 'DATA' && val != '' && col ==5) { And then if you're looking in E and applying to F, then your .setDataValidation(rule) would only be "col+1" since it's still just the next column over.
@@SheetsNinja You are a lifesaver. However, I had a curveball thrown my way. The sheet needs two sets of dependent dropdowns. How would i get the second set of dropdowns to clear? (e.g. main (E) > dependent (F) space (G) main2 (H) > dependent2 (i)
@@darroldwilliams5867 So in this case you would need to take the whole if statement (from the first { bracket to the last }), duplicate it, and then just change the "col" references to the appropriate column.
I've done that before. Typically I will use an if statement to determine when this happens. Let's say the parent dropdown is in column 2 (B) and the dependent dropdown in column 3 (C): if (col == 2 && val == '') { sheet.getRange(row,3).clearDataValidations() } That will reset the dependent data validation when you clear out the data in column 2.
Yes, you just have to specify the different spreadsheet in the script. E.g. let targetSS = SpreadsheetApp.openById("SheetId"); let targetSheet = targetSS.getSheetByName("TargetTab");
So once you've authorized the script, it won't do anything if you try to run it from the editor except give an error. Next you'll want to test it on the sheet, and if it isn't working, make sure the tab name and column numbers are correct in your script.
Hopefully my answer is still on time 😅 that happens because you are you are running the code from the script, when it's supposed to do it when you are editing the spreadsheet. So that's the solution, don't run the script and check that the trigger is set when editing
Yes, I know, I did a tutorial on a cool trick to be able to quick do dependent dropdowns without scripts here: th-cam.com/video/My1jJA-9K80/w-d-xo.html
It's aggravating that Google Sheets doesn't have a built-in method for this in their formulas that doesn't depend on either scripts or hidden rows / sheets.
I have worked on this and find that you don't show an entire view of the code. Since you don't have a download either, you have made this a laborious video to watch. There is a lot going on here and the smallest mistake take considerable time to troubleshoot. Do all your video's do this? Makes me consider not finding out.
I'm sorry you're finding this concept difficult, but I actually do show all the code as I build the whole thing in the video. If you're struggling with this, I would recommend checking out other videos on apps script and getting more familiar with the concepts. I did just add a link to make a copy of the source file, hopefully that helps you with the errors you're experiencing.
![กี่หมื่นฟ้า | Your Sky Series EP.1 [1/4]](http://i.ytimg.com/vi/vLGjxFL6U_I/mqdefault.jpg)
🙌 Great tutorial!
So glad this was helpful!
I like the tutorial. How do you get the dropdowns to work in a different column? (column E, for example)
So in the script, I use this line to add the dropdown:
sheet.getRange(row,col+1).setDataValidation(rule);
You would modify the "+1" to get to the column you need. So for example, if your main option is in column A, and you want the conditional dropdown in E, then you would do this:
sheet.getRange(row,col+4).setDataValidation(rule);
Or if the dependent dropdown was actually before the main column, you can do that as well, just flip the + to a - and you can do it that way as well.
@@SheetsNinja I apologize for not being clear enough. I meant that the main option would be in column E, and the conditional dropdown would be in column F (or whatever column you choose).
@@darroldwilliams5867
So in the first version, we have a line like this:
if (source.getName() == 'DATA' && val != '' && col ==1) {
You would just need to change the "col == 1" to be whatever column you want to watch (e.g. E in your case), so that would mean it would then look like:
if (source.getName() == 'DATA' && val != '' && col ==5) {
And then if you're looking in E and applying to F, then your .setDataValidation(rule) would only be "col+1" since it's still just the next column over.
@@SheetsNinja You are a lifesaver. However, I had a curveball thrown my way. The sheet needs two sets of dependent dropdowns. How would i get the second set of dropdowns to clear? (e.g. main (E) > dependent (F) space (G) main2 (H) > dependent2 (i)
@@darroldwilliams5867 So in this case you would need to take the whole if statement (from the first { bracket to the last }), duplicate it, and then just change the "col" references to the appropriate column.
Thank you so much Man you saved my day. That's really wonderful.
Awesome, glad the video was helpful!
Great tutorial, thank you!
Can’t seem to get it quite right. Any chance you can take a look at test sheet I creat?
How can I move a data that the script reads on a dynamic drop down from one column to another column to validate correctly?
I'm sorry, I'm not quite sure what you mean.
Is it possible to automatically clear DataValidation on Dependent dropdown when the parent dropdown is deleted?
I've done that before. Typically I will use an if statement to determine when this happens. Let's say the parent dropdown is in column 2 (B) and the dependent dropdown in column 3 (C):
if (col == 2 && val == '') {
sheet.getRange(row,3).clearDataValidations()
}
That will reset the dependent data validation when you clear out the data in column 2.
Is it possible to move the row to a different workbook instead of a different tab??
Yes, you just have to specify the different spreadsheet in the script.
E.g.
let targetSS = SpreadsheetApp.openById("SheetId");
let targetSheet = targetSS.getSheetByName("TargetTab");
I'm not sure why I keep getting an error about the undefined e.range even after setting the trigger, is there another step I need to take?
So once you've authorized the script, it won't do anything if you try to run it from the editor except give an error. Next you'll want to test it on the sheet, and if it isn't working, make sure the tab name and column numbers are correct in your script.
Hopefully my answer is still on time 😅 that happens because you are you are running the code from the script, when it's supposed to do it when you are editing the spreadsheet.
So that's the solution, don't run the script and check that the trigger is set when editing
Great video! it's possible to do this without scripts too
Yes, I know, I did a tutorial on a cool trick to be able to quick do dependent dropdowns without scripts here: th-cam.com/video/My1jJA-9K80/w-d-xo.html
It's aggravating that Google Sheets doesn't have a built-in method for this in their formulas that doesn't depend on either scripts or hidden rows / sheets.
Yes, that is annoying. Maybe someday they will allow you to use formulas to populate a dropdown and this video will become obsolete.
I have worked on this and find that you don't show an entire view of the code. Since you don't have a download either, you have made this a laborious video to watch. There is a lot going on here and the smallest mistake take considerable time to troubleshoot. Do all your video's do this? Makes me consider not finding out.
I'm sorry you're finding this concept difficult, but I actually do show all the code as I build the whole thing in the video. If you're struggling with this, I would recommend checking out other videos on apps script and getting more familiar with the concepts.
I did just add a link to make a copy of the source file, hopefully that helps you with the errors you're experiencing.