Автоматизация отправки уведомлений в Telegram Bot через Google Forms и Google Sheets

В этой статье я расскажу про использование 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, то изменения не считаются и далее внесение данных может сильно "поплыть".