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:

  1. Create a Google spreadsheet that has the following column names and the corresponding values: URL, Title, Description, and Keywords.
  2. Go to File » Publish to the web….
  3. 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.
  4. Pull the Google spreadsheet ID from the URL (eg. 1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY from https://docs.google.com/spreadsheets/d/1927NAILJ9_CSkhSYmdxB-kUBN_7Yx6ZN5GXqyK6tKbY/edit#gid=0)
  5. 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 your GSHEET_JSON_URL value.
  6. Create a new web page for your search and put the HTML found in this GitHub gist in it.
  7. Replace the string assigned to GSHEET_JSON_URL (on line 12) with your GSHEET_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!!! 😎

Categories: BlogJavaScript

1 Comment

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

Leave a Reply

Your email address will not be published. Required fields are marked *