This explanation was perfect. It's hilarious this isn't just built into GS, but you made this PERFECTLY clear, and I learned a little code at the same time. Thank you!
Thank you for the clear and concise explanation! I refactored the code a bit and added in functionality so the same item cannot be selected twice. This also works for a column of cells starting at row 3: function onEdit(e) { if (e.range.rowStart < 3 || e.range.columnStart !== 7 ) { return; } if (e.value && e.oldValue && !e.oldValue.includes(e.value)){ e.range.setValue(`${e.oldValue}, ${e.value}`); } else if (e.oldValue.includes(e.value)) { e.range.setValue(e.oldValue); } }
I Like to have Place holders for my drop down selection just to make the sheet prettier and identify what the user still needs to fill in. To acomplish this along with your method(that worked wounderfully btw) I used the modified code below: function onEdit(e) { if (e.range.columnStart != 4 ) { return; } if(e.value == ("Select a(n) option(s)")) { set.eValue("Select a(n) option(s)"); } if (e.oldValue == ("Select a(n) option(s)")) { return; } else if(e.value && e.oldValue && !e.oldValue.includes(e.value)){ e.range.setValue(`${e.oldValue}, ${e.value}`); } if (e.oldValue.includes(e.value)) { e.range.setValue(e.oldValue); } } To Change the Place Holder just change my selection of word choice ("Select a(n) option(s)") with whatever placeholder you have in your listing This function will also delete the value(s) beforehand if you re-select the placeholder; as-well as remove it if you select something else in the listing removing the need to delete the cell. Deleting the cell does not cause any flow breaks in the code as I can tell. This effect could also be stacked with further "special selection condtions" coding as long as you keep your logic consistant and/or similar Note: This does require you to have this physcial listing that you are calling from in your data; as-well as I am trying to read the whole column. So you will find that I have altered the code to work according to that. I left the code uncondensed here for better review :) Hope this can find someones use as did your comment and this video did for me!
@@SpencerFarris Thank you Spencer for this very helpful tutorial! Is there a way to use the code above that covers one column, but exclude certain rows?
@@helenmatsumoto6833 you could include an array of rows to exclude and check against them: const rows = [5,7,29]; if (rows.includes(e.range.rowStart)) return;
Wow! We had an IT consultant for our office website and I always request this feature for a specific part (we use wordpress) and told me there's no way we could limit it for an option to be selected twice. Thank you so much for this comment!
Super awesome - works for me like a dream. Especially thanks to the comments that include de-duplication, using the function in multiple columns, using linebreaks not comma-space, and working across multiple rows. This is really powerful in so few lines of code. Thanks for sharing! (and supporting so many people)
@spencerFarris I meant how do I script the code to run across multiple rows and multiple columns of the same sheet? Now, I have figured out how to do multiple selection for many sheets of the same file and across many rows but I am yet to get how to do it across many Columns of the same sheet.
@@johnb.babadara4141 This line controls what rows/columns it works on. Which range do you want it functioning on? if (e.range.rowStart != 2 || e.range.columnStart != 3) return;
I love this! Helped a lot! This is even better as this is more than something for us to copy and go. The explanation helped me a lot to alter a bit of the script to match my sheet better! 100% love this
What an awesome tutorial!! Thank you so much, it was clear, concise, and interesting to follow. And thank you for answering the questions below in the comments. I had to tweak the formula so that the dropdowns would function in multiple rows and columns and if you hadn't answered those comments, this tutorial wouldn't be very useful. So many people put tutorials on YT then don't bother to answer the questions that follow, but you made an effort. Again thank you!!
Thanks for this excellent tutorial. It worked really well as intended. How do I run this for multiple rows and columns in both the same sheet and multiple sheets? Once again, thank you.
This was an extremely helpful video! Unfortunately, I'm not following the coding language enough to figure out the answer to my question from reading your responses to the other questions... How would I need to change the code so that this applies to columns 16-20, starting on row 4?
This is very awesome! Thank you so much for this tutorial and to all the others who contributed in the comments section. You do not know how much you made my life easier at my job! Thank you very much!
@Spencer Ferris Thank you thank you thank you so much for doing this tutorial!!! I was able to get all the answers and solutions I needed by the comments and your video! YOU ROCK!!!
Did you try the code from InifinteS3t? function onEdit(e) { if (e.range.rowStart < 3 || e.range.columnStart !== 7 ) { return; } if (e.value && e.oldValue && !e.oldValue.includes(e.value)){ e.range.setValue(`${e.oldValue}, ${e.value}`); } else if (e.oldValue.includes(e.value)) { e.range.setValue(e.oldValue); } }
Hi Spencer, I have a question. Hopefully hopefully hopefully, you can help 😅🙏🏻 So for context, I have two sheets open in the SAME spreadsheet I'm using (in the video, yours is labeled sheet1 if you don't know what I'm talking about, for me, I have two open). One has my dropdown menu and the other is for my "Choices" that I have for the dropdown. I separated it because I have a lot of choices that I need for the dropdown and I'm constantly adding more. Also, the choices are organized by the columns in sheet2, so they are on Columns B-M and the choices go down to Row 65 on some of the columns, which may or may not go passed 65 once more choices occur to me (Though there are some that only go to like Rows 3-5, which leaves quite a few blank spaces in that particular column, though that's not important, cause if it leaves blanks on the dropdown, I will gladly take it if it lets me select multiple choices). And as for the dropdown in sheet1, at the moment, the data validation is set to be used for the entirety of Column G (minus the top 5 for organization headers) going from Row 6 onwards. So what I needed help with, was how do I use this if the "Choices" I want to use span that many columns and rows, while also being on a different sheet on the same spreadsheet? But yet can be used for the entire Column G on sheet1. Is this in anyway possible? 😅
Hey Michelle, I *think* it's possible. Could you please add me as an Editor on your Sheet so we can look at the actual setup together? spencer.farris@gmail.com
Hi Spencer, I'm trying to apply the function for all the cells in columns 10, 11 & 21 As suggested by you to a similar question, I tried the following code to no success. Any ideas? function onEdit(e){ const cols = [10,11,21]; const r = e.range; if (cols.indexOf(e.range) == -1) return; if (!e.value || !e.oldValue){} else e.range.setValue(e.oldValue + ", " + e.value); } Thank you!
Your tutorial was the only tutorial that gave me a straight answer and made everything clear. Thank you! I have a question, I was wondering how to prevent duplicates?
HI Spencer, I have learnt a lot from this video and the comments provided but I struggling to apply this function to specific rows, for example apply only to rows 4, 6, 11, 16. I tried this from a previous comment with no luck. const rows = [5,7,29]; if (rows.includes(e.range.rowStart)) return
I can't see the script editor inside Tools. Furthermore, I can see App Scripts under Extensions but unable to understand how to link that script to my google sheet.
I made this video before the update moving Apps Script under Extensions. A script made from the script editor on a Sheets, Docs, or Slides file is part of that file.
Super helpful! But what if i want to delete something? Like if i wanted to delete Cal Omas, so i only have Nas Choka and Abeloth? I cant delete only one option without it messing up
@Spencer Farris Hello! Great tutorial! I was hoping to apply this to more than 1 column and no limit to the rows. For example, I would like to apply this to columns 2 and 15. Would you mind sharing how that is done?
Hi Spencer This was very much helpful Just need to ask I have huge file with nearly 200 cols and over 300 rows and nearly 110-120 of these cols are either formulaised, have data validation or have conditional formatting. ISSUES I AM FACING: 1.I have tried the code with a small range for the selection criteria and I want to apply this for the enitre column expect the first 3 header rows [For which I have used the following code : function onEdit(e){ if (e.range.columnStart != 199) return; if (!e.value || !e.oldValue){} else e.range.setValue(e.oldValue + ", " + e.value);} ] Now when I wanted to check if the code works for the first time I had to run the code manually only then it considered multi select. 2. When I select more than 1 option it takes 1 - 2 seconds to display the combined result. 3. As this is the first time I am implementing app script to my file and more data is gonna be added in future possibly more formulaised columns as well as rows will the code make my file slower. Thankyou again for the tutorial.😇
@@SpencerFarris Thanks for the response man, I actually figured what the solutions with some help from google. I just have one doubt how much scalable this solutions is, i.e. Right now I dont have much data in my sheet so its working fine but will appscript make my sheet slow when I add more data ?
Hey Spencer, First of all thank you this was a huge help and you were super clear and concise. You didnt try to spent 5 minutes selling me a product or telling me to subscribe which is great. This has worked perfectly for me with one exception. Currently the code is applying to every sheet in the column the script is set to run on. I am just trying to have it run on one of the sheets in the same document so it does not mess with formulas on others. Any advice to have this script only run on one tab?
Look at this addition in the IF() statement: function onEdit(e){ if (e.range.rowStart != 2 || e.range.columnStart != 3 || e.souce.getActiveSheet().getName() != "name of sheet") return; if (!e.value || !e.oldValue){} else e.range.setValue(e.oldValue + ", " + e.value); }
@@SpencerFarris, thank you for your video and thank you for all the answers that you give in the comment section. For this function to work in a specific sheet you misspelled e.souce so the "r" is missing. I struggle a little bit to find out why it's not working :)
Is there something I could be missing to get this to work? I was able to find the app script under extensions, but when I change my dropdown, nothing in the script happens. I even removed the row/column parameters. If I have multiple sheets, do I need to specify the sheet somewhere? I assumed if it applies to everything and I remove row/column specifiers, it would apply to any cell on any sheet, but it's still not working. I just want to make one column on one sheet a list like this from a drop down.
@@SpencerFarris That would be awesome! I just tried again, and it's running something now, but no visible changes for me...just a delay. I left a comment in the cell of the things I tried. I have coding experience in R and python, but nothing in excel or sheets so I'm not really familiar with the language/syntax, but I know the concept. I just thought this would be fun to try out. Thank you so much for your help!! docs.google.com/spreadsheets/d/1uy0Y7hNhfpO5DKTZOioLyazGbeoVUwZLkVD2OMwTZNI/edit#gid=1345984007
Hey bro, How to limit this to only a particular sheet as I have multiple tabs and it runs on all tabs, also is there way to run script on copy of sheet
Hi, Spencer, great job! I tried to modify it but obviously, I'm doing something wrong. I want the code to work on the entire sheet 1. On sheet 1 I created two drop-don lists (in columns A & E, rows 2-30 [the first row is header]) based on two different ranges on sheet 2, then I did go to Extensions - Apps Script and pasted this code: function onEdit(e){ if (e.range.rowStart != 2 || (e.range.columnStart != 1 && e.range.columnStart != 26)) return; if (!e.value || !e.oldValue){} else e.range.setValue(e.oldValue + ", " + e.value); } saved it, and then back to the spreadsheet, but the new value still replaces the new value in the cell. Could you advise, please?
You have "e.range.rowStart != 2" as an escape condition, meaning this will *only* work on row 2. Try IF(e.range.rowStart == 1 || e.range.rowStart > 30 || (e.range.columnStart != 1 && e.range.columnStart != 5)) return;
Awesome tutorial. I have a question. What if I want each of my dropdown items to be on a different row in the same cell instead of being separated by a comma? Thanks
@@wildlifesafariadventures Shouldn't be that hard - I don't have it written but you would have to split apart everything in the cell (String.split()), sort them, and push it all out. There may even be a fast sort algorithm since it is just inserting a new item each time.
I am trying to apply the multiple selection dropdown to columns 20, 23 & 24 on 3 sheets (North, South & Central). How do I change this to be applied to the whole sheet? This is the script I am using: function onEdit(e) { multipleSelectionDropdown(e) }
function multipleSelectionDropdown(e) { //Grab information about the user's whereabouts var activeCell = SpreadsheetApp.getActiveRange() var activeRow = activeCell.getRow() var activeCol = activeCell.getColumn() var activeSheet = activeCell.getSheet() var activeSheetName = activeSheet.getName() var newValue = e.value var oldValue = e.oldValue
//See if the user is where I want him to be const COL_DROPDOWN = 20; const STARTING_ROW_DROPDOWN = 2 const NAME_SHEET_DROPDOWN = "North"
You would use the range on the IF() statement. So if you want it to apply to everything in A2:A10 - IF(e.range.rowStart < 2 || e.range.rowStart > 10 || e.range.columnStart > 1) return
@@SpencerFarris How about applying this code to more than 1 cell in another column? So the choices are in tab 2 and the selection drop down is in tab 1. Is that possible?
@@SpencerFarris your example shows the values and the drop-down on the same tab. How do you do it when the list of range is on tab 1 and the drop-down is on another tab on the same spreadsheet
Thank you for the tutorial, its excellent. I got it to work within one tab on a sheet, where I have several tabs that I would like to do a multi select picklist within, though I can't seem to write the script to make it work for one tab, as well as another. Am I not saving the script correctly, to then create a new one in another tab? Help?
Hi Spencer, Great Video. I have copied all the code you wrote line for line but it doesn't work here. Please help. On the sheet itself it says undefined; for example from a drop down list featuring construction, renovation, remodeling, demolition; it says undefined, demolition and when i select another from the drop down list it says undefined, construction. Also the error box is Invalid: input must fall within specified range. Please Help.
Hi! I know I have dropped so many comments already saying thank you. I just have one question, I tried scrolling to see if someone has asked the same and is already answered but couldn't find any :( I just want to ask if it is possible where I want column 4 to be separated by commas while the column 9 be separated by semi-colon. Thank you again and hope I get a response from this.
Sure! There are several places that could be added. I think I would do it like this: function onEdit(e){ let cols = [4,9]; let del = [", ","; "]; let index = cols.indexOf(e.range.columnStart); if (index == -1) return; if (!e.value || !e.oldValue){} else e.range.setValue(e.oldValue + del[index] + e.value); }
@@SpencerFarris Hello, it's me again. I just have another inquiry. I hope you do not mind. When I try to remove an option in between the list separated by commas, or when I try to edit the cell with multi-select drop down, it automatically copies and pastes the list in the same cell making it repetitive. Or whenever I mistakenly added the wrong dropdown option yet when I want to remove or delete it, it does not do it. Do you perhaps know how to resolve this? Thank you in advance.
Is this outdated? Not even the first pass of this (the simple concatenation) works. Just, nothing happens with the dropdowns, other text will change, just not the dropdowns.
@@isiakamusa-z9j This line says that it only runs on C2 if (e.range.rowStart != 2 || e.range.columnStart != 3) return; If you want it to be all of C use if (e.range.columnStart != 3) return;
This tutorial was completely useless for me. I have no idea how to deploy something, you don't show any of that process, no idea how to make this script active and work or how you managed to go to the sheet and select your ranges or cells you chose... I'm new to google sheets, used scripts before with excel, this video was missing some of the fundamentals of how to create a script in the first place, unfortunately this is useless for me.
Hi Araya! I'm very sorry this wasn't helpful for your needs. One great challenge is that since this and my other early videos, Google changed the menu. So when I made this video Tools > Script Editor was the correct path. Now, however, the path is Extensions > Apps Script. That will take you to the same place. The script itself is intended to be used on a range where Data Validation (which creates dropdowns) has already been created. That is, the script does not *create* the dropdowns, it only changes the functionality where dropdowns already exist so that you can perform multi-select. In my example I constrain the range using the initial IF statement. This particular one, for instance, only operates on row 2 column 3: C2. If I wanted it to work on all of B2:B I would use IF(e.range.rowStart < 2 || e.range.columnStart != 2) return; Go to Extensions > Apps Script, paste the code, and you should see it operating on C2, and the range of operation can be modified. I'm happy to help with any specific implementation.
This explanation was perfect. It's hilarious this isn't just built into GS, but you made this PERFECTLY clear, and I learned a little code at the same time. Thank you!
A lot of this stuff that isn't built-in. Hopefully someday they do add stuff like this!
Thank you for the clear and concise explanation! I refactored the code a bit and added in functionality so the same item cannot be selected twice. This also works for a column of cells starting at row 3:
function onEdit(e) {
if (e.range.rowStart < 3 || e.range.columnStart !== 7 ) {
return;
}
if (e.value && e.oldValue && !e.oldValue.includes(e.value)){
e.range.setValue(`${e.oldValue}, ${e.value}`);
} else if (e.oldValue.includes(e.value)) {
e.range.setValue(e.oldValue);
}
}
Awesome!
I Like to have Place holders for my drop down selection just to make the sheet prettier and identify what the user still needs to fill in. To acomplish this along with your method(that worked wounderfully btw) I used the modified code below:
function onEdit(e) {
if (e.range.columnStart != 4 ) {
return;
}
if(e.value == ("Select a(n) option(s)")) {
set.eValue("Select a(n) option(s)");
}
if (e.oldValue == ("Select a(n) option(s)")) {
return;
}
else if(e.value && e.oldValue && !e.oldValue.includes(e.value)){
e.range.setValue(`${e.oldValue}, ${e.value}`);
}
if (e.oldValue.includes(e.value)) {
e.range.setValue(e.oldValue);
}
}
To Change the Place Holder just change my selection of word choice ("Select a(n) option(s)") with whatever placeholder you have in your listing
This function will also delete the value(s) beforehand if you re-select the placeholder; as-well as remove it if you select something else in the listing removing the need to delete the cell. Deleting the cell does not cause any flow breaks in the code as I can tell.
This effect could also be stacked with further "special selection condtions" coding as long as you keep your logic consistant and/or similar
Note: This does require you to have this physcial listing that you are calling from in your data; as-well as I am trying to read the whole column. So you will find that I have altered the code to work according to that. I left the code uncondensed here for better review :) Hope this can find someones use as did your comment and this video did for me!
@@SpencerFarris Thank you Spencer for this very helpful tutorial! Is there a way to use the code above that covers one column, but exclude certain rows?
@@helenmatsumoto6833 you could include an array of rows to exclude and check against them:
const rows = [5,7,29];
if (rows.includes(e.range.rowStart)) return;
Wow! We had an IT consultant for our office website and I always request this feature for a specific part (we use wordpress) and told me there's no way we could limit it for an option to be selected twice. Thank you so much for this comment!
Super awesome - works for me like a dream. Especially thanks to the comments that include de-duplication, using the function in multiple columns, using linebreaks not comma-space, and working across multiple rows. This is really powerful in so few lines of code. Thanks for sharing! (and supporting so many people)
I love what I do! Thanks for the words
Could you kindly share how to use linebreaks instead of comma-space and work across multiple rows? Thanks.
@@johnblessingbabadara5245 Not sure exactly what you mean?
@spencerFarris
I meant how do I script the code to run across multiple rows and multiple columns of the same sheet?
Now, I have figured out how to do multiple selection for many sheets of the same file and across many rows but I am yet to get how to do it across many Columns of the same sheet.
@@johnb.babadara4141 This line controls what rows/columns it works on. Which range do you want it functioning on?
if (e.range.rowStart != 2 || e.range.columnStart != 3) return;
Man, this is life saving! Such a great tutorial... thanks!
I love this! Helped a lot!
This is even better as this is more than something for us to copy and go. The explanation helped me a lot to alter a bit of the script to match my sheet better!
100% love this
What an awesome tutorial!! Thank you so much, it was clear, concise, and interesting to follow. And thank you for answering the questions below in the comments. I had to tweak the formula so that the dropdowns would function in multiple rows and columns and if you hadn't answered those comments, this tutorial wouldn't be very useful. So many people put tutorials on YT then don't bother to answer the questions that follow, but you made an effort. Again thank you!!
I love the community aspect, so I can't give up on that! Glad it helped!
Thanks for this excellent tutorial. It worked really well as intended. How do I run this for multiple rows and columns in both the same sheet and multiple sheets? Once again, thank you.
This was an extremely helpful video! Unfortunately, I'm not following the coding language enough to figure out the answer to my question from reading your responses to the other questions... How would I need to change the code so that this applies to columns 16-20, starting on row 4?
This is very awesome! Thank you so much for this tutorial and to all the others who contributed in the comments section. You do not know how much you made my life easier at my job! Thank you very much!
yay!
Thank you soooo much from the video. I kinda figured it out from reading the comments although I am still clueless for the coding itself. More power!
Do you need any help implementing it?
@@SpencerFarris i do, i wanted to use the same function on all the rows in the same column but I can only make it work in one cell
@@projectdayao Do you want to add me as an editor on the sheet and we can look at it together?
@@SpencerFarris i want to unfortunately i cannot as this is a work file and includes sensitive info
@@projectdayao Then make a new, realistic copy. It's nigh impossible to help from just the comments given.
@Spencer Ferris Thank you thank you thank you so much for doing this tutorial!!! I was able to get all the answers and solutions I needed by the comments and your video! YOU ROCK!!!
Awesome, so glad!
How should the script be written if the options in validation are from "dropdown" and not from a range?
It's either Values or Range
Thanks for the tutorial! Was able to make some tweaks to the code but how would I change it so that you can't select the same items twice?
Did you try the code from InifinteS3t?
function onEdit(e) {
if (e.range.rowStart < 3 || e.range.columnStart !== 7 ) {
return;
}
if (e.value && e.oldValue && !e.oldValue.includes(e.value)){
e.range.setValue(`${e.oldValue}, ${e.value}`);
} else if (e.oldValue.includes(e.value)) {
e.range.setValue(e.oldValue);
}
}
Fantastic; much more elegant a solution than the one I'd previously found. Thanks! 👍
Glad it's working for you!
Hi Spencer, I have a question. Hopefully hopefully hopefully, you can help 😅🙏🏻
So for context, I have two sheets open in the SAME spreadsheet I'm using (in the video, yours is labeled sheet1 if you don't know what I'm talking about, for me, I have two open). One has my dropdown menu and the other is for my "Choices" that I have for the dropdown. I separated it because I have a lot of choices that I need for the dropdown and I'm constantly adding more. Also, the choices are organized by the columns in sheet2, so they are on Columns B-M and the choices go down to Row 65 on some of the columns, which may or may not go passed 65 once more choices occur to me (Though there are some that only go to like Rows 3-5, which leaves quite a few blank spaces in that particular column, though that's not important, cause if it leaves blanks on the dropdown, I will gladly take it if it lets me select multiple choices).
And as for the dropdown in sheet1, at the moment, the data validation is set to be used for the entirety of Column G (minus the top 5 for organization headers) going from Row 6 onwards.
So what I needed help with, was how do I use this if the "Choices" I want to use span that many columns and rows, while also being on a different sheet on the same spreadsheet? But yet can be used for the entire Column G on sheet1.
Is this in anyway possible? 😅
Hey Michelle,
I *think* it's possible. Could you please add me as an Editor on your Sheet so we can look at the actual setup together? spencer.farris@gmail.com
Hi Spencer,
I'm trying to apply the function for all the cells in columns 10, 11 & 21
As suggested by you to a similar question, I tried the following code to no success. Any ideas?
function onEdit(e){
const cols = [10,11,21];
const r = e.range;
if (cols.indexOf(e.range) == -1) return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
Thank you!
Your tutorial was the only tutorial that gave me a straight answer and made everything clear. Thank you!
I have a question, I was wondering how to prevent duplicates?
What do you mean "prevent duplicates?"
@@SpencerFarris I used the code and I was able to select Abeloth twice. I want to prevent that.
Do you know how? Thank you! :)
@@lvplvp Maybe like this
function onEdit(e){
if (e.range.rowStart != 2 || e.range.columnStart != 3) return;
if (!e.value || !e.oldValue || e.oldValue.contains(e.value)){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
@@SpencerFarris Thank you for replying back! I appreciate that. I tried the code but now I can't multi select anymore.
Hi Spence, could you help me, it is not working at all. I have used this:
function onEdit(e){
if (e.range.rowStart
HI Spencer,
I have learnt a lot from this video and the comments provided but I struggling to apply this function to specific rows, for example apply only to rows 4, 6, 11, 16. I tried this from a previous comment with no luck.
const rows = [5,7,29];
if (rows.includes(e.range.rowStart)) return
like usually simple and effective, thanks so much
thank you so much, it's works so well
That is a wonderfully simple solution thank you!
Fantastic ! Then how to apply to a whole column (example, column 7 ?)
Get rid of e.range.rowStart != 2 in the initial IF statement
I can't see the script editor inside Tools.
Furthermore, I can see App Scripts under Extensions but unable to understand how to link that script to my google sheet.
I made this video before the update moving Apps Script under Extensions.
A script made from the script editor on a Sheets, Docs, or Slides file is part of that file.
Lets say I want all of column j3-j25 what would the code be
on click data validation, I don't have the criteria list from a range, what might be the issue?
Super helpful! But what if i want to delete something? Like if i wanted to delete Cal Omas, so i only have Nas Choka and Abeloth? I cant delete only one option without it messing up
I don't have that written out, as these are meant to be specific sample tutorials. Do you have an idea of how to do that?
How can I make it to work on other cell on the C.
It only C2 that is working.
I have drag to c100 still not working.
It only working on C2 cell only
@Spencer Farris Hello! Great tutorial! I was hoping to apply this to more than 1 column and no limit to the rows. For example, I would like to apply this to columns 2 and 15. Would you mind sharing how that is done?
Use this if statement at the beginning:
if (e.range.rowStart != 2 || (e.range.columnStart != 2 && e.range.columnStart != 15)) return;
@@jahabarsadiq1951 As explained in another comment:
function onEdit(e){
if (e.range.rowStart != 2 || e.range.columnStart != 3) return;
if (!e.value || !e.oldValue || e.oldValue.contains(e.value)){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
Hi Spencer This was very much helpful
Just need to ask I have huge file with nearly 200 cols and over 300 rows and nearly 110-120 of these cols are either formulaised, have data validation or have conditional formatting.
ISSUES I AM FACING:
1.I have tried the code with a small range for the selection criteria and I want to apply this for the enitre column expect the first 3 header rows [For which I have used the following code :
function onEdit(e){
if (e.range.columnStart != 199) return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);}
] Now when I wanted to check if the code works for the first time I had to run the code manually only then it considered multi select.
2. When I select more than 1 option it takes 1 - 2 seconds to display the combined result.
3. As this is the first time I am implementing app script to my file and more data is gonna be added in future possibly more formulaised columns as well as rows will the code make my file slower.
Thankyou again for the tutorial.😇
I'm sorry, I'm not undersatnding the issues. Do you want to send me an email we can work from?
@@SpencerFarris Thanks for the response man, I actually figured what the solutions with some help from google. I just have one doubt how much scalable this solutions is,
i.e. Right now I dont have much data in my sheet so its working fine but will appscript make my sheet slow when I add more data ?
@@MayurPatil-d4o not usually, tbh.
Hi, I amusing your video but it is not working on the newest version of GS. Are you able to help? Thank you.
It still works just fine. What issue are you having?
Hey Spencer Farris,
I want to commit this code for the 2nd row to 100 rows in column 6 (F). Kindly guide me.
if (e.range.rowStart < 2 || e.range.columnStart != 6 || e.range.rowStart > 100) return;
@@SpencerFarris Thank you so much :)
Hey Spencer,
First of all thank you this was a huge help and you were super clear and concise. You didnt try to spent 5 minutes selling me a product or telling me to subscribe which is great.
This has worked perfectly for me with one exception. Currently the code is applying to every sheet in the column the script is set to run on. I am just trying to have it run on one of the sheets in the same document so it does not mess with formulas on others. Any advice to have this script only run on one tab?
Look at this addition in the IF() statement:
function onEdit(e){
if (e.range.rowStart != 2 || e.range.columnStart != 3 || e.souce.getActiveSheet().getName() != "name of sheet") return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
@@SpencerFarris, thank you for your video and thank you for all the answers that you give in the comment section. For this function to work in a specific sheet you misspelled e.souce so the "r" is missing. I struggle a little bit to find out why it's not working :)
@@dornescu.lucian So I did! Going fast outside the IDE it can be easy to miss things. Thanks
Thanks a ton, mate!
Awesome thank you for sharing!
Is there something I could be missing to get this to work? I was able to find the app script under extensions, but when I change my dropdown, nothing in the script happens. I even removed the row/column parameters. If I have multiple sheets, do I need to specify the sheet somewhere? I assumed if it applies to everything and I remove row/column specifiers, it would apply to any cell on any sheet, but it's still not working. I just want to make one column on one sheet a list like this from a drop down.
Do you want to share your file with me and I can take a look?
@@SpencerFarris That would be awesome! I just tried again, and it's running something now, but no visible changes for me...just a delay. I left a comment in the cell of the things I tried. I have coding experience in R and python, but nothing in excel or sheets so I'm not really familiar with the language/syntax, but I know the concept. I just thought this would be fun to try out. Thank you so much for your help!! docs.google.com/spreadsheets/d/1uy0Y7hNhfpO5DKTZOioLyazGbeoVUwZLkVD2OMwTZNI/edit#gid=1345984007
Hey bro, How to limit this to only a particular sheet as I have multiple tabs and it runs on all tabs, also is there way to run script on copy of sheet
In the initial IF statement add `|| e.souce.getActiveSheet().getName() != "tab name"`
@@SpencerFarris Thankyou soo much bro, You were quick & Helped me a lot.
Anything to avoid duplicate selection?
As in comments I saw you suggested something but it didn't worked out for that person
@@SpencerFarris bro it is not working, script runs on other tabs too
@@SpencerFarris function onEdit(e){
if (e.range.columnStart != 8 || e.souce.getActiveSheet().getName() != "New Audit Sheet") return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
What if the choices come from another tab, let's say sheet2 and the drop down selection is in Sheet1 tab, is that possible to work?
I don't follow the question, sorry
Hi, Spencer, great job! I tried to modify it but obviously, I'm doing something wrong. I want the code to work on the entire sheet 1. On sheet 1 I created two drop-don lists (in columns A & E, rows 2-30 [the first row is header]) based on two different ranges on sheet 2, then I did go to Extensions - Apps Script and pasted this code:
function onEdit(e){
if (e.range.rowStart != 2 || (e.range.columnStart != 1 && e.range.columnStart != 26)) return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
saved it, and then back to the spreadsheet, but the new value still replaces the new value in the cell. Could you advise, please?
You have "e.range.rowStart != 2" as an escape condition, meaning this will *only* work on row 2. Try IF(e.range.rowStart == 1 || e.range.rowStart > 30 || (e.range.columnStart != 1 && e.range.columnStart != 5)) return;
Awesome tutorial. I have a question. What if I want each of my dropdown items to be on a different row in the same cell instead of being separated by a comma? Thanks
Replace ", " with "
" which sets a new line in the string
Hi, One more question: is there a way to sort in alphabetical order within a drop-down cell?
@@wildlifesafariadventures Shouldn't be that hard - I don't have it written but you would have to split apart everything in the cell (String.split()), sort them, and push it all out. There may even be a fast sort algorithm since it is just inserting a new item each time.
What di I do if it isn't running automatically?
Check the executiona
I am trying to apply the multiple selection dropdown to columns 20, 23 & 24 on 3 sheets (North, South & Central). How do I change this to be applied to the whole sheet?
This is the script I am using:
function onEdit(e) {
multipleSelectionDropdown(e)
}
function multipleSelectionDropdown(e) {
//Grab information about the user's whereabouts
var activeCell = SpreadsheetApp.getActiveRange()
var activeRow = activeCell.getRow()
var activeCol = activeCell.getColumn()
var activeSheet = activeCell.getSheet()
var activeSheetName = activeSheet.getName()
var newValue = e.value
var oldValue = e.oldValue
//See if the user is where I want him to be
const COL_DROPDOWN = 20;
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = "North"
if (activeCol == COL_DROPDOWN && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN) {
//Execute the code
if(newValue=="" || !newValue) activeCell.setValue("")
else if(oldValue=="" || !oldValue) activeCell.setValue(newValue)
else activeCell.setValue(oldValue + ", " + newValue)
}
}
Where'd you find this code?
Make an array for COL_DROPDOWN and NAME_SHEEET_DROPDOWN then check if the edited cell / sheet is in those arrays
Thank you so much sir you really helped me ❣️ from india
How do I apply this code to more than 1 Cell in that column? Do I have to copy the code and change the cell number let's say 300 times?
You would use the range on the IF() statement. So if you want it to apply to everything in A2:A10 -
IF(e.range.rowStart < 2 || e.range.rowStart > 10 || e.range.columnStart > 1) return
@@SpencerFarris How about applying this code to more than 1 cell in another column? So the choices are in tab 2 and the selection drop down is in tab 1. Is that possible?
@@tiffanypearlbato1550 I don't follow the question
@@SpencerFarris your example shows the values and the drop-down on the same tab. How do you do it when the list of range is on tab 1 and the drop-down is on another tab on the same spreadsheet
@@tiffanypearlbato1550 Hi Tiffany, your question make sense. I was going to ask the same question.
Thank you for the tutorial, its excellent. I got it to work within one tab on a sheet, where I have several tabs that I would like to do a multi select picklist within, though I can't seem to write the script to make it work for one tab, as well as another. Am I not saving the script correctly, to then create a new one in another tab? Help?
It should work with all tabs already, there is no code restricting. The code as written works on all tabs on cell C2.
@@SpencerFarris I've got the same issue, it only works on the first tab, not on the others.
@@forgodani please show your full code
@@SpencerFarris
function onEdit(e){
if (e.range.columnStart != 32) return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
Hi Spencer, Great Video. I have copied all the code you wrote line for line but it doesn't work here. Please help. On the sheet itself it says undefined; for example from a drop down list featuring construction, renovation, remodeling, demolition; it says undefined, demolition and when i select another from the drop down list it says undefined, construction. Also the error box is Invalid: input must fall within specified range. Please Help.
Can you add me as an editor to the sheet?
@@SpencerFarris Yes bro sure. What is your email?
how would I extend the multiselect drop down menu to the entire column?
Remove the "e.range.rowStart" from the initial IF statement
Hi bro, what is the code if the dropdown and the database on the different sheet? NICE video by the way
This code only changes the dropdown. The location of the database is irrelevant.
Hi! I know I have dropped so many comments already saying thank you. I just have one question, I tried scrolling to see if someone has asked the same and is already answered but couldn't find any :( I just want to ask if it is possible where I want column 4 to be separated by commas while the column 9 be separated by semi-colon. Thank you again and hope I get a response from this.
Sure! There are several places that could be added. I think I would do it like this:
function onEdit(e){
let cols = [4,9];
let del = [", ","; "];
let index = cols.indexOf(e.range.columnStart);
if (index == -1) return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + del[index] + e.value);
}
@@SpencerFarris Thank you so much!
@@SpencerFarris Hello, it's me again. I just have another inquiry. I hope you do not mind. When I try to remove an option in between the list separated by commas, or when I try to edit the cell with multi-select drop down, it automatically copies and pastes the list in the same cell making it repetitive. Or whenever I mistakenly added the wrong dropdown option yet when I want to remove or delete it, it does not do it. Do you perhaps know how to resolve this? Thank you in advance.
@@ainachan29 I'm not following... can you add me as an editor? spencer.farris@gmail.com
What if want to use it for the entire column?
Then remove the part from the IF statement that says "e.range.rowStart != 2"
Is this outdated? Not even the first pass of this (the simple concatenation) works. Just, nothing happens with the dropdowns, other text will change, just not the dropdowns.
I just tested on the shared spreadsheet and it worked fine
Hi question what if you have multiple sheet what will be the code for it?
I would need to know more to help
@@SpencerFarris My spreadsheet have multiple number of tabs/sheet. So how would i be able to apply the script/code to one specific sheet only?
@@Brutnoy In the initial IF() statement add e.source.getActiveSheet().getName() != "tab name"
How will we apply this to the rest of the rows?
Just take out the part that says e.range.rowStart != 2
@@SpencerFarris yes I did that. Thanks.
And did it work? @@charmz0701
How to set for all row and 9 column?
For all of column 9?
IF(e.range.columnStart != 9) return
Getting error when running script: 19:57:10 errror
TypeError: Cannot read property 'range' of undefined
onEdit @ Code.gs:2
See: paste.pics/FE1C2
Don't run the script: onEdit() scripts are not meant to be run manually, but will run automatically when the proper edit occurs
@Himanshu Shukla Same answer I gave Robert
NOT WORKING
It worked fine when I tested it a couple of weeks ago. What issue are you having?
How can I make it to work on other cell on the C.
It only C2 that is working.
I have drag to c100 still not working.
It only working on C2 cell only
@@isiakamusa-z9j This line says that it only runs on C2 if (e.range.rowStart != 2 || e.range.columnStart != 3) return;
If you want it to be all of C use if (e.range.columnStart != 3) return;
I will try that now
thank you
done
but how do i do similar thin on D
This tutorial was completely useless for me. I have no idea how to deploy something, you don't show any of that process, no idea how to make this script active and work or how you managed to go to the sheet and select your ranges or cells you chose... I'm new to google sheets, used scripts before with excel, this video was missing some of the fundamentals of how to create a script in the first place, unfortunately this is useless for me.
Hi Araya!
I'm very sorry this wasn't helpful for your needs. One great challenge is that since this and my other early videos, Google changed the menu. So when I made this video Tools > Script Editor was the correct path. Now, however, the path is Extensions > Apps Script. That will take you to the same place.
The script itself is intended to be used on a range where Data Validation (which creates dropdowns) has already been created. That is, the script does not *create* the dropdowns, it only changes the functionality where dropdowns already exist so that you can perform multi-select. In my example I constrain the range using the initial IF statement. This particular one, for instance, only operates on row 2 column 3: C2. If I wanted it to work on all of B2:B I would use IF(e.range.rowStart < 2 || e.range.columnStart != 2) return;
Go to Extensions > Apps Script, paste the code, and you should see it operating on C2, and the range of operation can be modified. I'm happy to help with any specific implementation.
@@SpencerFarris Thank you very much for your detailed reply! I'll give all this a shot! Much appreciated.
function onEdit(e){
if (e.range.rowStart > 4 || e.range.columnStart != 25 || e.source.getActiveSheet().getName() != "2024") return;
if (!e.value || !e.oldValue){}
else
e.range.setValue(e.oldValue + ", " + e.value);
}
TypeError: Cannot read properties of undefined (reading 'range')
onEdit @ Code.gs:2
do you know this problem?
Yeah, you're running the script. It runs automaticaly whenever an edit is made.
You save my life