Fuzzy string matching for Google Docs

A few months ago I presented a simple VBA function based on N-Grams to do fuzzy string matching in Excel VBA called NLOOKUP(). This function works like the traditional Excel VLOOKUP() function, but uses N-Grams to do a fuzzy string matching. Josh, a reader who stumbled upon this article (original VBA function see here) asked me if I could convert this function to JavaScript for usage in Google Sheets. Yes, I can. The result is attached to this post (see below). Copy and paste the contents of the file into the Google Sheets script editor within your document (described here, 2nd paragraph "Creating a custom function"):

Download: nlookup.js

Google Sheets demo sheet (read-only): NLOOKUP-Demo

Circular LIFO buffer in VBA

In modern programming languages like .NET or Java there are predefined objects for "classic" data structures like stacks or sorted lists which work pretty well. I once had to develop a little VBA macro to process a list of internet url's quickly. Since the macro had to check the last 100 url's every iteration, I decided to develop a simple ring buffer – which is not part of the standard VBA function repertoire. A ring buffer is a type of buffer where the first entry gets overwritten when the buffer is full and one more entry is added. To accomplish this, the buffer keeps two pointers, called 'head' pointing to the start and 'tail' pointing to the end. Both move within an array if objects get push'ed to or pop'ed to the buffer:


Ring buffer (Source: Wikipedia, CC BY-SA 3.0 by User Cburnett)

A big advantage of a ring buffer is its speed: Since there is no need to make room for new entries if the buffer is full (like in a stack), there is no need for allocating or freeing memory during buffer operation (the downside is the static size of the buffer).

How it works

The following is taken from the Wikipedia entry 'Circular buffer' (CC BY-SA 3.0):

A circular buffer first starts empty and of some predefined length. For example, this is a 7-element buffer:


Assume that a 1 is written into the middle of the buffer (exact starting location does not matter in a circular buffer):


Then assume that two more elements are added — 2 & 3 — which get appended after the 1:


If two elements are then removed from the buffer, the oldest values inside the buffer are removed. The two elements removed, in this case, are 1 & 2, leaving the buffer with just a 3:


If the buffer has 7 elements then it is completely full:


A consequence of the circular buffer is that when it is full and a subsequent write is performed, then it starts overwriting the oldest data. In this case, two more elements — A & B — are added and they overwrite the 3 & 4:


Alternatively, the routines that manage the buffer could prevent overwriting the data and return an error or raise an exception. Whether or not data is overwritten is up to the semantics of the buffer routines or the application using the circular buffer.

Finally, if two elements are now removed then what would be returned is not 3 & 4 but 5 & 6 because A & B overwrote the 3 & the 4 yielding the buffer with:



You can download the source code for the ringbuffer in VBA here:

Ringbuffer class: Ringbuffer.txt / Test module: Modul1.txt (both files for Excel VBA)