Додаємо у Google Sheets посилання на всі файли у теці Google Drive

Mon, 25 Dec 2023 12:40:44 +0200

Досліджував автоматизацію у Фігмі (напишу окремо). В ній для автоматизованого додавання картинок, потрібно давати на них прямі посилання. Можна завантажувати файли на FTP або копіювати напряму з сайтів. Але часто це не дуже зручно і якщо ви працюєте з Google Drive та тримаєте там робочі файли, то буває потрібно отримати прямі посилання на картинки у Драйві.

Також буває необхідно скопіювати багато прямих посилань на файли, наприклад, щоб вставити їх у брендбук або відправити клієнту.

Тож я створив простий гугл-скрипт для використання Google Sheets. Працює так:

Додаєте до вашої таблиці скрипт через Extensions/Apps Script, вставляєте код, запускаєте та даєте всі доступи до вашого облікового запису, оновлюєте сторінку з таблицею.
У комірку A1 вставляєте посилання на теку у Драйві (вона може бути як публічною, так і ні), треба взяти посилання з вікна Share, яке містить в собі …/folder/….
У меню з’явиться новий пункт Links to files, в якому можна вибрати один з варіантів.
Скрипт створить заголовки колонок і вставить всі дані у рядки нижче.

Скрипт виводить такі колонки:
- Name — повна назва файлу
- Type — розширення файлу, знаходить по назві файлу

Date — дата та час зміни файлу
Size, KB — розмір у КБ
- URL — посилання на файл (якщо вся тека доступна для перегляду не тільки вам, то всі файли в ній теж будуть доступні)
- URL Download — інший формат прямого посилання на файли, який можна використати, наприклад, щоб вставити картинку у таблицю функцією =IMAGE().

Я зробив кілька варіантів функцій у скрипті:

Вивід всіх файлів по черзі у певній теці.
Вивід окремих типів файлів (картинки, відео, документи тощо).
І окремою опцією вивід всіх файлів у тій теці та у всіх підтеках (додається ще колонка Folder з назвою теки).

Завантажити скрипт або переглянути на Github Gist.

₴

Якщо вам було цікаво та корисно, можете пригостити мене кавою ☕️: картка Монобанку: 4441114450427339 або через сайт.

Автоматизація з Google Sheets

Sun, 16 May 2021 22:50:44 +0200

У контексті автоматизації таблиці можна застосовувати для переформатування (роз’єднання та з’єднання тексту, заміна тексту іншим і так далі) вхідних даних у необхідному вигляді для подальшої роботи у 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”: “вулиця”}, у два підходи:

Оскільки сама функція використовує подвійні лапки тож їх не можна просто так додати, то спочатку збираємо рядок з одинарними лапками CONCATENATE(«{’name’: ’»;C2;«’, ’type’: ’»;D2;«’},»).
Через функцію 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-код. Все інше можна сформувати всередині таблиці. Ось як це виглядає:

Магія створення наліпок безготівкової оплати ProstoPay

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

Таблиця

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

Наліпки зворотного зв’язку у різних частинах Peremoga Space

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

Таблиця для табличок

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

Макет в InDesign

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

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

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