Slack Hack: Make a completely automated question of the day system for your team (with no plugins!)
Quickly make a free, completely automatic Slack “question of the day” system that helps build rapport and conversation while working from home.
Working from home has been great, but over the past year I’ve tried to continuously think of new ways to keep team members engaged in building rapport, and not just talking about tasks.
A co-worker recently mentioned that her team started doing a ‘question of the day,’ and it sounded like a lot of fun. I figured with Slack Workflows, there had to be an easy way to set something up that was mostly automated — but as much as I searched, I couldn’t find anything. So I spent a bit of time making a little Google Sheet that could help.
So far, so good! It’s absolutely been worth the time. We’ve all learned a lot of silly things about one another, and everyone is excited for that time of day to roll around to see what the next question will be.
But since it was so difficult to find anything about how to set this up already, I wanted to share! Don’t worry — there’s no programming, or bots, or even Google Sheet formulas. You should be able to get this setup in less than 10 minutes!
What is it?
- It’s two “Slack Workflows” and a Google Sheet
- Anyone in a particular channel can submit a question that gets added to a Google Sheet.
- At a particular time on whatever days you’d like, Slack will automatically pull a new, unasked question from that Sheet and post it in a channel.
- The Google Sheet keeps track of which question you’re on!
How do I do it?
- Setup the Google Sheet: Make a copy of this Google Sheet to your own Google Drive that you’ll connect to your Slack workspace.
- Setup Workflow 1: Create a Slack Workflow that allows teammembers to submit a question
- Setup Workflow 2: Create a Slack Workflow that pulls a question at a particular day and time, and updates the sheet to increase the count of total questions asked.
1. Setup the Google Sheet
You can skip this step if you’d like.
First, go to this Google Sheet and Make a Copy into your personal Google Drive that you’ll connect to your Slack Workspace. If you’d like to learn how it works, read on:
The Sheet has three tabs.
Automations: This is where the magic happens. We form a string name that is a reference to which question we should ask next. The row number we use is a count value of all of the values in “Count” tab. That “Count” tab is updated with one more value each time Slack asks a question (you’ll see this in step 3).
- Column “Today” is the next upcoming question to be asked. It’s actually looking at cell E2 (Column Concatenated) to find out exactly which cell to pull the question from.
- Column “Pull From” exists as a hack for Slack. It tells slack to look at Row 2 to find the right cell under “Today”
- Column “Cell Name” is the column we want to look at in the Questions tab.
- Column “Cell Number” is the row number we want to look at in the Questions tab. It takes a count of all the rows in the “Count” tab to see which question we should be on next.
- Column “Concatenated” appends our tab name (Questions), column (A), and current row number to form a reference to the latest question we should ask.
Questions: Every time a question is asked, its added as a new row here. You can pretty safely delete questions — but always ensure there are no empty rows between questions. Note: If the “Count” tab has more entries than there are Questions, things will break. If you clear out or delete questions, be sure to clear out the Count values.
Count: Every time Slack posts a question, it also adds a “1” in a new row on this sheet. All of the rows with data are counted up, and that’s how the Automations sheet knows which question to ask next. You can safely delete entries from this table, but be sure to delete as many Questions.
2. Setup Workflow 1 (Submit a Question form)
First, let’s setup the workflow that allows your team members to submit new questions that get added to the Google Sheet.
This Workflow will:
- Add a Shortcut button in a channel of your choosing that your team members can click
- Open a form modal window when someone clicks the Shortcut button.
- When the form is submitted, the data is added to our Google Sheet
- After that, the person is private messaged with a confirmation.
To open the Workflow Builder in Slack, click the hamburger menu icon, select Go, then select Workflow Builder.
Click the Create button in the top right, and let’s name this workflow Submit a Question
A modal will pop up asking you how to start this workflow. Choose Shortcut.
In the Shortcut modal, choose the channel you’d like people to be able to submit questions from and the name of the shortcut. I used the same channel I’ll have the questions asked in. Click Save to close the modal.
From here, you’ll see a mostly empty screen with an Add Step button. Click it, and choose Send a Form.
In the Send a Form modal, the important form is the Add a Question. It’s what people will see when they’re submitting a question. You can add any explanation or requirements to the types of questions you’d like here. You can also change it to Long Answer, but I found that Short Answer worked best. Click Save to close the modal.
Click Add Step again, and select Add a spreadsheet row. You may need to connect your Google account to Slack at this point. Ensure that you’ve made a copy of the Google Sheet to your own drive as well.
In the Add a spreadsheet row modal…
- Make sure you’re connected to the Google Account you’d like to use.
- Under Select a Spreadsheet, find the Google Sheet (QOTD Template) you made a copy of.
- Under Sheet, select Questions
- Under Column Name, select Questions
- In the Value field, select the little blue link Insert a Variable
- You should see an option that says “Response to” with however you worded your question in the previous step. Select that.
- Click Save to close the modal.
Click Add Step once more, and select Send a message. We’ll message the original question submmited with a confirmation, letting them know they were successful.
Send this message to the Person who clicked your shortcut.
In the message text, write something letting the user know they were successful. You can click the blue link that says Insert a variable to add their submitted question. Click Save to close the modal.
And that’s it! Your Workflow steps should look like this. Click Publish in the top right hand corner to save the entire workflow.
3. Setup Workflow 2 (Slack Pulls a Question every so often)
We’re on the last step! This Workflow will:
- Start at a specific time of day, on a specific frequencies of days you choose.
- Select the spreadsheet row from our Automation sheet that has today’s question. (Slack’s implementation requires us to ‘find’ a row instead of select a specific cell.)
- Add a new row to our Count sheet, increasing the count of the number of times we’ve asked a question.
And that’s it! So head back to the main Workflow Builder screen and click Create in the top right. Name this workflow something like QotD Automation or whatever you’d like:
In the next modal, choose Scheduled Date and Time
In the Scheduled Date and Time modal, choose the start date, the time, and the frequency. I found 11am was a good time for my team, and selected Monday through Friday for frequency. Click Save.
Note: The workflow won’t know about PTO days or office closures!
Next, click Add Step and select Select a Spreadsheet row.
- Spreadsheet: Select the QotD Template again
- Sheet: Select Automations
- Choose a column to search: Select Pull From
- Define a cell value to find: 2
- Click Save.
You can ignore how this works — but basically, Slack will create a variable that allows you to pull the cell value from any other column’s row that is the same row as it find here. In our Google Sheet, I have a value of “2” in row 2 under “Pull From,” which is the same row our actual question is in (under the Today column). We basically need a piece of static data to consistently find. 2 is an arbitrary value, it just needs to exactly match what’s under ‘Pull From’ in the sheet.
Add a Step and choose Send a Message
Send this message to whatever channel you’d like. For me, it was the same channel I added the Submit a Question shortcut to.
In the Message Field, write something introducing the message. Click the blue link Insert a variable and choose Today. This pulls row 2 from the Today column, which is where our Google Sheet’s formula is putting the latest question!
Finally, we have to add a count row to update our Google Sheet so it knows another question has been asked.
Add a Step and select Add a spreadsheet row.
- Spreadsheet: Select the same you’ve been using
- Sheet: Select Count
- Column Name: Select Times Added
- Value: Enter 1
- Click Save
And that’s it! Your Workflow should look like this. Now just click Publish, and have your team start submitting questions!
Things to Watch Out For
This is an awesome little hack that’s added a lot for our team, but it is still a hack. You’ll have to watch out for a few things:
- Unless you stop the Workflow, it will keep automatically asking a question as often as you told it to. It won’t stop for work closures, holidays, etc.
- If people forget to submit questions for a while, your automation will break. I recommend checking your Sheet for questions once a week. You could add some more Google Sheet formulas and a Workflow that calculates how many questions you have remaining and direct messages you!
- People can write whatever they’d like — so it’s always smart to check the sheet and prune questions for whatever makes sense for your team.
Have any ideas on how to make this better, or other things this process could be used for? I’d love to hear about them!