Your own Bookkeeping Telegram Bot with Python | by Sebastian Caparroz | Feb, 2021
I will be using Google Drive to store the spreadsheet. I chose to do that because it allows me to access it from anywhere if I need to. It also keeps your data safe and you do not have to worry about having local files. Therefore, you will need a Google account. If you don’t have that already, you will have to create one.
In order to connect the Python script to your Google account, you will need to enable the Google Drive/Sheets API. This video in YouTube from Tech With Tim explains the process perfectly, which in summary is:
- Create a spreadsheet and fill in some cells, we will use that for testing. Give it a proper name.
- Go to Google Cloud Console and create a new project.
- Enable Google Drive API, setting web server access, to read application data, with Project>Editor role. Make sure to download your credentials in JSON format and store it in your project folder (keep it safe!).
- Enable Google Sheets API.
Now, to make sure the script and your sheet are playing nice, we can do some testing in Python. The first thing you will need to do is install some libraries to interact with you freshly created spreadsheet:
pip install gspread
pip install gspread-dataframe
pip install oauth2client
Now you can already try things like downloading data from your sheet, edit the data, and upload again to update the sheet:
Now you want to put some data in your sheet to test it out:
And then go to your creds.json file, and look for the “client_email”. That is your assigned address in Google Drive API. Share the sheet you created with that email:
Now, you can initialize your client in Python and connect to the sheet:
We are getting the data into a dataframe, since this will help later when we start manipulating rows. The dataframe will look like this:
Date Type Description Price
0 30/Aug/2020 Food Takeout 11.0
1 31/Aug/2020 Transportation Gasoline 42.1
2 1/Sep/2020 Food Groceries 56.5
3 2/Sep/2020 Food Burger 13.0
4 3/Sep/2020 Clothes T Shirt 19.5
5 NaN NaN NaN NaN
6 NaN NaN NaN NaN
7 NaN NaN NaN NaN
8 NaN NaN NaN NaN
.. ... ... ... ...
Now all these columns get imported as strings (dtype: object), so let’s give them the right format:
At this point you can get summaries for your data however you like. I am not going to get into that here, but just to give an example, you could aggregate using pivots and produce things like this:
Type Clothes Food Transportation
2020-08 NaN 11.0 42.1
2020-09 19.5 69.5 NaN
1. Case Study: Building Appointment Booking Chatbot
2. IBM Watson Assistant provides better intent classification than other commercial products according to published study
3. Testing Conversational AI
4. How intelligent and automated conversational systems are driving B2C revenue and growth.
Now it’s time to create your Telegram bot, and a channel where you will send your expenses. To create a bot, you will need to write to Telegram’s BotFather, send the command /start, and follow the instructions to create your bot. You will receive a TOKEN, save it securely!
After creating it, send the command /setprivacy, choose your new bot, and then Disable. This allows your bot to read all messages coming to the channel, instead of just the ones beginning with a “/” (called “commands”).
Now create a Telegram channel, and add your bot’s username.
We can start now to write the python script that will handle all our expenses. You can begin with the following steps:
- Import all the libraries you will be needing
- Define variables
- Open a connection with your expenses sheet
- Get the data into a formatted dataframe
Next to the functions. We want a simple workflow:
- We input an expense description and its price, separated with a comma.
- The bot asks what type does the expense belong to, offering a predefined set of possible types as buttons.
- We select the type by pressing the button, and the bot saves the expense as a new row in the sheet.
For this we will create 2 methods, one that handles incoming messages and offers “types” of expense to choose from (“input_expense”), and the other that saves the expense in the sheet once we push a button (“callback_query_handler”, you must respect this name):
Finally, create the bot object, add handlers, and start polling:
By now you should be able to send expenses to the channel (remember to send it in the form: description, price), and the bot will save your expenses in the sheet:
Credit: Source link