Google Sheets Formula & Telegram Message ✈️

Here’s a formula sample with IMPORTXML:

=IMPORTXML(
"https://script.google.com/macros/s/AKfyc...VnFp/exec?msg="&if(C3>500;"Hey sum is > 500!"&C3;"") &
"&chat_id=13246789";"/table/row")

It will send a new message to Telegram.

The idea is based on this post by Tanaike.

By the way, IMPORTDATA formula will also work:

=IMPORTDATA(
"https://script.google.com/macros/s/AKfyc...VnFp/exec?msg="&if(C3>500;"Hey sum is > 500!"&C3;"") &
"&chat_id=13246789")

Sample message:

Install

Full installation requires 4 files:

All files are available for view/copy here.

#1 of 4. 🤖 Telegram Bot

The process is not easy to repeat. Please carefully follow these steps:

1. Go to Botfather https://t.me/BotFather
2. Create a new Bot, remember Token
3. Create a new Google Apps Script project
4. Use the code from here.

The next steps are inside the script editor. After you save the code and the project, do the following:

1. Deploy a WebApp. Set who has access to Anyone
2. Remember WebApp URL. It is a rather long URL like this: https://script.google.com/macros/s/LongLongKeyGoesHere…/exec
3. Put WebApp URL into your script. Change the value for parameter: webAppUrl
4. Put Telegram Token into your script. Change the value for parameter: token
5. Run the function setBotHook. You’ll need to do it only once. This will connect your Bot with WebApp.

Next, go back to Telegram.

1. Go to Botfather and deploy your bot. By this, you can connect your Bot to yourself and send messages to you.
2. Create a new Telegtam group
3. Invite your Bot as a group member.
4. ⚠️ Make your Bot an admin of the group. This is crucial for Bot to work.
5. Write any test message to your new Telegram Group

Next, go back to your script editor:

1. Go to your saved project and run the function: logMemory
2. In your logs copy chat id

From the same script, you may already send a message to Telegram! To test your Bot:

1. Find in the code function: testSend. Inside the function change -123465789 to your chat id.
2. Run the function.
3. Check your Telegram group. A new message from the Bot must appear.

#2 of 4. ✏️ Memory

Please copy my Spreadsheet to use it as a Memory holder. It would be your place with information about sent messages to Telegram.

After you make a copy, please remember the file ID. Here’s mine:

1pI12qRVgIY7i03tVdd9jNd2GwPDgOi1RLthdpVpdMD8

I’ve copied it from the browser URL:

You’ll need this ID in the next step.

Why use memory. For now, there’s an issue. When you open a Spreadsheet, your formula will recalculate and send the same message again. One way to resolve the issue of duplicates is to write all sent messages into memory.

🟢Formula → 📢Web-App → 💬Telegram + ✏️Memory

I had to decide which type of memory to use. I stopped on simple writing data to a Sheet for these reasons:

1. The sheet is easy to manage.
2. The speed of reading/writing information is OK to me. Use this Spreadsheet only for holding memory. This will speed up the work of a script.
3. A sheet will save information forever. It won’t burn out after 6 hours as Cache memory does.
4. A sheet may hold lots of data. Cell text is limited to 50,000 characters. The sheet is limited to 5(10?) million cells. Source.

#3 of 4. 📢 WebApp for Beeper

In this step you may need some information from previous steps:

1. Telegram token from step #1
2. Memory spreadsheet ID from step #2

Please follow these steps:

1. Create a new Script. It should be another script file because you’ll need to deploy a WebApp again.
2. Put this code into your script editor. Save the code and the project.
3. Change token to yours.
4. Change memory_sets.id to your memory spreadsheet id.
5. You may also change test_chat_id in the script body. It is not required.
6. Deploy WebApp.
7. Remember WebApp URL. You’ll need it for your final step.

#4 of 4. ✈️ Formula 2 Telegram

In this step you may need some information from previous steps:

1. WebApp URL from step #3
2. Telegram group’s chat id from step #1.

Here’re we finally installed the code. Now you may use formulas to send messages to Telegram. I’ve made a template to make it easier:

This template demonstrates how to use the code.

The key here is to launch WebApp with a formula. The process is the same as if you run this URL with parameters from your browser as any other URL.

Here’s what WebApp URL looks like:

https://script.google.com/macros/s/LongLongKeyGoesHere…/exec

That’s not all! You also need to pass 2 parameters to your script. Your final URL will hold these parameters: message and chat id. Here’s a sample URL with parameters:

https://script.google.com/macros/s/LongLongKeyGoesHere…/exec?msg=Hello&chat_id=-12346789

URL has 2 parameters here:

1. msg
2. chat_id

And here the fun part starts. As this URL is passed via formula, you can use any other formulas to combine your message into Telegram. This means you now can control which messages will come to Telegram:

• how often they will come. Include date to your message to send messages daily 🙂
• you may also set conditions when sending messages: if an error occurs, if sales are low/high, if a manager needs to take action, etc.

Telegram Message Syntax

First of all please note: the message should be encoded because you send it via Web URL. Luckily we have a native Google Sheets function to encode the text: ENCODEURL. Please encode the message only. Your final URL may look like this:

https://script.google.com/macros/s/LongLongKeyGoesHere…/exec?msg=Hellooooo%20There&chat_id=-650350317

Hellooooo%20There = encoded string. The actual message is “Hellooooo There”.

To get your text encoded simply use this formula:

=ENCODEURL("Hellooooo There")

Put your message inside this formula and see how text is encoded.

Telegram API may change in the future. It now supports these HTML tags:

<b>Bold</b>
<i>Italic</i>
<a href="https://twitter.com/max__makhrov">URL</a>
<code>Code inside text</code>
<pre>Code</pre>

Try these texts and see what message you’ll get.

One important tag is not supported by Telegram, but I’ve implemented it into the script:

<br>

👆🏼 This one will create a new line. Please try:

Thanks for reading!<br>Max

Finally to send a message try one of the following formulas:

=IMPORTXML(
"https://script.google.com/macros/s/AKfyc...VnFp/exec?msg="&if(C3>500;"Hey sum is > 500!"&C3;"") &
"&chat_id=13246789";"/table/row")

or

=IMPORTDATA(
"https://script.google.com/macros/s/AKfyc...VnFp/exec?msg="&if(C3>500;"Hey sum is > 500!"&C3;"") &
"&chat_id=13246789")

You’ll need to change the Web App URL and chat_id, and now that's all!

Ten articles before and after

Monitor Server with Telegram Bot and Python – Telegram Group

How to create a Telegram bot with Python in under 10 min! – Telegram Group

How to Install and Run the Crypto-Telegram Bot – Telegram Group

The art of indexing +1PB data on Telegram – Telegram Group

Телеграм бот для уведомлений об ордерах Binance – Telegram Group

How to Create a Telegram Chatbot in 2022 – Telegram Group

Telegram Bot 跟我想的不一樣. 第一天進公司時，Slack就被邀進同事們訂便當的群組，在上面會宣布今天訂那一家便… – Telegram Group

TRX1 Dev Blog #11 (November 2021) – Telegram Group

Serverless Telegram bot with Kotlin, Firebase and Google Cloud Functions – Telegram Group

How To: Deploy Java Telegram Bot to Heroku – Telegram Group