График платежей с напоминалкой в Telegram с помощью Google Sheets & JavaScript

Меня заинтересовал челлендж: в течение 26 недель откладывать N+100 руб, где N - сумма, вложенная на прошлой неделе. Я решил откладывать деньги по средам, составил такую табличку в Google Sheets

В ячейке А2 я указал дату с помощью формулы =DATE(2022;1;19), чтобы дальше тянуть даты формулой =A2 + 7.

В столбце E2:E вставлены чекбоксы, которые представляют из себя значения булевого типа (TRUE/FALSE). То есть, значение ячейки E2 равно =TRUE, чекбоксы добавлены просто для красоты и удобства (их можно тыкать, чтобы сменить значение).

Вставляются они так: кликаем на ячейку, куда надо вставить чекбокс, открываем менюшку Insert, выбираем Checkbox

Дальше самое интересное, начинаем программировать. Открываем Extensions, выбираем Apps Script.

Определим константы

const SHEET_NAME = "Копилка челлендж"; // Название листа в таблице
const TABLE_RANGE = "A2:E27"; // Диапазон с данными из таблицы без заголовков
const SHEET = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);

Третья строчка обращается к гугловскому классу SpreadsheetApp, чтобы получить Spreadsheet т.е. всю таблицу (со всеми листами), с которой мы работаем. Класс импортировать не надо, все стандартные гугловские классы уже импортированы.

Дело в том, что Apps Script не привязан к Google Sheets, с его помощью можно создавать отдельные приложения, которые смогут обращаться к разным таблицам по ссылке. Именно поэтому надо было вызвать getActiveSpreadsheet(), чтобы получить текущую таблицу.

Далее надо вызвать метод getSheetByName(), передав в него название листа, чтобы получить объект Sheet т.е. лист, где располагается таблица с данными.

Чтобы обратиться к диапазону A2:E27, у Sheet надо вызвать метод getRange(), передав в параметры диапазон. Метод вернет объект Range, у него уже можно получить данные с помощью метода getValues(). Данные будут представлены в виде матрицы m x n (Object[][]), где m - кол-во строк, n - кол-во столбцов диапазона соответственно.

Чтобы удобнее работать с данными, я написал класс Payment

class Payment {
  constructor(date, week, amount, paid) {
    this.date = date;
    this.week = week;
    this.amount = amount;
    this.paid = paid;
  }

  // Считаем, сколько дней осталось до платежа
  daysBeforePayment() {
    let today = new Date();
    return Math.ceil((this.date - today) / 86400000);
  }

  isPaid() {
    return this.paid;
  }

  getAmount() {
    return this.amount;
  }
}

Далее пишем функцию, которая пройдется по записям и найдет текущий платеж.

function getCurrentPayment() {
  // получаем данные из диапазона, как описал выше
  let data = SHEET.getRange(TABLE_RANGE).getValues();
  for (line of data) {
    // собираем строчку в объект Payment. Столбец D2:D пропускаем
    let payment = new Payment(line[0], line[1], line[2], line[4]);
    // line[4] соотвествует столбцу E2:E, который содержит значения булевого типа
    if (!payment.isPaid()) {
      return payment;
    }
  }
}

Логика такая: проходимся по строчкам, находим ту, где не нажат чекбокс (где значение столбца E равняется FALSE).

Стоит заметить, что нам не нужно кастить данные из таблицы в типы JavaScript. Даты из диапазона A2:A уже будут в формате Date, а значения булевого типа уже замапятся на соответствующие типы JavaScript.

Теперь напишем сообщения, которые будем отправлять в телеграм, когда настанет время очередного платежа. Я решил сделать это с помощью словаря Dict<Integer, String>, где ключ - кол-во дней до платежа, значение - сообщение, которое будет отправлено в телеграм.

const PAYMENT_MESSAGES = {
  0: "Напоминалка: сегодня тебе надо внести %amount₽ в копилку для челленджа",
  1: "Напоминалка: завтра тебе надо внести %amount₽ в копилку для челленджа",
}

Прежде чем писать функцию для планировщика, напишем утилиту, которая будет отправлять сообщения в телеграм. Для этого создадим новый скрипт

Новый файл надо расположить выше текущего, чтобы объекты из нового файла были доступны в этом скрипте.

В новом скрипте объявляем константы

const BOT_TOKEN = "TOKEN";
const MY_CHAT_ID = 123456789;

Чтобы бот отправлял нам сообщения, нужно узнать свой chat_id. Например, это можно сделать с помощью бота @username_to_id_bot

Создание бота и получение токена оставлю за рамками этой статьи.

Чтобы не засорять пространство имен, я решил написать класс TelegramBot, после чего ниже создать его инстанс.

class TelegramBot {
  constructor(token) {
    this.token = token;
    this.api_url = `https://api.telegram.org/bot${BOT_TOKEN}/`;
  }

  sendMessage(message) {
    Logger.log(`Sending message to telegram: ${message}`);
    try {
      let isRequestSuccessful = this._apiSendMessage(message);
        if (isRequestSuccessful) {
          Logger.log("Message was sent successful!");
        } else {
          Logger.log("Message wasn't sent");
        }
    } catch (error) {
      Logger.log(`An error occurred while sending the request: ${error}`)
    }
  }

  _apiSendMessage(message) {
    let response = UrlFetchApp.fetch(`${this.api_url}sendMessage?chat_id=${MY_CHAT_ID}&text=${message}`);
    Logger.log(`Telegram response: ${response.getContentText()}`);
    return JSON.parse(response.getContentText())["ok"];
  }
}

const tgBot = new TelegramBot(BOT_TOKEN);

Logger.log() записывает логи, которые потом доступны во вкладке Executions.

UrlFetchApp позволяет отправлять HTTP запросы. Чтобы убедиться, что сообщение отправлено, возвращаем поле "ok" из ответа API (см. Telegram bots API).

Возвращаемся в первый скрипт. Последним штрихом будет функция, которая будет запускаться по расписанию.

function paymentScheduler() {
  let payment = getCurrentPayment();
  let daysLeft = payment.daysBeforePayment();
  // По кол-ву дней до платежа определяем, какое сообщение отправить
  // если в словаре нет такого ключа, переменной будет присвоено значение null.
  let messageToSend = PAYMENT_MESSAGES[daysLeft];
  if (messageToSend != null) {
    // тут шаблон %amount заменяем на значение из столбца C
    messageToSend = messageToSend.replace("%amount", payment.getAmount());
    // tgBot доступен, если скрипт, где он объявлен, располагается выше текущего.
    tgBot.sendMessage(messageToSend);
  }
}

Далее надо запустить скрипт, чтобы приложение запросило у гугл аккаунта права, необходимые для запуска (и за одно протестировать отправку). Надо выбрать функцию для запуска, после чего тыкнуть Run.

После предоставления прав надо настроить ежедневный запуск скрипта. Для этого открываем Triggers в меню слева, после чего справа в углу нажимаем Add Trigger.

Дальше все интуитивно понятно

Выбираем функцию, которая будет запускаться, остальные настройки как на скрине выше. Select time of day - выбираем подходящее время, нажимаем Save.

Trigger должен появиться в списке

Всё готово. Теперь бот будет уведомлять об очередном платеже, а нам нужно не забывать проставлять чекбоксы после оплаты.