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.
Finally, I got some results. Still, a lot of stuff to improve. Allow creating a tables by your own, change records, but for my purpose, it's quite enough.04:56 AM - 29 Jun 2020
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.
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
Let me make it real simple.
- 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
- Then user follow this link, grant permission or deny
- Google redirects me back to
redirect_uriwhich was part of generated URL
- 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.
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
sheetName and some service information.
With Heroku is very easy to add monitoring, logging, error aggregation.
For working with TelegramBOT API I used Telegraf. It's quite powerful, but a lack of documentation potentially could be a problem.
Before this, I tried it once-or-two, and I wasn't so amazed. But with the Expense task, I discover a few things.
1. First of all, NodeJS wrapper for a telegram API.
The functionality of this library is quite powerful but lack of documentation.02:54 AM - 22 Jun 2020
Let me clarify. Documentation for a telegraf is quite good, in terms of what you can use. But, for me, it's more important not just what, but how you can use it. Examples, where each method could be used, it's also a significant part of proper documentation.05:30 AM - 22 Jun 2020
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.
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
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.