Меня заинтересовал челлендж: в течение 26 недель откладывать N+100 руб, где N - сумма, вложенная на прошлой неделе. Я решил откладывать деньги по средам, составил такую табличку в Google Sheets
![](https://habrastorage.org/getpro/habr/upload_files/08e/6f5/c2a/08e6f5c2a7923432cd330d8d93e544cd.png)
В ячейке А2 я указал дату с помощью формулы =DATE(2022;1;19), чтобы дальше тянуть даты формулой =A2 + 7.
В столбце E2:E вставлены чекбоксы, которые представляют из себя значения булевого типа (TRUE/FALSE). То есть, значение ячейки E2 равно =TRUE, чекбоксы добавлены просто для красоты и удобства (их можно тыкать, чтобы сменить значение).
Вставляются они так: кликаем на ячейку, куда надо вставить чекбокс, открываем менюшку Insert, выбираем Checkbox
![](https://habrastorage.org/getpro/habr/upload_files/30e/789/539/30e7895390a774f65509b84fc10c4875.png)
Дальше самое интересное, начинаем программировать. Открываем Extensions, выбираем Apps Script.
![](https://habrastorage.org/getpro/habr/upload_files/4fe/e1b/92a/4fee1b92a398be9917dfcc392b359b37.png)
Определим константы
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₽ в копилку для челленджа",
}
Прежде чем писать функцию для планировщика, напишем утилиту, которая будет отправлять сообщения в телеграм. Для этого создадим новый скрипт
![Как создать новый скрипт Как создать новый скрипт](https://habrastorage.org/getpro/habr/upload_files/50e/eb1/c89/50eeb1c8906f9d7cf4af4dcb6f3607aa.png)
Новый файл надо расположить выше текущего, чтобы объекты из нового файла были доступны в этом скрипте.
![Как поднять файл выше Как поднять файл выше](https://habrastorage.org/getpro/habr/upload_files/cca/729/483/cca729483e6426350a46c2d28f52d55c.png)
В новом скрипте объявляем константы
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.
![](https://habrastorage.org/getpro/habr/upload_files/6a8/4d3/bd0/6a84d3bd0ef4caadd3ceac0a92d71110.png)
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.
![](https://habrastorage.org/getpro/habr/upload_files/27f/7d8/9ca/27f7d89ca6b93563efe5efc1ef120e36.png)
После предоставления прав надо настроить ежедневный запуск скрипта. Для этого открываем Triggers в меню слева, после чего справа в углу нажимаем Add Trigger.
![](https://habrastorage.org/getpro/habr/upload_files/71f/072/7d6/71f0727d6eef7ae2e1a031eec9546b64.png)
Дальше все интуитивно понятно
![](https://habrastorage.org/getpro/habr/upload_files/73a/b1a/fae/73ab1afaeecd0026fda56c4f75e582ae.png)
Выбираем функцию, которая будет запускаться, остальные настройки как на скрине выше. Select time of day - выбираем подходящее время, нажимаем Save.
Trigger должен появиться в списке
![](https://habrastorage.org/getpro/habr/upload_files/f76/cef/a56/f76cefa56ce8d89524f413996f6ab4ff.png)
Всё готово. Теперь бот будет уведомлять об очередном платеже, а нам нужно не забывать проставлять чекбоксы после оплаты.
UPD.
Полный код по ссылке.
Rebeiro1976
а чего по средам, без этого колхоза никак? или память плохая
dsemenko Автор
Была бы хорошая память, не пришлось бы программировать напоминалку ¯\_(ツ)_/¯