Create a Automated Data Entry Form & Dashboard Using Google Sheets + Apps Script | (In 5 Minutes), by Vedakarna on YouTube. 5 min 40 s.
- Video length
- 5 min 40 s
- Time to do it
- 30 minutes
- Difficulty
- Medium
- Steps
- 9
What you need
Steps
Open Google Sheets, create a blank spreadsheet, and add a sheet named Settings. List your dropdown options there, such as customer types, products and payment methods, one list per column.
Open Extensions > Apps Script. Add a file named Code.gs and an HTML file for the form, then write a doGet function that serves your HTML page.
Build the form in HTML with fields for Customer ID, name, contact details and purchase amount. Fill the dropdowns from the Settings sheet so the lists stay in sync.
Deploy the project: click Deploy > New deployment > Web app, set Execute as to yourself and Who has access to Anyone, then authorise it and copy the URL.
Add a sheet named Customers with one header per form field. Write a function that appends each submission as a new row so records save automatically.
Add a search box that looks up a Customer ID and fills the form with that record, so you can edit the details and write them back to the row.
Add a delete button that removes the matching row, and a reset button that clears every field on the form.
Create a Dashboard sheet with COUNTIF and SUMIF formulas and charts for totals, top products and today's sales, then refresh it after each entry.
Test the whole flow: submit a record, check the Customers sheet, edit it, then delete it and confirm the row disappears.
Tips and common mistakes
Use data validation with a range from the Settings sheet instead of typing list items by hand, so new options appear without editing code.
After changing Code.gs or the HTML file, create a new deployment version, otherwise the web app keeps running the old code.
Keep a copy of the sheet before testing the delete function, since removed rows cannot be undone.
Add LockService to your save function so two people submitting at the same time do not overwrite each other.
Questions
Do I need to know JavaScript?
Basic JavaScript helps, but you can paste the Code.gs and HTML files from the tutorial and change the field names to match your own sheet.
Can I open the form on my phone?
Yes. The web app URL works in a mobile browser, and every submission lands in the same Google Sheet.
Why do I get an authorisation warning?
Apps Script asks for permission the first time you run or deploy a script. Sign in with the account that owns the sheet and allow access.
This guide was drafted from the video's details and general task knowledge. It has not been checked against the full video. Check the video and relevant product instructions before following a procedure. Report a correction.
