How to create a search engine using Apps script and Google sheets
Introduction
The provided code offers a simple web interface that allows users to search through a Google Sheet using text queries. The functionality is based on Google Apps Script and HTML.
Functions Overview
The code consists of two main parts: server-side (Google Apps Script) and client-side (HTML & JavaScript).
Google Apps Script (doGet and searchInSheet)
doGet: This function returns an HTML output from an 'index' file. It's used to render the web interface.searchInSheet: This function takes a search query, looks for matches in a Google Sheet, and returns the relevant results in an array format.
HTML & JavaScript
- HTML Structure: The HTML provides a basic user interface with input fields for search queries, a button to trigger searches, and a table to display search results.
- JavaScript Functions:
search(): Retrieves the search query, calls thesearchInSheetfunction in the server-side script, and handles the results display.displayResults(results): Receives search results from the server-side script and displays them on the web page.- Functions for pagination (
showCurrentPage,previousPage,nextPage) manage displaying results in pages. openPopupandclosePopupmanage the display of a popup modal for detailed information on a selected search result.
How it Works
- The user enters a search query in the provided input field.
- Upon clicking the "Search" button, the
search()function is triggered. - The JavaScript communicates with the server-side Google Apps Script using
google.script.runto execute thesearchInSheetfunction with the user's query. - The results returned from the server are then displayed in a table format on the webpage, limited by pagination settings.
- Clicking on a row in the table triggers a popup modal that shows detailed information about the selected result.
Full code :
function doGet() { return HtmlService.createHtmlOutputFromFile('index'); } function searchInSheet(query) { var sheet = SpreadsheetApp.openById('sheet id').getSheetByName('Sheet1'); var data = sheet.getDataRange().getValues(); var results = []; for (var i = 0; i < data.length; i++) { for (var j = 0; j < data[i].length; j++) { if (data[i][j].toString().toLowerCase().indexOf(query.toLowerCase()) !== -1) { results.push({id: data[i][0], name: data[i][2],fname: data[i][6],vill: data[i][8]}); } } } return results; }
index.html
<!DOCTYPE html> <html> <head> <title>Google Sheet Text Search</title> <link rel="stylesheet" href="https://www.w3schools.com/w3css/4/w3.css"> </head> <body> <h1 >Search in Google Sheet</h1> <input type="text" id="searchInput"class='w3-input w3-small w3-col m2 s8' placeholder="Enter text to search"> <button onclick="search()"class='w3-btn w3-blue w3-small'>Search</button> <h2>Search Results:</h2> <table id="resultsTable" border="1"class="w3-table-all"> <thead> <tr> <th>Id</th> <th>Name</th> <th>Father's name</th> <th>Vill</th> </tr> </thead> <tbody id="resultsBody"></tbody> </table> <div id="pagination"class='w3-right w3-small w3-padding'> <button onclick="previousPage()"class='w3-btn w3-black'>Previous</button> <span id="pageInfo"></span> <button onclick="nextPage()"class='w3-btn w3-black'>Next</button> </div> <div id="popup" class="w3-modal"> <div class="w3-modal-content"> <div class="w3-container"> <span onclick="closePopup()" class="w3-button w3-display-topright">×</span> <h3>Result Details</h3> <p id="popupContent"></p> </div> </div> </div> <script> var currentPage = 0; var pageSize = 10; // Number of rows per page var searchResults = []; function search() { var query = document.getElementById('searchInput').value.trim(); google.script.run.withSuccessHandler(displayResults).searchInSheet(query); } function displayResults(results) { searchResults = results; currentPage = 0; showCurrentPage(); } function showCurrentPage() { var start = currentPage * pageSize; var end = start + pageSize; var currentResults = searchResults.slice(start, end); var tableBody = document.getElementById('resultsBody'); tableBody.innerHTML = ''; currentResults.forEach(function(result) { var row = tableBody.insertRow(-1); var cell1 = row.insertCell(0); var cell2 = row.insertCell(1); var cell3 = row.insertCell(2); var cell4 = row.insertCell(3); cell1.textContent = result.id; cell2.textContent = result.name; cell3.textContent = result.fname; cell4.textContent = result.vill; row.addEventListener('click', function() { openPopup(result.value); }); }); document.getElementById('pageInfo').textContent = `Page ${currentPage + 1}/${Math.ceil(searchResults.length / pageSize)}`; } function openPopup(content) { var popupContent = document.getElementById('popupContent'); popupContent.textContent = content; var popup = document.getElementById('popup'); popup.style.display = 'block'; } function closePopup() { var popup = document.getElementById('popup'); popup.style.display = 'none'; } function previousPage() { if (currentPage > 0) { currentPage--; showCurrentPage(); } } function nextPage() { if (currentPage < Math.ceil(searchResults.length / pageSize) - 1) { currentPage++; showCurrentPage(); } } </script> </body> </html>
Conclusion:
This code integrates Google Sheets data into a simple web interface, allowing users to search and view information conveniently.