Chatbot for sending messages with data from Google Sheets to Telegram

Task

We have previously discussed the automation of data collection for advertising campaigns and leads from Google Ads and Google Analytics, consolidating them into a single file in Google Sheets. To provide the project team and the client with this information in a more convenient manner, we decided to integrate the table with the Telegram messenger, which is commonly used in our workflow.

Solution

While Google Ads can be directly integrated with messengers using the service's internal script, Google Analytics lacks such an algorithm. Therefore, we utilized the Make (formerly Integromat) automation platform to integrate the file into Google Sheets and the Telegram service.

What have we done?

We developed a Telegram bot that retrieves data from the table every morning and sends it to the project chat in the messenger. Thanks to this, our team and client employees can view information about remaining credits and costs for all sites in the context of advertising platforms in Google Ads for the previous day, as well as details about the funds in personal accounts. The messages also include links to Google Sheets, allowing users to review the details.

To enhance convenience, we set up notifications upon the completion of scripts that collect data for the table. This ensures that the system functions as intended, and the customer is informed about the appearance of the final up-to-date indicators in the table. It's important to note that accessing the file while the script is running may result in inaccurate data.