To enhance the setup by adding a user interface in Google Sheets where users can
input their search criteria (either employee number or employee name) and display
results directly in a designated area of the sheet, follow these detailed steps:
## Step-by-Step Guide
### Step 1: Create Your Google Form and Link to Google Sheets
1. **Create your Google Form** with fields for Employee Number and Employee Name.
2. **Link the Form to a Google Sheet** to store responses.
### Step 2: Prepare Your Google Sheets
1. **Format the Spreadsheet**: Ensure it has columns for Employee Number, Employee
Name, and other relevant details.
2. **Designate an Area for Input and Output**:
- Choose a section of your sheet (e.g., A1 for Employee Number input, B1 for
Employee Name input, and C1 for displaying results).
### Step 3: Write the Google Apps Script
1. **Open Script Editor**:
- In your linked Google Sheets, click on `Extensions > Apps Script`.
2. **Copy and Paste the Following Code**:
```javascript
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Employee Search')
.addItem('Search', 'searchEmployee')
.addToUi();
}
function searchEmployee() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var empNumber = sheet.getRange('A1').getValue(); // Employee Number input
var empName = sheet.getRange('B1').getValue(); // Employee Name input
var data = sheet.getDataRange().getValues(); // Get all data from the sheet
var result = [];
for (var i = 1; i < data.length; i++) { // Start from 1 to skip header row
if (data[i][0] == empNumber || data[i][1].toLowerCase() ==
empName.toLowerCase()) {
result.push(data[i]); // Add matching row to results
}
}
// Clear previous results
sheet.getRange('D1:D').clearContent(); // Clear previous output area
// Display results
if (result.length > 0) {
for (var j = 0; j < result.length; j++) {
sheet.getRange(j + 1, 4, 1,
result[j].length).setValues([result[j]]); // Output to column D
}
} else {
sheet.getRange('D1').setValue("No matching records found.");
}
}
```
3. **Save and Name Your Project**: Click on the disk icon to save your script.
### Step 4: Set Up Trigger for Menu Creation
- The `onOpen` function automatically creates a custom menu called "Employee
Search" when the spreadsheet is opened.
### Step 5: Test Your Setup
1. **Reload Your Spreadsheet**: Close and reopen your Google Sheet to see the new
menu.
2. **Input Search Criteria**:
- Enter an employee number in cell A1 or an employee name in cell B1.
3. **Use the Custom Menu**:
- Click on `Employee Search` in the menu and select `Search`.
4. **Check Results**: The results will be displayed starting from cell D1.
### Additional Enhancements
- You can customize the output format or add more functionality (like editing or
deleting records) by expanding the Apps Script code.
- Consider adding validation to ensure that users enter valid data before
searching.
By following these steps, you will have created a user-friendly interface within
Google Sheets that allows users to search for employee details based on either
employee number or name, with results displayed directly in the sheet.
Citations:
[1] https://www.youtube.com/watch?v=Q-Io0Y59nYA
[2] https://www.youtube.com/watch?v=_Iq2-_WgFdY
[3] https://www.youtube.com/watch?v=TW5ZFyo0FwI
[4] https://www.youtube.com/watch?v=rlxQgOTNR5U
[5] https://www.youtube.com/watch?v=6S6ib0dv0qk
[6]
https://www.reddit.com/r/sheets/comments/snhr60/what_is_the_best_way_to_make_a_cust
om_gui_for/
[7] https://www.dronahq.com/google-sheets-app/
[8] https://developers.google.com/workspace/add-ons/guides/editor-style
