Лидеры

В этой статье я расскажу про использование Google Forms и Google Sheets для отслеживания расходов с автоматическими уведомлениями через Telegram Bot. После ввода суммы в форму бот должен отправлять сообщение с текущим статусом бюджета. Все скрипты в этой работе я вставляю во вшитый в в таблицы гугл редактор скриптов, который можно найти во вкладке “Расширения” под названием “Apps Script”. Там же настраиваются триггеры, по которым эти скрипты будут работать. Я уже давно пыталась найти связку между таблицами и ботом в телеграм, так как мне это было необходимо для решения множества задач. То же расписание в университете, к примеру, теперь будет реализовывать куда как легче и приятней, чем поднимать свой сервер для этой задачи и мучиться с тяжелейшей задачей переноса названия предметов в код. Изначально я рассматривала такие сервисы как IFTTT, pushbullet, однако эти капиталистические помойки были моментально посланы мной маршировать в закат сразу после требования оплатить подписку за вебхуки. И вот, о чудо! Я случайно наткнулась на то, что лежало на поверхности: Google App Script. На сегодняшнем простом примере расчета бюджета на каждый день я покажу, каким образом можно настроить взаимодействие между телегой и таблицами. Надеюсь, это будет полезно. Описание пошагово: 1. Сбор данных через Google Forms Сначала мне захотелось сделать так, чтобы каждый день в промежутке между 22:00 и 23:00 мне приходила ссылка на гугл формы с вопросом о том, сколько денег я потратила. Затем, по задумке, эта цифра заносится в специальный столбец и выступает главной переменной в формулах. Появится соблазн грузить данные формы в уже существующую таблицу, но опыт показал, что под данные формы все же лучше создавать отдельную книгу и уже в ней прописывать скрипт переноса данных в целевую таблицу: function onFormSubmit(e) { // Получаем данные из формы var formResponse = e.values; // Массив всех значений формы var sumValue = formResponse[1]; // Предполагаем, что сумма находится в первом столбце формы // Указываем идентификатор другой книги и лист, куда переносить данные var targetSpreadsheetId = 'sheetpost-id'; // ID целевой книги(можно найти в url таблицы между /d/ и /edit var targetSheetName = 'Расчеты'; // Название листа в целевой книге // Получаем целевой лист в другой книге var targetSpreadsheet = SpreadsheetApp.openById(targetSpreadsheetId); var targetSheet = targetSpreadsheet.getSheetByName(targetSheetName); // Находим первую пустую ячейку в столбце C начиная с C3 var targetRange = targetSheet.getRange('C3:C'); // Диапазон начиная с C3 var values = targetRange.getValues(); // Получаем все значения в столбце C var row = values.findIndex(function(row) { return row[0] === ''; // Ищем первую пустую ячейку }); // Если не нашли пустую строку, то используем первую пустую ячейку if (row === -1) { row = values.length; } // Записываем сумму в найденную ячейку targetSheet.getRange('C' + (row + 3)).setValue(sumValue); // Записываем сумму в найденную ячейку } 2. Обработка данных в Google Sheets После того как в нужный мне столбец инкапсулировалось значение, таблица рассчитывает оставшийся бюджет на основе введённой суммы. Формулы вычисляют: Сколько потрачено сегодня. Сколько осталось на текущий день. Сколько можно потратить завтра. Сколько было доступно в начале дня. Пример моего кода: // Функция для отслеживания изменений в таблице function checkForChanges() { var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Расчеты"); // Лист с данными var oldSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("OldValues"); // Лист для хранения старых значений var rangeC = sheet.getRange('C3:C50'); // Диапазон для Value1 var rangeD = sheet.getRange('D3:D50'); // Диапазон для Value2 var rangeB = sheet.getRange('B3:B50'); // Диапазон для Value4 var valuesC = rangeC.getValues(); // Получаем значения из столбца C var valuesD = rangeD.getValues(); // Получаем значения из столбца D var valuesB = rangeB.getValues(); // Получаем значения из столбца B // Получаем старые значения из листа OldValues var oldValuesC = oldSheet.getRange('C3:C50').getValues(); var oldValuesD = oldSheet.getRange('D3:D50').getValues(); var updatedOldValuesC = []; var updatedOldValuesD = []; for (var i = 0; i < valuesC.length; i++) { var value1 = Math.round(valuesC[i][0]); // Значение из столбца C (округляем) var value2 = Math.round(valuesD[i][0]); // Значение из столбца D (округляем) var value4 = Math.round(valuesB[i][0]); // Значение из столбца B (округляем) // Получаем старые значения var oldValue1 = oldValuesC[i][0]; var oldValue2 = oldValuesD[i][0]; // Вычисляем Value3 как сумму value4 + Value2 (округляем) var value3 = Math.round(value4 + value2); // Если значение изменилось, и value1 не равно 0, отправляем уведомление и обновляем старое значение if ((value1 !== oldValue1 || value2 !== oldValue2) && value1 !== 0) { sendTelegramMessage(value1, value2, value3, value4); // Отправляем уведомление в Telegram // Добавляем измененные значения в массивы для обновления updatedOldValuesC.push([value1]); updatedOldValuesD.push([value2]); } else { // Если значения не изменились или value1 = 0, добавляем старые данные updatedOldValuesC.push([oldValue1]); updatedOldValuesD.push([oldValue2]); } } // Обновляем старые значения за один раз oldSheet.getRange('C3:C50').setValues(updatedOldValuesC); oldSheet.getRange('D3:D50').setValues(updatedOldValuesD); } // Функция для отправки сообщения в Telegram function sendTelegramMessage(value1, value2, value3, value4) { var token = "bot-API"; // Вставьте ваш Telegram API токен var chatId = "id"; // Вставьте ваш chat_id, в который должны быть отправлены данные из google // Формируем сообщение для отправки var message = "УВЕДОМЛЕНИЕ ОБ ИЗМЕНЕНИИ\n\n" + "Потрачено денег: " + value1 + "\n" + "Осталось: " + value2 + "\n" + "Можно потратить завтра: " + value3 + "\n" + "Было доступно: " + value4; var url = "https://api.telegram.org/bot" + token + "/sendMessage"; var payload = { chat_id: chatId, text: message }; var options = { method: "post", payload: payload }; UrlFetchApp.fetch(url, options); // Отправка сообщения в Telegram } Описание работы кода Цель функции checkForChanges отслеживать изменения в диапазонах столбцов таблицы Google Sheets, сравнивая текущие значения с сохранёнными старыми данными. При обнаружении изменений отправлять уведомления через Telegram Bot и обновлять список старых значений. Пошаговый процесс: 1. Получение текущих и старых значений Текущие данные считываются из листа "Расчеты" в диапазонах C3:C50 (Value1), D3:D50 (Value2) и B3:B50 (Value4). Старые данные берутся с листа "OldValues" для диапазонов C3:C50 и D3:D50. 2. Сравнение данных Проход по каждой строке таблицы: Значения из текущих и старых столбцов округляются. Если значения в столбцах C или D изменились (и value1 ≠ 0), генерируется уведомление в Telegram с помощью sendTelegramMessage. Если изменений нет, старые значения остаются без изменений. 3. Вычисление Value3 Value3 рассчитывается как сумма Value4 (данные из столбца B) и Value2 (данные из столбца D), результат округляется. 4. Обновление старых данных Изменённые значения записываются в массивы updatedOldValuesC и updatedOldValuesD. По завершении цикла обновляются диапазоны C3:C50 и D3:D50 на листе "OldValues", чтобы зафиксировать последние данные. Функция sendTelegramMessage Формирует сообщение с информацией: Потрачено денег (Value1) Осталось (Value2) Можно потратить завтра (Value3) Было доступно (Value4) Отправляет сообщение через Telegram API с помощью метода UrlFetchApp.fetch. Результат работы кода После внесения изменений в таблицу: Если данные в столбцах C или D изменились, Telegram Bot уведомляет о новых расходах и текущем состоянии бюджета. Обновляются старые значения, чтобы избежать повторных уведомлений. Также отдельно можно добавить отправку сообщения по времени в бот с ссылкой на гугл форму, чтобы не забыть внести расходы за день: function mySendTelegramMessage() { var token = 'bot-API'; // Ваш API Token var chatId = ''; // Ваш chat_id var message = 'Заполни форму! Если не тратилась, введи любое число. Иначе все поломается и умрет в муках (в первую очередь, ты): https://forms.gle/xxxxxxxx'; var url = 'https://api.telegram.org/bot' + token + '/sendMessage'; var payload = { chat_id: chatId, text: message }; var options = { method: 'post', contentType: 'application/x-www-form-urlencoded', payload: payload }; try { var response = UrlFetchApp.fetch(url, options); // Отправка запроса через API Telegram Logger.log('Message sent successfully: ' + response.getContentText()); } catch (e) { Logger.log('Error: ' + e.toString()); } } function createTimeTrigger() { // Создание триггера для выполнения sendTelegramMessage каждый день в 22:00 ScriptApp.newTrigger('mySendTelegramMessage') .timeBased() .everyDays(1) .atHour(22) // Установите нужное время .create(); } Вот так выглядят сообщения в Telegram: P.S. Это весьма сырой вариант подобной реализации, потому важно каждый день вности в форму какое-то число, хотя бы единицу, потому как если внести 0, то изменения не считаются и далее внесение данных может сильно "поплыть".