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

My Interactive Resume – Part 1

Here is my first quick pass at an online resume:
http://CWestify.com/resume/

There are plenty of times when we want to add everything to our resume to let potential employers know our worth but it can be difficult to know how to really summarize everything.

Project Search

There are times when we want these potential employers to be able to browse our projects with ease, perhaps searching through them by keywords. For that reason I added a search which populates the typeahead suggestion box with keywords for my projects.
Resume - Project Search Section

Even though I ended up coding everything from scratch just because I wanted to challenge myself, it’s easily conceivable that one could do this using bootstrap and a typeahead plugin. My project information is actually stored away in JavaScript:

var projects = [
  {
    title: 'CWestify.com',
    description: 'A site full of custom code written in PHP and JS primarily containing small online apps such as the Emoji Search.',
    url: 'http://cwestify.com/',
    dates: [new Date('2015/01/01')],
    keywords: ['PHP', 'JavaScript', 'YourJS', 'HTML', 'CSS', 'HTML5', 'CSS3', 'MySQL'],
    languages: ['PHP', 'JavaScript', 'HTML', 'CSS', 'MySQL']
  },
  {
    title: 'CWestBlog.com',
    priority: true,
    description: 'My blog which showcases a lot of the JavaScript solutions I have come up with over the years.  This site gets thousands of visits every week due to the specificity of the solutions I have posted.  Besides JavaScript I also wrote articles about Python, CSS3, Java, VBScript, mathematics and much more.',
    url: 'http://cwestblog.com/',
    dates: [new Date('2011/03/31')],
    keywords: ['JavaScript', 'PHP', 'YourJS', 'HTML', 'CSS', 'HTML5', 'CSS3', 'Excel', 'node.js', 'Java', 'Python', 'Math', 'Blog', 'Wordpress', 'VBScript', 'JSON', 'VBA', 'HTA', 'MySQL'],
    languages: ['PHP', 'JavaScript', 'HTML', 'CSS', 'MySQL']
  },
  ...
];

As you can see I store the keywords for each project in an array. The keywords for all of the projects are then put into an array and their frequency is kept with each keyword.

Hiding Contact Information from Bots

Of course, if you want to put your resume online it is important that you take steps to hide your contact info from bots/web-scrapers. For that reason I decided to use reCAPTCHA. In order to actually see my contact information the user will have to click through the step(s) of reCAPTCHA.
Resume - Contact Info with CAPTCHA

Conclusion

I personally am mostly happy with how my interactive resume turned out, but I do have ideas for how to improve it:

  1. Make it mobile friendly.
  2. Add buttons that will allow me to pick and choose what I want printed if I decide to print it out.
  3. Change the design to make it a little more poppy but at the same time still professional.

Let me know if you have any other ideas of how to make an online version of a resume better and more interactive. 😎

JavaScript Snippet – isValidVarName()

Now Available in YourJS

Recently I was working on a function which needed to determine whether or not a string could be used as a variable name. Variable name validation can get tricky so instead of using a crazy regular expression which includes all keywords (which may change over time) and instead of testing for strange unicode characters which are rarely used for variable names, I decided to leverage the JavaScript Function constructor:

The above function takes the string in question and returns true if the string can be used a variable name. If the string can not be used as a variable name false is returned.

Some may wonder why I’m doing the following:

varName.replace(/[\s\xA0,\/]|^$/g, '.')

The reason I included the above replacement is to avoid false-positives in the case of an empty string, extra spacing, commas, and forward slashes.

Security

Others have attempted to make the same function using the evil eval() function which allows for JS injection. Even though the Function constructor can also be used for evil, when supplying arguments it does prevent you from doing JS injection by making sure the arguments don’t have parentheses.

Examples

The following is an example of what will happen when running for the function for a number of strings:

console.log(isValidVarName(''));           // -> false
console.log(isValidVarName('3'));           // -> false
console.log(isValidVarName('3d'));          // -> false
console.log(isValidVarName('D3'));          // -> true
console.log(isValidVarName('D3 '));         // -> false
console.log(isValidVarName('D3,Q'));        // -> false
console.log(isValidVarName('D3/*Qs*/'));   // -> false
console.log(isValidVarName('D3Q'));         // -> true
console.log(isValidVarName('var'));         // -> false
console.log(isValidVarName('true'));        // -> false
console.log(isValidVarName('undefined'));   // -> true
console.log(isValidVarName('null'));        // -> false
console.log(isValidVarName('coolio.pop'));  // -> false
console.log(isValidVarName('coolio'));      // -> true
console.log(isValidVarName('coolio_pop'));  // -> true
console.log(isValidVarName('$'));           // -> true
console.log(isValidVarName('$á'));          // -> true
console.log(isValidVarName('áÑ'));          // -> true
console.log(isValidVarName('_'));           // -> true

Here is a similar example hosted on JSBin:
JS Bin on jsbin.com