Google Sheets Web App Tutorial with Apps Script Using AI | Edit, Delete, Add, View | Part-3 – Zero Special – Your Hub for Tech Tricks and Custom Software

Welcome to Part 3 of our Google Sheets CRUD Tutorial Series using Google Apps Script and Bootstrap 5. In this part, we’ll make the interface responsive and add full support for Edit, Delete, and View operations using modals and validation.

🧠 AI Prompt Used to Generate This Project

Create a full Google Apps Script Web App to manage student records using Google Sheets. The app should include:

1. A Code.gs backend script that:
   - which responsible for CRUD operations and other related operation

2. An `Index.html` frontend with:
   - A clean responsive Bootstrap 5 layout.
   - A search field with Add buttons styled as Bootstrap on the right side.
   - A table with headers: ID,Name,Class,Section,ParentName,Contact,Address,Email,DateOfBirth,Gender,BloodGroup,AdmissionDate
   - The each row of the table have "Edit" and "Delete" buttons styled as Bootstrap `btn-warning` and `btn-danger` with icons.
   - Loads data into the table from the "Students" sheet using google.script function.  
   - When Add button clicked it will open a model with form with fields: ID,Name,Class,Section,ParentName,Contact,Address,Email,DateOfBirth,Gender,BloodGroup,AdmissionDate and hidden field if required and also have the Submit button styled as Bootstrap btn-success and a Clear button as btn-secondary to reset the form.
   - When Submit button clicked it will save the data to the sheet 
   - When edit button clicked in the table row it will open the model with the data for update and the Submit button will change to Update 
   - When Delete button clicked in the table row it will delete the record from the sheet and the result will reflect in the webpage.

Also include inline styles or Bootstrap classes to make the layout visually appealing, and ensure form validation is handled via required fields. The data return from the GAS should be JSON.stringfy.

📁 Code.gs – Google Apps Script Backend

function doGet() {
  return HtmlService.createHtmlOutputFromFile('Index').setXFrameOptionsMode(HtmlService.XFrameOptionsMode.ALLOWALL);
}

function getAllStudents() {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Students");
  const data = sheet.getDataRange().getValues();
  return JSON.stringify(data);
}

function saveStudent(formData) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Students");
  const data = sheet.getDataRange().getValues();
  const idIndex = 0;

  // Update existing
  for (let i = 1; i < data.length; i++) {
    if (data[i][idIndex] == formData.id) {
      sheet.getRange(i + 1, 1, 1, 12).setValues([[ 
        formData.id, formData.name, formData.className, formData.section, formData.parentName,
        formData.contact, formData.address, formData.email, formData.dob, formData.gender,
        formData.bloodGroup, formData.admissionDate
      ]]);
      return "updated";
    }
  }

  // Add new
  sheet.appendRow([
    formData.id, formData.name, formData.className, formData.section, formData.parentName,
    formData.contact, formData.address, formData.email, formData.dob, formData.gender,
    formData.bloodGroup, formData.admissionDate
  ]);
  return "added";
}

function deleteStudent(id) {
  const sheet = SpreadsheetApp.getActive().getSheetByName("Students");
  const data = sheet.getDataRange().getValues();
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] == id) {
      sheet.deleteRow(i + 1);
      return "deleted";
    }
  }
  return "not found";
}

🖥️ Index.html – Frontend with Bootstrap 5/h32>

<!DOCTYPE html>
<html>
<head>
  <title>Student Management App</title>
  <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0/dist/css/bootstrap.min.css" rel="stylesheet">
  <script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>
</head>
<body class="bg-light p-3">
  <div class="container">
    <div class="d-flex justify-content-between align-items-center mb-3">
      <h2>Student Records</h2>
      <button class="btn btn-primary" onclick="openModal()">+ Add Student</button>
    </div>

    <table class="table table-bordered table-striped table-responsive">
      <thead class="table-dark">
        <tr>
          <th>ID</th><th>Name</th><th>Class</th><th>Section</th><th>ParentName</th><th>Contact</th><th>Address</th><th>Email</th><th>DOB</th><th>Gender</th><th>BloodGroup</th><th>AdmissionDate</th><th>Actions</th>
        </tr>
      </thead>
      <tbody id="studentTableBody"></tbody>
    </table>
  </div>

  <!-- Modal -->
  <div class="modal fade" id="studentModal" tabindex="-1">
    <div class="modal-dialog modal-lg">
      <div class="modal-content">
        <div class="modal-header"><h5 class="modal-title">Student Form</h5></div>
        <div class="modal-body">
          <form id="studentForm">
            <input type="hidden" id="studentId">
            <div class="row">
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="name" placeholder="Name" required></div>
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="className" placeholder="Class" required></div>
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="section" placeholder="Section"></div>
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="parentName" placeholder="Parent Name" required></div>
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="contact" placeholder="Contact"></div>
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="address" placeholder="Address"></div>
              <div class="col-md-6 mb-2"><input type="email" class="form-control" id="email" placeholder="Email"></div>
              <div class="col-md-6 mb-2"><input type="date" class="form-control" id="dob" required></div>
              <div class="col-md-6 mb-2"><select class="form-control" id="gender">
                <option value="">Gender</option>
                <option value="Male">Male</option>
                <option value="Female">Female</option>
              </select></div>
              <div class="col-md-6 mb-2"><input type="text" class="form-control" id="bloodGroup" placeholder="Blood Group"></div>
              <div class="col-md-6 mb-2"><input type="date" class="form-control" id="admissionDate" required></div>
            </div>
            <div class="modal-footer">
              <button type="submit" class="btn btn-success" id="submitBtn">Submit</button>
              <button type="button" class="btn btn-secondary" onclick="clearForm()">Clear</button>
            </div>
          </form>
        </div>
      </div>
    </div>
  </div>

  <script>
    // Include your JavaScript logic here
  </script>
</body>
</html>

🧠 AI Prompt Used to Generate This Project

📁 Code.gs – Google Apps Script Backend

Leave a Comment Cancel Reply