Created
April 14, 2024 12:41
-
-
Save bpwebs/dd25435b57f46423ce36a7a689aa874c to your computer and use it in GitHub Desktop.
#How to Create HTML Data Entry Forms in Google Sheets
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
How to Create HTML Data Entry Forms in Google Sheets |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** | |
* Constants | |
*/ | |
const DATA_SHEET = "Sheet1"; | |
/** | |
* Creates a custom menu titled "My Menu" in the spreadsheet's UI. The menu includes | |
* an item "Open Form" that, when clicked, triggers the openForm function. | |
*/ | |
function onOpen() { | |
var ui = SpreadsheetApp.getUi(); | |
ui.createMenu("My Menu") | |
.addItem("Open Form", "openForm") | |
.addToUi(); | |
} | |
/** | |
* Opens the form as a modal dialog titled "Contact Details". | |
*/ | |
function openForm() { | |
var form = HtmlService.createTemplateFromFile('Index').evaluate(); | |
form.setWidth(700).setHeight(400); | |
SpreadsheetApp.getUi().showModalDialog(form, "Product Details"); | |
} | |
/** | |
* Appends form data (first_name, last_name, etc.) as a new row in the active spreadsheet's data sheet. | |
* | |
* @param {Object} formObject - The submitted form data object. | |
*/ | |
function processForm(formObject) { | |
const dataSheet = SpreadsheetApp.getActive().getSheetByName(DATA_SHEET); | |
try { | |
dataSheet.appendRow([ | |
new Date().toLocaleString(), | |
formObject.productName, | |
formObject.productDescription, | |
formObject.productCategory, | |
formObject.productCondition, | |
formObject.price, | |
formObject.quantity | |
//Add your new field names here | |
]); | |
} catch (error) { | |
Logger.log('Error appending data: '+ error.message); | |
} | |
} | |
/** | |
* Includes the content of an external HTML file. | |
* | |
* @param {string} fileName The name of the HTML file to include. | |
* @returns {string} The HTML content of the file. | |
*/ | |
function include(filename) { | |
return HtmlService.createHtmlOutputFromFile(filename).getContent(); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" | |
integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous"> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<form id="myForm" onsubmit="handleFormSubmit(this)"> | |
<!-- Text Field: Product Name Field --> | |
<div class="row mb-3"> | |
<label for="productName" class="col-sm-3 col-form-label">Product Name:</label> | |
<div class="col-sm-9"> | |
<input type="text" class="form-control" id="productName" name="productName" required> | |
</div> | |
</div> | |
<!-- Text Field: Product Description Field --> | |
<div class="row mb-3"> | |
<label for="productDescription" class="col-sm-3 col-form-label">Product Description:</label> | |
<div class="col-sm-9"> | |
<input type="text" class="form-control" id="productDescription" name="productDescription" required> | |
</div> | |
</div> | |
<!-- Dropdown Field: Category Dropdown--> | |
<div class="row mb-3"> | |
<label for="productCategory" class="col-sm-3 col-form-label">Product Category:</label> | |
<div class="col-sm-9"> | |
<select id="productCategory" name="productCategory" class="form-select form-select" required> | |
<option value="">--Select a category--</option> | |
<option value="Electronics">Electronics</option> | |
<option value="Clothing">Clothing</option> | |
<option value="Home and Garden">Home and Garden</option> | |
<option value="Sports and Outdoors">Sports and Outdoors</option> | |
</select> | |
</div> | |
</div> | |
<!--Radio Button: Product Condition --> | |
<div class="row mb-3"> | |
<label for="productCondition" class="col-sm-3 col-form-label">Product Condition:</label> | |
<div class="col-sm-9 mt-2"> | |
<div class="form-check form-check-inline"> | |
<input type="radio" id="productCondition1" name="productCondition" class="form-check-input" value="new" required> | |
<label for="new" class="form-check-label">New</label> | |
</div> | |
<div class="form-check form-check-inline"> | |
<input type="radio" id="productCondition2" name="productCondition" class="form-check-input" value="used" required> | |
<label for="used" class="form-check-label">Used</label> | |
</div> | |
</div> | |
</div> | |
<!-- Number Field: Price --> | |
<div class="row mb-3"> | |
<label for="price" class="col-sm-3 col-form-label">Price:</label> | |
<div class="col-sm-9"> | |
<input type="number" class="form-control" id="price" name="price" required> | |
</div> | |
</div> | |
<!-- Number Field: Quantity --> | |
<div class="row mb-3"> | |
<label for="quantity" class="col-sm-3 col-form-label">Quantity:</label> | |
<div class="col-sm-9"> | |
<input type="number" class="form-control" id="quantity" name="quantity" required> | |
</div> | |
</div> | |
<!-- Submit Button --> | |
<div class="row mb-3"> | |
<div class="col-sm-3"></div> | |
<div class="col-sm-9 d-grid"> | |
<button type="submit" class="btn btn-primary">Submit</button> | |
</div> | |
</div> | |
</form> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<!doctype html> | |
<html lang="en"> | |
<head> | |
<!-- Required meta tags --> | |
<meta charset="utf-8"> | |
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> | |
<?!= include('Css'); ?> | |
<?!= include('JavaScript'); ?> | |
<title>Contact Details</title> | |
</head> | |
<body class="bg-secondary text-light mt-3 mb-3"> | |
<div class="container-fluid"> | |
<?!= include('Form'); ?> | |
</div> | |
</body> | |
</html> |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
<script> | |
// Prevent forms from submitting. | |
function preventFormSubmit() { | |
var forms = document.querySelectorAll('form'); | |
for (var i = 0; i < forms.length; i++) { | |
forms[i].addEventListener('submit', function(event) { | |
event.preventDefault(); | |
}); | |
} | |
} | |
window.addEventListener('load', preventFormSubmit); | |
function handleFormSubmit(formObject) { | |
google.script.run.processForm(formObject); | |
document.getElementById("myForm").reset(); | |
} | |
</script> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment