loading...

How I build Telegram Bot for budget and what lessons I have learned.

dmfilipenko profile image Dmytro Filipenko Updated on ・3 min read

Intro

For the past few weeks, I was working on a small side project – home budget in Google Spreadsheets, which I can easily add through Telegram Bot.

Why did I decide to go with this stack?

Google Spreadsheets – compelling and extensible.
You can easily set up a massive variety of calculations and aggregations. It's an example of one of my formulae, which I'm using to calculate expenses per month per selected category.

SUM(
     IFERROR(
         FILTER(
            'Expenses'!D2:D,
            'Expenses'!C2:C =
            CELL(
                "contents",
                INDIRECT(
                     CONCATENATE(
                        "A",
                        ROW(

                        )
                    )
                )
            )
        ),
        0
    ),
    B1 = 'Expenses'!A2:A
)

So, before I started. I've created one document with three spreadsheets: expenses, category of expenses, and subscriptions. Then I've added all these aggregation formulas to calculate expenses by months automatically. And at the beginning, I've manually added all rows by opening google spreadsheets, usually from mobile, less often from the desktop.

But overall, this process took so much time, open google spreadsheet app, select expenses sheet, and start filling new row. It's working quite slowly from mobile.

So I've decided to automate this process somehow. First I tried to use Google Apps Script.

Google Apps Script allows you to get access to your spreadsheets, manipulate rows and columns. You could use your Javascript to write your Apps Script. It's a quite good solution, but I wanted to have more flexibility. I didn't want to operate in the context of Google App Script.

I went with a Telegram Bot API. To write it more easily Telegraf can help you to do this.

I first challenge was how to add Google Auth to a telegram bot. Google OAuth 2.0 documentaion provide you such images Google Auth workflow

Let me make it real simple.

  1. I generate a google authorization link, with callback URL among query params, it's done by google SDK. Example: https://accounts.google.com/signin/oauth/oauthchooseaccount?access_type=offline&state=9999999&prompt=consent&scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fdrive%20https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets&response_type=code&client_id=105404325697-1fs9rpeuhah4u4h8dfm4ma0p2ckpclb5.apps.googleusercontent.com&redirect_uri=https%3A%2F%2Fyour.url%2Foauth2callback&o2v=2&as=asda123casa&flowName=GeneralOAuthFlow
  2. Then user follow this link, grant permission or deny
  3. Google redirects me back to redirect_uri which was part of generated URL
  4. On a server, I handle authorization token or error status and save it to DB.

Why it was most complicated part for me? I've spent a few days to realize how to connect Google Oauth + Telegram + NodeJS server. How to save tokens to DB, and exact tokens need to be saved.

Server

I'm using fastify as a web server, coz I need only API, and fastify doing a good job. For hosting, I've used Heroku. It allow me to reduce DevOps work. As far I'm using the free plan, they keep my server in a sleep mode if the server not doing any activities for 15 minutes. So, for my case, it's not a problem, but when I'm adding new expenses, I need to wait for 30-40 sec while Heroku woke up a server. For DB – mongo, I need to save user, all metadata for user, such as documentId, sheetName and some service information.
With Heroku is very easy to add monitoring, logging, error aggregation.

Telegram API

For working with TelegramBOT API I used Telegraf. It's quite powerful, but a lack of documentation potentially could be a problem.



For some questions, I used examples to understand how it's working. If you don't want to invent everything from scratch you can use Telegram BOT template.

What's next

I'm planning to add some text recognition. I want to take a photo of the bills and get a total number. I'm still trying to figure whats better to use for this task. Some OCR solution as Tesseract or EasyOCR. Also, one way to solve this to train some simple ML models on bills to find a boundary with the total price, and OCR tried to extract total amount.

Discussion

pic
Editor guide