Have you ever wanted to make a simple search engine? There are plenty of ways to do it. One way that I have been using in my simple search engine for a while is leveraging Google Sheets.
Creating Your Own Google Sheets Search Engine
Here are a few simple steps that I used to make my simple search engine without a true database:
- Create a Google spreadsheet that has the following column names and the corresponding values:
URL
,Title
,Description
, andKeywords
. - Go to File » Publish to the web….
- At the bottom of the Public to the web dialog click and expand the Published content & settings option and then click the Start publishing button and after confirming that you want to publish the document you can close this dialog.
- Pull the Google spreadsheet ID from the URL (eg.
1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY
fromhttps://docs.google.com/spreadsheets/d/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/edit#gid=0
) - Navigate to
https://spreadsheets.google.com/feeds/list/{{SPREADSHEET-ID}}/1/public/values?alt=json
, replacing{{SPREADSHEET-ID}}
with the spreadsheet ID from the previous step. This URL will be yourGSHEET_JSON_URL
value. - Create a new web page for your search and put the HTML found in this GitHub gist in it.
- Replace the string assigned to
GSHEET_JSON_URL
(on line 12) with yourGSHEET_JSON_URL
value.
Resulting Search Engine
That’s it! Your search should now work just like this one:
See the Pen Simple Search Engine via Google Sheets by Chris West (@cwestify) on CodePen.
Cool Features
Since all the code is written in JavaScript you can easily change the functionality to fit your needs but I will list a few features that exist out of the box with this code:
-
Rank According To Fields
Here is a table indicating how much each matching search term counts for in each field:Field Points Title 8 URL 4 Description 2 Keywords 1 -
Match From Start of Words
Normal words as search terms only match fields if they match from the beginning of words in those fields. -
Quoted Strings
Adding double quotes around strings will make the search engine look for that entire substring in a case insensitive manner. -
JS Style Regular Expression Search Terms
You can use JS style regular expressions in the search (eg./\b[a-z]*[aeiou]{2,}[a-z]*\b/i
) -
Must Have Search Terms
You can indicate that a search term must be found by prefixing it with the plus sign (+
). -
Must Not Have Search Terms
You can indicate that a search term must NOT be found by prefixing it with the minus sign (-
). -
Pagination
Only 10 results will show on the page at a time. Links to additional pages of results are shown at the bottom of the page. -
Highlighting In Results
The search terms are highlighted in the title and description fields in the results.
Libraries & Frameworks
I’m using Bootstrap v4, jQuery, and Vue as can be seen in the head of the code. In addition, I inlined a minimal version of YourJS so that I could use (matchAll()
, clamp()
and quoteRegExp()
).
Final Notes
All-in-all I am very happy with how robust this simple solution turned out to be and I think in the future if I need to make a quick-and-dirty search engine I will use this solution. One thing to note is that because of how my Google spreadsheet is set up, the title and description is interpreted as raw HTML, but you can easily change the Vue annotated HTML to print those fields as text instead of raw HTML. Feel free to use my code and make it your own. Happy coding!!! š
6 Comments
Stephen Davis · October 8, 2018 at 3:16 PM
This is amazing! I used this for one of the dashboards I’m building for one of my clients and it works like a charm!
I was wondering if there was room to change the formatting of the outputted results?
– It’d be cool to be able to show the keywords in the output
– It’d also be cool if there could be output results with if/then statements
Michael · February 13, 2019 at 5:51 PM
This is very cool! I’m a super novice in html and was trying to see if I could futz with the spreadsheet headers (change title, add column).
I tried replacing “url” in the code (where appropriate) to another column title but didn’t get a good result.
Is there an easy way to add a column and change the title of a column?
Alex · March 5, 2019 at 5:46 PM
Hello Chris, thanks for this great post ! I did it and it perfectly works. I would like to do it but with a CSV (or JSON) file local hosted.
Could you tell me if that’s possible ? If yes, can you refer me how can I do the path and require it please ?
Thank you very much for your help
Alex
Luke · June 4, 2019 at 11:37 AM
Where could you filter the JSON results? Such as entry.filter(entry => entry.gsx$somedataname.$t.length > 7 );?
Luke · June 4, 2019 at 11:39 AM
Where could you filter the JSON results? Noted this on your github, but not sure if you are still reviewing that.
Land Surveyor · June 17, 2020 at 7:39 PM
This is terrific! Thank you for sharing! Im working on a new member raffle for my community and this is exactly what i needed for members to find their member number.. thanks!