Automated Data Entry Form in Google Sheets & Apps Script - Part 1 (User Form Design & Transfer Code)
How to create an Automated Data Entry form in Google Sheets and Apps Script?
Google Sheets and Google Apps Script allow you to add advanced features in existing application and automate all the repetitive task and data entry job.
In this series of tutorials, we will learn how to create an automated data entry form in Google Sheet with the help of Apps Script. We will cover the features of Create, Search, Edit and Delete.
This is the first part of tutorial where we will cover the designing of UserForm in Google Sheet and write the Google Apps Script to Validate and Transfer the data.
Part 1 - • Automated Data Entry F...
Part 2 - • Automated Data Entry F...
Part 3 - • Automated Data Entry F...
Part 4 - • Automated Data Entry F...
Please visit the below link to download the Google Apps Script code and view the data entry application.
thedatalabs.org/automated-dat...
website - www.thedatalabs.org
email - info@thedatalabs.org
#GoogleSheetAutomation #TheDataLabs
Thanks for watching! Please like, share, comment and subscribe.
Have a great day!
Пікірлер: 453
Very useful! This uses a lot of common functions needed in any data entry-related project, thanks so much for the clear demonstration!
Thank you man. I am building an excel sheet to manage the costings of my father's agri farm, and this is exactly what I was looking for
@hasanmehedi4809
3 жыл бұрын
you created by seeing this just only tutorial?
What an explanation everything clearly explained. Be it drag and drop or coding everything went smooth. Please upload the 2nd part asap.
Thanks for this Video you are really dedicated enough to teach us your Knowledge about Automated Data Entry for Beginners Like us I have watch your video and what I learn here is better than in school I wish all my Teachers are Like you when it comes to this OUR SCHOOL NEVER TEACH US THIS THING so I's clueless that DATA ENTRY is important to make things more efficient in Excel ONCE AGAIN THANK YOU FOR THIS TUTORIAL HOPE YOU GAIN MORE SUBSCRIBERS AND VIEWS
Your demonstration is so easy to follow for a newbie like me! Kudos!
Thank you for the uploading this tutorial. Catching some extent . My questions and doubts in respect of google sheets has been cleared. Thanks once again for your tremendous efforts you have made on this subject. Subscribed.
Excellent tutorial, just what I've been looking for. I have used Excel and VBA previously to create similar app but I need to use Google Sheets to allow access on mobile devices and have never used Apps Script. I have subscribed and look forward to the remaining tutorials in this series. Thank You.
@thedatalabs
3 жыл бұрын
Thanks for your feedback!
@prambondagangan1235
Жыл бұрын
@@thedatalabs sir can you make tutorial make drop down for save data with android phone.. tanks!!
@abuomardontadd2865
7 ай бұрын
can this work on mobile
@farhanbinashraf6783
7 ай бұрын
@@abuomardontadd2865we can see and entry data... But can't run the scripts
Amazing sharing👍👍
Really you are a BEST teacher who explain every step in easy mode. Thanks for your Video.Please UPLOAD second Part...
@thedatalabs
3 жыл бұрын
Thanks for your appreciation! Second part has been already uploaded. Will update third part of this series ASAP. Thanks!
Excellent tutorial, your explanation is so easy to follow and to understand, i want to thank you for sharing all your ideas about Data Entry, this is very useful.
This was excellent. Thanks a lot for such nice tutorial. Thank you so much for the hardwork done
@thedatalabs
3 жыл бұрын
Thanks for your appreciation!
Thank you so much for the wonderful tutorials.
This is very useful thank you so much. Very easy to follow 👌
just started and already i know this tutorial will assist me in so many ways Quyanaq!
Thank you for creating a very helpful and easy to follow video! Great job!!! :) :) :)
Apreaciable Work. I need to learn Visual Basic online. Cant afford classes. I am 50. Engineer turning into business as admin / qa/qc / quality control. Thank you for uploading more VB integrated Excel programming.
@thedatalabs
3 жыл бұрын
Thanks for your feedback! You can find several tutorials on KZread. You can visit our website too find the link of tutorials. Link is available in the description box. Thanks!
I did the same file in Excel thanks to your tutorial, and I want to share with multiple people so they can fill the info and now I found this video, you are really awesome man, thanks for the good work
@lionFTW
Жыл бұрын
how ?!!
@lionFTW
Жыл бұрын
@@chevapr thanks for replying 🥺
@chevapr
Жыл бұрын
@@lionFTW no problem, if you need any help let me know
@lionFTW
Жыл бұрын
@@chevapr probably i will. But i’ll try first. 🙏🏻
This is a brilliant form. I was looking to create a form to input my cryptocurrency transactions. I want to be able to enter the data without having to scroll through hundreds of entries just to be able to find a spot to enter new data. Thanks for the ideas. Think the only thing that could improve on this is if you did a pop up entry form.
@joshmack
2 жыл бұрын
this is a brilliant idea!
Great Work. It will solve a major problem
Nice, I love this.
Hi Dilip, thanks for informative video. Is it possible for multiple people to use same form at the same time ?
This is amazingly explained!!!!
Great video . Liked the simplicity
Super usefull and clear explanation with a code. Thanks for this
Well done!
thanks a lot your very clear and neat explanations with very supporting documents.
Thanks mate very good and explanatory video, keep the good work.
👍👍 Clearly explained Hat's off
Very useful. Thank you sir!
Thank you so much for this amazing tutorial! Is there anyway to avoid duplication of entry using app script?
great tutorial and very helppol video
WOW !!! there was only one good channel on google scripting .. , & this one of such good quality is from India !!!
@thedatalabs
3 жыл бұрын
Thanks for your feedback!
Thanx a million bro. Your video helps me lots!
Nice one. Thanks for the tutorial TDL
clear explanation...thank you
Thanks for the video.
Hello Sir .... Hope you are doing well... I am very much thankful to you on sharing such tutorial with us... It is a great help to my company project... I have zero knowledge in JS coding... But after watching your video I am able to write code... Thank you for that.... Sir could you make a tutorial video in this Google Form series about Dynamic Dependable drop down list (single level and multi level). Date of last entry of the customer (person) in search option... Please sir... 🙏🙏🙏 Also the way you explaining things is really remarkable.... So could you please make a complete tutorial series of videos from Beginner level to Expert level for Google App Script.... Please.... If you are providing any online class then please let me know... I would like to join that as well... Thank you Sir....
You guys are doing wonderful job. Easy and understandable explanation. Hits on like button and subscribed for next booming
Very very nice Sir thank you 🙏🙏🙏
Thanks so much, very pratical
Thank you so much for this tutorial, your explanation is super clear. I have a question though; is this something that other users that have access to edit the spreadsheet can use? Or only the owner of the google script can use it?
Amazing amazing
thankyou so much!!!! this is amazing
Hi, is the Google sheet has floating userform like vba? Because u used the sheet as a form. I like the idea of a sheet as a database and a floating userform as Ui window. Thank you to your response.
OMG I made it! thanks a lot.
Thank you so much every thing is so clear your explanation and presentation so great and easy and simple but unfortunately it did not work on android thanks again
great!
Thank you very much!! This is very helpful for saving many contacts. But I have a problem when I'm looking for a contact that I only remember his/her institution, instead of his/her name. Please teach me how to expand the search function, in order I can find a list of several contacts with the same category, instead of only 1 contact. Thank you again!!!
Nice One, Amazing Job
Thanks, i"ll try
This tutorial was so useful and had lots of learnings even I don't have a background in coding. I want to learn about code. Hope you can help me.
Awesome
In my Database Sheet, I have columns that are formulated based from another column. For example, "Age" Column has age formula based on the Birthdate Column. The Birthdate Column is a data to be inputed from the form, but not the age. How can we skip the Age Column when the new data is saved from the form to the Database Sheet? :)
In the Google Sheet Form we connect the script function 'submitData()' to the Save Button, great, I see how that works. However I don't understand how the Sheet knows to run the 'validateEntry()' function when we did put the name into the sheet anywhere. Is it a special name that the sheet assumes ? What would happen if we added another entry form to the sheet and another database. Would the have to use same validateEntry() function to deal with each form in a different context or could we have a validateEntry() function for each form somehow ?
Thank you! This is a very detailed tutorial with easy-to-follow instructions. However, why am I getting "TypeError: ui.alert is not a function" when I tried to save an entry in the form?
please do how to create the different sheets and different user forms simultaneously in one worksheet in the VBA Excel
Nice Work
Excellent .
Flawless tutorial. Awesome!
@thedatalabs
10 ай бұрын
Glad you liked it!
Thanks a lot. It's great. Can I customize form with input data to column? And ui.alert with row, no one cell in that row is blank? Example: Multi Row Employee ID, Employee Name...
Thank you!
Legend!
👍👍👍👍👍👍👍👍👍👌👌👌👌👌👌👌👌👌 very very excellent script, Thanks Sir
Thank you very much!!! Can you pls advise how can use this form multiple users in a time.. awaiting your valuable reply plzzzz 🙏🙏🙏
You deserve subscriber ,
thanks for this video
bro ur stuff is amazing...i have a clothing shop...i want to fill all inventory in excel and give the products code...also record the sale with the cost...i have womens, mens clothes and shoes also other things....can you help me out?
Hello, your tutorial really helped me a lot for my project, thank you very much. However, I would like to capitalize the data when saving to the table and I don't know how to do this. Could you help me on this subject. thanks again
thank you very much
thank you for this video
nice sir..
this is tutorial helped me a lot! i have a question. can it be used by multiple users simultaneously?
Hello thanks for the tutorial , I have a question how we can check the data duplication before when save the data . for example how can we can the employee id if its exists before we insert it to the database
im learning so much ...... thank u for the useful videos ........ any tutorial about sql and cloud network data apps? all the best to u
great video and thank you
@thedatalabs
3 жыл бұрын
Thanks!
Love to u brother 😘
thanks brother
Amazing video....Something I have been looking for so long. Just a quick question....is it possible to create a centralized database where if multiple users are working on the database then all of their data is saved in some centralized sheet??
This form is very informative, thanks. Just got one question in PC it works fine but in android buttons are not working, any clue?
THAK YOU SO MUCH
well done
Hi can this form in google sheets usable even with smart phones? Coz i want a data entry form that can be accessed by two users using smartphones only -Just subbed thanks for the tutorial btw
Dear Data Labs team It was wonderful Video regarding the automated data entry sheet on KZread and appreciate your effort to make some videos for common people. Since coding is all together is new subject for me so if you can help my business to develop some google sheet formats related to my business. You may Connect me with your offer undersigned with detailed information.
Very useful, i did it successful in the computer. but i wonder if we can run script in the phone or tab? Thank you so much!
TO EVERYONE STRUGGLING WITH submitData button errors. REFRESH the excel/sheet tab and that's what made it work for me :D
@mellacristobal445
Жыл бұрын
THANK YOU!
@shadowfox1989
Жыл бұрын
It still didn't work for me. :(
Thanks for the tutorial. Very useful. A proposal for the next video, please make the recording with slightly bigger font! Continue the Good Work!
@thedatalabs
Жыл бұрын
Thanks for your feedback!
Good sir Dapatkan di gunakankan secara bersamaan dgn 2 pengguna atau lebih tq
Hello. Is it possible to create a chart of the percentage of the "gender option"? Note gender specifically, but for the results in those boxes. Like for example, I create a data entry and my options there is (yes/no) and I would like to create a chart with the percentages of the yes / no options. Can you please help?
How would one send data to a different tab based on one or, really two sections that were made in the input sheet ? Say the second and third field ? If this field equals this and the second field equals this, then the data would send the data to a specific tab. New to this entirely, and am guessing it will be an entirely different function that would have to consume the entry and then process it. This is a great tutorial, by the way. You explain what you are writing is doing really well. So, I'm now researching, how to, after the data has been added to the main sheet, to have it automatically move to the tabs I want it separated to. I'm wondering if that can be done with just conditional formatting? Or if there should be a button function script created to do that. Something along the lines of:
@ttfrhoncho6716
2 жыл бұрын
function onEdit(e) { const sh=e.range.getSheet(); if(sh.getName()=="sheet 1" && e.range.columnStart==1 && e.value=="TRUE") { const tsh=e.source.getSheetByName('Sheet 2'); const nr=tsh.getLastRow()+1; sh.getRange(e.range.rowStart,2,1,1).copyTo(tsh.getRange(nr,2,1,1)); sh.getRange(e.range.rowStart,4,1,1).copyTo(tsh.getRange(nr,4,1,1)); sh.getRange('E3:E').copyTo(tsh.getRange(nr,11,1,1)); } }
Can it use with excel online office scripts , I mean that the scripts ( language) in google sheet same as with excel online ( Office script) or not ?
Is it possible to get the form data to sort to 3 different database tabs? We already have the workbook and tabs, but are looking for a way to simplify the data entry. The search bar would make it so much easier also, but not sure that would work with what we are trying to do. example of Tabs: T81B, T81G and Customer Information example of data collected: Scout Name, Customer, customer address, payment info, product name, product option 1, Product option 2 and Quantity I need the following "Scout Name, Customer, product name, product option 1, Product option 2 and Quantity" sorted to tabs depending on if they are 81B or 81G. I need the following "Customer, customer address, payment info" sorted to the customer information tab.
please upload the script for my our reference.. Very useful to start with.. Thank you so much for this video..
sir what video recording and editing tool did you use? Please?
Hello, is there a way we can assign a script, like a Save button? Once we hit Save it will be imported to another google sheet not only on the same sheet?
good job how can find if any duplicate entry the will inform if any employee id # issue ?
Thanks
thanks
Hi @TheDataLabs team - I have followed the entire series and really appreciate the work that has been done on it. I'm able to do all the steps and apart from Submit button, everything else is working as expected. Can you please help me on - after clicking on Submit button data is not being stored in the database sheet although I have declared the variable as you said?
@nurfatihahjunaidi1935
Жыл бұрын
same tho
It was amezing! If I wish this form to hide at a fix time of a day and show again at a fix time next day autometicaly, how to do it ? Please
Thank you so much for this, very useful on my project. May I also know how to search from 2 spreadsheet using 1 userform? thanks