Telegram bot + GoogleSheets: solving some problems

What problems you may encounter and how to get around them

The topic of Telegram bots is only growing. If you're developing Google Sheets add-ons, you're writing Google App Script (GAS) code. Therefore, probably, the brains for these bots are written in GAS.
There was an idea to write a bot that would look for keywords in chat messages and do something with these messages. For example, send me a notification that someone has written something on a topic that interests me. Or, on the contrary, delete obscene language and spam, and ban the authors of such messages in the chat.
As always, in the process of practical implementation of the plan, pitfalls emerge. I will talk about some of them and ways to bypass them below. But first, some theory.

getUpdates VS setWebhook?

It doesn't matter what language you write the brains for your bot in - GAS, PHP, Pyton or something else. In any case, you are using the Telegram API.
The API currently supports two ways to handle bot updates: getUpdates and setWebhook.
getUpdates is a pull mechanism, setWebhook is a push mechanism.
For example, you run getUpdates and get all the messages written in the chat during that hour. In GAS, you can set up a time trigger with this interval. But this means that for our example with an anti-spam bot, spam will hang in the chat with impunity for an hour. This is not good, it is advisable to shoot spam immediately on approach.
Therefore it is necessary to use setWebhook. As stated in Marvin's Wonderful Guide to All Things Webhook:
setWebhook, compared to getUpdates,

• saves your bot from having to frequently request updates.
• avoids the need for any polling mechanism in your code.

setWebhook() + fetch(url)

Now a few words about the mechanism for receiving updates or, more simply, messages from a Telegram chat.

1. We write the simplest script in the GAS editor:

function doPost(e) {
var contents = JSON.parse(e.postData.contents);
var chat_id = contents.message.from.id;
// code for extracting data and writing to the sheet
}
That's it, we have an update in the contents variable. With the help of further processing, everything that is needed can be extracted from it. For example, chat_id is the identifier of the message sender, text is the text of the message.
Then you can write this data on a sheet, send it to someone, etc.

1. Let's deploy the script as a web application.
   At the same time, we specify the parameters “Run as: On my behalf, Who has access: Everyone” and copy the link to the web application (webAppUrl).

2. We form a link.
   To do this, you need to connect the bot token and the web application URL as follows:
   var token = "1234567890:ABCDEFGHIJKLMNOPQRSTUVWXYZ";
var webAppUrl = "https://script.google.com/macros/s/XXXXXXXX/exec";
var telegramUrl = "https://api.telegram.org/bot" + token;
var telegramUrl = "https://api.telegram.org/bot" + token;

3. Fetch the url and set the webhook:
   function setWebhook() {
var url = telegramUrl + "/setWebhook?url=" + webAppUrl;
var response = UrlFetchApp.fetch(url);
}

You can go to this url in the browser without any script and see
{"ok":true,"result":true,"description":"Webhook is already set"}

This is a JSON object, parsing which, you can extract all 3 fields separately - ok, result and description. Then they can be used in conditional logic, shown to the user and all that.

Pitfalls

So we have a script that instantly writes all messages from the Telegram chat to a sheet. Then you can search for keywords in them and do something with these messages.
But imagine that you, delighted, have posted a link to this bot of yours in the public domain and expect that satisfied users will thank you for a useful tool.
It wasn't there!

Everyone is comfortable with Google services. And they provide you with sheets, and a script development environment, and powerful servers around the world on which these scripts work, and many other delicious goodies. However there are pitfalls and one of them is Google's quotas and limits. “Apps Script Services has daily quotas and limits on some features. If you exceed the quota or limit, your script will throw an exception and execution will stop.” For example, URL Fetch calls are limited to 20,000 per day, and the limit for simultaneously script executions is 30 users. This means that if the number of users of the bot becomes large enough, it will stop working.
And what to do?
For example, you can buy your own server or rent a server and process all the logic on it. It costs extra money and time, so it's logical to think about something else.

Way out

The developer may come up with the following.
Since the web application is running on behalf of me, my quotas are consumed. But what if you deploy it not on my behalf, but on behalf of the user?
An interested user can do this in 3 clicks. You just need to give him a sheet template with a script embedded in it. He will make a copy of the sheet for himself, deploy the script as a web application and insert the webAppUrl into the desired cell.
Now quotas and restrictions will be consumed not by the developer, but by the user.
And it is very unlikely that this user will have more than 20,000 messages per day in his Telegram group.
Everything seems to work.
It wasn't there!

Pitfalls again

Every developer wants the fruits of his creativity to be useful. Both for him and for the users.
How to distribute our script so that interested users know about it? You can write articles on thematic sites, advertise on Google, etc.
It's all great, but it's a sin not to use free tools.
Speaking of Google goodies, one cannot fail to mention the Google Workspace Marketplace. There, Google developers publish add-ons for Google products - for sheets, documents, mail, etc.
Thus, the idea of ​​​​implementing our Telegram bot appears as follows:

1. We make a Google sheet template.
   It draws an interface with the necessary headings, a web application script is attached to the table.

2. We transfer all the message processing logic from the webapp to the add-on and publish it on the Google Workspace Marketplace.

3. The user installs the add-on, makes a copy of the template for himself, makes the initial settings (enters the bot token, web application URL, his keywords into the required cells) and uses it to his delight.
   Yes, here we shifted part of the work to the user, but that's why I am writing this article. Perhaps someone who reads will suggest workarounds.
   Moreover, it was not there again, there are still pitfalls here.

Again

The problem is that you can NOT pass data directly into an add-on in ANY WAY. That is, you received a message via the webhook, but you cannot send this message to the handler, which, let me remind you, is in add-on.
You cannot run a function that is inside an add-on.
You can't even pass some tag or signal like “Hey add-on! There's a message! Run fetch(url) and read!”.
Never. I do not know why, whether for security reasons, or for some other reason. You can read about it here: How to transfer data from webapp to addon.

The thought may come to mind: “We write messages on a sheet. Put an onEdit() or onChange() trigger and as soon as the message is written to the sheet, the trigger will fire, read the data and process everything as it should.”
There are 2 catches here.
One of them is that all I/O type spreadsheet operations are relatively slow - it's a fraction of a second. With intensive communication in the Telegram chat, multiple calls to the sheet and from the sheet can cause significant delays.
In contrast to the situation, if the data is passed bypassing the sheets, directly from fetch() to the add-on code. Google's servers are fast and such operations take milliseconds.
The second catch is that onEdit() or onChange() triggers do NOT fire when the data on the sheet is changed programmatically.
These triggers are fired if the data on the worksheet has been changed by the user. For example, entered from the keyboard or selected from the menu.
More about this here: Event Objects.

There is an way!

The answer to the question in the post on the Stack was still found. The author even assigned a bounty for the correct answer, but the best thing that they suggested to him then was to use the Google cloud service. Yes, among the delicious buns there is one. And it's certainly powerful stuff.
That's just it is paid when certain indicators are exceeded.
And I have not seen good manuals for it.
According to GAS there are plenty of such resources. This is Google Apps Script, and mdn web docs and a bunch more.
On Google Cloud there are much fewer such resources.

Therefore, when Alan Wells came to the post six months later and wrote about the Sheets API and USER_ENTERED, everything immediately became clear.

The scheme is as follows:

1. Enable the Sheets API in web application services. You can verify that everything is correct if you look at the code in the appsscript.json file. If anyone forgot, then for this, in the script settings, check the box “Show the appsscript.json manifest file in the editor”.
   It should contain the following code:
   "dependencies": {
"enabledAdvancedServices": [{
"userSymbol": "Sheets",
"serviceId": "sheets",
"version": "v4"
}]
}
   Later, when the user does a copy of your template, this service will be included in its copy of the script.

2. How do we usually write data to a sheet?
   We use setValue(value).
   Like this:
   var ss = SpreadsheetApp.getActiveSpreadsheet();
varsheet = ss.getSheets()[0];
varcell = sheet.getRange("B2");
cell.setValue(100);

Into the fire!

Now we use Google Sheets API.

The code should look something like this:
var SHEETID = 'XXXXXXX';
function doPost(e) {
var data = e.postData.contents;
var rowValues ​​= [
[data, ""],
];
var request = {
'valueInputOption': 'USER_ENTERED',
'insertDataOption': 'INSERT_ROWS',
'data': [
{
"range": "TEMP!A2:B2",
"majorDimension": "ROWS",
"values": rowValues,
},
],
};
var response = Sheets.Spreadsheets.Values.batchUpdate(request, SHEETID);
}

There are two key points here:
A. We write to the sheet using batchUpdate().
B. The 'USER_ENTERED' as it were, tells the trigger that the data on the sheet has been changed not by the program, but by the user.
Everything, now the trigger will work. The data, albeit indirectly - through the sheet, is transferred to the add-in, and the whole circuit functions.

Conclusion

You can read more about all this here: Telegram Assistant: Description and manual. There is also a link to the add-on, and to the spreadsheet with the script. You can use all of this freely.
And I will be grateful for constructive criticism and advice!