Build a CRUD Application Using Google Sheets as Database with HTML, CSS, and JavaScript



HTML, CSS, JavaScript code for Crud App Using Google Sheet as a Database

This is an HTML code for a web page that allows users to add, update, and delete to-do items to a database through an API. The page contains a form with a text input field and two buttons, "Add Todo" and "Update Todo." It also includes a table that displays the to-do items from the database with their IDs and corresponding "Edit" and "Delete" buttons.

The script section of the code contains four functions: addData(), readData(), delData(), and updateCall(). addData() sends a POST request to the API endpoint with the inputted to-do item and adds the item to the table upon successful submission. readData() sends a GET request to the API endpoint and retrieves the to-do items from the database, which it then displays in the table. delData() sends a GET request to the API endpoint to delete the to-do item with the given ID, and updateCall() allows users to update to-do items.

The script uses the fetch() function to make requests to the API endpoint, and the response from the server is parsed using JSON. The join() function is used to concatenate the HTML strings for each to-do item into a single string to populate the table's body with the to-do items. The reset() function is used to clear the form after the submission of a new to-do item.

Additionally, the code uses Cascading Style Sheets (CSS) to style the buttons and table cells. The buttons have a red background with white text and a border radius, while the "Update" button is initially set to "display: none" to hide it until an item is selected for updating.


<!DOCTYPE html>
<!-- Define the document type as HTML -->
<html lang="en">
<!-- Start of HTML document, with "en" set as the default language -->
<head>
    <!-- Start of the header section -->
    <meta charset="UTF-8">
    <!-- Set the character encoding to UTF-8 -->
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <!-- Set the compatibility mode for Internet Explorer to the latest version -->
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <!-- Set the viewport for the page to the device's width and initial zoom level -->
    <title>Document</title>
    <!-- Set the title of the page to "Document" -->
    <style>
        /* Define some styles for the elements with the "edit" and "delete" classes */
        .edit,.delete{
            background-color:red;
            cursor: pointer;
            color:white;
            padding: 3px;
            border-radius:5px;
        }
        /* Define some styles for the element with the "update" class */
        .update{
            display: none;
        }
    </style>
    <!-- End of the header section -->
</head>
<!-- End of the header section -->
<body>
<!-- Start of the body section -->
    <form>
        <!-- Start of the form element -->
        <input type="text"class="todo"placeholder='Todo..'>
        <!-- Create a text input field with the "todo" class and a placeholder text -->
        <button class="add"type="button" onclick="addData()">Add Todo</button>
        <!-- Create a button with the "add" and "type" classes and an onclick event handler that calls the addData() function -->
        <button class="update"type="button">Update Todo</button>
        <!-- Create a button with the "update" and "type" classes -->
    </form>
    <!-- End of the form element -->
    <table>
        <!-- Start of the table element -->
        <thead>
            <!-- Start of the table header section -->
            <tr>
                <!-- Start of the table row -->
                <th>Id</th>
                <!-- Create a table cell with the text "Id" -->
                <th>Todo</th>
                <!-- Create a table cell with the text "Todo" -->
                <th>Update</th>
                <!-- Create a table cell with the text "Update" -->
                <th>Delete</th>
                <!-- Create a table cell with the text "Delete" -->
            </tr>
            <!-- End of the table row -->
        </thead>
        <!-- End of the table header section -->
        <tbody></tbody>
        <!-- Start of the table body section -->
    </table>
    <!-- End of the table element -->
    <script>
        // Start of the script section
        let api = "YOUR_API_URL_HERE";
        // Set the API URL as a variable
        let form = document.querySelector("form");
        // Find the form element and set it as a variable
        let add = document.querySelector(".add");
        // Find the element with the "add" class and set it as a variable
        let update = document.querySelector(".update");
        // Find the element with the "update" class and set it as a variable
        let tbody = document.querySelector("tbody");
        // Find the table body element and set it as a variable
        function addData() {
    // Change the text content of the add button to indicate that data is being added
    add.textContent="Adding.."
    // Create an object with the todo value obtained from the form
    let obj = {
        todo:form[0].value
    }
    // Use fetch to make a POST request to the API endpoint with the JSON stringified obj as the request body
    fetch(api,{
        method:"POST",
        body:JSON.stringify(obj)
    })
    // Parse the response text from the server and call readData() function to update the UI with the new data
    .then(res => res.text())
    .then(data => {
        readData()
        // Show an alert with the response message from the server
        alert(data)
        // Reset the form and change the text content of the add button back to its original state
        add.textContent="Add Todo"
        form.reset();
    });
}

function readData(){
    // Use fetch to make a GET request to the API endpoint and parse the JSON response
    fetch(api)
    .then(res=>res.json())
    .then(data=>{
        // Extract the todo data from the response
        let todo = data.todo;
        // Map each todo item to a string of table row HTML
        let trtd = todo.map(each=>{
            return `
            <tr>
            <td class="id">${each[0]}</td>    
            <td class="data">${each[1]}</td>
            <td class="edit"onclick="updateCall(this,${each[0]})">Edit</td>    
            <td class="delete"onclick="delData(${each[0]})">Delete</td>    
            </tr>
            
            `
        })
        // Join the array of table row HTML strings and set the innerHTML of the tbody element to it
        tbody.innerHTML=trtd.join("");
    })
}
// Call the readData() function on page load to populate the table with data from the API
readData()

function delData(id){
    // Use fetch to make a GET request to the API endpoint to delete the todo item with the given id
    fetch(api+`?del=true&id=${id}`)
    .then(res=>res.text())
    .then(data=>{
        // Update the UI by calling readData() function and show an alert with the response message from the server
        readData()
        alert(data)
    })
}

function updateCall(elm,id){
    // Hide the add button and show the update button
    add.style.display="none"
    update.style.display="unset"
    // Get the current todo value from the data cell of the current row and set the form value to it
    let todo = elm.parentElement.querySelector(".data").textContent;
    form[0].value=todo;
    // Set the onclick attribute of the update button to call updateData() function with the current todo item id as an argument
    update.setAttribute("onclick",`updateData(${id})`)
}
function updateData(id){
    update.textContent="Updating.."
    // send a fetch request to update the todo data
    fetch(api+`?update=true&id=${id}&data=${form[0].value}`)
    .then(res=>res.text())
    .then(data=>{
        // read the updated data
        readData()
        alert(data)
        // reset the form input and update button text
        form.reset()
        update.textContent="Update Todo"
        // show the add button and hide the update button
        add.style.display="unset"
        update.style.display="none"
    })
}
// This function is called when the user clicks the "Update" button in the table. It updates the todo data by sending a fetch request to the server with the updated todo text and the id of the todo to be updated. Once the server responds with a success message, the function calls readData() to update the table, displays an alert message, and resets the form input and update button text. Finally, it shows the "Add" button and hides the "Update" button.


Appscript Code For CRUD APP using Google Sheet as a Database



// Open the Google Spreadsheet using its URL and get the sheet named "Sheet1"
const app = SpreadsheetApp.openByUrl("Your_Spreadsheet_url_here");
const sheet = app.getSheetByName("Sheet1");

// Define a function to handle HTTP GET requests
function doGet(req){
  // Check if the request includes a "del" parameter, indicating a row deletion request
  if(req.parameter.del){
    // Delete the row with the specified ID (row number)
    sheet.deleteRow(req.parameter.id)
    // Return a text response indicating that the data has been deleted
    return ContentService.createTextOutput("Data Deleted!")
  // Check if the request includes an "update" parameter, indicating an update request
  } else if(req.parameter.update){
    // Update the data in the specified row (ID) and column (2) with the new value (data)
    sheet.getRange(req.parameter.id,2).setValue(req.parameter.data);
    // Return a text response indicating that the data has been updated
    return ContentService.createTextOutput("Data Updated!")
  // If no parameters are included in the request, assume it's a read request
  } else {
    // Get all the data from the sheet (excluding the first row, which contains headers)
    let data = sheet.getDataRange().getValues();
    data.shift();
    // Store the data in an object and return it as a JSON string
    let obj = {
      todo:data
    }
    return ContentService.createTextOutput(JSON.stringify(obj))
  }
}

// Define a function to handle HTTP POST requests
function doPost(req){
  // Parse the data in the POST request
  let data = JSON.parse(req.postData.contents)
  // Append the new data to the sheet (using a formula to automatically generate a unique ID)
  sheet.appendRow(["=row()",data.todo])
  // Return a text response indicating that the data has been received
  return ContentService.createTextOutput("Data Received!")
}

// Define a test function to log all the data in the sheet (for debugging purposes)
function test(){
  Logger.log(sheet.getDataRange().getValues())
}


Explanation of above appscript code

The code is written in Google Apps Script, which allows you to automate tasks and build custom applications in Google Workspace.

The first two lines of code define the spreadsheet and sheet to be used in the script. You'll need to replace "Your_Spreadsheet_url_here" with the URL of your own spreadsheet and "Sheet1" with the name of the sheet you want to use.

The doGet() function is used to handle GET requests to the script. If the request includes a del parameter, it will delete the row with the ID specified in the id parameter and return a message saying that the data was deleted. If the request includes an update parameter, it will update the row with the ID specified in the id parameter with the data specified in the data parameter and return a message saying that the data was updated. Otherwise, it will return a JSON object containing all the data in the sheet except for the first row (which is assumed to contain headers).

The doPost() function is used to handle POST requests to the script. It expects the request to contain a JSON object with a todo property. It will then append a new row to the sheet with the formula =row() in the first column (which will automatically populate the row number) and the todo value in the second column. It will then return a message saying that the data was received.

The test() function is just a helper function that logs all the data in the sheet to the script's log. It's not used in the actual script.

Overall, this script provides a simple API for creating, reading, updating, and deleting rows in a Google Sheet. It could be used, for example, to create a simple to-do list application that stores the data in a Google Sheet.


More Posts For You!


5 More HTML Tricks For You

5 HTML Tricks That You Can Use To Enhance Your Web Pages

Effortlessly Convert Excel to JSON with JavaScript: Streamline Your Data Management with Sheet Js

Integrating OCR Capabilities into Web Applications with JavaScript and AppScript

Efficient Text Extraction from PDFs: Implementing OCR with JavaScript and AppScript

Build a CRUD Application Using Google Sheets as Database with HTML, CSS, and JavaScript

Integrating OpenAI with Google Sheets using AppScript for Automated Responses

Upload Images to Google Drive and Google Sheets from HTML File Input: A Complete JavaScript and AppScript Guide

Convert HTML and CSS to PDF with JavaScript Using HTML2PDF.js

Create a Functional Contact Form Using HTML, CSS, JavaScript, and Google AppScript

Build a CRUD App Using HTML, CSS, JavaScript, and IndexedDB API

Reading Data From Google Sheets to HTML Using JavaScript and Apps Script

PrevNext