Skip to content

Instantly share code, notes, and snippets.

@bpwebs

bpwebs/#Create a Google Sheets Data Entry Form with AI

Created August 20, 2025 03:04
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save bpwebs/3796480bd8486240157c683a1de2f9cb to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save bpwebs/3796480bd8486240157c683a1de2f9cb to your computer and use it in GitHub Desktop.
Download ZIP
Create a Google Sheets Data Entry Form with AI
Raw
#Create a Google Sheets Data Entry Form with AI
How to Create a Google Sheets Data Entry Form with AI
bpwebs.com
Raw
Code.gs
/**
* @OnlyCurrentDoc
* The above comment directs Apps Script to limit the scope of file access for this script
* to only the current document. This is a good security practice.
*/
// This function runs when a user opens the web app URL.
// It serves the HTML file named "Index.html" as the user interface.
function doGet() {
return HtmlService.createHtmlOutputFromFile('Index')
.setTitle('Product Entry Form');
}
/**
* Saves the product data from the form into the Google Sheet.
* This function is called from the client-side JavaScript in the HTML file.
* @param {Object} formData - The data object submitted from the form.
* @returns {String} A success message to be displayed to the user.
*/
function saveData(formData) {
try {
// Get the currently active spreadsheet and the sheet named "ProductData".
// Make sure your sheet is named "ProductData" or change the name here.
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName('ProductData');
// If the sheet doesn't exist, throw an error.
if (!sheet) {
throw new Error("Sheet 'ProductData' not found. Please create it.");
}
const timestamp = new Date(); // Get the current date and time.
// Append a new row to the sheet with the data in the correct order.
sheet.appendRow([
timestamp,
formData.productName,
formData.category,
formData.quantity,
formData.unitPrice,
formData.remarks
]);
// Return a success message to the client-side script.
return 'Product details saved successfully!';
} catch (error) {
// Log the error for debugging and return an error message.
console.error('Error in saveData: ' + error.toString());
return 'Error: ' + error.message;
}
}
Raw
Index.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<!-- Bootstrap CSS for styling -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
<style>
/* Custom styles for better aesthetics */
body {
background-color: #f8f9fa;
padding: 2rem;
}
.container {
max-width: 700px;
background-color: #ffffff;
padding: 2rem;
border-radius: 15px;
box-shadow: 0 4px 12px rgba(0,0,0,0.1);
}
h2 {
color: #343a40;
margin-bottom: 1.5rem;
text-align: center;
}
.btn-primary {
background-color: #007bff;
border-color: #007bff;
}
#statusMessage {
margin-top: 1.5rem;
display: none; /* Hidden by default */
text-align: center;
padding: 1rem;
border-radius: 8px;
}
.spinner-border {
display: none; /* Hidden by default */
}
</style>
</head>
<body>
<div class="container">
<h2>Product Details Entry Form</h2>
<form id="productForm">
<!-- Product Name Input -->
<div class="form-group">
<label for="productName">Product Name</label>
<input type="text" class="form-control" id="productName" required>
</div>
<!-- Product Category Dropdown -->
<div class="form-group">
<label for="category">Product Category</label>
<select class="form-control" id="category" required>
<option value="" disabled selected>Select a category...</option>
<option>Electronic</option>
<option>Kitchen Appliances</option>
<option>Bedroom Furniture</option>
<option>Living Room Furniture</option>
</select>
</div>
<!-- Quantity Input -->
<div class="form-group">
<label for="quantity">Quantity</label>
<input type="number" class="form-control" id="quantity" required>
</div>
<!-- Unit Price Input -->
<div class="form-group">
<label for="unitPrice">Unit Price ($)</label>
<input type="number" class="form-control" id="unitPrice" step="0.01" min="0" required>
</div>
<!-- Remarks Textarea -->
<div class="form-group">
<label for="remarks">Remarks</label>
<textarea class="form-control" id="remarks" rows="3"></textarea>
</div>
<!-- Submit Button and Spinner -->
<button type="submit" class="btn btn-primary btn-block">
<span class="spinner-border spinner-border-sm" role="status" aria-hidden="true"></span>
Submit
</button>
</form>
<!-- Status Message Area -->
<div id="statusMessage"></div>
</div>
<script>
// Add an event listener to the form's submit event
document.getElementById('productForm').addEventListener('submit', function(e) {
e.preventDefault(); // Prevent the default form submission
// Show spinner and disable button
const submitButton = this.querySelector('button[type="submit"]');
const spinner = submitButton.querySelector('.spinner-border');
submitButton.disabled = true;
spinner.style.display = 'inline-block';
// Create an object with all the form data
const formData = {
productName: document.getElementById('productName').value,
category: document.getElementById('category').value,
quantity: document.getElementById('quantity').value,
unitPrice: document.getElementById('unitPrice').value,
remarks: document.getElementById('remarks').value,
};
// Call the server-side Apps Script function 'saveData'
google.script.run
.withSuccessHandler(onSuccess)
.withFailureHandler(onFailure)
.saveData(formData);
});
// Function to run on successful submission
function onSuccess(message) {
// Show a success message
const statusDiv = document.getElementById('statusMessage');
statusDiv.textContent = message;
statusDiv.className = 'alert alert-success';
statusDiv.style.display = 'block';
// Clear the form fields
document.getElementById('productForm').reset();
// Hide the message after 5 seconds
setTimeout(() => {
statusDiv.style.display = 'none';
}, 5000);
resetButton();
}
// Function to run on failed submission
function onFailure(error) {
// Show an error message
const statusDiv = document.getElementById('statusMessage');
statusDiv.textContent = 'Error saving data: ' + error.message;
statusDiv.className = 'alert alert-danger';
statusDiv.style.display = 'block';
resetButton();
}
// Function to reset the submit button state
function resetButton() {
const submitButton = document.querySelector('button[type="submit"]');
const spinner = submitButton.querySelector('.spinner-border');
submitButton.disabled = false;
spinner.style.display = 'none';
}
</script>
</body>
</html>
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment