Using Google Sheets as a randomizer to spice up your Slack bot

  • 4 March 2021
  • 0 replies
  • 56 views
Using Google Sheets as a randomizer to spice up your Slack bot
Userlevel 2

Bots - they repeat themselves repetitively over and over

At Zapier, we use Slack all the time, and there are a ton of “bots” running via Zaps. By their nature, bots can be a bit repetitive, and repetition can lead to people ignoring important messages. 

But what if the bot had a lot of different variations to keep people on their toes? And what if you could add that variety very simply using a single step? And this doesn’t have to just be Slack - maybe you send regular emails or texts to folks, and you’d like to give those a little bit of variety too!

Well, if you know me, you know that I see a problem and I think “Google Sheets can fix this!” It turns out that every problem truly is a nail when Google Sheets is your hammer. :hammer:

Let’s make a table!

First, we need to build a sheet with all of our snippets. Feel free to add as many snippets as you want - and in fact, you can add multiple columns if you want to have more than one snippet per message, like so:

b6a05da57a8fcb50f636ad5ca4cbb72a.png
(view larger)

And that’s our table! Simple enough.

Let’s make a formula!

Now that we have the data, what do we do with it? Well, we write a couple of fun formulas to pull the data. 

The first is a RANDBETWEEN formula. This will give us a random number between two specified integers - in other words, we’re telling Sheets to “pick a number between 1 and 10” and it gives us one at random every time the sheet is loaded or changed. So you could do something like this:

=RANDBETWEEN(2,7)

And that will return any number between 2 and 7, inclusive. But we’ll have to change that if we ever add more snippets, so let’s make this a little easier for ourselves down the road. Instead, we can do:

=RANDBETWEEN(2,COUNTA(A:A))

What’s that doing differently, you ask? Well, COUNTA will count all of the cells that have data in the specified range, in this case the entire column A. That way, when we add new snippets, that maximum number gets higher every time, and we never have to change this formula. Cool, huh?

Alright, now we’ve got a random number. How do we get the actual data? Another formula, of course, this time using INDIRECT to build a cell reference. So to get the Intro, we’d use this formula:

=INDIRECT("A"&D2)

In my example, I’ve put the random number generator in D2, so that’s where that number is coming from. So if the random number is 4, then this formula would equal “A4,” which would display “Hey folks! Time for your daily update.” You’ll want one of these for each column you want to pull, changing the letter in quotes to that column letter. So for our example, we’d also want 

=INDIRECT("B"&D2)

in another cell. At the end of the day, these formulas put together look like this:

590d9eee6dd15a4b9d1fbb9461381bba.png
(view larger)

And every time you change anything on this sheet, the random number changes, and therefore the two snippets change. 

Oh, but what’s with that last column? Well, we need something to look up the row by that never changes - I just wrote Zapier in a column, so that it’s easy to remember. 

That’s the whole sheet! I put the formula on the same worksheet for ease of use, but you can also put it in a different Worksheet if you want to keep things a little more organized. Just make sure to update your references accordingly! 

Let’s make a Zap!

This is probably the easiest part. We have all the data set up, now we just need to set up a Zap to use it. In my example, I have a bot that sends me a message every time I get a new email from Jira. Let’s spice this up with some random snippets as well.

All I need to add is a Google Sheets “Lookup Spreadsheet Row” step into that Zap:

adaa8d002b169cabe7c7c010a08810c5.png
(view larger)

This relies on a specific behavior in Google Sheets, namely that looking up a spreadsheet row causes all formulas to recalculate. That means that when we do a lookup, the random number will change, and we’ll get new snippets! So let’s set this up to find that row that has “Zapier” in column G:

8497bf041d670dde3dacdbf69cf35a68.png
(view larger)

And when we test that, we should get the row that has the random number:

f160feb61e21d880a9af04b65a5598a7.png
(view larger)

And now we can map that directly into the Slack step:

2fc03ee8d4ec2354dea75dcfb4e348e3.png
(view larger)

Now every time that sends, a random snippet will be at the top and bottom, hopefully making it a little more noticeable and cool. And you can probably think of loads more applications for this - sending randomized autoresponders, adding different emojis to your text messages, and more. And since you can update this list by just adding to the sheet, you don’t have to futz with extra steps every time you want to make a change!


0 replies

Be the first to reply!

Reply