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

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!

Leave a Reply

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