Google Sheets | Data Entry using HTML Form 🔥🔥
Вставка
- Опубліковано 8 лип 2024
- 🎬 Hey everyone! 🌐 In today's UA-cam tutorial, we're diving into the exciting world of data entry into Google Sheets using HTML forms! 📊✨
🤔 Wondering how to set it up? 🛠️ Before we dive into the nitty-gritty, let me give you a quick overview of what you'll need and why. First things first, grab your trusty Google spreadsheet where all the magic will happen. Attach some script magic, deploy it, and voila! You'll get a URL ready to handle POST requests.
💻 These POST requests will carry data from your HTML form straight into your Google Sheet, making the whole process seamless. And yes, you'll need an HTML form too! 📝 Don't worry, even if HTML isn't your best friend, I've got you covered with a link in the description for easy customization.
******* Support me by buying CRUD template with Whatsapp Support just for 10 USD *****
pritamsharma.gumroad.com/l/wtxnb
***********************************************************************************
☕️😊 Buy me a coffee! ☕️💖
buymeacoffee.com/pritamsharma
paypal.me/pritamsharma45
-------------------------------------------------------------------
🔗 Spreadsheet Link : docs.google.com/spreadsheets/...
🔗 HTML Form Link : drive.google.com/file/d/1SjRK...
🔗 Buefy Docs: buefy.org/documentation/input/
🔗 VS Code Download Link : code.visualstudio.com/download
#GoogleSheets #HTMLForms #DataEntryMagic #appsscript #htmlform #htmlforms
This was the perfect tutorial. Perfect audio and easy to understand, clear instructions, short enough for my attention span! Thank you so much!!!
Glad it helped!
Awesome
Incredible teaching ability. Congratulations!!
Glad you think so!
Yes, it would be quite useful to the part 2 = populating this fotm from a Google sheet directly.
simple and to the point, thank you it worked!! (dec 2023)
Thank you 🙏
lovely you content :) thank you so much
Beautiful work thank you very much
Thank you very much
Thank you!
many congratulations on your channel
Thank you so much! This really motivates me😊
😀Excellent!!!
Glad you liked it!
Great help 👍
Glad it helped
thans alot😊
I love your videos. Clear explanation and up to the point. Is there a way to add a signature pad, capture the signature and add it as a new column to the google sheet for each user? Also would it be possible to use "select" in html for multiple and single options and transfer the data for it to a new column in the google sheet as well?
Very impressive one I need to make a two forms first the data will be populated from one Google sheet and select any of the class and then this one which you have put it there so I need when someone has selected the glass from the first page of the form then how to populate the data of a name field and the father name your last name field automatically in the second page of the form so that the user cannot manipulate their names and only can enter the data of the max it will be helpful if you give me the guidance
This is so good
Thank you very much
you earned a subscriber :)
Thanks a lot🙃
Thanks it worked
Glad it helped😊
is there a way to get the input in the columns?? (A1 is the question and A2 is the input) pls help
P.S. Love your Video
You earned yourself a new Subscriber
Really nice, Thank you. can we read the data submitted?
Data to store offline when u got internet u can sync them all online?
how can i use the form using multiple devices via web browser?
Trying to create a roll call for a class. Multiple people taking the roll. Any ideas on how to host the html easily for this to input into the form?
Sir please tell me highlight second highest number formula in condition format
❤❤❤❤❤❤❤❤❤
Thank you very much.
Much better if use the google form instead og html and etc.
how do i edit this with my own spread shit in my google account as in linking the spread shhet link in the code??
Love asnd salute you always. You give an awesome Solutions. Whynot you build and Tracking system ofan item. And also student fee management system using Googlesheet and app script and beautiful web interface???
Thanks for the idea! Noted👍. Could you provide more details so that I can actually convert it into video in future.
@tech-lever student Fee Management system: it will work as By using html Form i can enter student details one by one or upload bulk record by using csv file.
Then by using html file i can see pending fee details of students and i can generate their fee vouchers for one student or for whole class.
The vouchers i can download as pdf and also if i wanna send them to each students parents by using their email addresses which is available in student records.
When i receive fee submissions vouchers i will enter them into one by one or choosing whole class or i can enter by using voucher id.
I should have updated records related to fee pending or submitted successfully. An smart Dashboard with details will be most helpful.
This whole system will work by three persons one is admin who can view report status and my accountant who will generate fee challans or collect it and third person my admin officer who will add , modify or disable any student with his record.
Male sure i have facilities to wave of whole fee of a student or can give a discount too.all system will start by proper logins of admin amd accountant and administration officer.
How wonderful and the best thing is that it can be used on a mobile.
Do you have videos of how you do it from the beginning?
I am from Venezuela and I am learning to use this great tool.
My respects!
I will do that as a part of its extension and show the process of how to use it on a mobile device. I hope I understood your question correctly. Thank you very much for taking interest in my video. 😊
@@tech-lever Hello
Thanks for answering.
Sorry if I don't make myself understood. I don't speak English and I use Google Translate.
The idea is that you can see the creation of this project from start to finish.
Thank you so much.
@@EcoDev-Solutions Sorry but I am unable to do full length video. It's a skill that I currently. Doing project while recording my voiceover is currently difficult for me because of my bad english.
@@tech-lever It's a shame because it is an excellent project that is worth making known.
Thanks, when i sign up, data entered sheet. But no success message. Why
If i type any columns Manually In Z4 and Than next data comes in Z5. Wole row skip How to solv this ?
What about multi sheets? Logically, in the HTML input form there is an option for the sheet destination to be filled in. Hopefully there is a solution. Thank you 🙏
Can you please add code for dropdown in HTML
Thanks so much for this! I got it to work even with some changes I needed. But I'm definitely a newbie. What are the steps needed to take the finished HTML file and load it onto a mobile device so I shows up as pretty as on my desktop??? If I send it to my phone and launch as a test, its tiny and B/W...no formatting. Also, can you point me to one of your videos (if you have one) that shows how to link one of the HTML form fields to a separate Sheet tab with a LONG list of items in a column as a pulldown in the app. I'm not sure what the code is and if it belongs in the HTML file, the Google apps script Code add on, or both.
Send the finished html file to your phone using WhatsApp may be.
Then open the file using chrome.
Great video. Question, what if they need to attach a file? How to attach files when submitting?
In this version it's not possible. But I will post another video with File attachments also. Please wait for few days or so
Thanks for your video, really impressive.
Is there a way to select the sheet where the data will be posted.
I've appended a value "sheetname" in the form on it submission, however, i could get it running.
Is there any tips that you can share with us ?
Thanks again.
Yes definitely. Please watch it again. The instructions are give. You can configure the data entry sheet.
will this work with google docs?
it doesnt work when i add vlookup function in the sheet. it will fill the cell where it doesn't have the function in it. is there any way to prevent this?
Why is it when the web app was deployed, it was set as anyone? Is it that html data will not populate the sheet if it isnt set to "anyone", why cant it be set to "only me", since it is only serving as a backend to receive the data??
Yes you can set as me also. Depends on your use cases. When you want it to be public then set it as anyone.
Thank you. Is there a tutorial on how to populate the form from Google Sheet?
Yes. I have got two such videos. Please browse in the home page.
What would be the use case? I'm just wondering why a form would have to be populated from a spreadsheet which is serving as a database.
Hey, thanks for this. When using mobile phone to access to html, what link should I use? I want to pass along the form to the team member so that they can easily input.
You can share the html file directly via WhatsApp or Google drive. When they double click on it after downloading, it will open in the mobile browser.
Just to let you know I am making more robust web app based interface for data entry. Please wait for few days.
I would recommend enable bell notification so that you get notified.
Thanks 🙏 for showing interest in my videos.
@@tech-leverThank you
Can you explain this case to me?
I changed the name of my google sheets page to and replaced that name in the variable DATA_ENTRY_SHEET_NAME but the log I get is "An error occurred while submitting the form".
Meanwhile, with the variable name of DATA_ENTRY_SHEET_NAME being "Sheet1", everything works normally.
I will take a look at it.
Can this be use Offline and then sync to Google Spreadsheet once there is an Internet Connectivity?
Sadly No😞
I think is better to use a server-side approach than this approach. This method exposes the data received to anyone who gets the link in the front-end, because it's set to “Anyone”.
For personal usage you can set it to myself.
@@tech-lever If you set to myself, how then do people fill the form? The whole idea is to use a custom form to send data to Google sheet. It would be better to use the custom HTML form -> Google form -> Google sheet post method, by grabbing the name attributes of the Google form input elements (which is now hidden by Google and difficult to find).
@@danielarthur2329 when you deploy it u have to use environment variables in your deployment service provider.
hello, thank you for your great content! i appreciate it.
I faced a problem. I have a google sheet which already has formulas and some special vaules that should be there. and i want to just fill the blank columns on this rows. But i cannot manage code for that on app script. Lets say on AR13 cell i have a invoice number that it shouldnt be change, but AR14 cell is empty. when i add new data, it adds to row 14 because row 13 (AR13) has value but Row A,B,C,D are empty and i want add data from form until N column.
I would be really happy if you would help me solve this problem.
i faced this problem too..
After following the instruction completely, the data does not populate back to the Google sheet, what do I need to do? Everything worked all the way up to getting a "data submitted successfully" response but the datas did not transfer into the Google Sheets doc.
Did you first tested with my shared spreadsheet?
Remember you need to deploy it, get the url, and then replace the url in the HTML form as well.
hi thats great. I want to add a popup and show the data we send in the popup, how can I do it? as a table
For simple popup you can use alert method.
But complete CRUD style video will come soon. In that you can view the data in a table and perform update and delete as well.
Can you assign an email alert to 2 users everytime the form is filled out with a snap shot of the form?
No. You may contact me in case you require any customized version.
hello first i want to say thank you for your work!! can you please tell me how can i display time and date of the submission
I have updated the code. Now you will be able to see date and time of Submission. Please Subscribe and share this channel if you want to support my work. 😊
Interesting approach, but can't you put this into the sidebar ? I know it can take HTML+css+js just fine, so is this fixing any sort of limitation ?
I have already done that in a separate video. Watch one of my videos titled Sidebar.
When i click run the script, it asks permission, but it is not going further says that the app is blocked. i also changed the initialsetup to dopost still facing the issue. caould you answer me
Have you first made a copy of the Spreadsheet and then followed all the instructions?
Is the Google form cannot handle it?
Can we do it for election? School election. Head boy, head girl, school caption vice captain, sports caption. Etc. The students will give one picture for them. Can you make one video for that.
Google Form will be best suited for your purpose.
thank you! what if we use google forms instead this hard work? isn't it the same thing? I'm asking to understand the difference
You can layout many form controls in whatever manner you like. You can have have 2 or 3 or 4 controls per row. Most importantly you can configure dependent dropdowns here also. However, I have not provided support of dependent dropdowns here. You can see my other videos for that.
This tutorial just introduces you the basic workflows. For simple forms Google form should be the preferred choice. But if you want to build really complex form, with different phases or steps, and whose dropdowns are populated using the spreadsheet content and that too are dynamic then you will have to take help of HTML and Javascript or other front end tooling. Please watch my other CRUD videos to better understand the benefits of custom building your forms.
If you can put more effort into this, it can do a lot more than google form can do. Because you will be in full control of the logic. Obviously you will have to bring in some HTML and Java script and it would be good for learning also.
omg
An error occurred while submitting the form.
how can I add a logo to the green banner please? can I change the colors of the green banner too?
Yes possible. You will have to edit the HTML of that page. You can refer Buefy docs for that.
hello, excellent tutorial, thank you, is there a way to avoid a duplicate data entry?
Not is this version. Maybe you can watch my other CRUD videos. Browse my channel page.
So forgive my ignorance, but its my first time. I copied everything in the script editor, but when I try to run the script, i'm getting an error! How would i determine...what line I may have deleted thats causing the error?
Start by making e a copy of the Spreadsheet from the link given in the description. Then do the deployment as per the instructions given. Hope it helps.
I get an error on the sheet link sayingWe're sorry. You can't access this item because it is in violation of our Terms of Service.
I will look at the issue. I did receive an email from Google about the violation.
I'm getting a CORS error. How do I set the Access-Control-Allow-Origin Header?
Well, I copied the script from your sheet and it worked. I wonder what I typed wrong, because now I already deleted the old code that I typed in from the video image, LOL.
while trying to Deploy the Google Sheet, I'm getting the "This app is blocked" without the option to deploy it anyway... do you know why?
Did you first make a copy of the Spreadsheet? Then you need to deploy it as per the instructions.
How to add upload file can be?
You can watch my other videos for file uploading
Here Im getting the App script Error.!
It's work
DATA
Can we retrieve data from sheet to html
Yes. That video will come in few days.
I need this video please@@tech-lever
Cancel button doesn't work. It submits the form data just as the submit button does.
Alright, I fixed it. Added an event listener for the CANCEL button for a "click" event and function inside to reset the form.
Can any one help me to solve CORS problem to regarding this project
Please start by making copy of the Spreadsheet. And in the access type select anyone. Hope it helps.
@@tech-lever thank you for your reply, access type is selected as anyone and also in fetch API header Access-Control-Allow-Origin set to '*' , but not resolved
@@user-ew1mx3uc2y I tested the attached HTML form by opening it with chrome. And it worked. And also CORS setting is not required here. Are you using NEXT JS? Maybe you are trying to integrate it with other project. Or you are deploying the HTML form somewhere. Please send me screen recording. Take my email id from About page.
IS THIS ACTUALLY SAFE? YOU'RE GIVING PERMISSION TO ANYONE?
This is suited when you want the form to be public one. If you want restrictions then refer my other video titled CRUD application. That can be deployed with restrictions and is even more powerful.
yes, is safe
2:42:49 Error
TypeError: Cannot read properties of undefined (reading 'split')
parseFormData @ Code.gs:16
doPost @ Code.gs:9
same as me'
That was very helpful, one question how do you add required to a text area? tried con class, didn't work
You need to add 'required' keyword within the input tag. Search for the textarea input and add required. That's it.
I would recommend to refer to form section of the Buefy docs.
@@tech-lever as in: type="text"
, -> type="text required"?
@@collab.stream No. type="textarea" required
Hey, its very nice and it was my first try and I am having an Error
TypeError: Cannot read properties of undefined (reading 'split')
parseFormData @ Code.gs:14
doPost @ Code.gs:7
please help me
Script function not found: doGet
also this error i am getting
Make sure you have deployed the script correctly and then pasted the deployed url in the html file also.
You can first try using my HTML file and Spreadsheet. And then convert it to your own.
Give it one more try. Then you can reach out me. You can find my email address in About section.
hello, i get this => Error
ReferenceError: postData is not defined
parseFormData @ Code.gs:16
doPost @ Code.gs:8 !!!
I have updated the link. First make sure if it's working with the file links that I have attached in the description. First you can download the HTML file and open it in the browser. Then open the attached Spreadsheet also to confirm if the entry is happening. Then you can modify it as per the instructions. Just to let you know, I have checked it myself and it's working. Please watch the video again and try.
@@tech-lever yes, it works. But how do I link it to my own account?
Thank you. Now it works
If you liked it please subscribe. It will support a lot.
@@tech-lever how to send image ?