Enhancing Wordle Scorekeeping with Zapier

A great way to foster some remote team building is by encouraging playful competition among your distributed folks. Wordle is a great way to do this! If you set up a channel at work and have players regularly share their scores, the competition heats up quickly. At Wrapmate, we currently have a #wordlebracket Slack channel that is getting plenty of activity.

However, when it comes time to say who won or lost, there's not much out there in the way of automatically calculating scores or reporting on them. Who has time to manually crunch numbers? Isn't that what automation is for?

Several weeks ago, Zapier's newsletter sent out a great link on how to automate the collection of Wordle scores by forwarding scores in an email and having Zapier parse those results and store them in a Google Sheet.

Today, we're going to up the ante. We'll keep the Google Sheet, but do 2 new things:

1. Ditch the email parser for a Slack parser, so that when a player shares a Wordle score in a specific channel, Zapier will capture the score and store it in the Google Sheet, and
2. Create a single bot-like response for any participating player in the same channel to have that player's score posted back to the channel.

This...is Zapier Wordle Leaderboard II Turbo: Champion Edition!

Part 1: Parsing Shared Wordle Score in Slack Channel

Before we get started, read up on the original Zapier post, and be sure to grab a copy of the Google Sheet they use.

The first zap we'll be importing is the one that parses a shared Wordle score in a specific Slack channel.

Since this will be an imported zap, I'll only go over the basics here. The zap consists of 5 steps:

1. Trigger: New Message Posted to Channel in Slack
2. Action: Only continue if...
3. Action: Only continue if...
4. Action: Text
5. Action: Create Spreadsheet Row in Google Sheets

Here are the key pieces to know for each of these 5 steps.

Step 1. New Message Posted to Channel in Slack

The key piece of this step is deciding on a single channel that you'll not only share your Wordle scores in, but also as the destination for Wordlebot to respond to requests.

Decide on that channel, and set it in the "Channel" field – in my version, we've decided to share Wordle scores in the #wordlebracket channel, so I've set mine to wordlebracket.

Step 2: Only Continue If...

This step is a Filter by Zapier that proceeds only if a Wordle score is detected, which begins simply with an attempt to find the word "Wordle" in that post.

Step 3: Only Continue If...

This next step is the key to the entire detection phase: determining if what was entered into the Slack channel is an actual Wordle score (as opposed to someone simply mentioning the word "Wordle" in discussions).

For this, we look for the existence of either:

• :black_large_square:
• :large_green_square:
• :white_large_square:
• :large_yellow_square:

The first guess of the shared Wordle score must have at least one of these four colored squares, so that's how we determine it is a message we care about parsing & logging into the Google Sheet.

Step 4: Formatter by Zapier / Text / Extract Pattern

The fourth step does the heavy lifting. At this point we've determined the Slack message is, in fact, a valid Wordle score. Now we must parse it. How? Regular Expressions, of course!

The input will be the actual Slack message posted in Step 1.

The pattern we'll use to parse is this:

Wordle ([0-9]+) ([0-9]+)/([0-9]+)

This grabs the three pieces of info we care about (indicated by the three sets of parenthesis in the pattern). They are (left-to-right)

1. The ID of the Wordle game being played,
2. The score, and
3. The hard mode score.

Step 5: Create Spreadsheet Row in Google Sheets

Now that we have our parsed score, it's time to write it directly to Google Sheets.

The key values you care about setting here are:

• Drive: The Google Drive where the Wordle spreadsheet resides (you did grab a copy of the spreadsheet from the original Zapier tutorial, right?)
• Spreadsheet: The actual spreadsheet copy mentioned in the previous bullet
• Worksheet: The tab/sheet inside the file you want to write data to – this should be the fourth tab, which (if not renamed) is called "Receive Info – Read Only"
• Sender: This should be the "User Real Name" property from the Slack message in Step 1.
• Wordle ID: This is "Output 0" from Step 4.
• Date: This is the "Ts Time" field from the Slack message in Step 1.
• Score: This is "Output 1" from Step 4.
• Hard Mode?: This is "Output 2" from Step 4.

When complete, it should look something like this:

Nice!

Finishing Up

Don't forget to turn your zap on! Now, play a game of Wordle and when you're prompted with the "Share" button, click it to copy the score into the clipboard, flip over to your Slack channel, and paste the score in!

That looks like something like this:

Then, pop open your Google Sheet, and head over to the fourth tab to confirm it wrote the score out!

Beautiful. Now...on to part 2!

Part 2: Have a Bot Read Back a Player's Wordle Scores

Let's take a look at the zap that allows a Wordle player to request their scores.

NOTE: As of this writing, I can't share a link to the zap (Zapier barks out an error), so you will have to set this up manually following the instructions below.

Step 1: Trigger (WHEN) New Message Posted to Channel in Slack

As with Part 1, select:

1. Your app (Slack) and your event trigger (New Message Posted to Channel in Slack), and
2. Your account (your Slack account).

In "Set up trigger", select the same channel as you used in the previous section – the channel that your team or office is sharing their Wordle scores to. Go ahead and leave "Trigger for Bot Messages?" set to "No."

Step 2: Only continue if...

For this 2nd step, select "Filter by Zapier" and enter in only a single piece of criteria:

• Only continue if... (Text) -> (Text) Contains -> Wordlebot scores

Nice! Moving on...

Step 3: Get many Spreadsheet Rows (Advanced, With Line Item Support) in Google Sheets

As with Part 1, select:

1. Your app (Google Sheets) and your event trigger (Get Many Spreadsheet Rows - Advanced, With Line Item Support), and
2. Your account (your Google Sheets account).

Then, under "Set up action", let's go over the individual settings:

The settings are as follows:

• Drive: Select the Google Drive that has a copy of the Wordle Scores template you copied from Part 1.
• Spreadsheet: Select the Google Sheets file that represents the copy of the Wordle scores template, again from Part 1.
• Worksheet: Select "Display Info" (or, if you've changed the names of the sheets, select the 2nd sheet – that sheet has contains the full leaderboard, and which looks like this:

• Columns: A:K
• Row Count: Set this to the maximum number of players you feel your scoreboard will ultimately have; it won't break if it is larger than your current number of players.
• First Row: Set it to 1, as we'll want to include the header row when we parse this out.
• Should this step be considered a "success" when nothing is found?: Leave this at "No."

Once set up, the test data should come back in the form of a "rows" object, with the 1st row having 11 columns – the 11 different column headers (eg. "Sender", "Played", "Win %", etc.)

Step 4: Code by Zapier / Run Javascript

For the next step, you're going to choose the "Code by Zapier" app, and for its "Action Event", you'll have it run custom Javascript. Here's how to set up that action:

Under "Input Data" you're going to create two keys / value pairs:

1. Set the first key to text and its value to the "Rows" element of the Google Sheet in Step 3.
2. Set the second key to player and its value to the "User Real Name" element of the Slack message in Step 1.

Then, in the "Code" section, copy and paste this block:

const csv = inputData.text.split(',');
const colCount = 11;
       
let obj = {};
let arrKeys = [];

// keys first
for (let i=0; i < colCount; i++) {
  arrKeys.push( csv[i] );
}

// rest of the values, starting with our prev. stopping point
for (let j=colCount; j < csv.length;) {

  let person = '';
  let stats = {};  

  // 1 row at a time, by # of columns in colCount
  for (let k=1; k % colCount != 0; k++) {
  
    let name = arrKeys[k-1];
    let value = csv[j];
       
    if (k == 1) {
      person = value;
    } else {
      stats[name] = value;
    }
    
    j++; // move forward in the csv
    
  }
  
  // if we've reached the end of a row
  obj[person] = stats; 
  
  j++; // move forward in the csv

}

output = obj[inputData.player];

In a nutshell, this:

• converts the entire Google Sheet (Rows) object into a single long CSV string.
• parses out the headers (row 1) first, and stores them in arrKeys.
• parses out the remainder of the csv, moving in blocks of colCount (11, for the number of column headers there are), breaking out each row by creating a string for the person's name or a stats object with a matching key and value (eg. stats["Played"] = 6).
• Adds the entire parsed set of data, by each person's name, to a parent obj.
• Returns only the key of the parent obj who originally requested the scores, eg the Slack user's full name, mapped to inputData.player

If you've been following along, you know realize the fun fact: the entire scoreboard is available via obj. We just choose to return an individual player's scores. You can expand on this!

Step 5: Send Channel Message in Slack

The final step is having our "Wordlebot" respond to the score request by writing out a response that includes all the juicy details of the person's score and posting it back to Slack.

1. Select "Slack" and for its "Action Event", choose "Send Channel Message"
2. Choose your account (same as in Step 1)
3. For "Set up action", use these values:

• Channel: Set this to the same channel as what was specified in Step 1
• Message Text: You can format this however you want, my script reads like this:

{{149341023__user__real_name}} has played {{149345309__Played}} games, with a win percentage of {{149345309__Win %}}, and a current winning streak of {{149345309__Current Win Streak}}. Their best Wordle score was {{149345309__Best Wordle Row}} rows, and accomplished this {{149345309__# of Best Wordles?}} time(s). Overall, they have an an average Wordle score of {{149345309__Current Average Wordle Score}}. For this week's competition, their score is {{149345309__Weekly Competition Score}}. Additionally, they have missed {{149345309__How Many Missed Wordles}} games, and have made {{149345309__Hard Mode Attempts?}} attempts at hard mode.

The key here is that wherever you see a {{number__header_name}} above, you're selecting a variable that is produced from the output of Step 4. Run Javascript. Your number will likely be different than mine, so if you get confused or aren't sure, remember that when you click inside the Zapier text fields, you can choose each value from the dropdown menu in the Zapier UI itself, like this:

• Send as a bot: Yes
• Bot Name: I like "Wordlebot" – call it whatever you want.
• Bot Icon: I used :robot_face: but you can use whatever you wish.
• Include a link to this zap?: No

Everything else can be left at their default.

Finishing Up

You're done! Don't forget to turn the zap on. Then, head over to your Slack channel and type "Worldlebot scores" and moments later, you should see a response similar to this:

Success! Zapier and your new Wordlebot do the heavy lifting of capturing scores, and reading them back to the Slack channel, so you can focus on the more important work – destroying your competition!