Как-то мы столкнулись с задачей: нужно быстро обеспечить доступ данных на бэкенде, который управляет шаблонами писем. И провернуть все так, чтобы инструмент был понятен рядовому пользователю. При этом с админкой не сильно хотелось заморачиваться.
Если пилить полноценную админку, то нужно писать бэк и фронт, сверху к этому добавлять сортировки, фильтры... Да, на такой случай есть и заготовки, но выходит все равно долго. Почему бы не подцепиться к Google Sheets? Есть и фильтры, и сортировки, права доступа, история изменений — все необходимое. Многие умеют там работать. А еще, это дешевое решение. Попробовали и делимся сценарием простых шагов.
Исходная таблица с данными
Для начала требовалось создать таблицу в Google Drive. Заказчики, которым нужен микросервис, уже предоставили доступ к ней и выглядела она примерно так:
В таблице был список шаблонов писем с колонками:
Название шаблона
Тема письма
Тело письма
...и еще какие-то другие колонки, необходимые заказчикам, но неважные в микросервисе.
Идентификация строчек
Строчки в этой таблице будут постоянно изменяться, добавляться и удаляться, поэтому рассчитывать на то, что, например, во второй строчке будут данные именно для шаблона с id=2 — неверно. Нужно однозначно идентифицировать строчки.
Для идентификации добавим колонку UUID, в которой будет UUID ;)
Чтобы UUID появлялся в ячейке, заведем одноименную функцию. Для этого идем в Tools / Script Editor.
И добавляем такой код:
function uuid() {
return Utilities.getUuid();
}
function onEdit(e) {
if (e.range.getFormula().toUpperCase() == "=UUID()") {
e.range.setValue(Utilities.getUuid());
}
}
Function UUID даст нам возможность использовать в таблице функцию =UUID (наряду с уже имеющимися). Нужно не забыть рассказать про нее редакторам, которые будут вносить изменения в таблицу.
Вот так выглядит ее использование:
Техническая строка
Теперь нужно как-то идентифицировать колонки, данные из которых хотим импортировать в микросервис. В микросервисе уже есть тип данных (сущность) Шаблон письма.
EmailTemplate:
uuid: Uuid
name: string
subject?: string
body?: stringИ данные такого типа должны сохраняться в БД.
Похоже, что колонка «Название» по адресу B2 соответствует свойству EmailTemplate.name и так далее. Это соответствие названий колонок и свойств сущности EmailTemplate можно оформить в виде еще одной строки (A2:2) после уже имеющийся строки с заголовками (A1:1).
Для редакторов она будет известна как «Техническая строка» и лучше заблокировать ее и скрыть от глаз — она нужна только микросервису. В Google Sheets для этого можно воспользоваться встроенными инструментами ограничения доступа на редактирование диапазонов. Причем диапазон ячеек (Шаблоны!A2:2), по которому ее можно найти микросервису, менять нельзя. Иначе все поломается.
Кнопка синхронизация данных
По какому-то событию и каким-то способом данные из таблицы Google Sheets должны попадать в микросервис. Пускай таким событием будет клик по кнопке синхронизации в самой таблице, а способом — http-запрос на endpoint микросервиса, обработчик которого сам сходит по API в Google Sheets и заберет нужные данные.
Кнопка синхронизации может быть покрасивше, а Sync Function интереснее, но в простейшем случае это выглядит так:
// sync
function sync() {
var response = UrlFetchApp.fetch('https://hostname/import/2b7977f7-fd77-4d52-9274-6020e91fc8ca', {
'method' : 'post',
'contentType': 'application/json',
'payload' : JSON.stringify({})
});
Logger.log(response.getAllHeaders());
Logger.log(response.getContentText("UTF-8"));
Browser.msgBox("Синхронизация прошла успешно");
}Простой способ добавить кнопку:
Используя Insert\Drawing, нарисовать кнопку и сохранить её.
Разместить кнопку на листе в удобное и видное место.
Назначить кнопке выполнение скрипта.
Сервисный аккаунт Google Cloud Platform
Для того, чтобы endpoint микросервиса смог загрузить данные из Google Sheets, его обработчику нужно авторизоваться. Например, через API keys. А перед этим нужно разрешить доступ к Google Sheets API и заодно к Google Drive API.
Заводим проект в Google Cloud Platform и заводим сервисный аккаунт. Создаем технического пользователя, у которого будет email примерно такого вида {название-аккаунта}@{название-проекта}.iam.gserviceaccount.com. Он нам понадобится для расшаривания доступа к табличке. В настройках создаем и скачиваем ключ, который будет использоваться в самом микросервисе при авторизации.
Обработчик на бэкенде
Запиливаем HTTP endpoint, который будет вызываться при клике на кнопку синхронизации. При желании туда можно навесить авторизацию или ограничиться UUID-ом в строке запроса, чтобы никто не догадался.
По типу такого:
https://hostname/import/2b7977f7-fd77-4d52-9274-6020e91fc8ca
Затем создаем обработчик endpoint-а на бекенде. На входе у него:
headerRange — диапазон ячеек для технических колонок. Например, Шаблоны1!A2:2
bodyRange — диапазон ячеек для данных. Например, Шаблоны!A3:ZZ
spreadsheetId — идентификатор Google Sheets таблички.
На выходе — данные из таблицы в виде списка объектов, где ключи объекта соответствуют названиям технических колонок, а значения — это соответствующие ячейки в таблице.
Уникальность
В нашем случае требовалось, чтобы при использовании шаблонов у пользователей не возникало путаницы. Для этого необходимо добавить ограничение уникальности для названий шаблонов.
Быстро можно воспользоваться встроенной функцией Data\Data Validation:
В поле Cell range указывается диапазон столбца, который будет проходить проверку на уникальность. В нашем случае это столбец B, значения вставлялись в него, начиная с 4 строки.
В поле Criteria выбрать «Custom formula is»
Формула:
=COUNTIF(ARRAYFORMULA(REGEXREPLACE(B$4:B;"[^а-яА-Яa-zA-Z0-9]";""));"="®EXREPLACE(B4;"[^а-яА-Яa-zA-Z0-9]";""))<2
По желанию можно добавить help validation text для информирования пользователя об ошибке.
Таким образом, можно повесить на столбец B ограничение уникальности.
Применение REGEXREPLACE в указанной выше формуле не обязательно. Нам формула позволила выявлять не только полностью совпадающие значения, но и «похожие», например:
«Пример шаблона»
«Пример шаблона»
«Пример-шаблона».
Если похожие значения превышают 1, то формула запрещает вставку и информирует пользователя об ошибке.
Реализация связи «много-ко-многим» в двух таблицах
Шаблоны в нашем случае добавлялись в различные группы. При этом один шаблон мог встречаться в нескольких группах, к одной группе могло принадлежать несколько шаблонов.
Самый простой способ: создать дополнительный лист с таблицей связей «много-ко-многим». Но такой подход не особо удобен для пользователей. Поэтому мы пренебрегли «нормальностью» и добавили соответствующий атрибут «Группы» в таблице «Шаблоны» (в нашем случае это столбец E соответствующего листа, значения вставлялись в него начиная с 4 строки).
В этом поле названия групп должны быть перечислены через запятую. Очевидно, требовалось реализовать проверку, что пользователь указал существующие группы (названия групп хранились на листе Группы в столбце B, начиная со второй строки).
Для реализации проверки опять был использован механизм Data\Data Validation и следующая формула:
=AND(ARRAYFORMULA(IF(ISERROR(MATCH(TRIM(SPLIT(E4;","));Группы!$B$2:B;0));FALSE;TRUE)))
Здесь было допущение: в названии группы не может быть использован символ «,». Для улучшения пользовательского опыта можно эти же формулы использовать в условном форматировании, чтобы изменить формат ячейки. Например, выделять красным цветом.
Что в итоге
Из главных плюсов подхода, помимо «просто, быстро, дешево», что уже немало:
валидация данных из коробки;
контроль доступа из коробки;
история изменения данных;
другие микросервисы могут также использоваться данные из Google Sheets, не нужно делать API для доступа к данным.
А еще, вы получаете разгруженного разработчика :)
Ложка дегтя в виде минусов:
каждая таблица должна иметь заблокированную техническую строку с именами свойств;
редакторы должны знать про функцию UUID для генерации UUID;
в таблице непросто настроить валидацию данных для связей многие-ко-многим.
Мы реализовали админку на Google Sheets быстро и сейчас планируем раскатить подход на другие проекты. Получилось относительно недорого и со множеством встроенных плюшек. В целом, такое применение Google Sheets подходит для небольших по объему данных с минимумом связей и для прототипирования.