Google Sheets is a powerful cross-platform app with a rich set of formulas and the ability to integrate with other apps.
However, such functionality is often redundant for simple tasks, which makes the work not very convenient. This is especially true for mobile devices. So it would be cool to make it possible to work with tables through other interfaces. One option is to create a Telegram bot. This bot will receive some data from user and write it as new row into Google Sheets table.
It can be hosted for free!
With zero cost, we get a convenient combination of 2 powerful tools.
Tables API
The first step is to look at the tables documentation: https://developers.google.com/sheets/api/quickstart/nodejs
You can found a very useful button on this page:
Click on that button to create a new project and download credentials file (credentials.json). Create a new file package.json
Интеграция Trello + Google Sheets + Telegram своими руками без сторонних сервисов и дополнений
< «name»: «spreadsheets-bot», «version»: «1.0.0», «description»: «», «main»: «index.js», «scripts»: < «test»: «echo «Error: no test specified» exit 1″ >, «keywords»: [], «author»: «», «license»: «ISC», «dependencies»: < «googleapis»: «^59.0.0», «telegraf»: «^3.38.0» >>
Enter fullscreen mode
Exit fullscreen mode
- googleapis — library for google api
- telegraf — Telegram Bot framework
You can run npm install to install this dependencies.
const fs = require(‘fs’); const readline = require(‘readline’); const = require(‘googleapis’); // If modifying these scopes, delete token.json. const SCOPES = [‘https://www.googleapis.com/auth/spreadsheets.readonly’]; // The file token.json stores the user’s access and refresh tokens, and is // created automatically when the authorization flow completes for the first // time. const TOKEN_PATH = ‘token.json’; // Load client secrets from a local file. fs.readFile(‘credentials.json’, (err, content) => < if (err) return console.log(‘Error loading client secret file:’, err); // Authorize a client with credentials, then call the Google Sheets API. authorize(JSON.parse(content), listMajors); >); .
Enter fullscreen mode
Exit fullscreen mode
Quickstart code solves the authorization problem to gain access to your personal tables, which is what we need.
However, it is advisable to immediately make a change: remove «.readonly» from the line
const SCOPES = [‘https://www.googleapis.com/auth/spreadsheets.readonly’];
Enter fullscreen mode
Exit fullscreen mode
This will change readonly access to read const readline = require(‘readline’); const = require(‘googleapis’); const < Telegraf >= require(‘telegraf’); // import Telegraf // Telegram bot access token: const BOT_TOKEN = ‘1234567890:a1b2Cg2cLR4LGO1EpvDHawYPVjunkW3Bfk9’; // spreadsheets scope: read
Enter fullscreen mode
Exit fullscreen mode
You can receive messages and send replies:
const bot = new Telegraf(BOT_TOKEN); // Load client secrets from a local file. fs.readFile(‘credentials.json’, (err, content) => < if (err) return console.log(‘Error loading client secret file:’, err); // Authorize a client with credentials, then call the Google Sheets API. authorize(JSON.parse(content), startBot); >); function startBot(auth) < bot.on(‘message’, (ctx) => < if (ctx.from.id !== ‘123456789’) < // your user id return ctx.reply(‘Access Denied’); >console.log(ctx.message.text); ctx.reply(«Hi, » + ctx.from.id); >); bot.launch(); >
Enter fullscreen mode
Exit fullscreen mode
Depending on the task, you can compose commands or a dialogue with the bot, in which you can use the ability to read and write from tables.
Here is an example of writing to a table:
. const rowNumber = «2»; let values = [ [ «Formula:», // string value «=128/2+ROUND(8/3;2)», // any formula «User:», «Ben», », // empty cell ] ]; const resource = ; let valueInputOption = [ ‘RAW’, // as string ‘USER_ENTERED’,// will be parsed as user input ‘USER_ENTERED’, ‘USER_ENTERED’, ‘USER_ENTERED’, ]; const range = sheet + ‘!A’ + rowNumber + ‘:E’ + rowNumber; const spreadsheetId = ‘mKj7bEUzG7miu4m5nsBt4KWTM6IIgstwn9g1a7IvVwz0’; sheets.spreadsheets.values.update(< spreadsheetId, range, valueInputOption, resource, >, (err, result) => < if (err) < // Handle error console.log(err); ctx.reply(err.toString()); >else < ctx.reply(«Data saved.»); //reply from bot to user console.log(‘%d cells updated.’, result.data.updatedCells); >>);
Enter fullscreen mode
Exit fullscreen mode
Upload your bot to cloud for free
For convenience, the bot must be running 24/7. Then you can use it from your phone even when all your computers are turned off.
One of the options is Free Trial on Google Cloud Platform:
https://console.cloud.google.com/
For me this is not charging anything. Because the load on the server is too low.
You need to setup a new minimal cloud VM with Ubuntu or what you like.
When you connect to your new server by ssh, you can install node, npm and run your bot.
I recommend using PM2 to run script.
Enter fullscreen mode
Exit fullscreen mode
Simple upload your script with rsync from local host to google cloud host:
rsync -ruv LOCAL_PROJECT_PATH SERVER_HOST:REMOTE_PATH
and start on the remote:
cd REMOTE_PATH pm2 start index.js
Enter fullscreen mode
Exit fullscreen mode
Note that you should replace all my «uppercase» with your data (path to the project, server’s ip, etc.)
Bot is started and you can use it 24/7
Источник: dev.to
Отправка сообщений через Telegram-бота из скрипта загрузки
В настоящий момент базовая версия Qlik Sense Enterprise имеет крайне небогатый функционал рассылки оповещений. А точнее, он вообще отсутствует.
В линейке продуктов присутствует такая вещь как Qlik Alerting, но он не везде подходит в виду своей стоимости и области применения (оповещения на основе данных из приложений). Поэтому для налаживания оперативных оповещений, нам придется использовать сторонние решения. Одно из них — боты Telegram.
Использование Telegram-ботов хорошо тем, что его можно начать без осваивания кликовского API, а также без установки дополнительных коннекторов. Поэтому, давайте начинать.
Создание Telegram-бота
Прежде всего, вам нужно добавить в Telegram в список контактов бота BotFather. Это служба, которая отвечает за создание новых ботов.
Пишем в чат с BotFather команду /newbot. После этого нас попросят указать имя нового бота. Оно должно заканчиваться на _bot (строчными буквами).
В случае успеха, вы увидите сообщение с токеном вашего бота (комбинацию цифр и букв). Она будет нужна для отправки сообщений через вашего бота.
Собственно, на этом все.
Отправка сообщений из скрипта
Для отправки сообщения из скрипта нужно использовать нижеприведенный код, и запустить загрузку данных.
set vTBot_Token=; //Токен бота set vTBot_ChatID=; //ИД чата или получателя set vWebConnection=; //Название любого подключения с типом Web-файл на сервере Qlik set vTBot_Message=; //Текст сообщения, %0A — перенос строки TG_Message: LOAD 1 FROM [lib://$(vWebConnection)] (url is [https://api.telegram.org/bot$(vTBot_Token)/sendMessage?chat_id=$(vTBot_ChatID)parse_mode=HTML]); drop table TG_Message; //Очистка переменных, чтобы не отображать их в визуальном слое set vTBot_Token=; set vTBot_ChatID=; set vTBot_Message=; set vTBot_Message=;
Чтобы обращаться к Telegram, вам потребуется создать подключение к данным с типом web-файл к любому адресу, хоть к https://yandex.ru. Т.к. URL для обращения мы будем передавать через параметр URL IS, в котором для коннекта к web-файлу можно указать совершенно любой адрес.
Забавный факт — обращение к URL не только приводит к загрузке данных из него, но и позволяет инициировать действия во внешней системе. Мы специально указываем цифру 1 как поле в загрузке таблицы TG_Message, чтобы Qlik мог выполнить обращение к адресу, ничего при этом из него не загружая.
Если вам интересно, то в ответ отдается Json-строка с информацией об отправленном сообщении. Но мы не будет тратить время на ее разбор.
Откуда взять ID чата или пользователя? Рекомендую использовать этого бота:
У него можно узнать собственный ИД. Чтобы узнать ИД чата, нужно добавить в него бота и обратиться с командой /getgroupid.
Добавьте вашего бота себе в контакты, пропишите все необходимые параметры и запустите скрипт. Вы должны получить сообщение от бота.
Примеры использования
Мониторинг сбоев в заданиях обновления данных.
Создайте приложение с названием типа Task Control. В него разместите скрипт отправки сообщения с текстом типа «Сбой обновления данных, проверьте ».
Создайте для приложения Task Control задачу на обновление данных.
В качестве триггеров укажите неуспешные выполнения тасков, которые вы хотите мониторить. На каждый таск нужно создавать отдельный триггер, иначе клик будет проверять одновременный провал всех тасков в триггере. а этого не случатся, т.к. цепочка остановится после первого провала.
Теперь в случае завершения таска с ошибкой вы получите сообщение такого вида:
Это самый оперативный способ получения информации о сбое ровно в тот момент, когда он случится.
Мониторинг полноты данных в источнике
При работе с облачными сервисами распространена практика выгрузки данных через API в промежуточную базу, откуда они потом сохраняются в QVD-файл. Иногда API-механизмы сбоят, что может привести к наличию в промежуточной базе только части данных.
Дальнейшая загрузка этих данных приведет к недостоверной картине в аналитике, и бизнес пользователя начнут вам писать что-то вроде «ВАШ Qlik опять сломался».
Чтобы этого не происходило, можно перед перезаписью QVD-файла сравнивать кол-во записей в нем на данный момент, и кол-во данных в источнике. Если данных в источнике меньше чем X% от того что есть в QVD, то загрузка данных из источника пропускается. При этом, вы можете отправлять себе сигнальные сообщения об этих инцидентах.
Вот например как выглядела перезапись из медленного API данных с нуля. Первое число — сколько записей в QVD, второе — сколько записей в БД.
При этом пользователи продолжают работать с самой полной версией данных, хоть и слегка неактуальной.
Рассылка информации по списку пользователей
Несомненно, вы уже подумали об организации рассылок оповещений через Telegram. Это можно реализовать через скрипт, хотя и не очень удобно — ведь вам придется средствами скрипта сгенерировать полный текст сообщения для каждого получателя. А значит, придется делать кучу джойнов и агрегирований.
Однако для MVP оповещений это весьма удобной вариант, т.к. позволяет гарантированно получить результат в предсказуемые сроки (в отличие от поднятия доп. сервисов, API-интеграций и прочего шаманства).
В общем механика такая: вам сначала нужно будет собрать таблицу, в которой каждая строка — это текст сообщения и ИД получателя, а после пройтись по этой таблице скриптом отправки сообщения через цикл. Структура скрипта может быть примерно такой:
Т.е. у нас есть таблица Messages с сообщениями, где каждое сообщение пронумеровано по номеру строки, в поле M_ID.
Дальше идем в цикле по значениям этого поля, при этом в качестве переменных получателя и сообщения через функцию peek забираем значения этих полей в соответствующей M_ID строке. Внутри Peek() вычитаем единицу от M_ID, т.к. нумерация строк в этой функции начинается с 0.
Заключение
Как видите, с помощью оповещений через Telegram очень легко повысить свою осведомленность о состоянии дел на сервере Qlik. Обязательно используйте эту механику, чтобы узнавать о нужных событиях до того, как вам начнут жаловаться пользователи.
А также, предусматривайте в своих скриптах сценарии реакции на нештатные ситуации.
Источник: bi2business.ru