Google Sheets Formula & Telegram Message ✈️

6 min readJan 20, 2022

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:

Please see this instruction on Miro Board

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:

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

Here’s the official instruction on how to create a new Bot on Telegram

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

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

When you deploy WebApp, Google will ask you to allow the script to run. Here’s an article on how to pass authorization: https://spreadsheet.dev/authorizing-an-apps-script

Next, go back to Telegram.

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

The bot must be an admin to read messages

Next, go back to your script editor:

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

👆🏼 The response from Apps Script looks is message info in JSON format. Copy chat id from it.

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

Find in the code function: testSend. Inside the function change -123465789 to your chat id.
Run the function.
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:

The sheet is easy to manage.
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.
A sheet will save information forever. It won’t burn out after 6 hours as Cache memory does.
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:

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

Please follow these steps:

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

Please don’t forget to change: 1) token, 2) Memory Spreadsheet ID

#4 of 4. ✈️ Formula 2 Telegram

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

WebApp URL from step #3
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:

msg
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")

=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!