#How to Create HTML Data Entry Forms in Google Sheets

#How to Create HTML Data Entry Forms in Google Sheets

How to Create HTML Data Entry Forms in Google Sheets

Code.gs

/**
 * 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();
}

Css.html

<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet"
  integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">

Form.html

<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>

Inde.html

<!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>

JavaScript.html

<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>