Multiple Selection Dropdown with Google Sheets and Google Apps Script - 2024
Вставка
- Опубліковано 20 гру 2023
- 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
practicalsheets.com/patreon
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: practicalsheets.com/patreon
➤Webpage: practicalsheets.com
➤Newsletter: practicalsheets.com/newsletter
➤Telegram: practicalsheets.com/telegram
➤Facebook: practicalsheets.com/facebook
➤Twitter: practicalsheets.com/twitter
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!
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!
Thank you so much!! used this at work and all my coworkers are impressed ;)
That's the idea!
Glas it worked
Kind Regards
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!
This was REALLY clearly and patiently explained - many thanks!!!!
Thank you so much!
Thanks a lot .so helpful for me and i am seeking It is possible to add color for each option
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
This was very helpful, Thank You!
Thank you!
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) ?
Useful and easy to understand tutorial. Thank you!!
Thank you for commenting
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
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
This was amazing, thank you so much!
Thank you!
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.
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
Thank you!
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)
}
}
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 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
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!
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
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?
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!
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
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! 😁
Thanks you!!
Thank you for commenting!
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!
What if we only want erase one value
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)
}
}
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 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.
How to extend column range in App script for multiple drop down with multiple columns
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!
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
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!!!!! :)
code share?
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
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!
Are you available for hire ?
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!
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
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);
}
}
}
@@christianpaulocampo5104 This wont work
var oldValue = activeCell.getValue();
It should be
var oldValue = e.oldValue
Kind Regards
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 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
"After 15 min of this, we haven't done anything actually" :D you are funny
Thank you for your words!
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
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 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
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!!