Как-то мы столкнулись с задачей: нужно быстро обеспечить доступ данных на бэкенде, который управляет шаблонами писем. И провернуть все так, чтобы инструмент был понятен рядовому пользователю. При этом с админкой не сильно хотелось заморачиваться.

Если пилить полноценную админку, то нужно писать бэк и фронт, сверху к этому добавлять сортировки, фильтры... Да, на такой случай есть и заготовки, но выходит все равно долго. Почему бы не подцепиться к 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("Синхронизация прошла успешно");
}

Простой способ добавить кнопку:

1. Используя Insert\Drawing, нарисовать кнопку и сохранить её.

2. Разместить кнопку на листе в удобное и видное место.

3. Назначить кнопке выполнение скрипта.

Сервисный аккаунт 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]";""));"="&REGEXREPLACE(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 подходит для небольших по объему данных с минимумом связей и для прототипирования.