Пользовательские форматы в Google Таблицах

Друзья, посмотрите на эту красоту на скриншоте. В столбце D "Прирост" нет условного форматирования или картинок. Это обычный числовой формат! Но не простой, конечно, а пользовательский. Пользовательские форматы - мощная штука, позволяющая создавать числовой формат специально под ваши задачи с помощью специальных кодов. Например, у формата ячеек на скриншоте код такой:
[Color10]+0% * ▲;[Color53]-0% * ▼

Чтобы разобраться со всем этим - заглядывайте в нашу статью. Там все о пользовательских форматах в Таблицах (Кстати, в Excel они работают практически аналогично):
— где они настраиваются
— какие символы используются в пользовательских числовых форматах
— как настроить отдельные форматы для положительных и отрицательных чисел, нуля и текста
— как добавить цвет в форматы
— как добавить условия в пользовательских форматах - и какие там есть ограничения
— пользовательские форматы для номеров телефонов
— округление до тысяч и миллионов с помощью формата
— выравнивание по десятичной запятой
— заполнение ячейки определенным символом
— функция ТЕКСТ
— пользовательские форматы даты и времени

https://shagabutdinov.ru/custom_format/

Файл со всеми примерами из статьи

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

Отправлятор СМС 2 (+ добавили автоматическую отправку)

Друзья, привет! Когда-то давно мы написали пост про отправку СМС из Таблицы: в нём про сервис, который можно использовать и как формировать правильные ссылки для отправки.

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

Инструкция
Таблица со скриптом

P. S. Спасибо нашему чату за идею.

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

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

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

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

Такие ссылки нельзя отделить от текста с помощью формул, но можно используя скрипты, а именно метод 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