Correction: as mentioned in one of the comments 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"){
Hello there. Fantastic video. I was wondering how would I need to modify the script if I want it to work in specific sheets only. Is it possible? Thank you so much.
i guess Im asking randomly but does someone know of a method to log back into an instagram account..? I was dumb lost the login password. I love any tips you can give me
@Harlan Kingston Thanks for your reply. I got to the site through google and I'm waiting for the hacking stuff now. Seems to take a while so I will reply here later with my results.
function onEdit() isn't working for me. It triggers the moment I run the script. EDIT: Nevermind. It all works fine. Thanks a lot for this clear tutorial!
Hi thanks for the great video with easy explanations. In my car the logger ins't reporting any values, just saying, "Waiting for logs, please wait..." what could be the problem for this?
Is there a way to have a third drop down row appear based on the value selected in the second drop down? I'm not familiar with AppScript. I just copied step by step what you did here in this video and it worked fine. However, I'm not sure how to duplicate it for a third drop down. Please help!
Your videos are amazing. I've been using this script for a while, but I would like to be able to utilize it in more than one tab of a document. And also in more than one section of a tab at the same time. How would I go about doing this? Making copies of the script with the new tab names didn't work for me. Is it because the variables also have to have unique names?
Fantastic tip - LOVE IT!!! I do have a question, however, when I try and replicate the main and subcategory to a let's say the two cells below, it doesn't work. boo! Any suggestions on how I copy the main category and its sub to the series of cells below? Thoughts? TY!
Hi, hi. Thanks for these info. Cought some tips, but not able to find what I was looking for, can you help? It si easy that I can't find a way to do it so if you can guide me to the correct direction. Basically, I need to make a dropdown for several rows -done-. On each row, from the selected item, need the next column to choose only one option from a list. I kind of knew how to do it on Office, but learning sheets. Can you tell me which tutorial to watch. Thanks.
Hi! I would like to combine dropdown lists with a gantt chart for my farmplanning. Exempel: In the dropdown lists I want to be able to choose a vegetable (broccoli) and in the second dropdown list I want to choose the variety (sprouting). Then I want to be able to see in a gantt chart when this type of broccoli will be in the ground. Do you have a tutorial on that? Or a link to a tutorial? Thanks!
Main Tab: ColD & ColE for DDL | Lists Tab: ColJ:ColZ (21 columns) & Value Set G2:G16 Fine until Time 1130, but onEdit fails to output 'Alert' on .setValues AND Log OK without [[Array | Arrays]] { var ColUIApps = ssdata.getRange(1,10,1,getLastColumn()) .getValues(); Logger.log(ColUIApps); ss.getRange("Lists!G2:G16").setValue('Alert'); } Also used 'getActiveRangeList' and simple (1,10,1,21*) *instead of getLastColumn but failed the log and Array Pls verify the snippets
Excellent Videos ...Please explain Why still google does not include easy function for Multi Dependent Drop Down list option...?( I mean unable to enter INDEX function in data validation in google sheet.) Do U have any idea..?Please explain..
Thanks for the video. By the way, I can't see any log. When I access the Log window it just says "No logs found. Use Logger API to add logs to your project." Any help? Thanks in advance!
this video is excellent, I would like to know more about the AppSheet introduction video that was mentioned. Would you please provide some reference? Thanks!
Hi, can we do this in MS Excel (not in Google Sheet) without using a script or VB? I have a column that needs data validation and each item in that column has this formula: =INDIRECT(SUBSTITUTE($D$2," ","_")) and the next item has to be D3, then D4, D5,... Thanks in advance!
AMAZING VIDEO!!! If the new Data Validation Range returns 0 or "" and there is not data for it to retrieve to populate the dependant dropdown: is there a way for the Validation to not appear? like a: if(validationRange = 0){ activeCell.offset(0, 1).clearContent().clearDataValidations(); } Not all of my options will require a dependant dropdown. Thank you.
Is there a way to do these dynamic drop downs if the two lists of data are in 2 columns instead of columns and rows? Ie. Column 1 is athlete names and columns 2 is athlete school. I want to be able to select the school on another sheet and be able to get a drop down of all the athletes assigned to that school
Hello Im kind of new to script. But im kind of following your explanation. I have a question and i just cant find answer. I have a spreadsheet with multiple sheets. One is for production, another for sales. Both sheets, need dynamic drop dwon lists, but, columns are distributed different on each sheet. i just cannot make it to work at the same time, both dropdown list in differente columns on different sheets. Is there a way? thanks
hi i like your video so much and its so useful. but i have small problem , i have to do entries in 3 columns and your video shows how to create dependent drop down list only referring to two columns so can u tell me how do such multi dependent google sheet
I haven't watched the vid yet but I'm guessing he achieves data validation through apps script; if you do it instead through the "data" tab you will see an option for "reject invalid data"
HI. I have seen and tried the instructions but when I tried using it with 2 dependent drop down., one is working and the other is not. what seems to be the problem? please help me. Thank you
Unfortunately I have no Idea what i'm doing wrong here. It's not working. I've watched this video about 15 times. It is bringing out the drop down arrow on column 2 but it just keeps loading. What I'm trying to do is Dynamic Drop down list where When I select a product in column 7 of the Registry sheet then only the related sub categories will show up as data validation in column 8 of the Registry sheet. I have the info on the ProductINFO sheet. Can anyone correct my mistake. What am I missing here? Thank you in advance. function onEdit(){ var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ProductINFO"); var activeCell = ss.getActiveCell(); if(activeCell.getColumn() == 1 && activeCell.getRow() > 2 && ss.getName() == "Registry"){ activeCell.offset(0 , 1).clearContents().clearDataValidations(); } var makes = datass.getRange(1,1,1, datass.getLastColumn()).getValues(); var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1 ; if (makeIndex !== 0) { var validationRange = datass.getRange(3, makeIndex, datass.getLastRow()); var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build(); activeCell.offset(0, 1).setDataValidation(validationRule); } }
Mate, I know it's 6 months too late - but I just figured out what your problem is. I had the same issue and I sat here error trapping for HOURS before I found the solution. I have no idea why it's not triggering the same error in the original spreadsheet, though I think it's got something to do with the fact that our respective reference lists don't start in row 1. Anyway. This line: var validationRange = datass.getRange(3, makeIndex, datass.getLastRow()); Needs to be: var validationRange = datass.getRange(3, makeIndex, datass.getLastRow() -2); What is happening is that it's setting the # of rows for validationRange to be the TOTAL # of rows of the sheet. When you START at row 3, it tries to extend down BEYOND the total length of the sheet and gets caught in an infinite loop. OMG it was such a pain in the ass to error-trap!
Hi I tried executing the script, I'm able to see the changes in values on my sheet but when I check the logger it says " Waiting for logs, please wait..."
Is it possible to choose more than one value within the drop down? For example, the column is called team, and in each cell below I can open the drop down and choose a person or several who participated in a given project. It's possible?
It's not going to work with a dropdown. It might be possible to do this with integrating webforms and make it work similar to that but it will be very complicated.
Im not sure what im doing wrong. Im trying to reference a particular cell based off 2 sets of data. I have a dropdown which inputs a range of data into a column and im trying to create a function based off the dropdown and range of data. Its a sports database so B1 is a particular team and A9 is their first opponent on the schedule. I want to create a function that gives their head to head record based off the table i created. I tried this but im just not getting anywhere =QUERY('Match-ups'!$A$2:$M14,"select * where A = '"&$B$1&"' AND 2 = 'A9'")
Hi, could someone please tell me how to do this when the tab isn’t the first in the sheet. I’ve tried using the .getSheetByName() the sheer inky registers inputs from cells on the 1st sheet/tab. I need it to work for the 4th tab.
Why am I getting this error ?? I just follow what you have written there. TypeError: SpreadsheetApp.getActiveSpreadSheet is not a function (line 3, file "Code")
Correction: as mentioned in one of the comments 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"){
Hello there. Fantastic video. I was wondering how would I need to modify the script if I want it to work in specific sheets only. Is it possible? Thank you so much.
i guess Im asking randomly but does someone know of a method to log back into an instagram account..?
I was dumb lost the login password. I love any tips you can give me
@Brendan Ty instablaster :)
@Harlan Kingston Thanks for your reply. I got to the site through google and I'm waiting for the hacking stuff now.
Seems to take a while so I will reply here later with my results.
@Harlan Kingston It did the trick and I now got access to my account again. Im so happy!
Thanks so much you really help me out !
This is what I have been looking for. Thanks Millions. Now I could create two level dependent drop down.
Great video. Just a newbie trying to learn here. I couldn't help but laugh at the name you gave the data sheet var. Keep up the good work!
Superb! You’ve saved the day
Anyone else just copying him exactly and subbing your own values. I have no idea what is going on but it seems to be working
This is what i Need Thank you! I havent done any script before, I was surprise how easy it is with this video.
function onEdit() isn't working for me. It triggers the moment I run the script.
EDIT: Nevermind. It all works fine. Thanks a lot for this clear tutorial!
this video made my day today. it's all i need. thank you
Tremendous effort you put on here! Now my sheet is doing just that. Thank you so much! :D :D :D
"var datAss = " in camel case
dat ass tho!
Hi thanks for the great video with easy explanations. In my car the logger ins't reporting any values, just saying, "Waiting for logs, please wait..." what could be the problem for this?
same and I can't figure it out, did you end up figuring it out
Is there a way to have a third drop down row appear based on the value selected in the second drop down? I'm not familiar with AppScript. I just copied step by step what you did here in this video and it worked fine. However, I'm not sure how to duplicate it for a third drop down. Please help!
Thank you so much!!!!
Your videos are amazing. I've been using this script for a while, but I would like to be able to utilize it in more than one tab of a document. And also in more than one section of a tab at the same time.
How would I go about doing this? Making copies of the script with the new tab names didn't work for me. Is it because the variables also have to have unique names?
This works great! I need to add a column to the left, this upsets the offsets, can anyone tell me which values in the script i need to change please?
What an effort!! Thanks a lot
Fantastic tip - LOVE IT!!! I do have a question, however, when I try and replicate the main and subcategory to a let's say the two cells below, it doesn't work. boo! Any suggestions on how I copy the main category and its sub to the series of cells below? Thoughts? TY!
This is exactly what I need., I'm following the exact same steps but cannot make it to run. No error messages nor log events. Any clues?
Hi, hi. Thanks for these info. Cought some tips, but not able to find what I was looking for, can you help? It si easy that I can't find a way to do it so if you can guide me to the correct direction.
Basically, I need to make a dropdown for several rows -done-. On each row, from the selected item, need the next column to choose only one option from a list. I kind of knew how to do it on Office, but learning sheets. Can you tell me which tutorial to watch. Thanks.
This is so easy in Microsoft Excel... A shame Excel is not working on Android with a 2019 Office subscription...
thank you very much
Hi! I would like to combine dropdown lists with a gantt chart for my farmplanning. Exempel: In the dropdown lists I want to be able to choose a vegetable (broccoli) and in the second dropdown list I want to choose the variety (sprouting). Then I want to be able to see in a gantt chart when this type of broccoli will be in the ground. Do you have a tutorial on that? Or a link to a tutorial? Thanks!
Thank you very much !!
great video! but can we make a cell to show an option by default instead of picking it everytime in every cell?
Main Tab: ColD & ColE for DDL | Lists Tab: ColJ:ColZ (21 columns) & Value Set G2:G16
Fine until Time 1130, but onEdit fails to output 'Alert' on .setValues AND Log OK without [[Array | Arrays]]
{ var ColUIApps = ssdata.getRange(1,10,1,getLastColumn()) .getValues();
Logger.log(ColUIApps);
ss.getRange("Lists!G2:G16").setValue('Alert'); }
Also used 'getActiveRangeList' and simple (1,10,1,21*) *instead of getLastColumn but failed the log and Array
Pls verify the snippets
Excellent Videos ...Please explain Why still google does not include easy function for Multi Dependent Drop Down list option...?( I mean unable to enter INDEX function in data validation in google sheet.) Do U have any idea..?Please explain..
I don't work for Google. I don't know.
Thanks for the video. By the way, I can't see any log. When I access the Log window it just says "No logs found. Use Logger API to add logs to your project."
Any help?
Thanks in advance!
Logger.log shows just the latest run. If you want a log with history Google has console log API for this.
this video is excellent, I would like to know more about the AppSheet introduction video that was mentioned. Would you please provide some reference? Thanks!
czcams.com/video/aPJ-2U45BpA/video.html
What is faster? Using dependent dropdowns using formulas? Or using AppScripts?
Formulas, if it works.
This is awesome! Could you please share the sheet with us, so that we could save as a copy, please?
omg yes pls
Hi, can we do this in MS Excel (not in Google Sheet) without using a script or VB? I have a column that needs data validation and each item in that column has this formula: =INDIRECT(SUBSTITUTE($D$2," ","_")) and the next item has to be D3, then D4, D5,... Thanks in advance!
AMAZING VIDEO!!!
If the new Data Validation Range returns 0 or "" and there is not data for it to retrieve to populate the dependant dropdown: is there a way for the Validation to not appear? like a:
if(validationRange = 0){
activeCell.offset(0, 1).clearContent().clearDataValidations();
}
Not all of my options will require a dependant dropdown.
Thank you.
How to make (WITH THIS EXAMPLE) three level dependents list on each other?
czcams.com/video/s-I8Z4nTDak/video.html
That is so great. Thank you so much, i have seen all your clip - Will you do something more soon ?
Just uploaded 5 more videos this week.
I am in VietNam, and every morning i get up to learn coding via your clip. It is so helpful in practise
When I type "." on my scripts the functions/formulas were not showing up, I want to have something like on your video (timestamp 6:45), thanks a lot!!
Is there a way to do these dynamic drop downs if the two lists of data are in 2 columns instead of columns and rows? Ie. Column 1 is athlete names and columns 2 is athlete school. I want to be able to select the school on another sheet and be able to get a drop down of all the athletes assigned to that school
Hello
Im kind of new to script. But im kind of following your explanation. I have a question and i just cant find answer.
I have a spreadsheet with multiple sheets. One is for production, another for sales. Both sheets, need dynamic drop dwon lists, but, columns are distributed different on each sheet.
i just cannot make it to work at the same time, both dropdown list in differente columns on different sheets.
Is there a way? thanks
hi i like your video so much and its so useful. but i have small problem , i have to do entries in 3 columns and your video shows how to create dependent drop down list only referring to two columns so can u tell me how do such multi dependent google sheet
Hello! Did you find any answers?
yes. here is link of it
czcams.com/video/rW9T4XZy-7U/video.htmlS.O.R.531721925318
@@sartajshaikh429 where your link is?
Hi, great video. Is there a way to make the validation where on invalid data to Reject input?
I haven't watched the vid yet but I'm guessing he achieves data validation through apps script; if you do it instead through the "data" tab you will see an option for "reject invalid data"
HI. I have seen and tried the instructions but when I tried using it with 2 dependent drop down., one is working and the other is not. what seems to be the problem? please help me.
Thank you
Hi, i would like to make a drop down list where the value in the data validation can only be used once
Could it work for multiple sheets and multiple columns on each? Thanks.
It is possible to data validation from other worksheet?
how do I pull historical stock prices from googlefinance in an array? does date needs separate data declaration?
Unfortunately I have no Idea what i'm doing wrong here. It's not working. I've watched this video about 15 times.
It is bringing out the drop down arrow on column 2 but it just keeps loading.
What I'm trying to do is Dynamic Drop down list where When I select a product in column 7 of the Registry sheet then only the related sub categories will show up as data validation in column 8 of the Registry sheet. I have the info on the ProductINFO sheet.
Can anyone correct my mistake. What am I missing here?
Thank you in advance.
function onEdit(){
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var datass = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ProductINFO");
var activeCell = ss.getActiveCell();
if(activeCell.getColumn() == 1 && activeCell.getRow() > 2 && ss.getName() == "Registry"){
activeCell.offset(0 , 1).clearContents().clearDataValidations();
}
var makes = datass.getRange(1,1,1, datass.getLastColumn()).getValues();
var makeIndex = makes[0].indexOf(activeCell.getValue()) + 1 ;
if (makeIndex !== 0) {
var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
var validationRule = SpreadsheetApp.newDataValidation().requireValueInRange(validationRange).build();
activeCell.offset(0, 1).setDataValidation(validationRule);
}
}
Mate, I know it's 6 months too late - but I just figured out what your problem is. I had the same issue and I sat here error trapping for HOURS before I found the solution. I have no idea why it's not triggering the same error in the original spreadsheet, though I think it's got something to do with the fact that our respective reference lists don't start in row 1. Anyway.
This line:
var validationRange = datass.getRange(3, makeIndex, datass.getLastRow());
Needs to be:
var validationRange = datass.getRange(3, makeIndex, datass.getLastRow() -2);
What is happening is that it's setting the # of rows for validationRange to be the TOTAL # of rows of the sheet. When you START at row 3, it tries to extend down BEYOND the total length of the sheet and gets caught in an infinite loop. OMG it was such a pain in the ass to error-trap!
Hi! what about if I already have an OnEdit function for another purpose? can I define an OnEdit2 or something like that? Thanks!
no just add in the same onEdit function or create onEdit2 function and call it inside onEdit function, like onEdit2(e);
In case the data in the first column is in date format. How do we work?
Hi I tried executing the script, I'm able to see the changes in values on my sheet but when I check the logger it says " Waiting for logs, please wait..."
Check under View->Executions
Is it possible to choose more than one value within the drop down? For example, the column is called team, and in each cell below I can open the drop down and choose a person or several who participated in a given project. It's possible?
It's not going to work with a dropdown. It might be possible to do this with integrating webforms and make it work similar to that but it will be very complicated.
Hey can I do this in google web app also? so that when I want to make dropdown in webapp it will have dependent dropdown? Thanks!
Im not sure what im doing wrong. Im trying to reference a particular cell based off 2 sets of data. I have a dropdown which inputs a range of data into a column and im trying to create a function based off the dropdown and range of data. Its a sports database so B1 is a particular team and A9 is their first opponent on the schedule. I want to create a function that gives their head to head record based off the table i created. I tried this but im just not getting anywhere =QUERY('Match-ups'!$A$2:$M14,"select * where A = '"&$B$1&"' AND 2 = 'A9'")
this part is wrong 2 = 'A9'")
2 should be the column letter and A9 should be concatenated. Like B = '" &A9&"'")
Hi, could someone please tell me how to do this when the tab isn’t the first in the sheet. I’ve tried using the .getSheetByName() the sheer inky registers inputs from cells on the 1st sheet/tab. I need it to work for the 4th tab.
YOU CAN REFER THE TOP COMMENT
Ray Zhu For clarity, does that mean this script only works on the Main Tab? Thank you
Can this work with a web app?
Why am I getting this error ?? I just follow what you have written there.
TypeError: SpreadsheetApp.getActiveSpreadSheet is not a function (line 3, file "Code")
It's probably getActiveSpreadsheet
Everything is case sensitive.
Could you plaaaaase put the code somewhere?
interest
Thank you so much for this! This was super helpful! Also I love the variable "dat ass" ;)
🙏🏻
There has to be a way to do this without using app scripts!
If you find how let me know :)
@@ExcelGoogleSheets Me too? I wish GooglelSheets could just update he DataValidation function to handle formula references like Excel does!
Spoiler alert- it is not the last video.
Can we do without script?
yes, only for one row though.
Video Quality is very low
It was just uploaded. CZcams makes higher quality versions available a bit later. Try in 15 minutes.
Yes Done