Search Engine Using Google Sheets

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!!! 😎

Easy Gradient Buttons for Bootstrap v4

I just started a job that is using Bootstrap v4. I’m happy with the functionality but I miss having a not so plain looking button. Here is the CSS code I am using to add easy gradients on my Bootstrap v4 buttons:

.btn {
    background-image: linear-gradient(to bottom, rgba(255,255,255,0.5), rgba(255,255,255,0.2) 49%, rgba(0,0,0,0.15) 51%, rgba(0,0,0,0.05));
    background-repeat: repeat-x;
}

Here are some example buttons to show you what this change looks like in the wild:

See the Pen Bootstrap v4 Gradient Buttons Examples by Chris West (@cwestify) on CodePen.

As you can tell by the 4 lines of CSS I added, the fix is simple and as seen in the example, the buttons look nice. Let me know what you think and as always, happy coding :cool:.

Responsive YouTube Embed

YouTube URL

Replace the URL below with whatever YouTube video’s URL you want to make responsive embed code for.

Embed Code

You can select and copy the code below to embed this responsive video in your site.

Example Embed

If you resize the window you will see the video change in size according to the width of the page.

How It Works

The reason this all works is due to the fixed aspect ratio of the YouTube video player and how padding percentages work in CSS.

YouTube Video Aspect Ratio

YouTube always shows videos in a 16:9 aspect ratio.

CSS – Padding Percentages

According to MDN, percentages used for padding “refer to the width of the containing block.”

Combining What We Know

Keeping all of this in mind we first create a container element which will maintain the 16:9 aspect ratio.
{{code.replace(/iframe.+iframe/, '!-- EMBED IFRAME CODE --')}}
  • We use position: relative knowing that the <iframe> is later going to need to have position: absolute.
  • Our padding-bottom: 56.25% comes from our aspect ratio because 9 ÷ 16 = 56.25%.
Now let’s add the embed code (<iframe>):

We use position: absolute; top: 0; left: 0; width: 100%; height: 100%; to make sure that the <iframe> fills the entire container (including the padding).

Concluding Thoughts

You can use this same train of thought to make embeddable videos from any site responsive if you want. The one thing you need to know before hand is the aspect ratio. As long as you know the aspect ratio you will be able to calculate the padding-bottom of the containing element. Happy coding! 😎