Google Sheets - Userform
Вставка
- Опубліковано 29 гру 2024
- Learn how to create a user form in Google Sheets using Apps Script and Materialize CSS. In this tutorial we'll add UI elements like Sidebar & Dialog Boxes with a userform to our spreadsheet and pass information from our user interface to our worksheet.
For more advanced user form check out Userform Level 2 Series:
• Google Sheets Userform...
Thank you for such a great tutorial. Just wanted to share if anyone is having trouble with the "google.script.run" not executing, it might have something to do with multiple accounts signed into Google (at least that was the case for me). So try signing out of the other accounts and it will work
Just spend the last two days dumbfounded on why my code was not working. Thank you so much for your comment!!!
Thanks, thanks a lot!!!!
Thanks for another great tutorial!! How can you create an edit button to edit/update/delete a previous form entry ? Could you make a tutorial on this?
You're such a good teacher! All the explanations are very clear! Thank you for this content.
I wasn't looking for this whatsoever but I'm glad that I found it
Thank you so much you are an awesome person
¡Gracias!
Gracias!
I watched your web app serie. Showing the HTML inside like this is also very nice! One thing I did differently was that I used HTML5 validation. Thank you!
You are a wonderful teacher! Loved it, and subscribed, as I am seriously getting hooked to this type of development.
With some modifications I managed to create more fields to add to my spreadsheet. Thank you, your tutorials are extraordinary. ☑
Great to hear!
Thank you very much for this video. I did not know we could do this inside Google Sheets until now. If possible, could you please do more videos about Google Sheets using Apps Script and Materialize CSS like this one? There are so many other useful things I saw on Materialize CSS and it would be so much easier to understand with your explanations. You're really good at teaching! I'm looking forward to seeing more of your tutorials.
I used this at my work and managed to significantly expand it. I also managed to insert formulas into a cell from gs
Thank you so much!!
@VNP SHEQ Consulting have you solved it? I have the same error :(
Intresting thing executed in simple way. Hats off buddy..
Great tutorial!! i followed all the step shown in the tutorial. But the data in the form din't update in my sheet. i can't figured out why. i have go through all the spelling and brackets but still fail to update the data on my sheet. Please help
This is incredible ! Thank you sooo much for sharing all this!
I've been following along the video and "google.script.run.appendData(data);" doesn't work, I have faced a similar problem in another tutorial as well! Please help
Thanks for this great and useful tutorial. This video is really useful to me.
Very good video explaining the use of macros. I like the use of multiple examples to demonstrate
Merry Christmast and A Happy New Year.
Thank you very much for your worthly lessons.
They give me the big inspiration!
Thank-you so much for great tutorial
Please make part 2 with following thing
1. Spreadsheet will come in read-only mode when cancel user form
2. Spreadsheet will close when cancel user form
3.Entry should be done only ten digit phone no in phone section
how login app to google sites with google apps script. please help me . thanks. sorrry for my english. i speak spanish
Excellent work young man ...
help i need a function that popup the userform when its reload that i dont need to run it over the Tools/Script
Hi, thanks a lot. Your clip is very easy to follow step by step. It's great! Thumbs up.
This is not working for me. I write the script exactly according to your video, but the data from the form does not load the sheet. Please help.
Same to me
same
Be sure to open the script editor from inside the spreadsheet: prnt.sc/r4jlwv
Same to me
Hello my friend, I have a web site and i need to put a form inside the web site y colect all data from clients in google sheets. How can I do that?
This is completely usefull! If you have an online class in Udemy or in another website, let me know 🙌
Awesome 👌. Great tutorials 👍
I have an existing problem. How to apply onOpen(e) here. And is it possible that the modal will not be closed?
This is a fantastic tutorial! Thank you for taking the time to do this. However, I am struggling with getting the button to do anything. Am I the only one who can't get the submit button to work? I have paired it down all the way to simply getting a message box to pop up indicating that I'm in the function that the button activates, but it's simply dead. It's not going into the function. No errors, just nothing happens. Any help would be great
i have a bit problem with this when i want to execute function google.script.run.function(); it don't do any things what should i do
fantastic guide, I'm a beginner, is it easy to use the same form to read and modify the data entered? if yes who can tell me how
TypeError: Cannot read property 'name' of undefined (line 48, file "Menus")Dismiss
what can i doo
when i remove data from append row, it works fine.
Had the same exact problem
you are actually awesome
Thanks for the video! How can I create a dropdown list within the input box?
Please watch my web app series.
@@ExcelGoogleSheets Thanks alot! I have managed to create it! Btw, how do I create a button to clear all the input fields?
I have a question. How do you save "name" and "phone" data in a spreadsheet in any selected columns, for example in columns C and E?
watch my basic apps script video series.
Is a way to read data to this userform, than edit and add again to sheet ?
in 9:17 shows "TypeError: template.evaluate is not a function" how to solve that
ensure that there are ()
How could you do an edit form which would display data from the row that you click in? Certain parts of the data would not be editable i.e. the persons iD number and name. Have you already addressed this somewhere? This was a great video!
@Learn Google Spreadsheets HI! thank you for helping those who wants to learn.
if the form was close how can you re-open it? can there be a shortcut on the sheet? or is there a way so that use can not close the form? or can the form show up when i open the form?
ua-cam.com/video/_Iq2-_WgFdY/v-deo.html
@@ExcelGoogleSheets thank you very much sir!
is there a way to pass data from the google sheet into the html form?
Hello, I have a add on installed in google sheet (when I fill the data in google sheet it triggers a data to add on app) . The problem is when I get the data in google sheet from google forms, it is not triggering data to add on . Pls give me solution. I have tried the spreadsheet.flush option, it’s not working
Is this support the Google sheet Android app?
Can we add multiple checkboxes and get the value of each checked box???
Will it work on android phone also?
hello, thank you for the video, very nice explanation and helpful....
what about if i want to validate some things, like if the userform input any same telephone number, then the msg box will pop up and shows that "duplicate phone number"?
Thanks! This helps a lot!
I have a question- how do you change the icons that are currently displayed next to the text input fields in the form?
I am halfway to the answer: account_circle within the div for that button.
my question is, where do I access the list of other prefix icons available?
For anyone else with this question- icons list is here: material.io/resources/icons/?style=baseline .
So I would change the icon by changing account_circle to thumb_up in
thumb_up
Very fantastic video
Please explain us about duplicate value
How to stop duplicate value via userform in google sheets
as always, great video dude... thank you
I suppose I should just give up at 01:30. What is puzzling me is the different names for like name, tel no. It would be a lot easier if you kept these uniform or gave a clearer explanation. I have followed to the T but just called SALES ORDER NO and PRODUCTS but the form won't populate the sheet. Most of the way there!
I have the following error while running the script.
Exception: Cannot call SpreadsheetApp.getUi() from this context. (line 5, file "Code")Dismiss
Thanks! Great tutorial!! I have a question: how to align a button in the center?
Can i implement them in Google sheet ? Can you pls tell how to create Login id Password form , using Google sheet which i can use google sheet. thaking you lots
yes very nice, but i think it would be nice to add the log data into another spreadsheet, not the current one, anyways is someone have the sheet link will face with the userform too ?
Thanks!! I am learning a lot from your tutorials. However, your web app series has some problem.
My and are showing in red color. I have checked all subsections of the HTML code are properly formatted ad closed. Why is this in red? --It works though.
With all due respect, there are no problems in the series. When you work with code, you have to be extremely detail oriented. One comma, semicolon, quote in the wrong place will throw everything off. Just because your HTML brackets are closed it does not mean you don't have some JavaScript mistakes or simply an extra div or attribute syntax error.
what about uploading files ? how to deal with that
it's great.. but can we publish them and deploy as web app?
yes.
i try to publish using deploy web app, but error doGet when open in my computer browser, when i tried open on my mobile browser the page not reach.. how to make it work?
Check my other channel to learn how to do it ua-cam.com/channels/JebzvfCuA5ymtb79DQm36A.html
Hello,
I try to copy the complete script and does't work.
when I full feel with name and phone after I click the name and phone number are not save i the sheets.
tell please what I shut do?
I followed everything, however, it does not work. The data I am inputting was not added.
this adds on the next blank rows... some parts of my rows has formulas.. how do i enter data on blank cells on a row
Litty, thank you so much 🔥🔥🔥🔥🔥
how can you trigger the form without running the script?
Awesome content! Thanks a lot!
I have tried to adapt this script to my project but i just can't get the submit button to work or the append function to collect the data. I've gone through all the suggestions below but nothing is working, wondering if you could provide some advice. Each function runs without errors, just no data is populated.
98% of the time spelling error or brackets missing in the HTML file.
@@ExcelGoogleSheets Thank you, found the problem bracket after a little sleep.
@@ExcelGoogleSheets I have checked everything and I cant get trhe button to work help please!!
Great tutorial but when i try on mobile such Chrome or Google sheet app the script not working. Please help how to script work on mobile.
Thanks for another great tutorial , please help me...
how to create a new name in a particular table, for example in the H section?...... thanks for the answer
Hello sir how to increase or decrease the width of the user form.
i am running the script in chrome v8 .. its not populating the spreadsheet...please advise
This should work fine on V8.
@@ExcelGoogleSheets sir request you to send your code ...I typed and followed you as it is.. but still it is not happening..I may be doing some mistake....
dileepade@gmail.com
html file :
account_circle
Your Name
phone
Telephone
Submit
send
var nameBox = document.getElementById("username");
var phoneBox = document.getElementById("tel");
function addRecord(){
var data= {
name: nameBox.value,
phone:phoneBox.value
};
google.script.run.appendData(data);
nameBox.value=" ";
phoneBox.value" ";
}
document.getElementById("btn").addEventListener("click",addRecord);
and file code.js
function doGet() {
return HtmlService
.createTemplateFromFile('page')
.evaluate();
}
function appendData(data){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var ws = ss.getSheetByName("Data");
var firstdata= ws.appendRow([data.name,data.phone]);
//Logger.log(firstdata);
}
please help i am not getting my spreadsheet populated as per the code.
Hi! I followed your steps but this statement google.script.run.appendData(myData); but it does not work for me (cannot call that function).
I ammended it to google.script.run.withFailureHandler(onFailure).appendData(myData);
However, I keep getting "ERROR: Authorization is required to perform that action." I have already given the authorisation in the pop up box. Do you have any suggestions to solve this?
Log out of all google accounts and then login with the right account and try again.
@@ExcelGoogleSheets
hi, this worked! thank you so much!!
How to add in user form print button. I hope you can help me.
Hi :-) Somehow buttons never work for me in forms. I have tried several tutorials including this and have verified that everyting is exactly the same - but iIeither get nothing og TypeError: Cannot read property 'name' of undefined. Please help me - what am I doing wrong?
One thing a lot of people miss is that JavaScript is case sensitive. So everything you do, make sure you match cases.
Is there a way to prevent duplicate entries?
Great channel content is great and another useful video.
However I did try the same steps described in the video and the inputs are not showing in the sheet. What could be the cause of this?
there could've been an error
possible error:
function addRecord(){
var data = {
name: nameBox.value,
phone: phoneBox.value
};
I typed in 'name = nameBox.value, '
I fixed it and it worked
Awesome Tutorial
Very nice!
Got to learn something new.
My Question: How can I creat patient records sheet?
Hi! Very useful videos on your channel.
I tried to write a script exactly according to your video, but the data from the form does not load the sheet.
What could be the reason?
I am having the same problem.
Help! I copied his script character by character and it still won't work! There will be a sidebar, but once I hit "Add", nothing happens! Can anyone help a brother out?
the same situation to me please help :))
just look at the very bottom, my input is put on bottom, because it already have content
is there any way to change the sidebar width? i've tryed a few things without any result! and it's getting me crazy ! please HELP!
you can do HtmlService.createHtmlOutputFromFile('Page').setWidth(300);
however, with sidebat I think there is minimum and maximum width, so it can only be changed a little. For more control use Dialog.
Hello, I just tried your code and I had to deactivate the new App Script runtime for chrome V8 for it to work. The issue occured when calling the appenData function. It failed to execute. Do you know what should be changed in the code for this to work in the new environment?
What's the error message?
I just took the time to write the exact code in this tutorial in V8 engine and it works fine.
Maybe try to move document.getElementById("btn").addEventListener("click",addRecord);
below addRecord function, instead of having it above.
Just to be clear, I didn't do this change, I just wrote the same exact code and it worked fine.
Learn Google Spreadsheets There is no error message. When I go in the execution log it just mentions that appendData failed to execute. I will try to do what you said and see if it works. Thank you for your answer
how to auto adjust the userform width and height automatically based on the content
Great tutorial, thank you pal ;)
Cannot call SpreadsheetApp.getUi() from this context. (line 5, file "Code" .....Do you Know what Does mean??? Help me Up Please!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Never Mine I Solved Open Google Drive and then the spreadsheet tools and appscript
Hi. First and foremost I would like to thank you for the veery helpful instructional videos. I have been able to replicate most of your tutorials however I'm trying to establish dependent drop down boxes on the UI. I'm stumped on how I can get the 2nd drop down box to update its values based on the input of the first box. Any video that can help with this? It's with the HTML side that I'm having trouble in getting to update.
If you're having issues with HTML side, then I really recommend watching the videos in this playlist ua-cam.com/play/PLv9Pf9aNgemuYdHkUa_knXKMYZcW9WEaf.html
Next week there will be an example of a dependent dropdown in this playlist
ua-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
Thank you so much... I learned a lot :)
The video is so helpful! Have you thought about doing a tutorial on combing google form instead of creating your own form?
I do have Google Forms videos. Web App is much more flexible but you should use whatever works for you.
Thanks sir, pls help to prevent editing in entered data
How to create search engine for those table?
Also, excellent video, and thank you so much.
You did pass html variables to google script. Can we pass google script variable to html?
Yes, it's in next parts.
Alright. Got the sidebar all working. Error message everything. Only thing is. Once I have closed the spreadsheet and reopen, no form. Open project and the black play button is greyed. How to get form embedded into sheet so it is there on opening? Di I need to be opening spreadsheet in here somehow
var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SALES");
Something like:
var ws = SpreadsheetApp.openSpreadsheet(THEN THE SSID IN HERE?).getSheetByName("SALES");
As you can probably tell am not coder but using G Sheets as my Uni project and other Sheets things like formula etc am cool with but coding is like reading Chinese Mandarin (Simplified).
ua-cam.com/video/_Iq2-_WgFdY/v-deo.html
@@ExcelGoogleSheets thank you is tonight's isolation entertainment.
thank you very much. i see ur all video
Many thanks for this!
Glad you like it. Check out the new level 2 series ua-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
so cool
¡Amazing Work!
Thanks a lot! Check out the new series ua-cam.com/play/PLv9Pf9aNgemuzTNWeHd8HziGVNzSlWACh.html
Very nice. how to create a fullsize HMTL page and deploy the link or share the link of html page
ua-cam.com/video/RRQvySxaCW0/v-deo.html
Hola puedes hacer un ejemplo donde se consulte un spreadsheets por medio del form, es decir, dado un identificador buscar y mostrar el nombre asociado al identificador almacenado en una tabla en sheets. Gracias y muy buen vídeo.
Hi, i'm amazed with the thing you doing with these videos, thanks for every video you upload!. With this i have a problem in "google.script.run." never run in the way you show, but i look over there in forums, and nothing, until i decide delete of minified JavaScript code, and they works, but of course, the animation of button an text stops, so i decided to put again but after the script you write, and works perfectly, i decided put here if anyone else have the same problem.
i ask you to make some video with explanation of Class Blob in Apps Script, and if its possible wok with that ina "showSidebar(html)", i try to read in other foums an in the same google documentation, but nothing, and you way of explain its very useful to absolute beginers, like me. Thanks again
i delete minified javascript but still not work,, anyone with same problem? i use G-Suite for education account, is that an issue?
i try again with my regular @gmail.com account, it's work!
anyone can do this on G-Suite For Education account? cause my company use G-Suite for education to work in shared drive
hello from Argentina. data its ok but form desapear when clicked the button. somebody have a same problem?? thankyou
A mi igual ¿Pudiste arreglarlo?
Awesome
Please how to login user html to google sheet
Watched this three times and copied it *to the absolute letter*; still can't get the form to add records to the worksheet 🤔
So do I
FWIW, I forgot to capitalize something in the javascript 🙃 Joined the channel's Patreon and got some help directly from the mastermind himself. Happy to support his excellent work here. 👏👏👏
Be sure to open the script editor from inside the spreadsheet: prnt.sc/r4jlwv