I made an in-house peer bonus tool (aggregation only) using Python and BigQuery

Introduction

Unipos is a well-known tool for sending gratitude and effort among employees. Since introducing services on a company scale is a heavy burden, I decided to make something like that for the time being.

Thing you want to do

If you create a dedicated channel with slack and skip mention + message there, it will be counted. It is aggregated monthly and visualizes & MHP (most, praised, person) is issued. I also want to summarize the sent messages for each user.

What I used

• slack
• Adopted because it is used by all employees
• python slackbot
• Originally used in-house, so adopted
• BigQuery
• Used for aggregation and visualization
• GAS
• google data portal

Creation procedure

slack settings

First, create a new BOT with slack api.

1. Click Create New App
2. Enter the App Name and select Workspace to Create App
3. This time I want to use it as a BOT, so select Bots
4. Turn on Always Show My Bot as Online
5. Issue Token with OAuth & Permissions and use it when writing from slackbot, so copy and paste locally
6. Create a compliment channel with slack and add the created app to it

This is almost the end of preparation on the slack side. Please set the icon to app as you like.

Google Spread Sheet and GAS settings

Considering that it may be operated by a non-engineer later, this time I made it written to Spread Sheet when I hit GET.

Saves 4 columns of thank-you / worker, message from person, and date.

• How to receive Get parameter in GAS and write it to spreadsheet

I used this sauce.

Because I used the company suite Execute the app as: to me, I couldn't write because of permissions unless Who has access to the app: was set to anyone.

Hit the created URL to confirm that it is written in each column and proceed to the next.

Bigquery and data portal settings

Create a project to read the spreadsheet created earlier as a data source. I thought about visualizing it using the bigquery function, Use the data portal because the operation is intuitive.

slackbot settings

This time I did the most coding-like coding.

import requests
import datetime
import re
from slackbot.bot import listen_to
from slackbot.bot import default_reply

Requests, datetime, re from the python default library, Use listen_to and default_reply from slackbot.

@listen_to('<@')
def mention_func1(message):
    api = "https://script.google.com/macros/s/[api]/exec?p1={p1}&p2={p2}&p3={p3}&p4={p4}"

if message.body['channel'] == '[channel ID]' :

        bodytext = re.split('\s+|\s*\n\s*', message.body['text'], maxsplit=1)
    
        userid = bodytext[0].replace('<@', '').replace('>', '')

        if "Joined the channel" in userid:
            exit()
        
        p1 = namelist[userid]
        p2 = bodytext[1]
        p3 = namelist[message.body['user']]
        p4 = datetime.date.today()
        
        url = api.format(p1=p1, p2=p2, p3=p3, p4=p4)
        r = requests.get(url)
        
        message.reply('We have received compliments!')

At first, when I tried to make it react with a specific custom emoji + @username, I found out that some employees didn't know how to use custom emojis, so I decided to work if I mentioned them on a specific channel.

In the message received by slackbot, the ID of the person who did it is the user ID of Slack, and the mention part is `<@USERID>`. Since I wanted to save it as a display name for operation, I created an object for the display name and user ID separately, and converted the user ID to the display name.

Finally, write to hit the URL in requests and finish.

Output

Write a message with mentions in slack, and when it is saved successfully, you will receive a reply from the bot.

The saved data is aggregated every month, and you can check the number of times / messages have been made / messages on the data portal. I made only the number of times and the message once, but since it is easy to create for each user or aggregate by different elements, I thought that it would be nice if the data portal could be changed in future operations. ..

reference

• How to receive Get parameter in GAS and write it to spreadsheet
• slackbot