Multiple Selection Dropdown with Google Sheets and Google Apps Script - 2024
Vložit
- čas přidán 28. 08. 2024
- In this tip, we will learn how to create a dropdown that allows to select multiple options in Google Sheets
➤You can find the template from this video in my patreon page, where you´ll also have priority responses to your questions
practicalsheet...
You can also join my academy practicalsheets.com, where you´ll find, not only the templates for this and all the videos, but also Google Sheets Courses, technical service, and more
➤You can also subscribe to the channel where you´ll find a new Sheets video every week.
Here are some of the places where you can follow and support me:
➤Patreon: practicalsheet...
➤Webpage: practicalsheet...
➤Newsletter: practicalsheet...
➤Telegram: practicalsheet...
➤Facebook: practicalsheet...
➤Twitter: practicalsheet...
Any suggestion, question or insights, feel free to comment below
Wow. I have *never* watched a coding video where I didn't have an problem (due to user error) until yours. Thank you SO much for explaining this clearly and giving the additional explanations so I actually knew why I was doing things.
Haven't coded anything since I was a teenager on tumblr and even then I was pretty helpless lol. You explained it so perfectly in a way that made each step logical and made a beginner like me understand. Thank you!
Such kind words!
Thank you so much!
J'aime beaucoup votre démarche, les erreurs qui surviennent dans l'exécution du code et les corrections que vous apportez progressivement permettent un véritable apprentissage👌. Sorry for the french, thank you so much.
Merci beacoup!
J'ai veux faire videos en francais mais mon francais c'est comme ci comme ca
Thank you!
Thank you. Best advice I've found for this. I appreciate you taking the time to write something out. Much appreciated!
Thank you for the kind comments!
Thanks so much for this! Not going to admit how long it took me to set up but I'm finally done.
Excellent!!!
Thank you so much!! used this at work and all my coworkers are impressed ;)
That's the idea!
Glas it worked
Kind Regards
This was REALLY clearly and patiently explained - many thanks!!!!
Thank you so much!
Thank you so much! It helps a lot
Also, it amazes me how how big and slow google became. To do a simple thing one has to watch 30 minutes video
Jaja, you are right, it should be simpler
Hopefully in the future
Kind Regards
@@practicalsheets es gibt kaum eine Chance, aber wir warten werden
I love how easy this was to follow! Thanks for sharing :)
Is it possible to have the multiple selections still appear as chips and have them color coded?
Thanks for the kind words
I am sorry for the wasted time, buuuuuuut, apparently Google Sheets will launch this functiolaty natively...IN AUGUST 2024. If I were you I would wait. Specially, because it will bring the chips and coloring you desire
You should have it in 2-3 weeks in your account
I know i'm self-sabotaging here, but what can I do
Kind Regards
Useful and easy to understand tutorial. Thank you!!
Thank you for commenting
Kind Regards!
Thank you for this! It took some time to make sure the code was right so that it was running smoothly but It works! I wanted to ask if it's possible for this to work on another column in a separate sheet under the same workbook? Ex: Column A in Sheet 1 (Multiple Select Drop) and Column B in Sheet 2 (Multiple Select Drop)
Thank you for commenting, and for the effort in making it work for you!
As for your second question: Yes. Use this snippet
Remove const COL_DROPDOWN = 2;
Use this
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = [["Main",2],["Data",1]]
let currentSheetArray=NAME_SHEET_DROPDOWN.find(row=>row[0]==activeSheetName)
if (activeRow >= STARTING_ROW_DROPDOWN && currentSheetArray.length==2 && activeCol==currentSheetArray[1]) {
The rest remains the same
Kind Regards
I had the exact same question. Thank you for asking this. And thank you, @practicalsheets for answering.
"After 15 min of this, we haven't done anything actually" :D you are funny
Thank you for your words!
This was amazing, thank you so much!
Thank you!
What could be the reson this doesn't work for me. The multiple selection dropdown does not work at all.
Hello again! Thank you for the helpful information! Do you have any advice on how to format the multi-select options like this "apple|orange"?
Thanks for the comments!
You could try something like this
else{
if(oldValue.indexOf(newValue)==-1) activeCell.setValue(oldValue + "|" + newValue)
else activeCell.setValue(oldValue)
}
Let me know if it works
Kind Regards
very nice video, that's exactly what I need. But the question is how reliable this script is, won't it crash with new Google Sheets updates and will a filter of the column show options with multiple selection dropdown option?
Hello!
It should work fine, HOWEVER, Google Sheets announced that it is planning to launch in August-September 2024 this function natively. If you are not in a rush, I would wait. Although it may render this video useless, any function that is native would be better, specially if you are thinking about reliability in the future.
Kind Regards
@@practicalsheets thanks for the great answer, do you have any link to the news or maybe you could provide with advice how to track that update?
@@avk1018 Of course, keep posted to this blog workspaceupdates.googleblog.com/. Here is the original workspaceupdates.googleblog.com/2024/07/release-notes-07-26-2024.html Regards!
@@practicalsheets THANK YOU VERY VERY VERY MUCH!!!
This was very helpful, Thank You!
Thank you!
Hello, thank you for the helpful information! I am trying to get the multiple selection dropdown across multiple different columns. Do you have any advice on how to copy this script across multiple columns?
Hello!
Yes
YOu could use something like this
//See if the user is where I want him to be
const DROPDOWN_COLS = [2,3,4]
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = "Main"
if (DROPDOWN_COLS.indexOf(activeCol)>-1 && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN) {
//Execute the code
Kind Regards
Thank you for your Dropdown solution. I have a few further queries. Let's say, my hobbies changed after selecting at the start. Initially, I selected Reading, Writing, Painting and Gardening. But now I want to update the list to only include Reading and Gardening. But now I need to delete the whole thing and add it again.
Is there a way to only deselect writing and painting?
Another query - How do I place each selected option on a new line? So if I selected Reading, Writing, Painting and Gardening as my hobbies, How do I put a line break? So the presentation should be:
Reading
Writing
Painting
Gardening
Lastly, is there a way we can remove the data validation error?
Hello!
1. Difficult, but not impossible. There would have to be some importaant modifications to the code
2. Instead of "," try using this "
•"
3. I haven't found a way to remove the validation error
Kind Regards
@@practicalsheets Thank you for your prompt reply.
Thank you so much for this. I am trying to get the multiple selection dropdown across 3 different sheets. Do you have any advice on how to copy this script across multiple sheets?
Hello!
Yes, just do it like this
Change these 2 lines
const NAME_SHEET_DROPDOWN = "Main"
if (activeCol == COL_DROPDOWN && activeRow >= STARTING_ROW_DROPDOWN && NAME_SHEET_DROPDOWN==activeSheetName) {
For these
const NAME_SHEET_DROPDOWN = ["Main","Data"] //Here you include all sheets you want it to work in, separated by commas
if (activeCol == COL_DROPDOWN && activeRow >= STARTING_ROW_DROPDOWN && NAME_SHEET_DROPDOWN.indexOf(activeSheetName)!=-1) {
Hope it helps!
@@practicalsheets I was able to get the script to work for my multiple columns but still not across multiple sheets! What am I doing wrong? Thank you so much for all of your help!
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, 23, 24];
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEETS_DROPDOWN = ["North", "Central", "South"]
if (COL_DROPDOWN.includes(activeCol) && activeRow >= STARTING_ROW_DROPDOWN && NAME_SHEETS_DROPDOWN.includes(activeSheetName)) {
//Execute the code
if (newValue == "" || !newValue) activeCell.setValue("")
else if (oldValue == "" || !oldValue) activeCell.setValue(newValue)
else activeCell.setValue(oldValue + ", " + newValue)
}
}
Hello! Thank you for this. I was able to get it working a couple of days ago, but now it has stopped. I need to apply to multiple workbook pages and multiple columns within each page. Suggestions?
Hello!
For multiple columns try this
const DROPDOWN_COLS = [2,3,4]
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = "Main"
if (DROPDOWN_COLS.indexOf(activeCol)>-1 && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN) {
For various sheets, try this
//See if the user is where I want him to be
const COL_DROPDOWN = 2;
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = ["Main","Data"]
if (activeCol == COL_DROPDOWN && activeRow >= STARTING_ROW_DROPDOWN && NAME_SHEET_DROPDOWN.indexOf(activeSheetName)!=-1) {
Now, your challenge is to mix both in one (Is not hard)
Kind Regards
If someone want to deselect instead of prevent duplicates like in [28:47] you can replace this else in line 26 with:
else {
var newValueArray = oldValue.split(", ");
var newArray = newValueArray.filter(function(item) {
return item !== newValue;
});
activeCell.setValue(newArray.join(", "));
}
And you are good to go :)
Nice one! Thank you for sharing
I commented on the wrong video. I need to add more sheets with this same formula. How do you do that?
Hello. You could watch this video
czcams.com/video/CmJTIWZvs60/video.html
Kind Regards
Firstly - thank you SO much for this fantastic video. I won't pretend to understand it all, but the step-by-step got me further than I've been with this project I am working on. I do have a question if you don't mind: Is there a way to separate added values by an ENTER or new paragraph key (rather than simply a space and a comma) - essentially stacking answers on top of each other? (I am also looking to convert to an array for the purposes of alphabetizing the responses, and I saw your reply to another user below - but I'm not quite sure where to put that coding).... I'm asking, as my dropdowns begin with a key character such as "A" for activities. So my dropdown list contains "A - activities, C - content, E - exercise, M1 - meal delivery, M2 - meal escort, M2 - meal reminder..." I am restricting my column widths to only show the first two characters, so I'd like to stack my dropdown entries as they are selected. I could edit my dropdown list to eliminate the wording after the hyphens, but my users will need those reminders for what the selections stand for. I hope this makes sense.
I am attempting to replace line 25 with this to get the new paragraph: if(oldValue.indexOf(newValue)==-1) activeCell.setValue(oldValue + "
" + newValue)
Hello!
Try this
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{
if(oldValue.indexOf(newValue)==-1) activeCell.setValue(oldValue + "
•" + newValue)
else activeCell.setValue(oldValue)
}
}
Kind Regards
Great 👍🏼
Is it possible to add searchable dropdown list in case the list had a lot of items to choose from?
Same question I have !
@abdullahquhtani4247 Don't know if it applies to this scripted dropdown list, and maybe you already have solved it yourself, but anyway... 😊 In a normal dropdown list you can just start to write the name of the Item you want to select, and you will get suggestions showing all the alternatives with that text in the name. For example: if you have the hobbies "Model building" and "painting models" in you list, and you write "model" in the cell where the dropdown list is, you will get both those hobbies as suggestions. Hope this helps. 🙂
thank you. very informative. what if i want to have multiple drop-down columns so not just column B but in column C and D as well? how do i change the code in line 16?
Yes. I'm wondering the same thing.
You could do it in one of two ways:
1. Include each column as a constant and a condition
const COL_DROPDOWN_1=3
const COL_DROPDOWN_2=4
if ((activeCol == COL_DROPDOWN_1 || activeCol == COL_DROPDOWN_2) && ...
2. Include all necessary columns in an array
const DROPDOWN_COLS = [2,3,4]
and then modify the conditions
if (DROPDOWN_COLS.indexOf(activeCol)>-1 && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN)
Hope it helps
Kind Regards
@@practicalsheets Thank you so much, I was having a lot of problems with this, but then I read this comment and it worked!
I have followed this and your other videos, and it keeps popping up with a "failed" on the test page. I am not sure what I am doing wrong. Is there a template I can buy and import my own information into it instead?
Thanks in advance.
Hello
You may download it on the patreon page
Hint: In a couple of weeks, Google will rollout this functionality natively to Sheets. If you can wait, I would try it out
Kind Regards
This is an extremely useful explanation! However, I still have a problem after adding the last if/else functions - it seems like they erase the selection completely, so I get no value recorded regardless of how many times I made a selection. If I remove them, the Undifined thing comes up and I didn`t figure out what`s wrong
Hi!
If you want you can paste your code and I can help you
Regards!
Great video. In my example I'm using this for assigned technicians to a job. After completing this, can I then multiply the selected technicians from the dropdown by another "hours worked" column if they all get paid different amounts, but all work the same hours?
For sure
Let's say you have another Sheet where you have the list of Technicians, and you want to see how much is owed
Then you would need to have a SUMIFS with the condition "*John*"
Maybe we would even need a SUMPRODUCT
Kind Regards!
@@practicalsheets thank you!
Great video, one question though. I did this for 2 columns in the same sheet, I just copied the script from the first and used it for the second and just edited the column portion.
The first column (E) is working perfectly, however the second column (I) only works up to row 3. Do you know how I can fix this or why this is happening when the script is identical (except for the column) and how I can fix it?
Thanks in advance for any help 🙏
For 2 columns, you may use something like this
//See if the user is where I want him to be
const DROPDOWN_COLS = [1,2]
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = "Main"
if (DROPDOWN_COLS.indexOf(activeCol)>-1 && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN) {
Kind Regards
@@practicalsheets That worked! Thank you so very much! 😁
Thank you for making this! It has made my database run more smoothly, especially when entering new data. Is there a way I can make it so the cell automatically sorts itself alphabetically when I have multiple things selected in a dropdown. Instead of it always being old value, new value.
Thanks for your words
Hmmm....interesting
MAybe turning the list into an array, sorting it, and then turing it back into a string
Something like this
if(oldValue.indexOf(newValue)==-1){
var values=oldValue + ", " + newValue
var array=values.split(",")
array.sort()
values=array.join(",")
activeCell.setValue(values)
}
You should test it and let me know if it works
Not: this "sort()" will sort alphabetically and separate words that begin with caps
Regards!
@@practicalsheets That did it! I would have never thought of turning it into an array first. Thank you so much.
@@practicalsheets Mind my asking where this code would be inserted in your example? Would I be replacing the if(oldValue...) in line 23 or would this be in addition to & placed elsewhere?
@@suzanne.oneill777 Replace the line with the **
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{
**if(oldValue.indexOf(newValue)==-1) activeCell.setValue(oldValue + ", " + newValue)**
else activeCell.setValue(oldValue)
}
}
Kind Regards
Hi I followed the same formula but when I select the options it is automatically disappearing. What could be the cause?
You should have soemthing a bit different. Double check the code please
Kind Regards!
Have the scripting commands changed? I don't see "getRow" or "getColumn" inside of Apps Script....
Hi!
If you are using the "e" variable, unfortunately these methods won´t show up in the autocomplete feature of GAS
This is why I dont like using e that much
Regards!
Thank you so much for this video! You explained everything so clearly, and the code worked perfectly. What if I have two columns with two different sets of selectable data that I want to have the script work in? For example I have a dropdown in column 13 with 4 dropdown options built, and then in column 17 I have another set of dropdown options. I tried changing this line "const COL_DROPDOWN = 13" to " const COL_DROPDOWN = 13 || 17" but this did not work..any suggestions??
Hello!
Although it may not be the most efficient solution, I would try this
1. create a new function like this
function multipleSelectionDropdown2(e) {
...
2. add the function on the onEdit, like this
function onEdit(e) {
multipleSelectionDropdown(e)
multipleSelectionDropdown2(e)
}
Then you can customize each function
Kind Regards
@@practicalsheets Wow! Worked like a charm..I was trying everything I could think of to get that to work properly! Thank you so much for all the help and knowledge!
I have everything exactly written out and essentially copied to how you have it on 16:07, but whenever I try to execute the code after saving, there is no notification that pops up for "you changed something" and it does not allow me to continue the rest of the steps throughout the video. What am I missing?
What specific error is the editor giving you?
Kind Regards
@@practicalsheets the editor isn’t showing any error, is there anywhere shown on 16:07 that might have to e biggest pool for error?
@@xaviergarcia4146 What I normally do, is test if the code is entering my condition. For this I add a Logger.log as a first line inside my if. Something like Logger.log("It enters!!!")
And then you may do the same for especific conditions
Normally is something very basic, for example you forgot a space in the name of the sheet, or a capital letter or your column is not 2 but 3
Kind Regards
There is no option under Data Validation Rule for "if the data is invalid" - the only advanced option is the style. I have made sure the sheet is not protected and can be edited, is there a way to get those options to come up in the advanced options?
They should appear
Are you using tables by any chance?
Kind Regards
@@practicalsheets yes using a table!
@@SteveHandler-i9s Unfortunately, and for reasons I don't know, dropdowns in tables don't have all options 😔
How to extend column range in App script for multiple drop down with multiple columns
Hello! Wednesday, July the 24th I'll publish a video with this specific case
Kind Regards
Thanks a lot .so helpful for me and i am seeking It is possible to add color for each option
I was wondering the same! Did you ever find a solution to this?
Thanks you!!
Thank you for commenting!
For some reason the old value and new value code is not working on my end. All I can see is undefined and the new value. May I know what is the reason for this?
could you share your code? sebastian@juansguzman.com
Regards!
@@practicalsheets
function onEdit(e) {
multipleSelectionDropdown(e);
}
function multipleSelectionDropdown(e) {
// Grab information about the user's whereabouts
var activeCell = e.range;
var activeRow = activeCell.getRow();
var activeCol = activeCell.getColumn();
var activeSheet = activeCell.getSheet();
var activeSheetName = activeSheet.getName();
var newValue = e.value;
// See if the user is where we want them to be
const COL_DROPDOWN = 5;
const STARTING_ROW_DROPDOWN = 2;
const NAME_SHEET_DROPDOWN = "Multiple Dropdown";
if (activeCol == COL_DROPDOWN && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN) {
// Get the old value from the cell before the edit
var oldValue = activeCell.getValue();
// Check if the cell is being cleared
if (!newValue) {
activeCell.setValue("");
} else if (!oldValue) {
activeCell.setValue(newValue);
} else if (oldValue.indexOf(newValue) === -1) {
activeCell.setValue(oldValue + ", " + newValue);
} else {
activeCell.setValue(oldValue);
}
}
}
@@ERROR5-8 This wont work
var oldValue = activeCell.getValue();
It should be
var oldValue = e.oldValue
Kind Regards
Is anyone else experiencing issues with copying? Whenever I try and copy a cell or drag the same value that includes multiple values in it, it reverts to blank..
You are right, for some reason copy and paste, won´t work well in this situation. I'm trying to find out why
Kind Regards
@@practicalsheets Legend, thank you so much!
I followed the video exactly, and I can't get my code to trigger. Any thoughts on why it's hung up and won't execute? I am a bit green to Apps Scripts
Could you show me the code
Regards!
@@practicalsheets absolutely! I'll C/P it here but if it's easier, I can share the project link as well.
function onEdit(e){
multipleSelectionDropdown(e)
}
function multipleSelectionDropdown(e) {
//Grab information about the user's wherabouts
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 them to be. (E.g. execute only in column B)
const COL_DROPDOWN = 3;
const STARTING_ROW_DROPDOWN = 3
const NAME_SHEET_DROPDOWN = "Project Status"
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)
}
}
@@practicalsheets Sure thing! I will just C/P it here but I can share a link to the project as well if it helps.
function onEdit(e){
multipleSelectionDropdown(e)
}
function multipleSelectionDropdown(e) {
//Grab information about the user's wherabouts
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 them to be. (E.g. execute only in column B)
const COL_DROPDOWN = 3;
const STARTING_ROW_DROPDOWN = 3
const NAME_SHEET_DROPDOWN = "Project Status"
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)
}
}
@@practicalsheets I keep trying to reply, but my comment is disappearing.
@@b.christinephotography3431 So weird
If you want you can write to practical.sheets@gmail.com
Hello, do I duplicate the code if I also want to create a multi-select for the student column (column 1 in this tutorial)? Because I tried it and I got the student column to work but then the hobbies column stopped working. Can you do a part 2 please to show up how to setup another column in the same 'main' sheet.
It is a great idea, I'll start working on it
For now, you could use this snippet
const COL_DROPDOWN_1 = 1;
const COL_DROPDOWN_2 = 2;
const STARTING_ROW_DROPDOWN = 2
const NAME_SHEET_DROPDOWN = "Main"
if ((activeCol == COL_DROPDOWN_1 || activeCol == COL_DROPDOWN_2) && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN)
Regards!
@@practicalsheets Hi, I do not know where to place that code. Did you make a part 2 to this tutorial yet?
@@Angela-bb8hg Not yet, but very soon
@@practicalsheets This is how I placed the extra code...The first column I have a drop down selector is col 12, so I am starting there, then I also want the multi-select dropdown in columns 13, 14 and so on. Not sure if this is right? Tried it and it's not working.
//SM Multi-select Code
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 them to be
const COL_DROPDOWN_12 = 12;
const COL_DROPDOWN_13 = 13;
const COL_DROPDOWN_14 = 14;
const STARTING_ROW_DROPDOWN = 3
const NAME_SHEET_DROPDOWN = "Main"
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)
}
}
Hi! I was wondering, can you still create graphs like this? So for example:
Sector x Focus Area [Multiple Dropdown]
Hmmm
I'm not sure I understand
I would say not, but I'm not completely sure what you want to achieve
Kind Regards
@@practicalsheets Thank you for the reply! I mean, is it still possible to make pivot charts from columns with multiple drop-downs, with the table still thinking that each variable is its own variable?
For example, if you have a multiple dropdown of fruits bought by a person:
Sally bought Mango, Papaya, Banana
Jeff bought papaya, banana
Then the table would be like this still?
EXAMPLE PIVOT TABLE
Sally (1 Mango) (1 Papaya) (1 Banana)
Jeff (0 Mango) (1 Papaya) (1 Banana)
or would it end up like this?
Sally (1 Mango, Papaya, Banana)
Jeff (1 Payaya, Banana)
@@ccahernandez You are right!
It is a drawback of this method
You would still need to "extract each item separately in order to use in reports or graphs
However, there are ways to do it with formulas such as split
Kind Regards
@@practicalsheets Thank you so much for this!
@@practicalsheets YOU ARE THE BEST!!!!! :)
Thank you!
Can we write a code to change the warning text to "Multiple options selected in this cell". That would be useful then.
Hmmm, very clever idea! You could change it in the advanced options of your dropdown
Kind Regards
@@practicalsheets Ok, so it doesn't require any coding then?
@@Angela-bb8hg Exactly. Just go to Data validations. Choose the rule, and go to advanced options (way down!). There is an option for custom alert text
Kind Regards
This is great! Thank you. How would I go about having multiple different multiple-selection columns in a sheet? Given that I have given parameters for the 'constants' i.e. COL_DROPDOWN etc.
I want to know this also
Figured it out. I added a new const
const COL_DROPDOWN_2 = 6;
updated
if (activeCol == COL_DROPDOWN || COL_DROPDOWN_2 &&....
@@nickduda8777Life saving. But do you find that the rest of your cells now when you type in something new (TEST) and then go back to edit it (TESTER), that the cell then shows both values separated with a comma (TEST, TESTER) ?
I am following closely but it says activeSheet is not defined. what did I miss?
Sometimes it may be a space or a capital letter missing from the name of the sheet
Check this first
Kind Regards
I have some issue with it : "TypeError: Cannot read properties of undefined (reading 'value')
at multipleSelectionDropdown(Code:12:18)
at onEdit(Code:2:3)"
any idea how to solve it please ?
I think in var newValue should be =e.newValue, not just value
Hi!
It won´t work if you execute it in the editor. It will only work using the dropdown
Regards!
Hi! It is value. Regards!
Do you have a document of this codes?
Hello!
Of course
You can find it in the patreon page
www.patreon.com/practicalsheets
Kind Regards
Can you let me know if there is a way to make the code work for multiple columns not just B it would be a real help
Hello! There are a couple of ways. You could have several variables: col1, col2. The condition would be: if((activeCol==col1 && activeCol==col2) &&
Regards!
@@practicalsheets Sorry for bothering with another question but which line of the code would I put that in just a little confused coding is new to me😅
@@Damakas73 No worries
Here
if (activeCol == COL_DROPDOWN && activeRow >= STARTING_ROW_DROPDOWN && activeSheetName == NAME_SHEET_DROPDOWN)
Kind Regards
Hmmm, can't find where I messed up the code, but whatever I select in the drop down, it gets deleted in a few moments 🙈
Can you share the code?
Regards!
Do you have a copy of the script that we can plug in?
Hello!
Yes!
In the patreon page you'll find the whole file and script to copy
Kind Regards
I hate that it is 2024 and this highly demanded feature is not built into Google Sheets.
Let´s hope there are news soon. Right now Google is hands-full with inserting Gemini anywhere they can. You'll soon find the Gemini sidebar
Kind Regards
I got the mistake, can you help with it? Code is exactly the same with yours:
7:41:30 PM Error
TypeError: Cannot read properties of undefined (reading 'value')
multipleSelectionDropdown @ Code.gs:12
onEdit @ Code.gs:2
Remember the code should not work if you run it directly from the editor.
Kind Regards
when i run the script. It give me an error "Cannot read properties of undefined (reading 'value')". Does Anyone know how to fix?
same ! :( "TypeError: Cannot read properties of undefined (reading 'value')
multipleSelectionDropdown @ Code.gs:12
onEdit @ Code.gs:2"
Hello!
The problem with running these functions with "e" is that they wont work if you hit "Run" on your GAS Editor. The only way they work well is if you do the action "live". In this case is going to the dropdown and trying it out
To see errors and Loggers, you need to go then to "Executions" in your GAS sidebar (the logo has 3 lines and a play button)
Kind Regards
code share?
What if we only want erase one value
I thought it was for free. It is unfortunate for student like me can access this for free :(
Hello!
I try to go step by step so that you are able to build it from scratch.
If you have any doubt plese let me know it
Kind Regards
Are you available for hire ?
This code does not work in 2024. Please don't spend time using this. Many of us are getting an error, "TypeError: Cannot read properties of undefined (reading 'value')"
Hi!
It does work
It won´t work if you execute it from the editor as I explain in the video
Regards!
I just did it and works,,, Thanks +Suscribed!
I had a proble with the indexOf, The message replies "Syntax Error"
Can you show me the code and the error?
Kind Regards
Hello! Thank you so much for your video! Is there a way to make it so when they click on an option twice, it deletes the option instead of just not adding it? I feel like that is a common expectation people have when selecting things. Thank you!
Interesting
You could try this
Change the last line for this one
else activeCell.setValue(oldValue.replace(newValue," "))
Let me know if it works
Kind Regards
@@practicalsheets Hello! Thank you! it does work! the only annoying thing is it is leaving the comma.
@@EmilyBigelow-bv4ph else activeCell.setValue(oldValue.replace(newValue+","," "))
Regards
@@practicalsheets Thank you!!