Building a Simple CRUD Web App with Google Apps Script and Google Sheets

Did you know you can build fully functional web applications without paying for hosting or servers? With Google Apps Script, you can create web apps that use Google Sheets as a database, completely free. In this tutorial, I’ll show you how to build a simple but powerful CRUD (Create, Read, Update, Delete) web application that you can customize for your own projects.

What We’ll Build

We’re going to create a basic inventory management system with these features:

• A web interface anyone can access (with permissions you control)
• Ability to add new items to inventory
• List view of all items
• Edit functionality for existing items
• Delete capability for removing items
• Google Sheets as the backend database

No prior coding experience is required, but basic familiarity with HTML and JavaScript will help.

Prerequisites

• A Google account
• A new Google Sheet (this will be our database)
• About 30 minutes of your time

Step 1: Set Up the Google Sheet Database

First, let’s create our “database” structure:

1. Create a new Google Sheet
2. Rename the first sheet to “Inventory”
3. Add these headers to row 1:

| A: ID | B: Item Name | C: Category | D: Quantity | E: Price | F: Last Updated | | —– | ———— | ———– | ———– | ——– | ————— |

This sheet will store all our inventory data.

Step 2: Create the Apps Script Project

Now let’s set up the Apps Script project that will power our web app:

1. In your Google Sheet, click Extensions → Apps Script
2. Delete any code in the editor
3. Rename the project to “Inventory Manager” (top left of the screen)
4. Copy and paste the following code:

// Global variables
let ss = SpreadsheetApp.getActiveSpreadsheet();
let inventorySheet = ss.getSheetByName("Inventory");

/**
 * doGet - Main entry point for our web app
 * This runs when someone accesses the web app URL
 */
function doGet(e) {
  // Check if we need to process any specific actions
  if (e.parameter.v == "add") {
    // Show the add item form
    return HtmlService.createTemplateFromFile("AddItem")
      .evaluate()
      .setTitle("Add New Inventory Item")
      .setFaviconUrl("https://www.google.com/images/icons/product/sheets-32.png");
  } else if (e.parameter.v == "edit" && e.parameter.id) {
    // Show the edit form with the specified item
    let template = HtmlService.createTemplateFromFile("EditItem");
    template.itemData = getItemById(e.parameter.id);
    return template.evaluate()
      .setTitle("Edit Inventory Item")
      .setFaviconUrl("https://www.google.com/images/icons/product/sheets-32.png");
  } else {
    // Show the main inventory list by default
    return HtmlService.createTemplateFromFile("MainInventory")
      .evaluate()
      .setTitle("Inventory Management System")
      .setFaviconUrl("https://www.google.com/images/icons/product/sheets-32.png");
  }
}

/**
 * Include HTML files in other templates
 */
function include(filename) {
  return HtmlService.createHtmlOutputFromFile(filename).getContent();
}

/**
 * Get all inventory items
 */
function getAllItems() {
  // Get all data from the sheet except the header row
  const data = inventorySheet.getDataRange().getValues();
  const items = [];
  
  // Skip the header row (start at index 1)
  for (let i = 1; i < data.length; i++) {
    // Only add non-empty rows
    if (data[i][0]) {
      items.push({
        id: data[i][0],
        name: data[i][1],
        category: data[i][2],
        quantity: data[i][3],
        price: data[i][4],
        lastUpdated: data[i][5]
      });
    }
  }
  
  return items;
}

/**
 * Get a specific item by ID
 */
function getItemById(id) {
  const data = inventorySheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] == id) {
      return {
        id: data[i][0],
        name: data[i][1],
        category: data[i][2],
        quantity: data[i][3],
        price: data[i][4],
        lastUpdated: data[i][5]
      };
    }
  }
  
  return null; // Item not found
}

/**
 * Add a new inventory item
 */
function addItem(item) {
  // Generate a unique ID (timestamp-based)
  const id = new Date().getTime();
  
  // Add new row to the sheet
  inventorySheet.appendRow([
    id,
    item.name,
    item.category,
    item.quantity,
    item.price,
    new Date()
  ]);
  
  return { success: true, id: id };
}

/**
 * Update an existing item
 */
function updateItem(item) {
  const data = inventorySheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] == item.id) {
      // Found the item, update it
      inventorySheet.getRange(i + 1, 2).setValue(item.name);
      inventorySheet.getRange(i + 1, 3).setValue(item.category);
      inventorySheet.getRange(i + 1, 4).setValue(item.quantity);
      inventorySheet.getRange(i + 1, 5).setValue(item.price);
      inventorySheet.getRange(i + 1, 6).setValue(new Date());
      
      return { success: true };
    }
  }
  
  return { success: false, message: "Item not found" };
}

/**
 * Delete an item
 */
function deleteItem(id) {
  const data = inventorySheet.getDataRange().getValues();
  
  for (let i = 1; i < data.length; i++) {
    if (data[i][0] == id) {
      // Found the item, delete the row
      inventorySheet.deleteRow(i + 1);
      return { success: true };
    }
  }
  
  return { success: false, message: "Item not found" };
}

1. Save the project

This JavaScript code handles all the server-side logic for our web app.

Step 3: Create the HTML Templates

Our web app needs several HTML templates to render the user interface. Let’s create them one by one.

3.1. MainInventory.html

This is the main screen showing the inventory list.

1. Click File → New → HTML file
2. Name it “MainInventory”
3. Paste this code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
  </head>
  <body>
    <div class="container">
      <header>
        <h1>Inventory Management System</h1>
        <a href="?v=add" class="btn add-btn">+ Add New Item</a>
      </header>
      
      <div class="inventory-list">
        <table id="inventoryTable">
          <thead>
            <tr>
              <th>Item Name</th>
              <th>Category</th>
              <th>Quantity</th>
              <th>Price</th>
              <th>Last Updated</th>
              <th>Actions</th>
            </tr>
          </thead>
          <tbody id="inventoryItems">
            <!-- Items will be loaded here dynamically -->
            <tr><td colspan="6" class="loading-message">Loading inventory...</td></tr>
          </tbody>
        </table>
      </div>
    </div>
    
    <script>
      // Load inventory data when page loads
      window.onload = function() {
        google.script.run
          .withSuccessHandler(displayInventory)
          .withFailureHandler(showError)
          .getAllItems();
      };
      
      // Display inventory data in the table
      function displayInventory(items) {
        const tbody = document.getElementById('inventoryItems');
        tbody.innerHTML = '';
        
        if (items.length === 0) {
          tbody.innerHTML = '<tr><td colspan="6" class="empty-message">No items in inventory. Add some!</td></tr>';
          return;
        }
        
        items.forEach(item => {
          const row = document.createElement('tr');
          
          // Format the date
          const lastUpdated = new Date(item.lastUpdated);
          const formattedDate = lastUpdated.toLocaleDateString() + ' ' + 
                               lastUpdated.toLocaleTimeString([], {hour: '2-digit', minute:'2-digit'});
          
          row.innerHTML = `
            <td>${item.name}</td>
            <td>${item.category}</td>
            <td>${item.quantity}</td>
            <td>$${parseFloat(item.price).toFixed(2)}</td>
            <td>${formattedDate}</td>
            <td class="actions">
              <a href="?v=edit&id=${item.id}" class="edit-btn">Edit</a>
              <button onclick="confirmDelete(${item.id}, '${item.name}')" class="delete-btn">Delete</button>
            </td>
          `;
          
          tbody.appendChild(row);
        });
      }
      
      // Show error message if data loading fails
      function showError(error) {
        document.getElementById('inventoryItems').innerHTML = 
          `<tr><td colspan="6" class="error-message">Error loading data: ${error.message}</td></tr>`;
      }
      
      // Confirm before deleting an item
      function confirmDelete(id, name) {
        if (confirm(`Are you sure you want to delete "${name}"?`)) {
          google.script.run
            .withSuccessHandler(function(result) {
              if (result.success) {
                // Reload the inventory list
                google.script.run
                  .withSuccessHandler(displayInventory)
                  .getAllItems();
              } else {
                alert("Error: " + result.message);
              }
            })
            .withFailureHandler(function(error) {
              alert("Error: " + error.message);
            })
            .deleteItem(id);
        }
      }
    </script>
  </body>
</html>

3.2. AddItem.html

This template renders the form for adding new items.

1. Create another HTML file named “AddItem”
2. Paste this code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
  </head>
  <body>
    <div class="container">
      <header>
        <h1>Add New Inventory Item</h1>
        <a href="?" class="btn back-btn">← Back to Inventory</a>
      </header>
      
      <div class="form-container">
        <form id="addItemForm" onsubmit="submitForm(); return false;">
          <div class="form-group">
            <label for="name">Item Name:</label>
            <input type="text" id="name" name="name" required>
          </div>
          
          <div class="form-group">
            <label for="category">Category:</label>
            <input type="text" id="category" name="category" required>
          </div>
          
          <div class="form-group">
            <label for="quantity">Quantity:</label>
            <input type="number" id="quantity" name="quantity" min="0" required>
          </div>
          
          <div class="form-group">
            <label for="price">Price ($):</label>
            <input type="number" id="price" name="price" min="0" step="0.01" required>
          </div>
          
          <div class="form-actions">
            <button type="submit" class="btn submit-btn">Add Item</button>
            <a href="?" class="btn cancel-btn">Cancel</a>
          </div>
        </form>
      </div>
    </div>
    
    <script>
      function submitForm() {
        // Get form values
        const formData = {
          name: document.getElementById('name').value,
          category: document.getElementById('category').value,
          quantity: parseInt(document.getElementById('quantity').value),
          price: parseFloat(document.getElementById('price').value)
        };
        
        // Disable form
        toggleFormElements(true);
        
        // Submit to Google Apps Script
        google.script.run
          .withSuccessHandler(onSuccess)
          .withFailureHandler(onFailure)
          .addItem(formData);
      }
      
      function onSuccess(result) {
        if (result.success) {
          // Redirect back to main inventory
          window.location.href = '?';
        } else {
          alert("Error: " + result.message);
          toggleFormElements(false);
        }
      }
      
      function onFailure(error) {
        alert("Error: " + error.message);
        toggleFormElements(false);
      }
      
      function toggleFormElements(disabled) {
        const elements = document.querySelectorAll('#addItemForm input, #addItemForm button, #addItemForm a.btn');
        elements.forEach(el => {
          if (el.tagName === 'A') {
            el.style.pointerEvents = disabled ? 'none' : 'auto';
            el.style.opacity = disabled ? '0.5' : '1';
          } else {
            el.disabled = disabled;
          }
        });
      }
    </script>
  </body>
</html>

3.3. EditItem.html

This template is for editing existing items.

1. Create another HTML file named “EditItem”
2. Paste this code:

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
    <?!= include('Stylesheet'); ?>
  </head>
  <body>
    <div class="container">
      <header>
        <h1>Edit Inventory Item</h1>
        <a href="?" class="btn back-btn">← Back to Inventory</a>
      </header>
      
      <div class="form-container">
        <form id="editItemForm" onsubmit="submitForm(); return false;">
          <input type="hidden" id="itemId" value="<?= itemData.id ?>">
          
          <div class="form-group">
            <label for="name">Item Name:</label>
            <input type="text" id="name" name="name" value="<?= itemData.name ?>" required>
          </div>
          
          <div class="form-group">
            <label for="category">Category:</label>
            <input type="text" id="category" name="category" value="<?= itemData.category ?>" required>
          </div>
          
          <div class="form-group">
            <label for="quantity">Quantity:</label>
            <input type="number" id="quantity" name="quantity" min="0" value="<?= itemData.quantity ?>" required>
          </div>
          
          <div class="form-group">
            <label for="price">Price ($):</label>
            <input type="number" id="price" name="price" min="0" step="0.01" value="<?= itemData.price ?>" required>
          </div>
          
          <div class="form-actions">
            <button type="submit" class="btn submit-btn">Update Item</button>
            <a href="?" class="btn cancel-btn">Cancel</a>
          </div>
        </form>
      </div>
    </div>
    
    <script>
      function submitForm() {
        // Get form values
        const formData = {
          id: document.getElementById('itemId').value,
          name: document.getElementById('name').value,
          category: document.getElementById('category').value,
          quantity: parseInt(document.getElementById('quantity').value),
          price: parseFloat(document.getElementById('price').value)
        };
        
        // Disable form
        toggleFormElements(true);
        
        // Submit to Google Apps Script
        google.script.run
          .withSuccessHandler(onSuccess)
          .withFailureHandler(onFailure)
          .updateItem(formData);
      }
      
      function onSuccess(result) {
        if (result.success) {
          // Redirect back to main inventory
          window.location.href = '?';
        } else {
          alert("Error: " + result.message);
          toggleFormElements(false);
        }
      }
      
      function onFailure(error) {
        alert("Error: " + error.message);
        toggleFormElements(false);
      }
      
      function toggleFormElements(disabled) {
        const elements = document.querySelectorAll('#editItemForm input, #editItemForm button, #editItemForm a.btn');
        elements.forEach(el => {
          if (el.tagName === 'A') {
            el.style.pointerEvents = disabled ? 'none' : 'auto';
            el.style.opacity = disabled ? '0.5' : '1';
          } else {
            el.disabled = disabled;
          }
        });
      }
    </script>
  </body>
</html>

3.4. Stylesheet.html

Finally, let’s create a stylesheet to make our app look professional:

1. Create an HTML file named “Stylesheet”
2. Paste this code:

<style>
  * {
    box-sizing: border-box;
    font-family: Arial, sans-serif;
  }
  
  body {
    margin: 0;
    padding: 0;
    background-color: #f5f5f5;
  }
  
  .container {
    max-width: 1000px;
    margin: 0 auto;
    padding: 20px;
  }
  
  header {
    display: flex;
    justify-content: space-between;
    align-items: center;
    margin-bottom: 20px;
  }
  
  h1 {
    color: #2c3e50;
    margin: 0;
  }
  
  .btn {
    display: inline-block;
    padding: 8px 16px;
    background-color: #3498db;
    color: white;
    text-decoration: none;
    border-radius: 4px;
    border: none;
    font-size: 14px;
    cursor: pointer;
    transition: background-color 0.3s;
  }
  
  .btn:hover {
    background-color: #2980b9;
  }
  
  .add-btn {
    background-color: #2ecc71;
  }
  
  .add-btn:hover {
    background-color: #27ae60;
  }
  
  .back-btn {
    background-color: #7f8c8d;
  }
  
  .back-btn:hover {
    background-color: #6c7a7a;
  }
  
  .cancel-btn {
    background-color: #7f8c8d;
  }
  
  .submit-btn {
    background-color: #2ecc71;
  }
  
  .delete-btn {
    background-color: #e74c3c;
    margin-left: 5px;
  }
  
  .delete-btn:hover {
    background-color: #c0392b;
  }
  
  .inventory-list {
    background-color: white;
    border-radius: 5px;
    box-shadow: 0 2px 5px rgba(0, 0, 0, 0.1);
    overflow: hidden;
  }
  
  table {
    width: 100%;
    border-collapse: collapse;
  }
  
  th, td {
    padding: 12px 15px;
    text-align: left;
  }
  
  th {
    background-color: #34495e;
    color: white;
    font-weight: normal;
  }
  
  tr:nth-child(even) {
    background-color: #f9f9f9;
  }
  
  tr:hover {
    background-color: #f1f1f1;
  }
  
  .loading-message, .empty-message, .error-message {
    text-align: center;
    padding: 20px;
    color: #7f8c8d;
  }
  
  .error-message {
    color: #e74c3c;
  }
  
  .form-container {
    background-color: white;
    padding: 20px;
    border-radius: 5px;
    box-shadow: 0 2px 5px rgba(0, 0, 0, 0.1);
  }
  
  .form-group {
    margin-bottom: 15px;
  }
  
  label {
    display: block;
    margin-bottom: 5px;
    color: #2c3e50;
  }
  
  input {
    width: 100%;
    padding: 8px 12px;
    border: 1px solid #ddd;
    border-radius: 4px;
    font-size: 14px;
  }
  
  .form-actions {
    margin-top: 25px;
    display: flex;
    gap: 10px;
  }
  
  .actions {
    white-space: nowrap;
  }
</style>

1. Save all files

Step 4: Deploy the Web App

Now it’s time to deploy our web app so users can access it:

1. In the Apps Script editor, click Deploy → New deployment
2. For deployment type, select Web app
3. Fill in these details:
   • Description: “Inventory Management System”
   • Execute as: “Me” (your account)
   • Who has access: Choose one of:
     • “Only myself” for personal use
     • “Anyone within [your organization]” for team use
     • “Anyone” for public access
4. Click Deploy
5. Copy the Web App URL that appears—this is your live application!

Step 5: Test Your Web App

Open the URL in your browser, and you should see your new inventory management system in action:

1. Click “Add New Item” to create inventory items
2. View all items in the main list
3. Edit items using the “Edit” button
4. Delete items using the “Delete” button

All changes are automatically saved to your Google Sheet in real-time.

How It Works

Let’s break down the key components of our web app:

Google Sheet as Database

We’re using Google Sheets as our database. Each row represents an inventory item, and we use the sheet APIs to:

• Read data (getAllItems and getItemById functions)
• Write data (addItem function)
• Update data (updateItem function)
• Delete data (deleteItem function)

Server-Side Script

The Apps Script code handles all the server-side logic:

• Routing (doGet function)
• Data processing
• CRUD operations on the spreadsheet

HTML Templates

The HTML templates determine what users see:

• MainInventory.html: The list view of all items
• AddItem.html: Form for adding new items
• EditItem.html: Form for updating existing items

Client-Side JavaScript

The JavaScript embedded in our HTML templates handles:

• Form submission
• Data validation
• Communication with the server-side script
• DOM manipulation

Customization Ideas

Now that you have the basic structure, you can customize this app for various purposes:

1. Customer Database: Track customer information and interactions
2. Project Management: Track projects, tasks, and deadlines
3. Event Planning: Manage attendees, vendors, and schedules
4. Content Calendar: Plan and track content publishing
5. Asset Tracking: Monitor equipment, devices, and resources

Security Considerations

Keep these security points in mind:

1. Access Control: Set appropriate access permissions when deploying
2. Data Validation: Add more robust validation on both client and server sides
3. Sensitive Data: Don’t store highly sensitive information in this setup
4. Rate Limiting: For public apps, consider adding rate limiting to prevent abuse

Performance Tips

For larger datasets:

1. Pagination: Implement pagination for large inventory lists
2. Caching: Cache data where appropriate to reduce Sheet API calls
3. Batch Operations: Use batch operations when updating multiple items

Conclusion

Congratulations! You’ve built a fully functional web application with Google Apps Script and Google Sheets. This demonstrates the incredible power of Google’s platform for creating no-code/low-code solutions without worrying about hosting, servers, or databases.

The best part is that you can continue to enhance this application with additional features as your needs grow. Google Apps Script connects to many other Google services, opening up possibilities for integrations with Gmail, Calendar, Drive, and more.

Have you built web apps with Google Apps Script before? Share your experiences in the comments below!

---

Enjoyed this tutorial? Check out our guides on personalized email newsletters and time-saving scripts for more Google Apps Script automation ideas.