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

PHP – Simple GitHub Login (OAuth)

If your codebase is in PHP and you would like user’s to be able to login to your site using GitHub (single sign-on) you can use the following as a good starting point:

Next you will need to setup an OAuth application within GitHub by doing the following:

  1. Login to GitHub.com.
  2. Go to your settings by clicking on your avatar image in the top-right corner of any GitHub page and then clicking on Settings in the drop-down menu.
  3. Scroll down to the Developer settings menu on the left and click OAuth Applications.
  4. Click the Register a new application button.
  5. Enter all of the necessary information to register your new OAuth application and then click Register application when you’re done.
  6. Take the Client ID and the Client Secret and place them in the constants defined in your PHP code from the beginning of this post.

That should be it. Now when you run this code you should be prompted to authorize your application to read your public information and private email addresses. You can remove the scope if you don’t need to be able to read any private information such as email addresses or you can change the scope after reviewing this page. If you want more information about OAuth apps for GitHub you can check out this page.

Happy coding! 😎

JavaScript Snippet – Get Video Frame As An Image

Lately I have dedicated a little more time to developing an electron app which saves a frame of every video it encounters as PNG images:

JW Videos with images captured via a video element and a canvas element.
JW Videos with images captured via a video element and a canvas element.

Believe it or not the code to turn a frame of a video into an image element is not terribly complicated. Below is a simple function that can be used to get the an image element corresponding to a specific frame in a video:

This getVideoImage() function takes three arguments:

  1. path {string}:
    The path to the video. In the case that you are using this code in a web page this must be a video within the same domain.
  2. secs {number | function(duration): number}:
    If this is a non-negative number this will indicate the time of the frame to capture in seconds. If this is a negative number this will indicate the time of the frame from the end of the video to capture in seconds. If this is a function it will be passed the duration as a number and the return value should be a number (positive or negative) which indicates the time of the frame that should be captured.
  3. callback {function(img, currentTime, event)}:
    The function which is called either after loading the frame’s image successfully or after getting an error. The first argument passed will be the Image object that is created (if no error occurred). The second argument passed will be the actual time in seconds used to pull the frame’s image (if no error occurred).
    The third argument will either be a seeked event or an error event.

This function can be used as is or can be modified. It is important to note that unless the video is in the exact same domain as the web page this code will NOT work due to the canvas being tainted. You can paste the following code into the code editor on this W3Schools sandbox page to see an example of getting multiple frames:

Let me know if you have any questions or suggestions and as usual, enjoy the free code! 😎