⭐Grab the FREE demo sheet here: www.gotsheet.xyz/c/multiple-selection-data-validation ❗MOST COMMON MISTAKE: When you make a copy of the sheet, go to Data Validation Advanced Options and under "If data is invalid:" make sure "Show a warning" is checked instead of "Reject Input". Otherwise, nothing will happen when you try to fill the current selection from the script.
Hi Eamonn, I am trying to run this, however, it is not doing anything when clicking Fill current - I have checked all the issues you mentioned and in the comments. Does this only work if there is one column of Data Validation? I have 3 columns, and it is not working on any. - Any thoughts?
@@penpapertheatre Hey there, the 3 columns shouldn't cause an issue...are you able to share or make a copy and share the spreadsheet you're using, and I'm happy to take a look (my full name at gmail)
@@federicopereira2902 There have been continued issues in cases involving Google Workspaces permissions. For instance if you're using or you created the sheet with a Workspace account that is at your work, for instance, there may be restrictions on the permissions allowed to run the script. Several people who have run into this have solved it by using a different, personal Google account.
I've been looking for something like this for sooo long! I found Ivanov's video yesterday and have been trying to find something more current. Nothing I've tried up to now gave me the results I needed. Thank you soooo much!!!!!
Hi Eamonn, thank you for this! Super helpful. I got the script to work for me, but I'm wondering if you know if there's a way for the side-bar to automatically popup when clicking into a cell that has data validation (instead of having to click on the multiple select data validation -> show dialogue)??
So, yes, in theory you could wrap some IF statements inside an onSelectionChange trigger. This way, every time you click any new cell, the onSelectionChange trigger would fire. Then inside there, you could grab the active cell's address and check it against the range where your data validation lives. If it matches, then you could have the side-bar popup code nested in there. The trouble is, I suspect this'll take a big performance hit since so much stuff is happening every time you click anywhere.
This is great! However I was wondering, is it now possible to filter or sort the results to quickly show all where a given item from the list was selected? Of course you can sort by column as usual, but if you're looking for all rows with "Red", say, and sometimes it's "White, red, green" and sometimes it's "Red, yellow" or "Brown, red", when just sorting alphabetically those will be all over the place in the column.
now how would i go about pulling this data so that it can be imported into a chart? i got this to work but now i need to pull the data into another tab for progress tracking specific to key parameters
You can use Arrayformula to simply pull the full dataset into another tab. From there you can manipulate it further if you need to. In the example sheet, =ARRAYFORMULA('3⃣ Turtles Sample'!A1:F7) will take that main data range and put it on another tab.
Hi, Eamonn! Noce tutoria! I really need your help. After applying the script, the question arose of calculating the values. According to the classics, I tried to use COUNTIF, but it basically does not see the value separated by commas. When I output a list with a multiparameter, the formula counts it as a single cell. Do you know the solution?
Hey there, sure thing...Let me know if I'm on the right track for what you need: If you're trying to add up the numbers separated by commas, you can do something like this: =SUM(UNIQUE(SPLIT(A2,", "))) If you just wanted to count how many of the values there were, you could do this: =COUNT(UNIQUE(SPLIT(A2,", "))) So if you've got 55, 44, 11 in cell A2, the first formula gives you 110 and the second gives you 3
Thank you for this. It works wonders. One point though, I can get it to do multiselect in one column only. I have another column with a separate dropdown list. When I hit the refresh in a cell from this new column, the list gets refreshed and I can see the new choices. However, although I can multiselect by ticking the boxes, when I press 'get validation from current' the script does not do anything. Is there a limitation to the number of columns this code can work in? Am I missing something?
Great question; I hadn't tested that until now. Check out I21:K25 in the demo sheet. Yes, you should be able to do this fine. Pressing 'get validation from current' is only going to pull the values available in the data validation into that side bar check list. It won't have any of them checked. But you can use this in multiple ranges with different sets of data validation like I've done in that demo range. Let me know if that makes sense, or if I can clarify further. 👍👍
Sure thing, in column F it's counting the unique values in each of the lists of multiple selections in column E. That uses COUNTA() to count the values in a dataset. But we're using the SPLIT() function inside it to split out each value that is separated by commas. Then in column I, we're looking for each specific value in the multiple selection values that have been picked in column E by splitting them out again. In this one, I'm using JOIN and SPLIT because I'm looking in all of the rows in E instead of just one cell. For example, if you wanted to count the number of orange values in just E6, you could just use =COUNTIF(SPLIT(E6, ", "),"orange")
Wonderful! Thanks for the clarification. I believe that there was a recent update with Sheets, where there is only 'checkbox' or 'dropdown', 'dropdown (from a range)', I don't see 'list (from a range)'. Can this be done with dropdown or checklist? I tried and have not been successful.
I keep clicking 'fill current' after clicking multiple options and it doesn't seem to either accept the action from 'fill current' or it ran but nothing in the cell changed
@@ughmlyUnder advanced options in the data validation rules, make sure that "Show a warning" is selected under the "If the data is invalid" section. This is the most common reason for the fill current not to work.
Thank you so so much for this. We were searching for a solution on this for a while. Finally able to get it done! I have a question though: Currently we can select multiple options and they are visible with commas, is there a possibility that after being selected the data is seen in different rows within the cell? If yes, how can we do it? Would be a great help if you can help with this as well. Thanks!
Glad to help! And, yes, you can change on thing on line 25 to get the values on newlines within the cell. Line 25. Change the join(', ') part to join(' '). I've put the line as an example, but commented out, on line 26 of the demo file. Email me if you need a hand with this - my full name at gmail.
I have a question. if I put a values on those colours and I chose 2 colours, did I get a sum of the value on each color I choose or a separate value numbers?
Hi! Thanks for making this useful video. I have a question, I add some values to my range and refresh the scripts. The values of cells that I have filled before with the oldest range just be there as a value. I mean nothing has been selected and ticked. So when i want to add another value to it, I should select all of them again. Thanks
Good question; yes, if you modify the list of values and then want to go back and change a previously filled cell, you will need to tick all the options you now want it to fill. It will in essence overwrite anything in that cell already. The list of values and the values already filled are not dynamically linked. Think of it as the program automatically copying and pasting for you. If you do it again it will paste over the previous values.
Hi there! Thank you so much for putting this together Eamonn, I believe that I've followed the instructions but I'm still running into an issue with the sheet populating the values, as they're not coming up. I changed the setting under "If data is invalid:" to be "Show a warning" instead of "Reject Input," but still nothing is happening. Would you be able to take a look? Thank you!
Happy to check it out and see what's going on. Sometimes there's an issue with being signed into a different chrome browser profile than the profile that owns or created the spreadsheet. You can share your with me at my full name at gmail, though, and I'll see what I can uncover. Thanks!
Hello! I've looked through the comments and have checked on common issues such as: 1. Show a Warning instead of Reject Input 2. Re-logging in my google drive 3. Make a copy of the original sheet that I created. I could use the 'Reset Checkbox Selections' but not the fill current. Wonder if you can help me with this. Thank you!
Thank you for this, it has been a great help. I was wondering is it possible to make the sidebar a little bit narrower, its not the end of the world if not.
Unfortunately, no, they've got a fixed width currently that can't be modified as far as I know: developers.google.com/apps-script/add-ons/concepts/dialogs#:~:text=Note%3A%20Add%2Don%20sidebars%20have,the%20sidebar%20using%20that%20HtmlOutput%20.
Thank you very much!! How can you make the data to be always in the same order? I use the same validation data in a filter so I can filter but the data adds in a diferent order and then I cannot find a coincidence… thank you!
Works awesome, thanks! I need others to also be able to add this data unfortunately so went back to creating a list with all combinations of the multiple selection items. But great video!
Thank you for this video, it's also working on my end. However I just want to ask how to fix this problem: I tried making a list similar to your "Count of Colors" but it doesn't tally correctly. Also, I want to ask if this gsheets can be collaborated on by multiple people? As long as they run the script? Thank you, hoping you can help me out.
Glad it's working! For the Count of Colors, check that your delimiter in the JOIN and SPLIT functions have the spaces after the commas. If you still have issues, you can share with me and I can take a look (my full name at gmail)...and yes, multiple people can collaborate on your sheet. I believe that for one running the script, it will likely pop up that initial warning box having them accept the permissions required to run the script.
Is there a way to then sort by favorite color and still see the turtles with multiple selections, when I tried the group of selected colors was a new filter and trying to sort by any of the individual resulted in the multi group not showing.
Do you mean after you've added multiple items to a cell? You can re-fill the data into that cell and it will overwrite what you'd previously put there.
Yes, you'll need to File -> Make a Copy in order for anything to be editable. Once you do, you should be able to run that script, get validation from current cell and fill current cell (if there is data validation in the current cell) 👍
When selecting 'Get Validation From Current' - the sheet/script seems to think there's no data validation (post adding said validation...). Any thoughts?
Interesting. And you're on the cell with the data validation when you click that? I checked the demo sheet and the script is still working. Did you make any changes? I can take a look if you share yours with me (my full name at gmail)
@@LachlanSkene-r4r Emailing you the solution...short version is you found a glitch with how the new tables in Google Sheets work...but also some code needed correcting. Email coming...
Hello, thank you for the informative video. I have followed the steps right to the end. When I get to the data validation step I dont get a pop-up, I have a sidebar. In the sidebar there is no list from range, only drop down from range. When I click get validation from current and then try to click ,multiple boxes, nothing appears in the cell. Thanks for any help you can give
Hey there, they changed the data valida4tion to a sidebar and the wording to "drop down from a range". Use that in the same way that I did in the video for list from a range. Everything should be working the same. You can make a copy of the sample sheet and edit for your purposes if you're running into issues still. Make sure in advanced options that show a warning is selected instead of reject input. Here's the sample sheet: docs.google.com/spreadsheets/d/1yxc4k1x5idcS_moQ1Bq8LnHGZF2nm9dpARglY7Rv0UI/edit#gid=0
@@EamonnCottrell Hello, thank you for the quick response. So I have done this, used the 'dropdown from a range' and then when I try to click for example 3 of the checkboxes, then press 'fill current' it does not fill anything in the cell. What could be causing this
@@AnneClarke-s5pHuh. Interesting. I've run into issues with Google account permissions where folks have been logged into Chrome using a different profile than the one they're using in Sheets. You might try making sure you're in the same profile; I honestly don't know why this causes issues. Feel free to share your file with me if you still have issues & want another pair of eyes on it (my full name at gmail)
Hi Eamonn - really useful script, thanks for this. I have noticed that the order the multiple selections appear in the cell is random, and can change depending on order clicked etc. Is there a way to ensure they always appear alphabetically for example? I want to then use a look up of the chosen selection to work out a calculation so need to limit the combinations? Thanks
Huh. I hadn't noticed that before, and that is weird. I will have to look at that and maybe re-write the code because it's not obvious to me why it is random like that.
To add to this I noticed it's random when it's viewed but in the application menu and drop-down it shows up in the in the correct order @@EamonnCottrell
@@EamonnCottrell Hi and many thanks. I noticed the same thing. The selected data does not respect the order of the list. It also happens in your video around 3:09 - blue, red they have the opposite order as shown in the List column. Have you had a chance to check, please? It would be wonderful to respect the order of the column in which we put the data. Thanks again for the fabulous solution!
@@andreamichielinI haven't had a chance to dive deeply into this yet. It's not immediately clear to me why it's not respecting the order, so it's gonna take a bit of digging when I am able to dedicate time to a potential re-write.
@@EamonnCottrellI had this same question! Will you reply to my comment to notify me when/if you ever solve the randomization? Thank you, and great video!
Seen a few other examples that I couldn't get to work but this one worked for me and is the most flexible. I had to deal with Google Authorization issues though. With 2FA on the account, it looks like you can't use the default built-in Google Cloud project but you have to change the project to a "standard" project. Fortunately, I'd messed with it before and had a project I could attach it to. The process is not for the faint of heart. Once I got it, however, it worked.
Interesting, David. I haven't run into any 2FA issues so before. Would it not let you simply accept the pop-up warnings after making a copy of the file?
So, yes, you could create two form sections in the sidebar. I don't think you can create two sidebars. But, the way the code is setup, it's only reading the data validation values for the active cell, so the forms would have identical values in them. If in your sheet you have different cells with different data validations in them, though, the sidebar will update by clicking get validation from current for each of the active cells. You don't have to add anything for that. You would just set up new data validation values in different cells.
Hi Eamonn - This video is great! Quick question: Is it possible to have each individual item in the cell come up as separate chips and not just one long list? I am trying to populate a dashboard with different options based on the results.
In the result cell instead of the comma-separated list of favorite colors? You could have them split up by using SPLIT(", ",E2) for example. This will put each value in cell E2 between commas in it's own cell. But if I misinterpreted, let me know. Happy to look at a sample sheet if you want to share one with my full name at gmail
Hey Annie; have you made a copy of my sheet so you can edit it? And then have you accepted the pop ups when you first click the dropdown menu? It'll ask you if you trust the code etc. Let me know any details about the issue you're having and I'll try and help further 👍👍
When everything else has been followed correctly, some folks have run into issues being signed into one google profile on chrome but using a different one in sheets.
Did exactly the same, but not working on my sheets version, when i am clicking fill current, can you help? Is it the version issue, as there is not list(from a range), it is dropdown (from a range now)
No, it's probably that it's set to reject the input instead of show a warning. Go to Data Validation and click Advanced Options. Change "if data is invalid" to "Show a warning" and you should be good.
Awesome video!! Very clear and helpful. Question - do you know of any other reasons why when I select "fill current" the dropdown box doesn't fill? I made sure the box says "show a warning"
Hey Ally; glad it's helpful for you! I have had a couple folks who had issues because of some glitches with how they were signed into Google Chrome profiles. For instance, being signed in under one profile in Chrome, but using the Google Sheet on another one. We have not pinpointed the exact reasons for this. But if you try out creating a copy from another Google account or making sure you're signed in the same way everywhere, that can sometimes help. I think there may be some Workspace permissions at the root of a few of the issues when folks have used work accounts for instance. Let me know if you keep hitting a wall and I'm happy to take a look (my full name at gmail)
@@EamonnCottrell Thanks so much for the quick reply! I played around with a few different browsers and google accounts and got it to work! Appreciate your help!
Oh no! 😥 have you changed anything in the apps script or data ranges? I can take a look if you still are having issues. Share it with my full name at gmail
The most recent issue was resolved when the user signed out of Google Drive and then back in. We never figured out what was going on; a glitch in the matrix maybe.
When you share the sheet with integrated multiple selection options, as showed in the video, does the script automatically share between users or is it locked and seen only to the creator of the original? I am trying to create a workspace shared file, that has this option, but it is pointless if the script has to be added manually for every person, that uses the sheet. Thank you in advance for your response
Great question! The script does share with anyone you have added to the Google Sheet. It's just like information you have in a cell in the spreadsheet. When the other user goes to use it for the first time, they will be prompted with the same security pop up that they will have to click through the first time they execute the script. But you won't have to manually add the script for every person.
Hello, I was hoping you could assist me. When running this script I keep receiving this error "Exception: Cannot call SpreadsheetApp.getUI( ) from this context." Any ideas? Thank you.
That's strange; I have not received that error before. I've investigated it a little and am not sure what is causing it for you. Did you make a copy of the spreadsheet before you tried to run the script? When is it giving you the error? I did find someone suggesting to restart the browser as well as refresh the spreadsheet. I hope one of these is helpful for you! Try making another copy first because there shouldn't be a problem with the script itself. Thanks!
If I create a sheet with this type of multiple selection and share it with others to fill out - will they be able to multi-select without having to download the same script?
They'll be able to use it no problem. The script is already attached to the sheet you'll share with them. However, it will prompt them like it does for you on the first time it's run to accept those permissions. Every new user running a script will have to click through that pop up dialog box on the first time they run it.
I found this because my old code stopped working. I followed the tutorial first and then video second. Both times the "get validation from current" does not work. checked, the warning/reject setting, reauthorized the script, tried a different browser and still havent figured it out. Any ideas? Thanks for being so active in the comments!
Hey Carlton; sorry it's giving you trouble. The last person who had this kind of difficulty had a sign-in issue where they were in a different Chrome profile than the Google Drive account. Try signing out of GDrive and back in. You can share it with me if you continue to hit a snag and I can take a look (my full name at gmail)
@@EamonnCottrell Thanks! I just shot you an email with the link. I tried totally signing out and duplicating it in safari to make sure and still didnt see a change. Also tried it with a regular gmail account instead of google workspace with the same result.
@@EamonnCottrell I see what you are saying now. In the past I had it set up so that it would pull the validation fields as well as check the existing values that were in the cell. This was quite helpful when I wanted to make a slight change to a longer list without having to reselect them all. Thanks for the help.
This was very helpful, particularly when I have no idea about writing code or adding scripts. One thing I would like to do is have each option select appear on a new line rather then a continuous line, is that possible? I know you can do this in Excel. If it can be done can you tell me how to adjust the script to make that work. Thanks
Hey Alyson! Yes, this is an easy alteration. In the Code.gs file on line 24 change the value from a comma to so it reads: s.join(' ')); at the end of the line of code. That will put the values on newlines in the cell without the comma. Thanks!
Dear Cottrell Thank you so much for this video. I have been looking for it. I need your assistance, fill current can't work when drop down selection is in the same cell with validation. Multiple selection requires another cell different to cell with validation criterias to display results. Besides, One selection work perfectly. Thank you
Hi there! I'm not able to reproduce you're problem-everything is working ok for me in the sheet. Are you working in a copy of the sheet and have you allowed the permissions on the script to run like in the video? If you built you're own sheet, you can share it with me and I can take a look.
@@constantinrukataza2139 Thanks! I found the issue...the data validation rules are set to reject the input on invalid data. What we're doing with the script will technically get flagged by Google Sheets as invalid which is why there are still those little red arrows in the top right of the cells in the video. For your spreadsheet, select the E2:E8 range where you're trying to use the multiple selection and open the data validation rules. (Data -> Data Validation). Click Advanced Options and then select "Show a Warning" under the "if the data is invalid" spot. Thanks!
Everything seems to work except the "Fill current" button, which only works if I have one items selected. If I select more than one item the "Fill current" button does not work. Any help would be appreciated! Thank you.
That's weird, Austin. I've re-tested on my end and it's working ok still. Did you make a copy of the spreadsheet? Sometimes Google Sheets bugs out on me and closing and reopening my browser will fix the issue.
@@janglingjack Good call; that could be it. Under the advanced options in the data validation menu. The original sheet is set up to show a warning, but if a copy was made it could be on reject input.
Open up the Apps Script editor and change line 24 of the Code.gs file. Here's what the full line will look like. All you need to do is replace the comma and space inside that join() statement toward the end with whatever you want instead: if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(' | '));
I keep running into the same issue where when running the script - when selecting "fill current" nothing happens! have selected dropdown from a range and checked everything else to make sure it is the same as well
Hey Arman, try closing your browser all the way and going back in. Several others with that issue have had it clear up this way. If it's still an issue, can you share the sheet with me and I'll take a look at what's going on? Thanks!
Is there any way I can avoid the script for multiple dependent drop down lists? I havea sheet with over a 1000 rows and every time I add a new data, I must refresh the sheet so that there are no errors Please help
Are you able to share your sheet or a copy of it so I can take a look at what you mean? Not sure exactly but I’ll take a stab at it. (My full name at gmail)
For some reason this does not work for me. When I hit "Fill Current" nothing happens. I have noticed that sheets only has the data validation is "dropdown (from a range)" now, which is different than List From a Range? It's the only difference I can see...
Hey there, I've reconfirmed that it works for me still; I've got it using dropdown(from a range). Can you share a link to the sheet you're having trouble with and I can check it out?
@@EamonnCottrell I somehow got it working after I posted this. I think I was trying to fill the wrong cell with data. It's fine now, thank you for this!
Hello Sir. Thanks for the video. If the cell already contains one or more entries, would it be possible that the checkboxes are already checked when the sidebar loads?
You'll need to create a filter to filter or sort by colors. This guy did a great, short video showing how to do that: ua-cam.com/video/7VgpDfLlKmc/v-deo.html
@@moiragray2649 Ah, gotcha! Sorry...yes, you can do that in kind of the same way. You'll want to select the table including the column titles (i.e. fav color) and create a filter (Data->Create Filter). That'll let you click the Fav Color filter and select the "text contains" option. Type in purple or whatever there, and it'll filter down to only those that include whatever you type. Let me know if that makes sense; I can do a short video if it doesn't translate well here!
There have been continued issues in cases involving Google Workspaces permissions. For instance if you're using or you created the sheet with a Workspace account that is at your work, for instance, there may be restrictions on the permissions allowed to run the script. Several people who have run into this have solved it by using a different, personal Google account.
Thanks a lot for this! For some reason the selection appears in the cell on the right instead of the actual cell with the drop down. Do I need to change something in the script?
You're welcome! That's interesting - so it is possible to click "fill current" while on another cell after "getting validation" from the current cell...and it will put the selection there on the active (but incorrect) cell. Did you perhaps move to that other cell on accident?
@@EamonnCottrell Thanks for getting back to me. No, I thought I did but I'm staying on the cell with the drop down and it doesn't work like you. It just fills the next cell. When I only select one option though, it works as intended and fills the cell with te drop down. I also notice that when I paste the Code.gs, the line of code that says "if(i.substr(0, 2) == 'ch') s.push(e[i]); " the "substr" appears crossed out. Not sure if that could be it.
@@irinamakasso70 Huh, weird. I'm not sure what would be causing that. I can take a look if you want to share the sheet you're using with me. And, no, that substr thing is crossed through on mine too (I'm not sure why, but it still works 😀)
Hi Alessandra, did you make a copy of the sheet and then allow the script permission to run? I double checked just now by making a fresh copy and it works normally like in the video. From time to time, Google Sheets will bug out, though. If you've followed the instructions, try closing your browser all the way and then re-opening. This has cleared up random bugs like this in the past. Let me know when you get it fixed and if it was something else! Thanks!
@@alessandrasilva5407 Interesting. Can you share a link to the sheet for me to check out? I made another one with a bunch more options, and it let me fill in 28 unique values. Not sure if there is a limit or not, though.
🤔 I'm fairly certain you could do this natively without a script. For one, your solution is a bit of a bad data practice - you're jamming multiple values into a single cell. If you wanted to filter characters based on who likes purple, 3:30, you will have to do some data cleaning in order to split Raph's color choices. A better method would be to create a separate table with the color choices. This is how it should be done, especially if the data is going to be used for analysis in something like Power BI. The table would contain all color choices, then combined only on this table. I get it that it's just for a kid to easily select colors, but I see a lot of comments with people wanting to use this for more than just playing with spreadsheets. 😕
💯agree, Chris, and thanks for the note. Re-doing this video with other/better methods has been on my agenda for a while now. It works, but I've hit the same snag with it being a little more work than necessary especially for cleaning purposes. Keep an eye out for an update soon...have a great one!
I have done all you suggested in the comments, shut down browser, show warning instead of reject, and reput in the code data multiple times. I was hoping i could share it with you if you have a free moment to help?
Probably 🤣🤣. I've thought about revisiting this to try a simpler approach. I am pretty confident it will always require some programming via Apps Script, though, until Sheets changes the data validation requirements.
I have made a separate video about building a search box. Check it out here and let me know if you've got an idea for an enhancement: ua-cam.com/video/5xgwvokDhT0/v-deo.html&lc=UgxDGsPpSXWLmRRd2fh4AaABAg
@@EamonnCottrell Thanks for your quick response. Dynamic search bar is good. But what I requested was search feature inside drop down list. For example : Type 'an' and it should filter Animal Rohan Rajan Janwar
@@sajinj2457 Ah, I see. Yes, that would be nice. I will add it to my project list. That will require some additional coding to add a search bar. I would probably re-write the whole thing if I added this in order to split out a javascript file to handle the search bar logic.
@@eMaNoN-kk UPDATE: Yes, I've got a working version of this now. You can check it out here for the time being. I will make a new, cleaner sheet and video with this added in the future. This was a great idea. Thanks for nudging me; I'd forgotten to add this functionality: docs.google.com/spreadsheets/d/146b24P792kgcXIuWvupdqmV1ETq8zhXotf3PXNuGanw/copy
this is great! and almost works for me---except when i hit "fill with current". literally nothing happens. don't know why. I've followed exactly as you've shown. is there a limit to the amount of items you can have in the range? i have 71.
Yay! Glad you got it working. I will add a comment about the advanced settings to the sheet today. I think it’s defaulting to that reject input when it used to default to show a warning. Have a great one!
Thank you for making this video Eamonn! It helped me a ton. I was able to get your sheet to populate with the Multiple Selection Data Validation. However when I try and use this new menu option on another sheet I get a ERROR code "Script function not found: showDialog" If you could you help me resolve this I would really appreciate it.
So it sounds like you have the menu pop up code in the page.html but perhaps not the actual function in the code.gs file. Did you perhaps copy everything into one scrip file instead of two separate script files?
My understanding is that Google will automatically prompt for permissions based on the script written. This is just a script and not an app that has been submitted as an official addon yet, so it prompts for permissions the first time it’s run, even when I make a new spreadsheet and use it again. This article breaks it down pretty well too: spreadsheet.dev/authorizing-an-apps-script
@@DoDo-dy2fs nothing to worry about 👍. Here's an article I wrote breaking down in more detail everything the script is doing: www.freecodecamp.org/news/google-sheets-multiple-data-validation-selections/
⭐Grab the FREE demo sheet here: www.gotsheet.xyz/c/multiple-selection-data-validation
❗MOST COMMON MISTAKE: When you make a copy of the sheet, go to Data Validation Advanced Options and under "If data is invalid:" make sure "Show a warning" is checked instead of "Reject Input". Otherwise, nothing will happen when you try to fill the current selection from the script.
Hi Eamonn, I am trying to run this, however, it is not doing anything when clicking Fill current - I have checked all the issues you mentioned and in the comments.
Does this only work if there is one column of Data Validation? I have 3 columns, and it is not working on any. - Any thoughts?
@@penpapertheatre Hey there, the 3 columns shouldn't cause an issue...are you able to share or make a copy and share the spreadsheet you're using, and I'm happy to take a look (my full name at gmail)
@@EamonnCottrell Thanks! I have sent this across - Cheers
Jordan
@@penpapertheatre @EamonnCottrell I am also having this same issue - any suggestions?
@@federicopereira2902 There have been continued issues in cases involving Google Workspaces permissions. For instance if you're using or you created the sheet with a Workspace account that is at your work, for instance, there may be restrictions on the permissions allowed to run the script. Several people who have run into this have solved it by using a different, personal Google account.
I've been looking for something like this for sooo long! I found Ivanov's video yesterday and have been trying to find something more current. Nothing I've tried up to now gave me the results I needed. Thank you soooo much!!!!!
That's awesome! So glad you found it; such a relief to find the quirky solutions like this after searching for so long! 😁😁
Hi Eamonn, thank you for this! Super helpful. I got the script to work for me, but I'm wondering if you know if there's a way for the side-bar to automatically popup when clicking into a cell that has data validation (instead of having to click on the multiple select data validation -> show dialogue)??
So, yes, in theory you could wrap some IF statements inside an onSelectionChange trigger. This way, every time you click any new cell, the onSelectionChange trigger would fire. Then inside there, you could grab the active cell's address and check it against the range where your data validation lives. If it matches, then you could have the side-bar popup code nested in there.
The trouble is, I suspect this'll take a big performance hit since so much stuff is happening every time you click anywhere.
this is the closest feature that we need for better management. thanks, we really appreciate it 👍
This is great! However I was wondering, is it now possible to filter or sort the results to quickly show all where a given item from the list was selected? Of course you can sort by column as usual, but if you're looking for all rows with "Red", say, and sometimes it's "White, red, green" and sometimes it's "Red, yellow" or "Brown, red", when just sorting alphabetically those will be all over the place in the column.
Yes, check out K3 on the Turtles Sample tab. This is how you can filter the results if they contain the color selected in L1
now how would i go about pulling this data so that it can be imported into a chart? i got this to work but now i need to pull the data into another tab for progress tracking specific to key parameters
You can use Arrayformula to simply pull the full dataset into another tab. From there you can manipulate it further if you need to. In the example sheet, =ARRAYFORMULA('3⃣ Turtles Sample'!A1:F7) will take that main data range and put it on another tab.
Hi, Eamonn! Noce tutoria! I really need your help. After applying the script, the question arose of calculating the values. According to the classics, I tried to use COUNTIF, but it basically does not see the value separated by commas. When I output a list with a multiparameter, the formula counts it as a single cell. Do you know the solution?
Hey there, sure thing...Let me know if I'm on the right track for what you need:
If you're trying to add up the numbers separated by commas, you can do something like this: =SUM(UNIQUE(SPLIT(A2,", ")))
If you just wanted to count how many of the values there were, you could do this: =COUNT(UNIQUE(SPLIT(A2,", ")))
So if you've got 55, 44, 11 in cell A2, the first formula gives you 110 and the second gives you 3
@@EamonnCottrell Thank you very much, this is exactly what I need! =)
Thank you for your help. I appreciate it. I did get it to work. Now I can select multiple values where it is appropriate.
Excellent! You’re welcome.
Thank you for this. It works wonders. One point though, I can get it to do multiselect in one column only.
I have another column with a separate dropdown list. When I hit the refresh in a cell from this new column, the list gets refreshed and I can see the new choices. However, although I can multiselect by ticking the boxes, when I press 'get validation from current' the script does not do anything.
Is there a limitation to the number of columns this code can work in?
Am I missing something?
Great question; I hadn't tested that until now. Check out I21:K25 in the demo sheet. Yes, you should be able to do this fine. Pressing 'get validation from current' is only going to pull the values available in the data validation into that side bar check list. It won't have any of them checked. But you can use this in multiple ranges with different sets of data validation like I've done in that demo range. Let me know if that makes sense, or if I can clarify further. 👍👍
@@EamonnCottrell Cheers - will have a look a report back
Hello, thanks for the tutorial!
I want to ask something, Could you please explain how to count the data from the multiple selection?
Sure thing, in column F it's counting the unique values in each of the lists of multiple selections in column E. That uses COUNTA() to count the values in a dataset. But we're using the SPLIT() function inside it to split out each value that is separated by commas.
Then in column I, we're looking for each specific value in the multiple selection values that have been picked in column E by splitting them out again. In this one, I'm using JOIN and SPLIT because I'm looking in all of the rows in E instead of just one cell.
For example, if you wanted to count the number of orange values in just E6, you could just use =COUNTIF(SPLIT(E6, ", "),"orange")
Wonderful! Thanks for the clarification.
I believe that there was a recent update with Sheets, where there is only 'checkbox' or 'dropdown', 'dropdown (from a range)', I don't see 'list (from a range)'.
Can this be done with dropdown or checklist? I tried and have not been successful.
I keep clicking 'fill current' after clicking multiple options and it doesn't seem to either accept the action from 'fill current' or it ran but nothing in the cell changed
Ah, gotcha. The Dropdown(from a range) option is the same as the old "list(from a range)"
@@ughmlyUnder advanced options in the data validation rules, make sure that "Show a warning" is selected under the "If the data is invalid" section. This is the most common reason for the fill current not to work.
Damn, this still works and is exactly what I was looking for! Thank you so much!
Outstanding! It’s the script that keeps on giving
thank you sir. it's really works and help me alot.
Thank you so so much for this. We were searching for a solution on this for a while. Finally able to get it done! I have a question though: Currently we can select multiple options and they are visible with commas, is there a possibility that after being selected the data is seen in different rows within the cell? If yes, how can we do it?
Would be a great help if you can help with this as well. Thanks!
Glad to help! And, yes, you can change on thing on line 25 to get the values on newlines within the cell. Line 25. Change the join(', ') part to join('
').
I've put the line as an example, but commented out, on line 26 of the demo file.
Email me if you need a hand with this - my full name at gmail.
I have a question. if I put a values on those colours and I chose 2 colours, did I get a sum of the value on each color I choose or a separate value numbers?
To get the sum of the values, I believe you'd need to split them up and then sum them. You could do this with one formula: =SUM(SPLIT(,","))
Thanks so much Eamonn!
You're welcome!
Hi! Thanks for making this useful video.
I have a question, I add some values to my range and refresh the scripts. The values of cells that I have filled before with the oldest range just be there as a value. I mean nothing has been selected and ticked. So when i want to add another value to it, I should select all of them again.
Thanks
Good question; yes, if you modify the list of values and then want to go back and change a previously filled cell, you will need to tick all the options you now want it to fill. It will in essence overwrite anything in that cell already. The list of values and the values already filled are not dynamically linked. Think of it as the program automatically copying and pasting for you. If you do it again it will paste over the previous values.
Thank you Eamonn, really helpful, thank you for taking the time to put this together.
You’re welcome! Glad it was helpful for you, Kirten 👍
Hi there! Thank you so much for putting this together Eamonn, I believe that I've followed the instructions but I'm still running into an issue with the sheet populating the values, as they're not coming up. I changed the setting under "If data is invalid:" to be "Show a warning" instead of "Reject Input," but still nothing is happening. Would you be able to take a look? Thank you!
Happy to check it out and see what's going on. Sometimes there's an issue with being signed into a different chrome browser profile than the profile that owns or created the spreadsheet. You can share your with me at my full name at gmail, though, and I'll see what I can uncover.
Thanks!
Hello! I've looked through the comments and have checked on common issues such as:
1. Show a Warning instead of Reject Input
2. Re-logging in my google drive
3. Make a copy of the original sheet that I created.
I could use the 'Reset Checkbox Selections' but not the fill current. Wonder if you can help me with this.
Thank you!
Interesting. Not sure what's going on here. Can you share the sheet with me to check out? my full name at gmail.
@@EamonnCottrell Hello! Thank you for the reply :) I've shared the file with you.
@@LeonardDCA Great; emailing you now...
Thank you for this, it has been a great help. I was wondering is it possible to make the sidebar a little bit narrower, its not the end of the world if not.
Unfortunately, no, they've got a fixed width currently that can't be modified as far as I know: developers.google.com/apps-script/add-ons/concepts/dialogs#:~:text=Note%3A%20Add%2Don%20sidebars%20have,the%20sidebar%20using%20that%20HtmlOutput%20.
Thankyou for responding :D@@EamonnCottrell not everyone does
@@corky608You bet! Happy to help if I can
Thank you very much!! How can you make the data to be always in the same order? I use the same validation data in a filter so I can filter but the data adds in a diferent order and then I cannot find a coincidence… thank you!
How to Sort Values in a Cell in Google Sheets
@@EamonnCottrell thank you very much!
Works awesome, thanks!
I need others to also be able to add this data unfortunately so went back to creating a list with all combinations of the multiple selection items. But great video!
You're welcome! Glad you found a workaround to what you needed it to do too 👍👍
Thank you for this video, it's also working on my end. However I just want to ask how to fix this problem: I tried making a list similar to your "Count of Colors" but it doesn't tally correctly.
Also, I want to ask if this gsheets can be collaborated on by multiple people? As long as they run the script?
Thank you, hoping you can help me out.
Glad it's working! For the Count of Colors, check that your delimiter in the JOIN and SPLIT functions have the spaces after the commas. If you still have issues, you can share with me and I can take a look (my full name at gmail)...and yes, multiple people can collaborate on your sheet. I believe that for one running the script, it will likely pop up that initial warning box having them accept the permissions required to run the script.
Is there a way to then sort by favorite color and still see the turtles with multiple selections, when I tried the group of selected colors was a new filter and trying to sort by any of the individual resulted in the multi group not showing.
Hmm. Are you able to share the sheet in question. I don't quite follow what's going on.
Yeah no problem. I've sent an email with a few more details and the link to the sheet.@@EamonnCottrell
Sure, I've sent an email with a few more details and the link.@@EamonnCottrell
thank you so much!! But is there a way to remove the repetitions?
Do you mean after you've added multiple items to a cell? You can re-fill the data into that cell and it will overwrite what you'd previously put there.
Hi! I need your help. The "fill current" option isn't working on my sheet. Do I need to start over on a new sheet?
Yes, you'll need to File -> Make a Copy in order for anything to be editable. Once you do, you should be able to run that script, get validation from current cell and fill current cell (if there is data validation in the current cell) 👍
When selecting 'Get Validation From Current' - the sheet/script seems to think there's no data validation (post adding said validation...).
Any thoughts?
Interesting. And you're on the cell with the data validation when you click that? I checked the demo sheet and the script is still working. Did you make any changes? I can take a look if you share yours with me (my full name at gmail)
@@EamonnCottrell Thank you, shared!
@@LachlanSkene-r4r Emailing you the solution...short version is you found a glitch with how the new tables in Google Sheets work...but also some code needed correcting. Email coming...
Hello, thank you for the informative video. I have followed the steps right to the end. When I get to the data validation step I dont get a pop-up, I have a sidebar. In the sidebar there is no list from range, only drop down from range. When I click get validation from current and then try to click ,multiple boxes, nothing appears in the cell. Thanks for any help you can give
Hey there, they changed the data valida4tion to a sidebar and the wording to "drop down from a range". Use that in the same way that I did in the video for list from a range. Everything should be working the same. You can make a copy of the sample sheet and edit for your purposes if you're running into issues still. Make sure in advanced options that show a warning is selected instead of reject input. Here's the sample sheet: docs.google.com/spreadsheets/d/1yxc4k1x5idcS_moQ1Bq8LnHGZF2nm9dpARglY7Rv0UI/edit#gid=0
@@EamonnCottrell Hello, thank you for the quick response. So I have done this, used the 'dropdown from a range' and then when I try to click for example 3 of the checkboxes, then press 'fill current' it does not fill anything in the cell. What could be causing this
@@AnneClarke-s5pHuh. Interesting. I've run into issues with Google account permissions where folks have been logged into Chrome using a different profile than the one they're using in Sheets. You might try making sure you're in the same profile; I honestly don't know why this causes issues. Feel free to share your file with me if you still have issues & want another pair of eyes on it (my full name at gmail)
Hi Eamonn - really useful script, thanks for this. I have noticed that the order the multiple selections appear in the cell is random, and can change depending on order clicked etc. Is there a way to ensure they always appear alphabetically for example? I want to then use a look up of the chosen selection to work out a calculation so need to limit the combinations? Thanks
Huh. I hadn't noticed that before, and that is weird. I will have to look at that and maybe re-write the code because it's not obvious to me why it is random like that.
To add to this I noticed it's random when it's viewed but in the application menu and drop-down it shows up in the in the correct order @@EamonnCottrell
@@EamonnCottrell Hi and many thanks.
I noticed the same thing.
The selected data does not respect the order of the list.
It also happens in your video around 3:09 - blue, red they have the opposite order as shown in the List column.
Have you had a chance to check, please? It would be wonderful to respect the order of the column in which we put the data.
Thanks again for the fabulous solution!
@@andreamichielinI haven't had a chance to dive deeply into this yet. It's not immediately clear to me why it's not respecting the order, so it's gonna take a bit of digging when I am able to dedicate time to a potential re-write.
@@EamonnCottrellI had this same question! Will you reply to my comment to notify me when/if you ever solve the randomization? Thank you, and great video!
This is gold, thank you so much!
You're welcome!
Seen a few other examples that I couldn't get to work but this one worked for me and is the most flexible. I had to deal with Google Authorization issues though. With 2FA on the account, it looks like you can't use the default built-in Google Cloud project but you have to change the project to a "standard" project. Fortunately, I'd messed with it before and had a project I could attach it to. The process is not for the faint of heart. Once I got it, however, it worked.
Interesting, David. I haven't run into any 2FA issues so before. Would it not let you simply accept the pop-up warnings after making a copy of the file?
@@EamonnCottrell nope - just said it was blocked. I looked up the message and found out about the 2FA and the other project type.
Gotcha. Good to know; thanks!
Are you able to create 2 tabs in the sidebar and have each one with their own multiple selection data validation?
So, yes, you could create two form sections in the sidebar. I don't think you can create two sidebars. But, the way the code is setup, it's only reading the data validation values for the active cell, so the forms would have identical values in them.
If in your sheet you have different cells with different data validations in them, though, the sidebar will update by clicking get validation from current for each of the active cells. You don't have to add anything for that. You would just set up new data validation values in different cells.
Hi Eamonn - This video is great! Quick question: Is it possible to have each individual item in the cell come up as separate chips and not just one long list? I am trying to populate a dashboard with different options based on the results.
In the result cell instead of the comma-separated list of favorite colors? You could have them split up by using SPLIT(", ",E2) for example. This will put each value in cell E2 between commas in it's own cell.
But if I misinterpreted, let me know. Happy to look at a sample sheet if you want to share one with my full name at gmail
I'm having issues when I click fill current it doesn't fill
Hey Annie; have you made a copy of my sheet so you can edit it? And then have you accepted the pop ups when you first click the dropdown menu? It'll ask you if you trust the code etc. Let me know any details about the issue you're having and I'll try and help further 👍👍
same issue
Same issue with mee too
The fill current button does nothing.
When everything else has been followed correctly, some folks have run into issues being signed into one google profile on chrome but using a different one in sheets.
Did exactly the same, but not working on my sheets version, when i am clicking fill current, can you help?
Is it the version issue, as there is not list(from a range), it is dropdown (from a range now)
No, it's probably that it's set to reject the input instead of show a warning. Go to Data Validation and click Advanced Options. Change "if data is invalid" to "Show a warning" and you should be good.
Awesome video!! Very clear and helpful. Question - do you know of any other reasons why when I select "fill current" the dropdown box doesn't fill? I made sure the box says "show a warning"
Hey Ally; glad it's helpful for you! I have had a couple folks who had issues because of some glitches with how they were signed into Google Chrome profiles. For instance, being signed in under one profile in Chrome, but using the Google Sheet on another one. We have not pinpointed the exact reasons for this. But if you try out creating a copy from another Google account or making sure you're signed in the same way everywhere, that can sometimes help. I think there may be some Workspace permissions at the root of a few of the issues when folks have used work accounts for instance. Let me know if you keep hitting a wall and I'm happy to take a look (my full name at gmail)
@@EamonnCottrell Thanks so much for the quick reply! I played around with a few different browsers and google accounts and got it to work! Appreciate your help!
@@allylott1757 fantastic! Glad you got it to work. Have a great one!
I got so close but when I click fill it doesn't do anything :(
Oh no! 😥 have you changed anything in the apps script or data ranges? I can take a look if you still are having issues. Share it with my full name at gmail
This is very useful and help me and my team a lot with organizing item list. Thank you so so so much!!
Awesome! So glad it was helpful for you and the team 👍😁
I'm having issues as well, not selecting when I click fill current. I've authorized everything that was asked but it's not working.
The most recent issue was resolved when the user signed out of Google Drive and then back in. We never figured out what was going on; a glitch in the matrix maybe.
@@EamonnCottrell gonna give it a try! But the script is pretty good!
When you share the sheet with integrated multiple selection options, as showed in the video, does the script automatically share between users or is it locked and seen only to the creator of the original?
I am trying to create a workspace shared file, that has this option, but it is pointless if the script has to be added manually for every person, that uses the sheet.
Thank you in advance for your response
Great question! The script does share with anyone you have added to the Google Sheet. It's just like information you have in a cell in the spreadsheet.
When the other user goes to use it for the first time, they will be prompted with the same security pop up that they will have to click through the first time they execute the script. But you won't have to manually add the script for every person.
finally I found the solution Thank you
You’re welcome! 🎉
Muchas gracias!!!...lo estuve buscando por mucho tiempo
Hello, I was hoping you could assist me. When running this script I keep receiving this error "Exception: Cannot call SpreadsheetApp.getUI( ) from this context." Any ideas? Thank you.
That's strange; I have not received that error before. I've investigated it a little and am not sure what is causing it for you. Did you make a copy of the spreadsheet before you tried to run the script? When is it giving you the error? I did find someone suggesting to restart the browser as well as refresh the spreadsheet. I hope one of these is helpful for you! Try making another copy first because there shouldn't be a problem with the script itself. Thanks!
Thank you so much for this, so helpful for my team.
You're welcome!!
Exactly what I was looking for! Thank you so much!!!😁
Glad it was helpful for you! ☺
If I create a sheet with this type of multiple selection and share it with others to fill out - will they be able to multi-select without having to download the same script?
They'll be able to use it no problem. The script is already attached to the sheet you'll share with them. However, it will prompt them like it does for you on the first time it's run to accept those permissions. Every new user running a script will have to click through that pop up dialog box on the first time they run it.
Thank you! @@EamonnCottrell
Thank you so much! I have been trying to implement something like this for so long and no solution comes close to this in terms of simplicity.
Glad it was helpful for you!
I found this because my old code stopped working. I followed the tutorial first and then video second. Both times the "get validation from current" does not work. checked, the warning/reject setting, reauthorized the script, tried a different browser and still havent figured it out. Any ideas? Thanks for being so active in the comments!
Hey Carlton; sorry it's giving you trouble. The last person who had this kind of difficulty had a sign-in issue where they were in a different Chrome profile than the Google Drive account. Try signing out of GDrive and back in. You can share it with me if you continue to hit a snag and I can take a look (my full name at gmail)
@@EamonnCottrell Thanks! I just shot you an email with the link. I tried totally signing out and duplicating it in safari to make sure and still didnt see a change. Also tried it with a regular gmail account instead of google workspace with the same result.
@@carltonriffel3982 Huh...scratching my head over here because it does still work for me as normal...shooting you an email reply...
@@EamonnCottrell I see what you are saying now. In the past I had it set up so that it would pull the validation fields as well as check the existing values that were in the cell. This was quite helpful when I wanted to make a slight change to a longer list without having to reselect them all. Thanks for the help.
This was very helpful, particularly when I have no idea about writing code or adding scripts. One thing I would like to do is have each option select appear on a new line rather then a continuous line, is that possible? I know you can do this in Excel. If it can be done can you tell me how to adjust the script to make that work. Thanks
Hey Alyson! Yes, this is an easy alteration. In the Code.gs file on line 24 change the value from a comma to
so it reads: s.join('
')); at the end of the line of code. That will put the values on newlines in the cell without the comma. Thanks!
@@EamonnCottrell Thank you very much that was very helpful.
Dear Cottrell Thank you so much for this video. I have been looking for it. I need your assistance, fill current can't work when drop down selection is in the same cell with validation. Multiple selection requires another cell different to cell with validation criterias to display results. Besides, One selection work perfectly. Thank you
Hi there! I'm not able to reproduce you're problem-everything is working ok for me in the sheet. Are you working in a copy of the sheet and have you allowed the permissions on the script to run like in the video? If you built you're own sheet, you can share it with me and I can take a look.
@@constantinrukataza2139 Thanks! I found the issue...the data validation rules are set to reject the input on invalid data. What we're doing with the script will technically get flagged by Google Sheets as invalid which is why there are still those little red arrows in the top right of the cells in the video. For your spreadsheet, select the E2:E8 range where you're trying to use the multiple selection and open the data validation rules. (Data -> Data Validation). Click Advanced Options and then select "Show a Warning" under the "if the data is invalid" spot.
Thanks!
@@EamonnCottrell Perfect, Thank you very much it is working.
@@constantinrukataza2139 Excellent!
WWOOOO FREAKING HHOOOOOO!!!! Thank you SO much, you saved my bacon and HOURS of trying to figure something out. AWESOME!!!
Who the man?! You the man!! Thanks a lot!
Everything seems to work except the "Fill current" button, which only works if I have one items selected. If I select more than one item the "Fill current" button does not work. Any help would be appreciated! Thank you.
That's weird, Austin. I've re-tested on my end and it's working ok still. Did you make a copy of the spreadsheet? Sometimes Google Sheets bugs out on me and closing and reopening my browser will fix the issue.
I'm guessing its set to reject input instead of show warning.
@@janglingjack Good call; that could be it. Under the advanced options in the data validation menu. The original sheet is set up to show a warning, but if a copy was made it could be on reject input.
How to replace the "," with "|" as separator?
Open up the Apps Script editor and change line 24 of the Code.gs file. Here's what the full line will look like. All you need to do is replace the comma and space inside that join() statement toward the end with whatever you want instead:
if(s.length) SpreadsheetApp.getActiveRange().setValue(s.join(' | '));
@@EamonnCottrell Thank you a lot!
@@jirongzhang3237You're welcome!
I cannot find the link in the video description? Thanks! SO handy!
Thanks! Here's that link: docs.google.com/spreadsheets/d/1yxc4k1x5idcS_moQ1Bq8LnHGZF2nm9dpARglY7Rv0UI/edit#gid=0
I keep running into the same issue where when running the script - when selecting "fill current" nothing happens! have selected dropdown from a range and checked everything else to make sure it is the same as well
Hey Arman, try closing your browser all the way and going back in. Several others with that issue have had it clear up this way. If it's still an issue, can you share the sheet with me and I'll take a look at what's going on? Thanks!
@@EamonnCottrell I tried that again here and also in a private browser and no luck! what's your gmail to share the spreadsheet with? thanks soo much!!
@@ArmanKhayyat my full name with no space at gmail
@@EamonnCottrell shared!
@@ArmanKhayyat Cool; I'll check it out in a bit and let you know!
Do I need to repeat this process for every new spreadsheet created?
Hey Bryan, yes, you'd need to duplicate the script into each spreadsheet you want to use it on. Hope it's helpful for you!
Is there any way I can avoid the script for multiple dependent drop down lists? I havea sheet with over a 1000 rows and every time I add a new data, I must refresh the sheet so that there are no errors
Please help
Are you able to share your sheet or a copy of it so I can take a look at what you mean? Not sure exactly but I’ll take a stab at it. (My full name at gmail)
Thank you...I shared with you the file.
@@EamonnCottrell
Thanks for this !
I would add a check is empty before generating all checkboxes. But look good .
Can’t believe this is not out of the box
I know; I couldn't believe it wasn't available w/o custom script either.
This is very useful. Thank you sir
You're welcome Mashal! Glad it was helpful.
For some reason this does not work for me. When I hit "Fill Current" nothing happens. I have noticed that sheets only has the data validation is "dropdown (from a range)" now, which is different than List From a Range? It's the only difference I can see...
Hey there, I've reconfirmed that it works for me still; I've got it using dropdown(from a range). Can you share a link to the sheet you're having trouble with and I can check it out?
@@EamonnCottrell I somehow got it working after I posted this. I think I was trying to fill the wrong cell with data. It's fine now, thank you for this!
@@kida026 Yay! I'm so glad it's working for you now. Have a great one!
Sooo helpful! Thank you
You’re welcome!
Hello Sir. Thanks for the video. If the cell already contains one or more entries, would it be possible that the checkboxes are already checked when the sidebar loads?
Not at the moment, but the original apps script is editable if you wanted to customize it further.
Hello! Can I now sort my data range? (For example, only have the color purple show up)?
You'll need to create a filter to filter or sort by colors. This guy did a great, short video showing how to do that: ua-cam.com/video/7VgpDfLlKmc/v-deo.html
@@EamonnCottrell I meant sort by the variable color that you created in your example.
@@moiragray2649 Ah, gotcha! Sorry...yes, you can do that in kind of the same way. You'll want to select the table including the column titles (i.e. fav color) and create a filter (Data->Create Filter). That'll let you click the Fav Color filter and select the "text contains" option. Type in purple or whatever there, and it'll filter down to only those that include whatever you type. Let me know if that makes sense; I can do a short video if it doesn't translate well here!
@@EamonnCottrell That's super helpful, thanks so much!
Thanks so much for this, it was a life saver!!!!
You're welcome! Glad it helped!
I am having a similar issue to that which the top comment experienced. Any useful tips?
There have been continued issues in cases involving Google Workspaces permissions. For instance if you're using or you created the sheet with a Workspace account that is at your work, for instance, there may be restrictions on the permissions allowed to run the script. Several people who have run into this have solved it by using a different, personal Google account.
I need to learn how to tie this up with pivot tables
Thanks a lot for this! For some reason the selection appears in the cell on the right instead of the actual cell with the drop down. Do I need to change something in the script?
You're welcome! That's interesting - so it is possible to click "fill current" while on another cell after "getting validation" from the current cell...and it will put the selection there on the active (but incorrect) cell. Did you perhaps move to that other cell on accident?
@@EamonnCottrell Thanks for getting back to me. No, I thought I did but I'm staying on the cell with the drop down and it doesn't work like you. It just fills the next cell. When I only select one option though, it works as intended and fills the cell with te drop down. I also notice that when I paste the Code.gs, the line of code that says "if(i.substr(0, 2) == 'ch') s.push(e[i]); " the "substr" appears crossed out. Not sure if that could be it.
@@irinamakasso70 Huh, weird. I'm not sure what would be causing that. I can take a look if you want to share the sheet you're using with me. And, no, that substr thing is crossed through on mine too (I'm not sure why, but it still works 😀)
Awesome video!
Would be even better if we could filter it for of a single item (in the checkbox).
Tell me more. Filter the items in the sheet after adding all the multiple sections? Or filter the list of options in the sidebar pop up?
Thanks for the video, but the fill current command doesn't work. I cannot select more than one option from the list. Any tips to fix?
Hi Alessandra, did you make a copy of the sheet and then allow the script permission to run? I double checked just now by making a fresh copy and it works normally like in the video. From time to time, Google Sheets will bug out, though. If you've followed the instructions, try closing your browser all the way and then re-opening. This has cleared up random bugs like this in the past. Let me know when you get it fixed and if it was something else! Thanks!
@@EamonnCottrell Hi Eamonn! Thanks. The error occurs when I apply too many lines in the list of options. Is there a limit?
@@alessandrasilva5407 Interesting. Can you share a link to the sheet for me to check out? I made another one with a bunch more options, and it let me fill in 28 unique values. Not sure if there is a limit or not, though.
🤔
I'm fairly certain you could do this natively without a script. For one, your solution is a bit of a bad data practice - you're jamming multiple values into a single cell. If you wanted to filter characters based on who likes purple, 3:30, you will have to do some data cleaning in order to split Raph's color choices.
A better method would be to create a separate table with the color choices. This is how it should be done, especially if the data is going to be used for analysis in something like Power BI. The table would contain all color choices, then combined only on this table.
I get it that it's just for a kid to easily select colors, but I see a lot of comments with people wanting to use this for more than just playing with spreadsheets. 😕
💯agree, Chris, and thanks for the note. Re-doing this video with other/better methods has been on my agenda for a while now. It works, but I've hit the same snag with it being a little more work than necessary especially for cleaning purposes. Keep an eye out for an update soon...have a great one!
@@EamonnCottrell I think you're saying that I should subscribe? 😏😁
@@chrishoffman4635 😜😜
Mahalo for the killer script
A 'ole pilikia. Glad it was helpful!
thank you🤩🤩
You're welcome!
Really impressive
I have done all you suggested in the comments, shut down browser, show warning instead of reject, and reput in the code data multiple times. I was hoping i could share it with you if you have a free moment to help?
Definitely. Share with my full name at gmail
Isn't there a simpler solution?
Probably 🤣🤣. I've thought about revisiting this to try a simpler approach. I am pretty confident it will always require some programming via Apps Script, though, until Sheets changes the data validation requirements.
I watched this tutorial. It would be more interesting if you could add a search function list too.
I have made a separate video about building a search box. Check it out here and let me know if you've got an idea for an enhancement: ua-cam.com/video/5xgwvokDhT0/v-deo.html&lc=UgxDGsPpSXWLmRRd2fh4AaABAg
@@EamonnCottrell Thanks for your quick response. Dynamic search bar is good. But what I requested was search feature inside drop down list. For example : Type 'an' and it should filter
Animal
Rohan
Rajan
Janwar
@@sajinj2457 Ah, I see. Yes, that would be nice. I will add it to my project list. That will require some additional coding to add a search bar. I would probably re-write the whole thing if I added this in order to split out a javascript file to handle the search bar logic.
@@EamonnCottrell Hi, any good news on this project? 😁
the search feature sounds very useful when you have a long drop down list
@@eMaNoN-kk UPDATE: Yes, I've got a working version of this now. You can check it out here for the time being. I will make a new, cleaner sheet and video with this added in the future. This was a great idea. Thanks for nudging me; I'd forgotten to add this functionality: docs.google.com/spreadsheets/d/146b24P792kgcXIuWvupdqmV1ETq8zhXotf3PXNuGanw/copy
this is great! and almost works for me---except when i hit "fill with current". literally nothing happens. don't know why. I've followed exactly as you've shown. is there a limit to the amount of items you can have in the range? i have 71.
read through the other comments and figured it out. had to change advanced settings to "show a warning". Yay--thanks for making this.
Yay! Glad you got it working. I will add a comment about the advanced settings to the sheet today. I think it’s defaulting to that reject input when it used to default to show a warning. Have a great one!
error code I can't
What error are you getting?
my fill current is not working
Sorry i looked at the further comments below and notice that mine is set to automatic reject input!
@@LupePena-p4h Awesome! Glad you got it working! 😀😀
Thank you for making this video Eamonn! It helped me a ton. I was able to get your sheet to populate with the Multiple Selection Data Validation. However when I try and use this new menu option on another sheet I get a ERROR code "Script function not found: showDialog" If you could you help me resolve this I would really appreciate it.
So it sounds like you have the menu pop up code in the page.html but perhaps not the actual function in the code.gs file. Did you perhaps copy everything into one scrip file instead of two separate script files?
Why does this app need access to my entire Google account?
My understanding is that Google will automatically prompt for permissions based on the script written. This is just a script and not an app that has been submitted as an official addon yet, so it prompts for permissions the first time it’s run, even when I make a new spreadsheet and use it again. This article breaks it down pretty well too: spreadsheet.dev/authorizing-an-apps-script
@@EamonnCottrell so there's nothing to worry about?
@@DoDo-dy2fs nothing to worry about 👍. Here's an article I wrote breaking down in more detail everything the script is doing: www.freecodecamp.org/news/google-sheets-multiple-data-validation-selections/
There is no "App Script Validation" tap like your in mine. Any Idea why and how? @EamonnCottrel
What exactly are you missing? The validation pop up when you first run the script? I'm not quite sure what you mean