Using Google Sheets as a Database for HTML Forms: A Step-by-Step Guide:

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