Try to make BOT by linking spreadsheet and Slack with python 1/2 (python + gspread + slackbot)

You don't have to bother to search, but there are things that interest you.

I've started learning python lately, and anyway, let's make a BOT by linking with Slack that I usually use so that if I enter a word I wrote in a spreadsheet in advance on Slack, it will return the meaning. I thought.

Thing you want to do

Link python with a spreadsheet (used as a database) and run it alone first
After that, install the Slackbot library and have it return the meaning corresponding to the word sent to BOT.
Enables word registration, list output, and deletion on Slack (will be written in the next article)

Preparation

With great reference to this page, implement from the python program to the point where the spreadsheet is opened.

`test.py`


import gspread
import json

from oauth2client.service_account import ServiceAccountCredentials 
scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']

credentials = ServiceAccountCredentials.from_json_keyfile_name('Downloaded JSON file name.json', scope)

gc = gspread.authorize(credentials)

SPREADSHEET_KEY = 'Spreadsheet key'

worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1#I will write up to this line.

I will write the continuation.

`test.py`


values_list = worksheet.col_values(1) #Get the first column of the sheet as a list

search_word = input('Enter the word you want to look up: ')


#Compare the searched word with the list searched for all, and if there is a value, True,Returns False if not
tof = search_word in values_list #tof...Appropriate variable
if tof == True:
  cells = worksheet.find(search_word)  #Get coordinates that match the input result
  val = worksheet.cell(cells.row, cells.col+1).value  #Adjust the coordinates and get the description column
  print('Details:', val)  #Output the explanation column
else:
  print('The details could not be output.')

When comparing the entered values, I was wondering if there was a smarter function in the gspread library, but the return value didn't work ... I had no choice but to use the in operator. Please let me know if there is a better way.

Try to move

Prepare the contents of the spreadsheet. スプレッドシートの中身

$ py test.py
Enter the word you want to look up: Mickey
Details: Haha

It's done.

Works with Slack

What I was worried about before implementation was

How to make the input function on Slack side
How to write the response

This is around here.

Directory structure

I will implement it with great reference to the article here. It doesn't matter if you have test.py that you moved earlier.

Write code

`run.py`


# -*- coding: utf-8 -*-
from slackbot.bot import Bot
                                                         
def main():
    bot = Bot()
    bot.run()
                                                         
if __name__ == "__main__":
    print('start slackbot')
    main()

`slackbot_setting.py`


# -*- coding: utf-8 -*-
                                                         
API_TOKEN = "xxxx-xxxxxxxxxxxx-xxxxxxxxxxxxxxxxxxxxxxxx"
                                                         
#Default reply
DEFAULT_REPLY = "Enter "Search:" and then enter the characters."
                                                         
#Match with the directory name where the file containing the code to add the function is located
PLUGINS = ['plugins']

`my_mentions.py`


# -*- coding: utf-8 -*-
import json
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import re
from slackbot.bot import respond_to     # @botname:Decoder that reacts with
from slackbot.bot import listen_to      #Decoder that responds to in-channel remarks
from slackbot.bot import default_reply  #Decoder that reacts when there is no corresponding response

scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']


credentials = ServiceAccountCredentials.from_json_keyfile_name(
    'Downloaded JSON file name.json', scope)


#Log in to the Google API using your OAuth2 credentials.
gc = gspread.authorize(credentials)

#Variables for shared spreadsheet keys[SPREADSHEET_KEY]Store in.
SPREADSHEET_KEY = 'Spreadsheet key'

#From here on, the operation details of the spreadsheet.#####################################
#Open Sheet 1 of a shared spreadsheet
worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1

values_list = worksheet.col_values(1)


@respond_to('Search:(.*)')
def mention_func(message, search_word):
  tof = search_word in values_list
  if tof == True:
    cells = worksheet.find(search_word)  #Get coordinates that match the input result
    val = worksheet.cell(cells.row, cells.col+1).value  #Adjust the coordinates and get the description column
    message.reply('result: {0}'.format(val))  #Mention
  else:
    message.reply('I searched, but couldn't find it.')  #Mention

@listen_to('Decide the app name or something')
def listen_func(message):
    message.reply('If you have a request, "@After entering "App name search:", search for any character.')                       #Mention

respond_to () describes what happens when you mention your app in a channel and send a message. By setting ". *", Any character string is accepted and passed to the BOT side. In this case, by sending "Search:" any string "" to Slack, it will search the spreadsheet for a match for that word and return the meaning if there is one.

If you change this, it seems that the application will be quite effective.

By the way, listen_to () describes the behavior when sending a message without mentioning it.

When you run run.py, SlackBOT will start.

However, at this point, I have to run run.py every time to start the BOT, so in my case I deploy the application to heroku so that it always responds.

at the end

After implementing it, you can link it with a local file separately, or is it safer? I thought, so I may rewrite it soon. For the time being, I understand the basics of sending and receiving messages in Slackbot, so I will change it steadily and implement registration / deletion etc. (Next article is here).