3 posts tagged

гугл таблицы

Индекс джедая

У агентства IT-Agency есть план обучения сотрудников — он открыт и опубликован на сайте.

Материалы в плане обучения разбиты по категориям:

I. Система организации работы V. Построение отчётности IX. Управление людьми
II. Тексты VI. Дизайн X. Управление проектами
III. Яндекс.Директ и Google Adwords VII. Разработка XI. Работа с клиентами
IV. Аналитика VIII. SEO XII. Английский

Плюс бонус — XIII. На что подписаться — позволяет сходу получить 27 баллов к индексу.

Всего 290 материалов — чтобы всё изучить потребуется часов 300-400. Прикинул, сколько это займёт времени:

Сколько тратить в день Сколько займёт времени
режим «Илон Маск» три недели
40 часов как на работе всего два с половиной месяца
по часу каждый день 13 месяцев
по часу по вечерам в будни полтора года
по часу только по выходным чуть меньше четырех лет

Понятно, что с наскоку такой объём не взять. Надо разделить слона на кусочки. А чтобы через два дня не забыть, что у меня на кухне разделанный слон, сделал гугл-таблицу со списком материалов и ссылок.

расписал порядок действий, конкретные формулы и приёмы оформления таблицы в заметке «Как спарсить веб-страницу гугл-таблицей»

Обобщенные данные автоматически собираются в аккуратный дешборд. Который можно поставить себе в блог.

Мой индекс джедая — 70 баллов из 290

Посчитать свой индекс джедая

  1. Скопировать таблицу на свой гугл-драйв: файл → сохранить копию.
  2. Сбросить прогресс в чекбоксах → на второй странице выделить столбцы G и H, нажать пробел.
  3. Пройтись по всем пунктам и проставить чекбоксы у изученных материалов.
  4. Поменять имя в А1 на первом листе.
 26   2019   IT-Agency   гугл таблицы   Сделал

Как спарсить веб-страницу гугл-таблицей

У агентства IT-Agency есть план обучения для сотрудников — он открыт и опубликован на их сайте.

Я хочу пройти этот план, для этого решил сделать себе список материалов и ссылок, где я мог бы отмечать прогресс.

Три способа собрать данные в таблицу:

  • Ручной. Можно скопировать всё руками: текст сюда, достать ссылку, поставить рядом, указать номер.
  • Автоматический. Написать парсер на Питоне (как я делал с блогом Бирмана). Но потом придется всё равно как-то копировать данные в гугл-таблицы, где надо будет отмечать прогресс.
  • Полуавтоматический. Как-то сразу получить данные с сайта в таблицы. Видел в гугл-таблицах формулы для импорта HTML.

Выбираю третий вариант — будут парсить сразу в гугл-таблицы.

Парсинг

Через внутреннюю справку ищу подходящую формулу для парсинга. Нахожу IMPORTHTML:

Imports data from a table or list within an HTML page.

Синтаксис формулы: IMPORTHTML(url, query, index). Здесь query это либо список, либо таблица. Удобно для узкой задачи, но у нас текст и заголовки — не подходит.

Смотрю похожие формулы, нахожу IMPORTXML:

Imports data from any of various structured data types including XML, HTML, CSV, TSV, and RSS and ATOM XML feeds.

Синтакс IMPORTXML(url, xpath_query), где

  • url — ссылка,
  • xpath_query — запрос на языке XPath.


Вроде похоже. Для теста запускаю формулу с ссылкой на веб-страницу IT-Agency с параметром по умолчанию:

=IMPORTXML("https://www.it-agency.ru/academy/jedi-plan/","//a/@href")

Получаю такой результат. Формула работает, остаётся отладить детали.

Иду на веб-страничку с планом и изучаю как выглядят нужные мне элементы через хром девтулс:

<h2 class="fix-after-p">I. Система организации работы</h2>

<p>1.1. <a href="/academy/solo_on_the_keyboard">Пройти Соло на&nbsp;клавиатуре</a>, чтобы&nbsp;экономить время, печатать быстро и&nbsp;без&nbsp;ошибок даже&nbsp;в&nbsp;темноте. Будет полезно, если <a href="https://nabiraem.ru/test/">в&nbsp;тесте</a> покажете скорость ниже&nbsp;250 или&nbsp;допустите ошибок больше десяти.</p>

Значит, мне нужны тэги h2, h3, p и атрибут href у тэгов a (адреса ссылок). Лезу в мануал изучать синтаксис языка XPath.

Чтобы убрать из выдачи технические элементы, добавил условие, что элементы должны быть дочерними от div с любым атрибутом. В результате получаю такую формулу:

=IMPORTXML("https://www.it-agency.ru/academy/jedi-plan/",
    "//div[@*]//h2 | //div[@*]//h3 | //div[@*]//p | //div[@*]//a/@href")

И весь текст с сайта, разбитый на колонки в таблице.

Обработка

С парсингом разобрался, теперь есть данные. Пока что выглядит не очень — здесь сложно отслеживать прогресс и изучать материалы. Оформим всё как надо.

Некоторые тэги p разбиты на несколько ячеек — соберём текст в одну ячейку через формулу JOIN.

Но список большой, хочу взять формулу и скопировать на всю длину. Но тогда она заджойнит и ссылки. Разделю на этом этапе текст и ссылки в разные колонки, чтобы потом получить удобный список.

Делаю ячейку True / False с детектором ссылок через простое регулярное выражение:

=REGEXMATCH(G20, "^((http.)|(/.))")

Добавляю в формулу join проверку на условие True / False из ячейки с детектором ссылки:

=if(NOT(C20), join(" ", G20:AL20), "")

В соседнюю колонку собираю отдельно ссылки по тому же условию только инвертированному

=if(NOT(C23), "", G23)

Получаю две колонки: отдельно весь текст и все ссылки

Эти две колонки удобно взять и скопировать на новый лист, чтобы оформить.

Оформление

Копирую колонки с текстом и ссылками на новый лист. Использую копирование, а не автоматические ссылки, чтобы зафиксировать ячейки. Дальше будет оформление и не хочу, чтобы оно потом «поползло». На этом этапе теряется автоматизация — если страница на сайте агентства поменяется, то контент в таблице останется прежним.

Заменил дефолтный шрифт Arial на приятный Proxima Nova, который чем-то похож на шрифт Gerbera на сайте агентства.

Взять фирменный цвет агентства с их сайта — #D82C2C

Добавим условное форматирование цвета заголовков через регекс:

=regexmatch(B1,"^[I, V, X]")

Оформить заголовок, добавить туда ссылку на оригинал.

Через ручной поиск и замену исправить внутренние ссылки типа /academy/… до полных www.it-agency.ru/academy/…

Сделаем ссылки great again — добавим каждой название. Для этого спарсим название страницы по каждоый ссылке:

=join(" — ",IMPORTXML(J14, "//h1"))

Так, например из ссылки http://vsevolodustinov.ru/blog/all/lyubite-kritiku-ischite-kritiku/ получим «Блог Всеволода Устинова — Любите критику, ищите критику»

Из ссылки и спарсенного названия составляем красивую ссылку через формулу HYPERLINK

=hyperlink(J9,IF(iserror(K9), J9,K9))

Получаем название со ссылкой Блог Всеволода Устинова — Любите критику, ищите критику

В таблицах есть какое-то системное ограничение на парсинг, поэтому только часть из 290 формул смогли спарсить название страницы. Для этого в формулу HYPERLINK добавил проверку на ошибку парсинга — если есть ошибка, то название ссылки будет самой ссылкой.

У каждой ссылки ставим чекбокс, чтобы не забыть, что уже прочитал. Чтобы разделить ссылки на открытые и внутренние, я ставил чекбоксы в два разных столбца для каждого типа.

Группируем строки по темам, чтобы было легче перемещаться по всей длине документа.

Скрываем рабочие поля, получаем опрятную страницу.

Дешборд с прогрессом

Теперь eсть список материалов по теме с чекбоксами у каждой ссылки. Хочу видеть общую информацию о прогрессе по каждой области на отдельной странице. Такие страницы со сводкой называют дешбордами (dashboard).

Чтобы понять прогресс по каждому разделу, нужно знать, сколько чекбоксов отмечено — считаем по формуле:

=COUNTIF(G9:G73, True)

Рядом так же считаем столбец с чекбоксами внутренних ссылок. И считаем общее количество ссылок в разделе.

Здесь на каждый раздел пришлось руками ставить границы формулы. Не придумал, как можно автоматизировать.

Делаю новый лист. С помощью SQL-подобного запроса соберём с листа названиями разделов. Повезло, что они пронумерованы римскими цифрами — это упрощает дело. Есть всего три знака, с которых может начинаться искомые строки: ’I’, ’V’ или ’X’.

=QUERY('The Path'!B:B,
  "select B WHERE B IS NOT NULL
    AND (B LIKE 'I%' OR B LIKE 'V%' OR B LIKE 'X%')")

Через VLOOKUP собираем счётчики по каждому разделу

=VLOOKUP($B6,'The Path'!$B:$I,6,FALSE)

Получается 4 числа:

  1. сколько пройдено открытых материалов,
  2. сколько пройдено закрытых,
  3. сколько всего материалов,
  4. и ещё сколько из них открытых.

Чтобы понимать прогресс из этих цифр собираем строку вида «25 / 63» формулой

=sum(F6:G6)&" / "&H6

Рядом тот же прогресс в процентах.

Подбиваем «Итого» — в числах и в процентах.

Выводим прогресс в заголовок: собираем строку из имени и процента.

=A1&" — джедай на "&ROUND(E19*100)&" %"

Получается такой дешборд-сертификат-грамота

 313   2019   IT-Agency   гугл таблицы   Сделал

Календарь жизни

Если взять 90 лет по 52 недели и разложить их на одном листе — получится календарь жизни.

Я заморочился и сделал такой календарь в гугл таблицах. Получилось так:

Календарь помог мне понять пару вещей:

  • увидеть, сколько времени прошло с момента условной самостоятельности — например, выпуска из универа;
  • сравнить, как это время относиться к оставшемуся до «пенсии». В кавычках потому что это условность, просто удобное число для точки отсчета;
  • понять, что жизнь в 30 не заканчивается, а только начинается: впереди ещё лет 30 трудоспособности;
  • просто для интереса переложить годы на возраста. Типа 35 лет мне будет в 2022 году. В голове эти две шкалы были никак не связаны. (Место для шутки, что 90-е были не 10 лет назад).

Ссылка на таблицу. Слева сверху год рождения — можно поставить свой (скопировав себе табличку). Годы переучиваются, ячейки перекрасятся. Автоматизация неидеальная, считает ДР как 1 января ¯\_(ツ)_/¯

Ещё по теме:

  1. Timestripe — красивый проект ребят из студии Лебедева
  2. Рассуждения и иллюстрации Тима Урбана на эту тему.
  3. Восприятие времени и фильм «Прибытие»

UPD: чтобы вносить изменения в таблицу, сохраните копию к себе на диск.

Табличка открыта только для просмотра, иначе все будут редактировать один и тот же календарь.

Файл → сохранить копию → выбрать папку на своём диске
 1 comment    33   2019   время   гугл таблицы   Сделал