Автоматизація дизайну з 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 на дві частини: ліворуч від дефіса "-" та праворуч від нього. Розділені дані будуть у кількох комірках праворуч від комірки з формулою.
![](https://cdn.cases.media/image/wide/77710788-7c95-45f5-9b9a-c6d8cc3efae5.png)
Функція SPLIT, яка розділяє по дефісу
- 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 у кожному рядку видаляємо всі конструкції, перелічені у першій комірці та отримуємо назву орендаря без зайвого тексту, який у навігації буде зайвим.
![](https://cdn.cases.media/image/wide/19798086-a42b-4bcc-80c8-33d6ec6fbc4d.png)
За допомогою SPLIT розділяємо номер, щоб відокремити номер корпусу за входу, наприклад, 1(D), від номера офісу.
![](https://cdn.cases.media/image/wide/847190ae-3e06-4274-98eb-aa4e96ecc3cb.png)
Через PROPER форматуємо кожну назву в однаковій вигляд з першою прописною та іншими заголовними.
![](https://cdn.cases.media/image/wide/bccbb6c1-2196-417b-a877-009f9b3c758e.png)
За допомогою LEFT, MIDDLE, RIGHT виокремлюємо з номерів входів та офісів окремі фрагменти, щоб сформувати номер у форматі 1D-003.
![](https://cdn.cases.media/image/wide/b17f9593-38b1-4ba6-9461-b1ab30517248.png)
![](https://cdn.cases.media/image/wide/1cb43df1-8929-48a2-8a1c-bd8488780526.png)
![](https://cdn.cases.media/image/wide/48a349d0-0eda-4634-abb2-79e8f626afd8.png)
![](https://cdn.cases.media/image/wide/5e1a9aa4-02ec-46f8-8be6-79ae1814edf4.png)
Через CONCATENATE об‘єднуємо номер в одну сутність та додаємо розділювач-тире.
![](https://cdn.cases.media/image/wide/07c19677-3c62-40be-8cc6-023af9081b5f.png)
![](https://cdn.cases.media/image/wide/ae8ff248-c5a9-4935-b0b0-c2181ec3a683.png)
![](https://cdn.cases.media/image/wide/6fc40492-1512-4332-a355-d427dbd290d7.png)
![](https://cdn.cases.media/image/wide/223a2642-c035-4702-9f56-9821eb71fb20.png)
Далі цю таблицю можна експортувати у CSV та під’єднати у макет в InDesign через панель Data Merge. Про це у мене було окреме відео:
JSON
Якщо буде потрібно для сайту сформувати JSON-файл з даними для автодоповнення у формі чи десь ще, наприклад, у генератори покажчиків підказуємо правильні назви вулиць, то так само можна зібрати дані у потрібному форматі у таблиці.
Через CONCATENATE беремо назви вулиць та об‘єднуємо у рядок вигляду {“name”: “Назва”, “type”: “вулиця”}, у два підходи:
- Оскільки сама функція використовує подвійні лапки тож їх не можна просто так додати, то спочатку збираємо рядок з одинарними лапками CONCATENATE("{'name': '";C2;"', 'type': '";D2;"'},").
- Через функцію SUBSTITUTE(G2;"'";"""") заміняємо всі одинарні лапки на двійні.
![](https://cdn.cases.media/image/wide/a9dde28b-a2b0-405d-b749-a6fabf6bf4aa.png)
![](https://cdn.cases.media/image/wide/fd68be72-d6c8-4ae0-82ba-fd8b472c29d2.png)
Консольні команди
Наприклад, потрібно зробити кількадесят макетів з різними 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-коди вставляємо вже куди потрібно.
![](https://cdn.cases.media/image/wide/faeeaead-aa62-486a-9b4d-8d553a7a3313.png)
Консольні команди, зібрані у таблицях
Комбінування та застосування
Іноді в одній таблиці простіше формувати, і консольні команди для створення qr-кодів, і дані для підставляння в InDesign. Далі кілька кейсів, в яких я це застосовував.
ProstoPay
Я робив дизайн комунікації про безготівкову оплату для ProstoPay (оплата у вендингових автоматах телефоном через Apple|Google pay). Спочатку розробив дизайн, а потім треба було розмножити цей дизайн на 1500+ наліпок.
На вході нам потрібен лише номер автомата та формат посилання, яке потрібно закласти у qr-код. Все інше можна сформувати всередині таблиці. Ось як це виглядає:
Магія створення наліпок безготівкової оплати ProstoPay
Peremoga
Наліпки зворотного зв’язку у різних частинах коворкінгу Peremoga. Кожен код веде у налаштованого бота в телеграмі. На вході маємо чіткі посилання та назви локацій. Через таблицю робимо мітку локації, яку потім підставимо на макет, та команди генерації кодів.
![](https://cdn.cases.media/image/wide/4a210440-2818-48eb-95f9-44ebc83db7d4.png)
![](https://cdn.cases.media/image/wide/89161a87-72fa-40f4-9792-7dccac54573d.png)
![](https://cdn.cases.media/image/wide/774b7111-1547-4e1b-943c-56c53702f09d.png)
![](https://cdn.cases.media/image/wide/48bbe63d-1a1d-4986-b366-3a3f55a9ec62.png)
Як наліпки виглядають наживо:
![](https://cdn.cases.media/image/wide/62906605-28e3-4ffa-b86f-ce5ca45fb5d6.jpg)
Наліпки зворотного зв’язку у різних частинах Peremoga Space
Паркувальні таблички
Маючи затверджений дизайн табличок, потрібно було зробити такі макети на купу майданчиків. На вході маємо номер майданчику, з якого можна взяти зону (перша цифра), для кожної зони маємо фіксований тариф та фіксований штраф (тариф×20). Далі також з номера формуємо посилання, з якого потім формуємо команду генерації коду.
Таблиця для табличок (увага на поле формули):
![](https://cdn.cases.media/image/wide/735c71aa-c09c-4d00-80c8-80884bef660e.png)
![](https://cdn.cases.media/image/wide/395ece68-8262-487c-b2d8-5b8c9f7bbf42.png)
![](https://cdn.cases.media/image/wide/ce3856b2-1480-4338-884e-8506dc887950.png)
![](https://cdn.cases.media/image/wide/7e830a5b-7e06-4b8a-b795-a1d579a0af98.png)
![](https://cdn.cases.media/image/wide/0bc4d5b9-1129-4c61-92a5-37b67224518d.png)
![](https://cdn.cases.media/image/wide/acec2097-7df6-487b-8e29-95ec23005154.png)
![](https://cdn.cases.media/image/wide/22073044-c226-487e-9ced-a01eb6df6a4e.png)
![](https://cdn.cases.media/image/wide/036b40a3-3590-468c-a7d9-87039491b16f.png)
У макет додаємо змінні у потрібних місцях:
![](https://cdn.cases.media/image/wide/65012189-bf97-4c53-bdeb-f6135f2245e2.png)
Макет в InDesign
Отримуємо купу макетів, готових до друку.
![](https://cdn.cases.media/image/wide/19e5e36e-6ee5-4eb8-b190-7b7d0e900cbd.png)
У такого автоматизованого створення та підключення qr-кодів є додатковий плюс: якщо все вірно налаштовано, то не можливо буде сплутати коди та підставити не той, не витрачати час на перевірку та нерви, якщо знайдеться ця помилка.
❓
Напевно можна знайти ще більше прикладів використання таблиць в автоматизації роботи або якоїсь окремої її частини. Напишіть в коментарях, які дії ви виконуєте постійно та монотонно та які вас бісять. Думаю, більшість такої роботи має виконувати машина замість людини, а ви тим часом витрачатимете свій час на цікавіші речі або просто відпочинете.
☕️
Якщо вам було цікаво та корисно, можете пригостити мене кавою чи донатом.