Google Sheets Form for Data Entry - Apps Script

Introduction

This tutorial will guide you through creating a data entry form in Google Sheets using Apps Script. By the end of this guide, you'll be able to build a functional form for entering, editing, and deleting records efficiently, enhancing your data management capabilities.

Step 1: Build the Form User Interface

1. Open Google Sheets and navigate to Extensions > Apps Script.
2. In the Apps Script editor, create a new HTML file for the form.
3. Design the form UI by adding input fields. For example:
   • Text input for names
   • Number input for age
   • Dropdown for categories
4. Use the following sample code to set up the basic structure of your form:

   <form>
       <label for="name">Name:</label>
       <input type="text" id="name" name="name">
       <label for="age">Age:</label>
       <input type="number" id="age" name="age">
       <label for="category">Category:</label>
       <select id="category" name="category">
           <option value="Option1">Option 1</option>
           <option value="Option2">Option 2</option>
       </select>
       <button type="submit">Submit</button>
   </form>
   

5. Style your form using CSS for better appearance if desired.

Step 2: Create Records

1. In the Apps Script editor, define a function to handle form submissions.
2. Use the following sample code to capture input values and append them to your Google Sheet:

   function submitForm(formData) {
       const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
       sheet.appendRow([formData.name, formData.age, formData.category]);
   }
   

3. Link the form submission to this function using the onSubmit event.

Step 3: Clear Fields After Submission

1. After appending the data to the sheet, clear the input fields using JavaScript.
2. Add the following code to your form submission function:

   document.getElementById("name").value = '';
   document.getElementById("age").value = '';
   document.getElementById("category").value = '';
   

Step 4: Build Form Search Functionality

1. Create a search field in your form to allow users to look up existing records.
2. Write a function that retrieves and displays records matching the search criteria.
3. Use the following code to filter and display results:

   function searchRecords(searchTerm) {
       const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
       const data = sheet.getDataRange().getValues();
       const results = data.filter(row => row[0].includes(searchTerm)); // Adjust index based on search column
       displayResults(results);
   }
   

Step 5: Edit Existing Data

1. Implement a method to select a record from the search results and fill the form fields with its data.
2. Create an edit function that updates the selected record in the Google Sheet when the user submits the form again.
3. Use similar code as in Step 2 for updating, but find the row to edit based on the unique identifier.

Step 6: Delete Records

1. Add a delete button next to each record in the search results.
2. Create a function that removes the selected record from the Google Sheet:

   function deleteRecord(rowIndex) {
       const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
       sheet.deleteRow(rowIndex);
   }
   

3. Ensure the button is linked to this function for seamless operation.

Conclusion

You have now created a fully functional data entry form in Google Sheets using Apps Script. This includes features for creating, searching, editing, and deleting records. Feel free to expand on this foundation by adding more functionalities or styling the form to fit your needs. Experiment with different input types and validations to enhance user experience further.