Life hack — Track your monthly expense spreadsheet through Telegram Bot
In this article, you will learn how to create own Telegram Bot to track your monthly expenses. You no need to be coding expert to do so. Follow the instructions, build the bot step by step. Ultimately, you can build your own expenses tracker by your own. Let’s start.
Step 1: Create a new google spreadsheet, named it “Monthly expenses”. New tab with named “Aug_2021”.
Now let’s set some values for Budget, and formula for total expenses, and balance. In column B1, we set our monthly budget, in this tutorial I set value 500 as this month budget. In column B2, formula of total expenses is “=sum(C:C)”, in column B3 “=B1-B2” is formula for balance.
Next, we proceed with prepare our expenses’s table. You can design the table with any colour you like. Just like below screenshot.
Alright, we are prepared the spreadsheet. Now we can proceed to more challenging part.
Step 2: Open Google Apps Script editor from Tools > Script editor
Step 3: Before jump to Google App Script, we need to get telegram authorization token and spreadsheet ID.
To get telegram authorization token, let’s jump to telegram app. Firstly type “BotFather” from the search bar. Click on the *BotFather chat and click START button on the bottom on chat.
You will see a list of commands in the chat bot.
We going to create a new bot so type “/newbot” to start. Next choose a name for your bot. Let’s give it a name called “expenseBot”. Now let’s choose a name for username: “expense3_bot” because “expense_bot” is taken. If the name you choose is taken rename and try again. Until you got a congrats message with authorization token. Here you go, we got token that we needed in google app scripts.
Second thing that we need before we can proceed to google app scripts is spreadsheet ID. Now we can go back to google spreadsheet. From the browser url bar you can find your Google sheet ID from URL:
https://docs.google.com/spreadsheets/d/{ID_HERE}/edit
Is time to go back to Google App Script editor, to get ready for coding part. Firstly, copy and paste below code to Code.gs.
var token = "<REPLACE_THIS_WITH_YOUR_TOKEN>";
var telegramUrl = "https://api.telegram.org/bot" + token;
var webAppUrl = "<REPLACE_THIS_WITH_DEPLOY_ID>";We’re going to use Webhooks, this means every time there is an update in for the bot ( when someone sends a command to our bot ), they will send an HTTP POST request to a specified URL containing a JSON-serialized update.
function setWebhook() {
var url = telegramUrl + "/setWebhook?url=" + webAppUrl;
var response = UrlFetchApp.fetch(url);
}Following, we going to create sendMessage function. This function will be use later for sending message to telegram bot.
function sendMessage(id, text, keyBoard) {
var data = {
method: "post",
payload: {
method: "sendMessage",
chat_id: String(id),
text: text,
parse_mode: "HTML",
reply_markup: JSON.stringify(keyBoard)
}
}
UrlFetchApp.fetch(telegramUrl + "/", data);
}When an user or a program (Bot API) sends to our script an HTTP Post request, Apps Script will run the special callback function doPost(e). What we need to do is define that doPost function in script. The e argument represents an event parameter that contains the informations of the request. This mean doPost will receive an object, and converted into String using method JSON.parse();
function doPost(e) {
var contents = JSON.parse(e.postData.contents);
var ssId = "<REPLACE_THIS_WITH_YOUR_SSID>";
var expenseSheet = SpreadsheetApp.openById(ssId).getSheetByName("<REPLACE_THIS_WITH_YOUR_SHEET_TAB_NAME>");
if (contents.callback_query) {
var id = contents.callback_query.from.id;
var data = contents.callback_query.data;
if (data == "balance") {
var balance = expenseSheet.getDataRange().getCell(3,2).getValue();
return sendMessage(id, balance + " is how much you have left.");
} else if (data == "total") {
var total = expenseSheet.getDataRange().getCell(2,2).getValue();
return sendMessage(id, total + " is your total expense.");
} else if (data == "budget") {
var budget = expenseSheet.getDataRange().getCell(1,2).getValue();
return sendMessage(id, budget + " is the budget you allocated.");
}} else if (contents.message) {
var id = contents.message.from.id;
var text = contents.message.text; if (text.indexOf("-") > -1) {
var item = text.split("-");
var dateNow = new Date;
var formattedDate = Utilities.formatDate(dateNow, 'GMT+0800', 'dd MMM, yyyy') + ', ' + showDay(dateNow.getDay());
expenseSheet.appendRow([formattedDate, item[0], item[1]]);
return sendMessage(id, "Ok added to your expenses.");
} else {
var keyBoard = {
"inline_keyboard": [
[{
"text": "Budget",
"callback_data": "budget"
}],
[{
"text": "Total expense",
"callback_data": "total"
}],
[{
"text": "Balance",
"callback_data": "balance"
}]
]
};
return sendMessage(id, "Please send me your purchases using this format: [item] - [price]", keyBoard);
}
}
}function showDay(day) {
switch(day) {
case 0:
return 'Sun';
break;
case 1:
return 'Mon';
break;
case 2:
return 'Tue';
break;
case 3:
return 'Wed';
break;
case 4:
return 'Thu';
break;
case 5:
return 'Fri';
break;
case 6:
return 'Sat';
break;
}
}
We almost done, now is time to deploy this to Web app. Click on the Deploy button on the top right.
Choose “New deployment”, choose select type “Web app”, set “Deploy 1” as New description. Click “Deploy”
Choose Anyone from lists of Who has access.
Proceed with Authorize access button.
Choose Advanced to proceed. Go with “Go to Expenses (unsafe)” and press “Allow”.
And here you go, we got our first deploy done. But we still have few more steps to do before we can test it out.
Once we got deployment done. Copy the Web app URL and paste it to your code replace it with <REPLACE_THIS_WITH_DEPLOY_ID>; The final step is to click on Run button.
Now is time to test it out. Go to telegram expenseBot dashboard. Click start button to start the track your expense. Your track expenses bot is ready.
BotFather is the one bot to rule them all. Use it to create new bot accounts and manage your existing bots. Send Message. If you have Telegram, you can contact. He will also give us our authorization token.
