Spreadsheet Bot: Regular Expressions, VLOOKUPs, oh my!

At Dexter, we’re focused on developing tools that allow people to create incredible apps and digital experiences without writing a single line of code. One of our most popular such tools is Spreadsheet Bot, a dead-simple app that allows anybody to create a SMS or Slackbot just by filling in cells in a spreadsheet.

Look, a bot! Build one yourself over SMS or Slack

We understand though that if you’re coming to Dexter as a non-programmer, the heart and soul of Spreadsheet Bot could be a bit foreign or even scary to you: the regular expression. In this post, we’ll explore what these things are and how you can better utilize them and other tools to build even more powerful and interesting apps.

What’s a regular expression?

It’s a pattern. Full stop. (Albeit, a very special kind of pattern that fancy computer and math people have defined in great detail, but nevertheless, they are patterns for words, and hey, everybody knows patterns!)

They may look complicated, but regular expressions are just patterns. In fact, the search box in your browser might even use regular expressions, which are just patterns which are regular expressions which are patterns which are regular expressions wh

A pattern can match text. In Spreadsheet Bot, when somebody sends a message to your bot, it’s going to look down the rows of patterns in Column A, find the first one that matches the message, and spit back the message or image you’ve supplied in Columns B or C. (Note: by default, Spreadsheet Bot has some magic built in that ignores uppercase & lowercase letters and treats them all the same.)

Patterns & Matches

The most basic kind of pattern contains just characters. If your pattern is cat, it will match any of these messages your bot receives:

Cats are great
Have you seen my cat?
It’s complicated
Seriously, where is my cat?

(Follow along with our examples or try it yourself here)

It’s often useful to match full words in people’s messages though, so to do that, surround your pattern with \b. So the \bcat\b pattern will match Have you seen my cat? but not Cats are great or It’s complicated.

Including a . in your pattern means “any character.” So the pattern b.t will match bot, bat, and even bite. But it won’t match boot. (b..t will match boot and beat but not bot.)

A ? in your pattern means “the character immediately before is optional.” So the pattern i?mac will match iMac as well as Mac (remember by default these are case-insensitive!)

+ means “the character immediately before is repeated one or more times.” So bo+t matches bot, boot, and booooooooot. But not bt.

* is the same repetition concept as +, except the character doesn’t even have to show up once. So bo*t will match bot, boot, booooooooot, and bt.

Therefore, .* means “match any character, which can be repeated 0 or more times.” It’s the “catch-all” pattern and is included as the last row in the default spreadsheet.

You can combine many disparate patterns into a single one by using the “or” character: |. So if your pattern is cat|dog the following messages will match: Dogs are great and Cats are great.

The ^ and $ characters mean “start of message” and “end of message,” respectfully. This means ^cat will match Cats are great but not Have you seen my cat?. ^help$ will match messages whose content is exactly help.

You can also surround characters with brackets [] to quickly create patterns for many words. For example, ba[tmr] will match batting average, Bam!, and bar tab. It won’t match ba, but you can start combining all these together to create a pattern that will: ba[tmr]? will match just ba.

One final useful tool to create your patterns with are parentheses (), which group characters together. These are often useful when used in conjunction with our other tools. For example, \b(re)?tweet\b matches the whole words tweet and retweet. \b(re)+tweet\b will even match rerereretweet (you must be really popular!)

Play around with these patterns yourself by looking at and copying this spreadsheet with all the examples above, or you can use online tools like RegExr. If you want some inspiration for what you can build by combining all these concepts together, take a look at the inner workings of Botwick.

The Magic of Being Basic

Let’s also not lose the basic beauty (💁) of Spreadsheet Bot, namely that it’s a spreadsheet! So everything that you already know and love (and ok, hate) about working with data in spreadsheets is available to you and bots you create. So creating a Slackbot that responds to your team with your latest DAU numbers or top sales prospects is incredibly simple—go ahead and VLOOKUP your heart out. And what games or tools can you build with RANDBETWEEN, GOOGLETRANSLATE, and other functions?

To make your functions come alive and reference what your users are sending in, you can use special characters in Column B to reference input in Column A. For any pattern that has parentheses grouping, you can reference the actual sequence of characters with \1. Example: your pattern is i like (cats|dogs); you can make your response ‘=”I also love ”&”\1" to say “I also love [whatever your user wrote].”

If you have more questions around Spreadsheet Bot, regular expressions, or just want to show off something cool you’ve made, come find us on Twitter or Slack!