Using Google Sheet as Your Database
When I was in Second year (CSE) I took this project called Bus pass Registration System, at that point of time I didn't know anything about Database later in 4th sem I did learn about MySQL but I just wanted a solution where I can seamlessly incorporate Database in my web application without the hassle of setting up complex infrastructure. In this article I will showcase how you can use Google sheet as your online Database via API Integration.
Step 1: Update HTML Form Inputs
In your HTML code, locate the form section and modify the input types by adding names in the format of name="data[name]" for each input label. Ensure consistency by applying this modification to all input labels within the form.
For example:
<div class="col-md-6" class="name"> <label for="Name" class="hov">Name</label> <input type="text" name="data[name]" id="Name" placeholder="Full name" class="PRN" required /> </div> <div class="col-md-6"> <label for="Contact NO.">Contact NO.</label> <input type="tel" name="data[phoneno]" placeholder="Contact" class="PRN" id="phoneno" required /> </div>
Step 2: Create a Google Sheet
Next, create a Google Sheet where the data from your HTML form will be stored. Arrange the data parameters in the same sequence as your form inputs for easy mapping. (The data parameters are the ones that you have put in your form such as name, phoneno, etc. Its the data that you want user to enter, make sure the names used in code and in the google sheet are same.)
Step 3: Generate API Link using SheetDB
Visit SheetDBAPI and sign in with your Google account. Grant the necessary permissions and proceed to create a new API. Paste the URL of your Google Sheet, which will generate a new API link. Copy this link to your clipboard for later use.
Step 4: Integrate API Link into Form
Within your form section in the HTML code, set the action attribute of the form tag to the API URL obtained from SheetDB. Ensure the method is set to "post" for data submission.
<form action="api_url_from_clipboard" method="post" id="sheetdb-form-submit"><!-- Form inputs here --></form>
Step 5: Add JavaScript Code
Refer the following JavaScript code & you can also retrieve the code from the GitHub repository (link provided in the bio). Paste this code below the form tag or create a new javascript file and attach it to your HTML document. This JavaScript code facilitates the submission of data from the HTML form to the Google Sheet via the API.
var form = document.getElementById("sheetdb-form"); form.addEventListener("submit", (e) => { e.preventDefault(); fetch(form.action, { method: "POST", body: new FormData( document.getElementById("sheetdb-form") ), }) .then((response) => response.json()) .then((html) => { window.open("nextpage.html", "_blank"); });
Step 6: Test and Deploy
Before deployment or using it as mainstram in your project, thoroughly test your form to ensure seamless functionality. Enter data into the form and submit it to verify that it correctly populates your Google Sheet. Once validated, deploy your application and start collecting data effortlessly!
By following these simple steps, you can harness the power of Google Sheets as a lightweight and efficient database solution for your web application. Stay tuned for more insightful tips and tricks!
Connect with me on LinkedIn for more interesting tech tutorials and updates! Shardul Kulkarni
#FullStackDeveloper #WebDevelopment #GoogleSheets #APIIntegration #HTMLForms #DatabaseIntegration #TechTutorial
FAQs
To store data from HTML forms in a database, use a server-side scripting language (e.g., PHP, Python) to process and insert form data into the database. Create an HTML form with input fields for the data you want to store, specifying the form's action attribute to point to a server-side script, like PHP.
How do I use Google Sheets as a database? ›
How to Create a Database in Google Sheets: 6 Standard Steps
- Step 1: Open Google Sheets and create a new spreadsheet. ...
- Step 2: Create columns to define data fields. ...
- Step 3: Add the data. ...
- Step 4: Sort your data. ...
- Step 5: QUERY your database. ...
- Step 6: Share your database with the team.
To store data from HTML forms in a database, use a server-side scripting language (e.g., PHP, Python) to process and insert form data into the database. Create an HTML form with input fields for the data you want to store, specifying the form's action attribute to point to a server-side script, like PHP.
How do I use Google form as a database? ›
By connecting Google Sheets and a database through Zapier, a chain of events occurs every time someone submits a form:
- The user submits the form response.
- Google Forms sends the form data to Google Sheets.
- Zapier detects that a new row has been created in the spreadsheet that it's connected to.
Here are the 10 best Google Sheets alternatives available in the market today.
- Top 10 Google Sheet Alternatives.
- ClickUp.
- Smartsheet.
- Microsoft Excel Online.
- Zoho Sheet.
- Jotform.
- OpenOffice Calc.
- Basecamp.
The Google Sheets add-on allows you to analyze pull data from your local database, run and schedule queries, and auto-refresh data down to a 1-minute frequency. It's compatible with MySQL, PostgreSQL, SQL Server, MariaDB, and Oracle databases and Redshift and Snowflake data warehouses.
How to store data of HTML form? ›
To store data using HTML we first need to design a registration form, then install a virtual server, next thing we need is code editor, write the HTML code in it, use CSS to design looks properly and apply CSS code on HTML code. All your data will be stored in MySQL.
What database to use for HTML? ›
MySQL. MySQL is an open-source relational database management system developed and maintained by Oracle. It's one of the most popular databases, used by 46.85 percent of the respondents to the 2022 Stack Overflow developer survey.
How do I connect my HTML page with a database? ›
For this you need to follow the following steps:
- Step 1: Filter your HTML form requirements for your contact us web page. ...
- Step 2: Create a database and a table in MySQL. ...
- Step 3: Create HTML form for connecting to database. ...
- Step 4: Create a PHP page to save data from HTML form to your MySQL database. ...
- Step 5: All done!
Importing data from Google Sheets is also possible to send them to Google Forms. Although it is not a native connection, as said above, so you also will need an add-on here to complete the task. On Google Sheet, you can get several add-ons that can convert your spreadsheets into forms.
Google Cloud databases provide you the best options for building enterprise generative AI apps for organizations of any size. Learn how Bayer Crop Science modernized their data solution tool with AlloyDB for PostgreSQL to handle increasing demands and improve collaboration.
Is Google Forms good for data collection? ›
Benefits of Using Google Forms for Data Collection
This integration ensures that data collection is streamlined and organized. Forms can be customized with various question types like multiple-choice, short answer, and scales. This allows for flexibility in capturing the needed data effectively.
Can Google Sheets be used as a relational database? ›
It's important to note that while Google Sheets can technically be used as a relational database, it is only suitable for small, simple data management tasks. For large or intricate data management needs, I recommend opting for a dedicated relational database system like Airtable, MySQL, or PostgreSQL.
Does Google have a database app like Access? ›
Sheets is Google's alternative to Microsoft Excel. But, it also offers a whole range of capabilities that can make it a viable alternative for certain kinds of Microsoft Access users.
Can Google Sheets do data tables? ›
In Google Sheets, tables can simplify data creation and reduce the need to repeatedly format, input, and update data by automatically applying format and structure to ranges of data. Tables are well suited to track and organize information like: Project tracking.
