Работаем со вшитыми (богатыми 😊) в ячейки ссылками
Примеры простых скриптов

Друзья, встречалась вам ситуация, когда приходилось обработать столбец, в котором были вшитые в текст ссылки?

Такие ссылки нельзя отделить от текста с помощью формул, но можно используя скрипты, а именно метод RichTextValue.

Простой скрипт: выделяем диапазон ссылок → запускаем скрипт → получаем три столбца (текст отдельно, ссылка отдельно, формула с ссылкой и текстом):

function fromUrl() {
//определяем активный диапазон, активный лист и забираем значения
const range = SpreadsheetApp.getActiveRange();
const sheet = SpreadsheetApp.getActiveSheet();
const richText = range.getRichTextValues();

//наполняем в цикле массив: текст, ссылка, формула
const values = richText.map(g => [g[0].getText(), g[0].getLinkUrl(), `=HYPERLINK("${g[0].getText()}";"${g[0].getLinkUrl()}")`]);

//вставляем, ориентируясь на границы выделенного диапазона
sheet.getRange(range.getRow(), range.getColumn() + 1, values.length, values[0].length)
.setValues(values)
}

И обратное превращение: выделяем диапазон [текст, ссылка] → получаем вшитые в текст ссылки

function toUrl() {
//определяем активный диапазон, активный лист и забираем значения, создаём пустой массив
const range = SpreadsheetApp.getActiveRange();
const sheet = SpreadsheetApp.getActiveSheet();
const values = range.getValues();
let richText = [];

//наполняем массив в цикле, добавляя вшитые ссылки (RichTextValueBuilder)
values.forEach(g => {
richText.push([SpreadsheetApp
.newRichTextValue()
.setText(g[0])
.setLinkUrl(g[1])
.build()]);
});

//вставляем
sheet.getRange(range.getRow(), range.getColumn() + 3, values.length, 1)
.setRichTextValues(richText)
}

Таблица с кодом, в которой все работает

Код с комментариями отдельно (если сложно читать в посте)

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

Ошибки в формулах Google Таблиц

А-а-а! Формула не работает! Что делать (кроме как сходить к коллеге, у которого больший опыт в Таблицах)?

В сегодняшней статье разбираем:
- какие типы ошибок бывают в Google Таблицах и почему каждая из них может случиться;
- несколько типовых ошибок со сложными формулами;
- несколько простых приемов, которые помогут написать/разобрать/починить сложную формулу;
- функции, которые помогают "отлавливать" ошибки или отображать код ошибки/наличие конкретной ошибки.

Благодарим Михаила Смирнова за помощь в подготовке статьи

Для тех, кто в веб-версии и не видит ссылку: https://teletype.in/@renat_shagabutdinov/fgeGs-67J

Скрипт внутри Google Формы:
— берём последний ответ и отправляем его в Таблицу
— отправляем в телеграм чат
— отправляем на электропочты

Друзья, доброе утро, мы решили писать скрипты везде, даже внутри Google Формы. Там тоже можно. Сегодня у нас пример простого скрипта, который берёт последний ответ (последнее заполнение Формы), добавляет к нему наш текст и отправляет эту строку в Таблицу, которую мы указали в коде.

Целевой скрипт, код с подробными комментариями: https://pastebin.com/WesF5ky3

Чтобы скрипт работал автоматически и запускался при каждом новом заполнении формы (вот как на гифке строка в Таблицу добавилась как только я нажал "отправить") скрипт нужно поставить на триггер: триггеры → добавление триггера → выбираем название целевой функции → при отправке формы.

Добавляем отправку в телеграм / на почту
Вот здесь: https://pastebin.com/V48MQ4kS простые функции отправки в телеграм чат и на емейл. Мы снабдили функции подробными комментариями и даже новичок (если прочитает всё внимательно) сможет разобраться.

Совместив этот код с целевой функцией вы сможете отправлять сообщение о том, что форма заполнена не только в Таблицу, а еще всем заинтересованным товарищам, как на почту, так и в чаты мессенджера. В тексте сообщения, конечно, можно использовать ответы формы, как к ним обратиться – мы показываем в целевой функции.

P.S. Чтобы провалиться в редактор скриптов формы – открываем её на редактирование → жмём кебаб (три точки справа - сверху) → редактор скриптов.

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

Идём на веб-страницу → находим ссылку на нужный EXCEL файл → добавляем в Таблицу и всё это скриптами

Друзья, у Романа Игнатова, старожила и модератора нашего чата вышла очень полезная статья, делимся:

https://ignatov-script.blogspot.com/2019/01/google-i.html

Простые лайфхаки для Google Диска / Документов / Презентаций

Друзья, не Таблицами едиными жив человек!

Мы решили собрать несколько простых приемов и горячих клавиш для тех, кто пользуется Google Диском и Документами/Презентациями.

Сгруппировали их так:
- Google Диск в целом
- То, что работает в Таблицах/Презентациях/Документах
- Документы + Презентации
- Только Документы
- Только Презентации

Пишите в комментариях, какие приемы и клавиши помогают вам — мы добавим их в статью!

Ссылка на статью в телетайп (отличие от предпросмотра – скриншоты не будут искажены): https://teletype.in/@renat_shagabutdinov/J0TKzjV7M

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

Выполняем скрипт больше 6 минут

Максимальная длительность выполнения скрипта в GAS – шесть минут, после чего скрипт завершается по таймауту. Что делать, если скрипт планирует выполняться дольше?

Вы можете поступить как Роман Игнатов – в своей статье он показывает, как переложить долгий скрипт по созданию реестра файлов вашего диска через drive api в html-окно, которое будет формироваться в Таблице. В этом случае сервером выступает ваш браузер и вы не тратите квоту GAS и не зависите от выделенных шести минут.

В коде html вы можете обращаться к Таблицам через sheets api, к Google Диску через drive api, через telegram bot api написать скрипт рассылки многих сообщений на многие контакты.

Пробуйте и делитесь в комментариях тем, что у вас получится.

Статья
Таблица с примером

Импортируем только выбранные (в выпадающих списках) столбцы с данными из другой Google Таблицы

Задача: допустим, у вас есть таблица сбольшим количеством столбцов. И вам нужно выгружать в другую только часть этих столбцов. Хотим выбирать заголовки в выпадающих списках и выводить соответствующие столбцы из исходника в любом порядке.

Просто загрузить данные — дело нехитрое, нужна функция IMPORTRANGE. Но мы хотим выбирать, какие столбцы загружать. Для этого сгодится, например, QUERY — в запросе этой функции мы можем перечислить конкретные столбцы.

Разбираем всю задачу по порядку в статье. https://teletype.in/@renat_shagabutdinov/V_JLtNamY

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
☺️ ДОНАТЫ: реквизиты

Парсим WILDBERRIES из Таблицы: цены* и наличие товара

Недавно на пробежке в лесу (!) я услышал, как человек по телефону рассказывал невидимому собеседнику про то, как торгует на Вайлдберрис. Кажется, онлайн-ретейл с каждым днём всё популярнее, а покупателей и продавцов – всё больше. Мы тоже решили не отставать и внести свою google-табличную лепту, сегодня показываем простой скрипт, с помощью которого вы можете спарсить выбранные страницы с карточками товаров из ВБ и выводить их цену, а если цены нет – что товара нет в продаже. Код маленький и простой, ~20 строк, но они позволяют спарсить много страниц с карточками товаров.

Как работает:
1. копируете таблицу к себе и заполняете столбец A – карточки товаров, которые нужно спарсить
2. снимаете чекбоксы в столбце D
3. дальше скрипт открывает каждую ссылку без чекбокса, забирает код страницы, ищет в нём регуляркой одно из тех мест, где на странице лежит цена, либо кнопка, что товара нет в наличии
4. скрипт вставляет цену, вставляет, есть ли товар в наличии и опускает чекбокс, говоря о том, что строка обработана
5. далее скрипт ищет следующую строку с выключенным чекбоксом и повторяет 1-4 пока не пройдет все строки, либо пока не закончатся выделенные 6 минут, в таком случае скрипт можно запустить еще раз, пока он не пройдет весь список

Таблица с кодом

ДЗ для вас: добавьте другие регулярки, чтобы помимо цены забирать из кода страницы что-то еще – цвета товара, комментарии, оценку, всё, что нужно вам и тоже вставлять это в таблицу. Попробуйте переписать код на fetchall, чтобы обрабатывать не каждую ссылку отдельно, а сразу пачку ссылок, https://developers.google.com/apps-script/reference/url-fetch/url-fetch-app#fetchAll(Object), это должно уменьшить время выполнения скрипта. Отправляйте свой код / таблицы в комментарии, а мы из этого сделаем следующую версию парсера и выложим её на канале.

* цена будет совпадать с той, которую вы увидите в режиме инкогнито

а кто не хочет читать, а хочет просто ссылку на парсер WB

Парсим WILDBERRIES 2 – 1000 карточек товара за 9 секунд!

Друзья, судя по комментариям к предыдущему посту – тема с парсингом ВБ интересна. Поэтому мы решили далеко не уходить и сразу выпустим вторую версию приложения.

Что добавили / изменили:
Ключевое изменение – алгоритм переписан на UrlFetchApp.fetchAll, метод позволяет обращаться к ссылкам пачками, а не открывать каждую отдельно. Скорость существенно возросла – 1000 ссылок с карточками товаров обработались всего за 9 секунд.

Теперь выгружаются метрики:
– наименование
– стоимость
– валюта
– бренд
– заказы!🔥
– доступно!🔥
– оценка!🔥
– отзывы!🔥
– ссылка на изображение

Как скрипт работает сейчас:
1. вставляете ссылки на карточки товаров на лист "URLS", запускаете скрипт
2. скрипт обрабатывает массив и вставляет метрики товаров на лист "RESULT"
3. на листе "LOG" появится запись: ссылок обработано / из них успешно / длит. в секундах

ПАРСЕР wb от @google_sheets 2

=
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
☺️ ДОНАТЫ: реквизиты

Спарклайн для отслеживания прогресса

Любой бар хорош, даже прогресс-бар. Особенно если пройдено больше половины важного проекта и хочется наблюдать, сколько осталось, дабы вдохновляться на дальнейшую работу.

В Таблицах это можно реализовать с помощью спарклайна. Зададим два аргумента: сколько пройдено и сколько нужно сделать всего, исходя из них рассчитаем, сколько осталось - и будем использовать два числа в спарклайне. А в его параметрах зададим параметр charttype = bar (горизонтальная линия) и два цвета. У спарклайна типа bar по умолчанию будут два разных цвета, если "скормить" ему два числовых значения. так что цвета задавать и не обязательно, но если хотите конкретные - нужно.

Получается конструкция:

=SPARKLINE({Сделано ; План-Сделано} ; {"charttype" \ "bar"  ; "color1" \ "обозначение первого цвета"; "color2" \ "второй цвет"})

Например, если план в ячейке B1, а выполненный объем в B2, и мы хотим зеленый цвет для выполненного и оранжевый для оставшегося:

=SPARKLINE({B2 ; B1-B2} ; {"charttype" \ "bar"  ; "color1" \ "green"; "color2" \ "orange"})

Понятно, что можно использовать прогресс-бар и для дат. Например, посмотреть, сколько прошло и осталось рабочих дней (ЧИСТРАБДНИ / NETWORKDAYS в помощь), сколько осталось до конца года (ДОЛЯГОДА / YEARFRAC) etc.

UPD: по просьбам трудящихся добавили в файл с примером второй лист с формулой, которая будет работать для случаев, когда факт уже больше плана. Берем минимальное значение из двух, модуль разницы, а цвета можно выбирать на свой вкус для каждого из 2 вариантов - недо- и перевыполнение. Это с помощью функции ЕСЛИ / IF прямо в массиве с параметрами спарклайна.

Ссылка на файл с примером (хотя вы можете просто скопировать формулу)

Бонус: как сделать прогресс-бар в Excel (ранее -- через спарклайн в Google Таблицах)

1 Выделяем две ячейки – сколько пройдено и сколько осталось.

2 Строим диаграмму (Alt+F1 или через ленту – "Вставка")

3 Выбираем/меняем тип диаграммы – нам нужна "линейчатая с накоплением"

4 Заходим в настройки горизонтальной оси (выделяем ось, Ctrl+1) и устанавливаем максимум по этой оси = 1

5 Удаляем все границы, оси, названия и прочие элементы диаграммы. Меняем цвета, добавляем подписи данных – это по вкусу.

ПОЛЕЗНЫЕ СКРИПТЫ от @GOOGLE_SHEETS

Друзья, за последние полгода мы создали для вас ряд полезных в быту скриптов, давайте вспомним про главные:

=
ОТПРАВЛЯТОР (В ТЕЛЕГРАМ-ЧАТЫ) PDF/JPG 2.0
t.me/google_sheets/643
Вы заполняете в Таблице конфиг отправки и получаете скрипт, который по расписанию отправляет диапазоны из ваших Таблиц в формате PDF/JPG в телеграм-чаты.

=
ОТПРАВЛЯТОР (В ТЕЛЕГРАМ-ЧАТЫ) PNG
t.me/google_sheets/604
Первая версия отправлятора – скрипт конвертирует диапазоны в PNG и отправляет в телеграм-чаты.

=
ТЕЛЕГРАМ-БОТ
t.me/google_sheets/556
Разворачиваете всё по инструкции и получаете Телеграм бота, который записывает всё, что ему пишут в Таблицу, а файлы записывает на ваш Google Диск.

=
СОБИРАТОР (ТАБЛИЦ В ДРУГИЕ В ТАБЛИЦЫ) 4
t.me/google_sheets/661
Это Таблица с конфигом для сбора Таблиц через sheets api. Скажем, вы компания, у которой 20 Таблиц региональных представителей и вам нужно регулярно эти 20 Таблиц собирать в одну, что-то фильтруя и что-то добавляя.

=
🧞‍♂️ПРЕВРАЩАТОР ЛИСТА ТАБЛИЦЫ В XLSX / PDF / CSV
t.me/google_sheets/652
— файл мгновенно скачивается
— либо сохраняется на Google Диск

=
ВЫГРУЖАТОР
t.me/google_sheets/635
Создаём в Таблице реестр файлов из выбранной папки, вложенность - учитываем

=
ДОПУСКАТОР 2
t.me/google_sheets/580
Убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке.

==
ОТПРАВЛЯЕМ СМС ИЗ ТАБЛИЦЫ
t.me/google_sheets/666

=
ПАРСИМ WILDBERRIES ИЗ ТАБЛИЦЫ 2
t.me/google_sheets/686
– 1000 карточек товара за 9 секунд!

===
Канал про Таблицы: @google_sheets
Чат: @google_spreadsheets_chat
Оглавление канала: goo.gl/HdS2qn

Видео полной установки телеграм-бота (для тех, кто делал по инструкции, но не получилось)

Бот, который сохраняет всё: t.me/google_sheets/556

Сделаем Телеграм-бота для вашего бизнеса (и с ТЗ поможем!)

Друзья, у нас вы можете заказать телеграм-бота. Сложного, простого, полезного.

По ссылке – всё, чтобы вы смогли сориентироваться:
– про нашу команду
– наши последние реализованные проекты
– про стоимость и про этапы
– и куда писать для заказа

https://teletype.in/@google_sheets/bot_ex_machina

Подборка больших статей и постов по важным темам

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

Магия пользовательских форматов
https://shagabutdinov.ru/custom_format/
Создаем свои форматы - для чисел, дат, номеров телефонов, темпов прироста и не только

Ошибки в формулах
https://teletype.in/@renat_shagabutdinov/fgeGs-67J
Разбираемся, какие типы ошибок бывают, почему возникают - и что делать

Лайфхаки для работы с Google Диском, Документами и Презентациями
https://teletype.in/@renat_shagabutdinov/J0TKzjV7M
Советы для упрощения работы с основными приложениями Google Диска

Загружаем в прайс-лист изображения товаров из поисковой выдачи Яндекс. IMPORTXML + INDEX + IMAGE
https://tttttt.me/google_sheets/367
Формула, которая относит в Яндекс.Картинки название товара из вашей таблицы и приносит из выдачи собственно картинку с этим (ну если повезет) товаром. Формулы работают - люди отдыхают!

Функции баз данных
https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7
Мощнейшая и простая в применении штука, когда у вас расчеты по большому количеству сложных условий

Дата и время в Google Таблицах
https://www.youtube.com/watch?v=Lbj5kh0q5KQ
Это видеоурок (длится недолго - всего-то 0,00898148😉). Разбираем, как отображаются время и даты и обсуждаем функции СЕГОДНЯ, ТДАТА, РАЗНДАТ, РАБДЕНЬ, ЧИСТРАБДНИ, НОМНЕДЕЛИ.

Функции подсчета и суммирования
https://www.youtube.com/watch?v=sqBhegW-JYU
Еще одно видео - по функциям СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ.

Спарклайны в Google Таблицах. Синтаксис и примеры
https://www.youtube.com/watch?v=ijkGAXCWaKU
Это урок про основы работы со спарклайнами. А вот еще немного магии мини-графиков:

Спарклайн с условием
https://tttttt.me/google_sheets/519

Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)
https://tttttt.me/google_sheets/528

Спарклайн с расчетом данных прямо в формуле
https://tttttt.me/google_sheets/610

Переключение дэшборда между днями и неделями в Google Таблицах
https://teletype.in/@renat_shagabutdinov/4njaw5-Ce
Чудо-юдо функция SEQUENCE и как с помощью нее создавать заголовки для отчетов

Мы с вами уже делали прогресс-бар с помощью спарклайна, а теперь сделаем прогресс-круг.

Порядок действий:
- Строим диаграмму на основе двух ячеек с планом и "выполнено" (выделяем ячейки -> Вставка -> Диаграмма)
- Выбираем в редакторе диаграмм тип диаграммы "Кольцевая"
- Настраиваем размер отверстия в % в разделе "Круговая диаграмма" (на скриншоте 50%)
- Ярлык сектора меняем на "Процент"
- В разделе "Легенда" выбираем "Не выбрано" (чтобы убрать выноски)
- В разделе "Сектор" настраиваем цвета по вкусу для обоих частей
- Щелкаем на ярлыки и делаем шрифт покрупнее.

ГИФКА: https://recordit.co/ss61BuuDqb

Как сделать спарклайн, чтобы отслеживать прогресс: t.me/google_sheets/688