Google Sheets Userform - Add a Dropdown
Vložit
- čas přidán 29. 08. 2024
- Learn how to add a dropdown to your Google Sheets userform using HTML select elements.
This tutorial is Part 3 from Google Sheets Userform - Level 2 series
• Google Sheets Userform...
#googlesheets #form #dropdown #tutorial
Thank you this series. You are one of the reasons why I won't walk out of lockdown with the guilt of wasting time during the lockdown. I shall implement these kinds of forms and replace the google forms across my organization and this saves an immense amount of time during data entry. Thank you, Thank you, Thank you
A couple of days ago I was trying to create the same thing, you are a time saver thanks.
I started webapp coding by watching your tutorials. I am a mechanical engineer.
Cool!
Great explanation! I'm learning a lot, thanks for your work.
Glad to hear it!
🔥Loving these vids. I am using these and building custom tools based on your starting points. Please know these are highly useful and also please keep doing them!
👍
Waiting for other videos. It is really so fruitful and useful. Keep up the creat content.
Lovely, I wish at some point you can also share the final code.
That is perfectly what I was trying to do for weeks, finally found something that works ! Thank you very much. I have only a very minor issue and I'm sure that can easily be fixed, at the end of my list in the dropdown I have 17 empty choices and I don't know where they come from. In my Sheets I still have the 1000 default rows and only maybe 70 options so that can't be it. I also selected all the empty cells after my list and erased to make sure I didn't have spaces of stuff like that but that didn't do it. Thanks in advance !
I have a video on the channel for different methods of getting last row in your data. search for "last row apps script" you should be able to find it.
@@ExcelGoogleSheets awesome I'll go check that out, thank you!!
Hi, great video and I learn so much from your tutorial. I have a question, if the range in spreadsheet is a list of date, should I declare arrayofArrays as newDate first in function afterDropDownReturned? Thank you
Excellent tutorial! I have a question. Is it possible to write in the form, progressively load the content that matches while you write and select the correct one? Because if I have a big list of products it will be difficult to scroll down and find what I was searching
The part where he didn't type the function right is totally relatable 😂. I spent half an hour trying to figure out why the input data wasn't going through then realized "getElementByID" 😬
all other videos are very useful
Thank you
Uncaught TypeError: Cannot read property 'appendChild' of null
at userCodeAppPanel:183
at Array.forEach ()
at afterDropDownreturn (userCodeAppPa
function afterSidebarloads(){
google.script.run.withSuccessHandler(afterDropDownreturn).getDropDownArray();
}
function afterDropDownreturn(arrayOfArrays){
var item = document.getElementById("LosCursos");
arrayOfArrays.forEach(function(r){
var option = document.createElement("option");
option.textContent = r[1];
item.appendChild(option);
});
}
HTML:
Escoger El Curso
Mes de Asistencias
Escoge la fecha de hoy
Thanks for this tutorial!! But i am facing a select issue not updating the drop down. Please could you help
Hello thank you for the great content. When changed text box into dropdown somewhere on the road i had un error that just couldn't resolve. All that comes back was that the line from js where
ws.appendRow([rowData.item, rowData.qty.currentDate]);
CANNOT READ PROPERTY 'item' of undefined( line mentioned above)
All the lines and color code was in order.
Btw i retype the code dot by dot and the thing just doesn't work.
Can you 🙏 help.
Also is there a way to automate date input in format DD/MM/YYYY without time.
HI, i am not getting anything on item name drop down list . If add item on options it is coming.. kindly help!
Your all videos are very useful and well described, so it looks like we are seeing actual actions not recorded and edited version.
Thanks.
Can you suggest to me how we may set Sheet wise permission or filtered data so that only logged-in users may only see data entered by him? (Idea is to use his Gmail-using to see the sheet or data).
Boss, you are awesome!
Please help me. Mine did not work. the dropdown list is completely empty.
The "option" variable was not changed to Blue like yours but remained black
var option = document.createElement("option");
option.textContent = r[index];
el.appendChild(option);
currentlyAdded.push(r[index]);
me too :(
i founded try this
arrayOfArrays.forEach(function(r){
var option = document.createElement("option");
option.textContent = r[0];
kod.appendChild(option);
})
check out all ")" .
I've been following these videos to help with a project I'm working on to learn more about coding.
I've been trying to debug an issue for a couple of days but I'm not having any luck. I've got this code in my Apps Scripts exactly as it is displayed in this video, however the dropdown is not returning / displaying anything when selected. It seems as though the code for the dropdown isn't recieving any of the options via the functions that are added.
Is there likley to be anything simple that I have missed? Any help would be appreciated as this is driving me mad!
Thanks This helped
Hi, do you have a playlist for this project? I just came across this today and i don't know which video is the one before this. Thanks
Edit found it in the desc. Thanks!
Hey dude, here the playlist link: czcams.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
Make sure to subscribe to the channel, hit the bell button to keep informed when a new video is released, like the video, and share it with your friends.
Best regards.
Sir, Your video is very useful, I like most, now I am looking for a searchable drop down list like this video, suppose if I have 100+ items, it's very difficult to find out those items.
Hi, can i use datetime picker to complete the form i am trying to create please help me to implement it.
Hi. This video is very good! I've learned a lot with it. I have one html form, where i have a select field. I used this to get the list values. When i try to submit and post in other google sheet, i get results for all others fields but for selected option i get only blank spaces. It seems that by some reason, value is not being recorded. Can you help? Thanks.
Hi this is great content. I have a question regarding sending Multiselected options from a dropdown to Googlesheets. If I select multiple items in a multiselect dropdown I can only get the first item selected sent to the Googlesheet. Can you perhaps explain how this can be achieved? ps I have learned way more than I ever thought I could following your tutorials. Your teaching methods are the best. Thank you.
var multiDropDown = document.getElementById("multiID");
var valueOfAll = Array.prototype.map.call(multiDropDown.selectedOptions,function(v){return v.value}).join(",");
@@ExcelGoogleSheets thank you I am attempting to implement this into the "afterButtonClicked" section of the code. Cheers
@@ExcelGoogleSheets I have tried adding the code to the 'afterButtonClicked" so it can be passed to the append row function but I keep getting errors. Any tips? My multiselect field is: var openingDays = document.getElementById("inputDays"); & then I added this next line: var valueOfAll = Array.prototype.map.call(openingDays.selectedOptions,function(v){return v.value}).join(","); - on the funcs.gs file I have added rowData.inputDays to the const addRow. I am pretty much at a dead end :(
You need yo add valueOfAll to the object you are sending to rowData object, like all the other fields.
If you don't understand how objects work watch this 1:45 - 6:45
czcams.com/video/3dGQ4d7JF1U/video.html
Very good. Thank you.
Thanks for watching!
Awesome Video!! Instead of inserting the data into a new row is it possible to match the item name to the item name list & then insert the qty into the next cell to the right of the item matched on the list?
Yes, it's possible. This will be covered in the next series after this one.
Can you also put a date picker in the form?
Just make the input of type "date".
Thank you for this video. Can you help me with an issue I'm having with the drop down menu not populating? Developer tools is telling me:
Uncaught ReferenceError: item is not defined
at userCodeAppPanel:28
at Array.forEach ()
at afterCategoryArrayReturned
I don't know what is the problem. Anyone else run into the same issue?> @Learn Google Spreadsheets
Here is my code:
function afterSidebarLoads() {
google.script.run.withSuccessHandler(afterCategoryArrayReturned).getCategoryArray();
}
function afterCategoryArrayReturned(arrayOfArrays){
var category = document.getElementById("category-name");
arrayOfArrays.forEach(function(r){
var option = document.createElement("option");
option.textContent = r[0];
item.appendChild(option);
});
}
looks like "DOMContentLoaded" didn't work in my script
I already follow all instruction
Can somebody tell me why?
Lovely, I have tried and succeeded, but I tried to create similar data for different select options but failed, what should I do?
can you upload the other videos in 4 days? We have lockdown in India for 7 more days. So I shall have time to learn and implement these forms, please. Thank you
I tried to add a small dropdown input selector to my project that would include text like "received - " with a TimeStamp. I a unsure how to get this working. I tried adding a span to to each option like - and add a corresponding function and event handler but there is something that I am not doing right. Any assistance would be great.
How do I make a slider in Google Sheets?
Your video is so helpful. I am currently building a CRM with bootstrap admin dashboard, and app script. I found it still pretty hard to do so, especially how to present data on webpage and make changes. Then using chart js for some visual presentation. Do you think this is possible or using other ways such as Django to build it will be more efficient? Regards,
It's certainly possible. I'm not sure how to compare Django to a Google Wep App, for me these are totally different.
If you want a full admin panel with user privileges and stuff like that then go Django.
Is there a way to debug the client-side code. I don't seem to get options in my dropdown list, just blank, i suspect it is not getting that far for some reason. Can I log to the console or use Logger?
Watch this video czcams.com/video/flxj-QB50zo/video.html
Thanks for pointing me at the debugging video
I followed along with this and had it working for a bit but now it doesn't. Could you possibly help?
Boss how to submit multiple row data at one click? please make a video thereof.
Hey LGS, I'm looking for an app script which pops open the "Make a Copy" dialog box (found by manually clicking File > Make a Copy). The script can stop there. Would love if you could assist!
You can't open the menu. If your intention is to make a copy then you should be able to do something like this.
function makeACopy(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var destFolder = DriveApp.getFolderById("xxxxxxxxxxxxxxxxxxxxx");
DriveApp.getFileById(sheet.getId()).makeCopy("New File Name", destFolder);
}
Maaan You are best body 😄
Hello Sir I'm from India, thank you for new task, I'm learned more from your channel, but I need these script's, so how to get it. Please reply
Is there any way to edit afterDropDownArrayReturned function, so than I could add different values to options. what i want to do is to have a name in dropdown, but when selected, value of the field should be uniq id, not the name.
Googled it, apparently all I needed to do was to add "option.value = r [0];" under "var option = document.createElement("option"); "
Hey there
Can u please tell me how to do a section based answer thing like that in google forms.
Please provide the script
Is there a way to reload the sidebar in the afterSubmit function?
I have tried below, but just get no form only the sidebar with nothing in it
function afterSubmit(e){
// clean up - reset values to blanks
location.reload();
loadForm();
return false;
}
replace form tag with div
Thanks for your reply. I have a few dropdowns using select as below, but it is still not loading, maybe I misunderstood which form you wanted me to replace, I have the below now, also how can I replace the top title of the sidebar (Apps Script Application) with my own :
Select Player 1
copy of script can give receive?
Hi. Thank you so much for this. I have copied exactly what you have shown and i just don't get the drop downs. I have been over it several times and can't seem to find any mistakes. I have noticed that the text at the bottom ( and ) have now turned red - generally red means error so I am not sure if there is an error. I have taken a screenshot of it
drive.google.com/file/d/1RnaFgLB_9ikTNhKNH6cJsPlMUubshIl6/view?usp=sharing
Please could you help and seeif theres errors.
Thank you
Please show us how to update the data from the form. CRUD operation
ok
Also does anyone know if this will work if the drop down list is 300 items long?
Sure.
@@ExcelGoogleSheets Thanks for the reply. I just can't get it to work =( i thought because my list was 300 items long.
In my test sheet i have 4 sections collecting data. 2 are normal type and 2 are dropdown.The first drop down has 2 options in the script - the second one i have tried to script using your technique. Could this be the issue?
It' doesn't work with me, i try more
Drop down is not working
I have a question, can you centralise the form, not to be in the left side only, and make it in a way that we can minimise or maximize and centerlise in the middle. To be potable and movable the way we want. Thnx a lot.
What's on the left side?
@@ExcelGoogleSheets the inter faceform that we fill out.
You can position them wherever you want using bootstrap grid system getbootstrap.com/docs/4.4/layout/grid/
@@ExcelGoogleSheets Sorry, I made the entire interface form with all the staff. Many thanks for your amazing videos, but the only thing I couldn't do is making the interface form flexible, I mean I could be able to make it wider and move it to a different position, middle, right, or left. Many thanks.
Please give me this sheet download link.
where is level 1 tutorial form google sheets userform
Please share Codes.
haven't success on this, still trying (crying)
I had a few problems, they were typos in the html section - see the debug link in response to my earlier question.