Автоматизація дизайну з Google Sheets

У контексті автоматизації таблиці можна застосовувати для переформатування (роз’єднання та з’єднання тексту, заміна тексту іншим і так далі) вхідних даних у необхідному вигляді для подальшої роботи у Data Merge в InDesign, JSON для використання на сайтах, консольних командах тощо.

Найцікавіші функції у Google Sheets:

• CONCATENATE — об’єднання текстів з різних комірок та з заданим вручну текстом. Наприклад, CONCATENATE (A1;"-";B1;".svg") візьме текст з A1, додасть дефіс, додасть текст з B1 та в кінці додасть «.svg».
• JOIN — схоже на CONCATENATE, об’єднає два тексти з вказаним розділювачем між ними. Наприклад, JOIN("—";A1;B1) візьме текст з A1, додасть два дефіси та додасть текст з B1.
• SPLIT — розділить текст у комірці на кілька частин по вказаному символу-розділювачу. Наприклад, SPLIT(B3; "-") розділить текст з комірки B3 на дві частини: ліворуч від дефіса "-" та праворуч від нього. Розділені дані будуть у кількох комірках праворуч від комірки з формулою.

• SUBSTITUTE — замінить текст в комірці іншим. Наприклад, SUBSTITUTE (A1;"ex";"pex") візьме текст з комірки A1 та замінить всі комбінації «ex» на «pex».
• REPLACE — замінить певну кількість символів тексту в комірці, яка починається з певної позиції. Наприклад, REPLACE(A1;2;4;"0000") візьме текст з комірки A1, та починаючи з другого символу заміняємо чотири символи на «0000».
• REGEXREPLACE — замінить частини тексту іншим через регулярні вирази (regex). Наприклад, REGEXREPLACE(A1; $E$2; "") візьме текст з A1, подивиться у комірку E2, сприйме текст звідти, як регулярні вирази, та замінить все що підходить на пропонований текст.
• REGEXEXTRACT — видалить частину тексту за допомогою регулярних виразів.
• LEFT, MID, RIGHT — функції, які дозволяють взяти конкретну кількість символів з лівої частини, з середини або з правої частини від тексту. Наприклад, LEFT(A1, 2) візьме перші два символи з A1, MID(A1, 5, 2) візьме два символи з A1 після п’ятого символу, а RIGHT(A1, 2) візьме останні два символи.
• LOWER() — робить всі літери маленькими (lower case).
• UPPER() — робить всі літери великими (UPPER CASE він же ALL CAPS).
• PROPER() — робить всі перші літери великі, а ніші маленькі (Title Case).
• SENTENCE() — робить перша літера речення велика, інші маленькі (Sentence case).
• TRIM — прибирає в тексті всі пробіли.
• GOOGLETRANSLATE(A2;"uk";"en") — переклад тексту в комірці A2 з української uk на англійську en.

Рекомендую переглянути всі функції Google Sheets. Функції в MS Excel можуть відрізнятись, але в ньому можна робити більшість таких самих дій.

CSV та Data Merge

Наприклад, у навігації потрібно створити список орендарів з номерами корпусів та офісів. На вході ми маємо базу орендарів у виглядів excel-таблиці, яка записана у якійсь своїй логіці у базі даних. Можна переформатувати цю таблицю вручну, але такий список постійно оновлюється. І, щоб кожен раз не робити вручну, краще мати інструмент, який зробить це за тебе.

Отримуємо таблицю, в яку можна копіювати дані з бази та отримувати правильно відформатовані дані. Потім ці дані можна підключати з зовнішнього csv-файлу в InDesign (панель Data Merge) або Illustrator (панель Variables).

Далі по кроках.

У окремій комірці через символ «|» перелічуємо конструкції, які треба буде прибирати включно з пробілами, наприклад, «ТОВ », «ФОП » тощо. Потім за допомогою REGEXREPLACE у кожному рядку видаляємо всі конструкції, перелічені у першій комірці та отримуємо назву орендаря без зайвого тексту, який у навігації буде зайвим.

За допомогою SPLIT розділяємо номер, щоб відокремити номер корпусу за входу, наприклад, 1(D), від номера офісу.

Через PROPER форматуємо кожну назву в однаковій вигляд з першою прописною та іншими заголовними.

За допомогою LEFT, MIDDLE, RIGHT виокремлюємо з номерів входів та офісів окремі фрагменти, щоб сформувати номер у форматі 1D-003.

Через CONCATENATE об‘єднуємо номер в одну сутність та додаємо розділювач-тире.

Далі цю таблицю можна експортувати у CSV та під’єднати у макет в InDesign через панель Data Merge. Про це у мене було окреме відео:

JSON

Якщо буде потрібно для сайту сформувати JSON-файл з даними для автодоповнення у формі чи десь ще, наприклад, у генератори покажчиків підказуємо правильні назви вулиць, то так само можна зібрати дані у потрібному форматі у таблиці.


Через CONCATENATE беремо назви вулиць та об‘єднуємо у рядок вигляду {“name”: “Назва”, “type”: “вулиця”}, у два підходи:

1. Оскільки сама функція використовує подвійні лапки тож їх не можна просто так додати, то спочатку збираємо рядок з одинарними лапками CONCATENATE("{'name': '";C2;"', 'type': '";D2;"'},").
2. Через функцію SUBSTITUTE(G2;"'";"""") заміняємо всі одинарні лапки на двійні.

Консольні команди

Наприклад, потрібно зробити кількадесят макетів з різними qr-кодами, в яких закладені різні посилання (номери автоматів, якісь коди, посилання у бот зворотного зв‘язку тощо). І можна ці коди робити через різні онлайн-генератори або плагіни до Photoshop, Illustrator або InDesign. Але якщо потрібно таких кодів зробити 100 чи 1500, краще знайти інший спосіб.

На мою думку, найкращий — генерація кодів через консоль (термінал). Взагалі через термінал можна робити багато цікавих речей, але потрібно трохи розібратись.

Для генерації qr-кодів, якщо встановлений python, є команди qr або segno. Друга мені подобається більше, бо в ній можна задати додатково кольори, розміри самого коду та зберегти одразу в EPS.

Для команди segno потрібно створити рядок вигляду:

segno --border 2 --dark=2D2424 --light=D2C7B9 --output=TradingStalls.eps "https://reherit.org.ua/object/tsentralnyj-rynok-torgovi-ryady/"

Де border, dark та light це налаштування границі та кольорів коду, output — назва файлу на виході, а в лапках потрібне посилання чи інший текст.

Тут так само допоможе функція таблиць CONCATENATE, щоб сформувати рядки команд. Потім ці команди просто копіюємо у консоль та запускаємо: комп‘ютер виконає кожну по черзі. Далі готові qr-коди вставляємо вже куди потрібно.

Комбінування та застосування

Іноді в одній таблиці простіше формувати, і консольні команди для створення qr-кодів, і дані для підставляння в InDesign. Далі кілька кейсів, в яких я це застосовував.

ProstoPay

Я робив дизайн комунікації про безготівкову оплату для ProstoPay (оплата у вендингових автоматах телефоном через Apple|Google pay). Спочатку розробив дизайн, а потім треба було розмножити цей дизайн на 1500+ наліпок.

На вході нам потрібен лише номер автомата та формат посилання, яке потрібно закласти у qr-код. Все інше можна сформувати всередині таблиці. Ось як це виглядає:

Peremoga

Наліпки зворотного зв’язку у різних частинах коворкінгу Peremoga. Кожен код веде у налаштованого бота в телеграмі. На вході маємо чіткі посилання та назви локацій. Через таблицю робимо мітку локації, яку потім підставимо на макет, та команди генерації кодів.

Як наліпки виглядають наживо:

Паркувальні таблички

Маючи затверджений дизайн табличок, потрібно було зробити такі макети на купу майданчиків. На вході маємо номер майданчику, з якого можна взяти зону (перша цифра), для кожної зони маємо фіксований тариф та фіксований штраф (тариф×20). Далі також з номера формуємо посилання, з якого потім формуємо команду генерації коду.

Таблиця для табличок (увага на поле формули):

У макет додаємо змінні у потрібних місцях:

Отримуємо купу макетів, готових до друку.

У такого автоматизованого створення та підключення qr-кодів є додатковий плюс: якщо все вірно налаштовано, то не можливо буде сплутати коди та підставити не той, не витрачати час на перевірку та нерви, якщо знайдеться ця помилка.

❓ 

Напевно можна знайти ще більше прикладів використання таблиць в автоматизації роботи або якоїсь окремої її частини. Напишіть в коментарях, які дії ви виконуєте постійно та монотонно та які вас бісять. Думаю, більшість такої роботи має виконувати машина замість людини, а ви тим часом витрачатимете свій час на цікавіші речі або просто відпочинете.

☕️ 

Якщо вам було цікаво та корисно, можете пригостити мене кавою чи донатом.