Google Sheets is a powerful tool for managing data, and with a little HTML and scripting, you can turn it into a backend for a custom data entry form. This is perfect for teachers, small businesses, survey forms, or anyone who needs to collect data through a user-friendly interface.
In this blog post, weβll guide you through creating an HTML form that submits data directly to Google Sheets, without needing a server or database.
β What Youβll Need
- A Google account
- A new or existing Google Sheet
- Basic knowledge of HTML
- A simple script using Google Apps Script
π Step 1: Create Your Google Sheet
- Go to Google Sheets.
- Create a new spreadsheet and name it (e.g.,
Data Form Entries). - Label the first row with the column headers you want to collect. Example:
π§ Step 2: Write the Apps Script
- In your Sheet, click on Extensions β Apps Script.
- Delete any default code and paste the following:
1 2 3 4 5 | function doPost(e) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var data = JSON.parse(e.postData.contents); sheet.appendRow([data.name, data.email, data.message]); return ContentService.createTextOutput("Success").setMimeType(ContentService.MimeType.TEXT); |
- Click Deploy β Test deployments, and choose Web app.
- In the dialog:
- Execute as: Me
- Who has access: Anyone
- Click Deploy, then Authorize it using your Google account.
- Copy the Web App URL shown β youβll need this for the form.
π§Ύ Step 3: Create the HTML Form
You can now create a simple HTML form and use fetch() to submit the data.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 | <!DOCTYPE html> <html> <head> <title>Data Entry Form</title> </head> <body> <h2>Submit Your Info</h2> <form id="dataForm"> <label>Name:</label><br> <input type="text" name="name" required><br><br> <label>Email:</label><br> <input type="email" name="email" required><br><br> <label>Message:</label><br> <textarea name="message" required></textarea><br><br> <button type="submit">Submit</button> </form> <p id="responseMessage"></p> <script> const scriptURL = "PASTE_YOUR_WEB_APP_URL_HERE"; const form = document.getElementById('dataForm'); form.addEventListener('submit', e => { e.preventDefault(); const formData = new FormData(form); const data = Object.fromEntries(formData.entries()); fetch(scriptURL, { method: 'POST', body: JSON.stringify(data), headers: { 'Content-Type': 'application/json' }, }) .then(response => document.getElementById("responseMessage").innerText = "Form submitted successfully!") .catch(error => document.getElementById("responseMessage").innerText = "Something went wrong!"); form.reset(); }); </script> </body> </html> |
π Security Tip: This form works for public or open-access forms. Do not use for sensitive information.
π How to Use It
You can:
- Upload the HTML file to your website.
- Host it on GitHub Pages.
- Use it locally (double-click to open in browser).
Any submission will be automatically added to your Google Sheet!
π― Bonus Features
You can extend this setup by:
- Adding Google reCAPTCHA for spam protection.
- Sending confirmation emails via Apps Script.
- Embedding the form in a Google Site or blog.
β Conclusion
This method is a simple, free way to collect data using a custom interface with Google Sheets as your backend. Whether youβre managing student feedback, event registrations, or collecting survey responses, this setup is quick to implement and easy to use.
Leave a Reply