Храним данные в Properties и обращаемся к ним
Аналог функции ВПР

Друзья, Properties Service — это такие специальные отсеки для хранения данных. Вы можете положить туда данные, вы можете к ним обращаться и вы можете их удалить. Есть Properties пользователя, есть скрипта и есть Таблицы. Данные хранятся в парах ключ — значение. Подробнее про службу можно прочитать вот здесь: https://developers.google.com/apps-script/guides/properties?hl=ru.

Сегодня у нас отличный пример того, как можно работать с этой службой — гостевой скрипт от нашего читателя @vitalich. В первой функции Виталич берёт лист Таблицы, создает из него словарь и кладёт в Properties.

Потом, при выборе значения в выпадающем списке активируется вторая функция — триггер onEdit(), который обращается к этому словарю, ищет в нём выбранный ключ и выводит два соответствующих ключу значения в соседние ячейки редактируемой строки.

Сказать спасибо @vitalich-у или задать ему вопрос можно в нашем чате.

>> Таблица со скриптом

Чёрная магия IMPORTXML
Скрипт: вставляем формулу, заменяем ее на значения

Друзья, IMPORTXML — это мощнейшая функция Таблиц, она позволяет обращаться к выбранной веб-странице и доставать из неё то, что соответствует запросу Xpath, который вы ввели. Подробнее мы рассказывали здесь.

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

Мы подготовили для вас Таблицу, которая решает эту задачу.
Работает так:
1. Вводите в диапазон «A3:C» ссылки на страницы, Xpath, отмечаете чекбокс и нажатием на 🚀 запускаете скрипт
2. Скрипт вставит функции с этими аргументами друг под другом и сразу же заменит их результат на значения. К каждой строке добавит дату и время.

Такая механика тоже не позволит пользоваться IMPORTXML бесконечно, но точно позволит вам загрузить больше результатов, чем если бы вы просто вставляли формулы. Адаптируйте решение под свои задачи =)

Таблица IMPORTXML

Сортировка и фильтрация по цвету в Google Таблицах

Ура! Заработала еще одна опция, которой не хватало в Таблицах. Еще одна вещь, которую теперь можно делать без установки дополнений.

Теперь данные можно сортировать и фильтровать по цвету заливки ячейки или по цвету текста.

1. Ставим фильтр любого типа ("Фильтр", т.е. обычный фильтр для всех пользователей или "Фильтры", т.е. filter views для индивидуального пользования)

2. Щелкаем на кнопку фильтрации на нужном столбце -> Сортировать по цвету / Фильтровать по цвету

Фильтр по цвету можно совместить с фильтром по условию. То есть искать бирюзовые ячейки с числами меньше 500, например.

В срезах пока осталась только фильтрация по условию.

Опция будет доступна и для пользователей GSuite, и для пользователей с обычными бесплатными аккаунтами Google Диска.

Появилась пока не у всех - в некоторых личных аккаунтах опции пока нет.

Готовим антисептик по рекомендации ВОЗ

Друзья, всемирная организация здравоохранения выпустила памятку о том, как изготовить антисептик в домашних условиях:
Guide RUS
Guide ENG

Наш подписчик Евгений Михайлов создал в Таблице небольшой калькулятор, который позволяет по этой памятке рассчитать, в какой пропорции нужно смешивать все ингредиенты. Для заполнения используются ячейки B10 или B21 на листе "formulation".

На листе "preparation" - этапы изготовления антисептика по шагам, с картинками.

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

Таблица с калькулятором | Наш чат

Дано: есть список контактов с адресами электропочты в Google Таблицах (ваши партнеры, клиенты, сотрудники и т.д.) и каким-либо параметром (например, регионом).

Задача: одним щелчком формировать в почтовом клиенте письмо на выборку адресатов из этой таблицы. Например, всем клиентам из города N. Или всем сотрудникам отдела M.

Решение
1 С помощью FILTER отбираем только нужные нам адреса по условию - например, по одному региону, как в нашем примере.
FILTER(столбец с адресами; столбец с условиями=ячейка с выпадающим списком с выбором условия)

2 С помощью JOIN собираем все отобранные адреса в одну текстовую строку - через запятую.

JOIN(",";FILTER(...))


3 С помощью ГИПЕРССЫЛКА/HYPERLINK формируем ссылку на отправку письма. Чтобы все адреса были в скрытой копии и адресаты не видели ящиков друг друга, используем ссылку вида "mailto:?bcc=". Туда подставляем полученные на предыдущих шагах адреса. Второй аргумент ГИПЕРССЫЛКА - то, как ссылка будет отображаться в ячейке. Можно ввести любой текст, который вам хочется там видеть. Например, "Рассылка".

=ГИПЕРССЫЛКА("mailto:?bcc="&JOIN(",";FILTER(...));"Рассылка")


4 Можно пойти еще дальше и добавить тему письма - например, брать тот самый параметр туда или указать фиксированный текст. Для этого справа к полученной ссылке добавляем &"&subject=" и после знака "равно" вводим тему или ссылаемся на ячейку, откуда ее нужно брать (в примере берем регион в тему)
и добавить текст письма - для этого после темы добавим также &"&body=Добрый день!", чтобы в формирующемся письме сразу появлялся текст "Добрый день!".
Здесь мы видим по два амперсанда & - первый нужен, чтобы соединить текстовые фрагменты ссылки, это оператор внутри функции, а второй используется непосредственно в получающемся тексте, чтобы отделять параметры bcc, subject и body.

5 Теперь по щелчку на ячейку с формулой будет формироваться письмо по нужному списку адресатов, исходя из выбранного региона, с заданными темами и телом письма.

Файл с примером

Про функцию FLATTEN
(объединяем диапазоны в один список)

Друзья, активный участник нашего чата @akanat прислал пост про функцию Таблиц FLATTEN. Функции официально нет, в документации она не описана, но тем не менее она работает.

Канат в своем посте показывает, как сделать объединение диапазонов с помощью классических формул Таблиц, а потом - как всё решить с помощью тайной функции FLATTEN.

Передаем слово Канату: https://teletype.in/@google_sheets/u2Q0MOZ1T

(А за наводку на функцию спасибо Саше Иванову)

Наш лучший скрипт для сбора данных V2.0

Привет, сейчас идёт карантин. Что можно сделать дома? Пить винишко. А еще можно оптимизировать свои Таблицы.

Наша команда подготовила для вас Таблицу со скриптом загрузки данных через sheets api. Это Таблица - апгрейд Таблицы, которую мы сделали для вас в январе.

Из новшеств:
— блок "настройки" вынесен на отдельный лист
— можно выбрать листы, куда будут загружаться данные
— столбцы копируемых таблиц можно перечислять в любом порядке и повторять
— добавлен комментарий, он будет добавляться к каждой строке выгруженных данных
— добавлен отступ - количество строк с данными, которые копироваться не будут

Как применить скрипт? Представьте - у вас сорок менеджеров по продажам. Все менеджеры ведут свои отдельные Таблицы в одном формате. Используя наш скрипт вы сможете настроить регулярную выгрузку всех сорока Таблиц в одну.

Таблица со скриптом

Если будете копировать код к себе, то зайдите в редактор скриптов > ресурсы > доп. функции Google и включите Google Sheets Api

Оглавление нашего канала

Друзья, у нашего канала есть оглавление. Разумеется - в Google Таблице. Новые посты попадают наверх. Реализовано всё очень просто — есть другая Таблица с планом публикаций, мы переносим строки из неё с помощью функций IMPORTRANGE. Потом добавляем QUERY и функцией фильтруем от пустых строк и сортируем по дате поста (см скриншот).

На сегодня в оглавлении 267 постов про Таблицы и скрипты, в большинстве случаев есть Таблица с живым примером, которую можно скопировать к себе и разобраться.

Есть вопрос про Таблицы — начните с оглавления нашего канала 🤗

>> Оглавление

>> Предложить свою тему

Таблица / приложение для голосования от @google_sheets

Друзья, тема голосования этой весной очень актуальна. Голосование по поправкам в Конституцию перенесли, но вы можете провести своё собственное голосование. К примеру — избрать старосту или выбрать лучшего сотрудника. Ну того, кто работая на удалёнке успевал не только играть и смотреть сериалы, а еще что-то делать.

Мы создали для вас специальную Таблицу для голосования. Внутри три листа — кандидаты, лог, результат.

За голосование отвечает скрипт, чтобы он заработал нужно выбрать одного или нескольких кандидатов и запустить скрипт нажатием на "vote". Скрипт проверит, голосовали ли вы раньше (сверив ваш адрес электропочты с адресами проголосовавших на листе лог) и если не голосовали — запишет ваш голос на лист.

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

>> Таблица со скриптом

Наш чат 👨‍💻

Функции баз данных

Функции БД - мощный инструмент. Они есть и в Excel и в Google Таблицах и хороши для работы с несколькими условиями, с наборами условий.

Подготовили для вас статью про эти функции и про то, как ими пользоваться.

Наглядно и с примерами: https://teletype.in/@renat_shagabutdinov/4lVaI_Pj7

Считаем сумму по каждой строке / столбцу в формуле массиве

Среди наших читателей есть любители всё сделать с помощью одной формулы. Сегодняшний пост — как раз для них.

Посчитаем сумму по каждому столбцу тремя способами — c помощью DSUM, SUMIF и QUERY.

Самый короткий способ. Функция баз данных DSUM / БДСУММ:
=ARRAYFORMULA(DSUM(A1:F6;{2\3\4\5\6};G2:G3))

- первый аргумент, "данные": указываем всю таблицу;
- второй аргумент, "столбец суммирования": задаём руками массив {2\3\4\5\6} либо вставляем формулу SEQUENCE(1;5;2) или SEQUENCE(1;COUNTA(B1:F1);2);
- третий аргумент, "критерии": условий фильтрации у нас не будет, поэтому мы можем указать либо всю исходную Таблицу еще раз, либо две пустые ячейки;

Добавляем ARRAYFORMULA и превращаем нашу конструкцию в формулу массива.

>> Таблица с примерами

Вариант с помощью SCAN и LAMBDA (добавлено в 2022): t.me/google_sheets/1006

C помощью SUMIF

=ARRAYFORMULA(SUMIF(if(COLUMN(B2:F2);ROW(B2:F6));ROW(B2:B6);B2:F6))

>> Таблица с примерами

И решение с помощью QUERY

=INDEX(QUERY({B2:F6};"select sum(Col"&join("), sum(Col";SEQUENCE(1;5))&")";0);2)

>> Таблица с примерами

Программисты, проектировщики, видеомейкеры… все, чья работа требует долго высматривать мелкие детали на мониторе, любят темные интерфейсы. Не знаю почему для электронных таблиц все еще нет официальной темной темы, но мне иногда помогает расширение для браузера Care Your Eyes. Работает почти на всех сайтах, не только в Google Docs, но на недорогих мониторах качество картинки может получиться не очень. Единственное неудобство в том, что в темной теме меняются не только цвета интерфейса, но и форматирования (заливки, шрифта, границ), но временно разгрузить глаза часто помогает, особенно при работе на большом мониторе, с которого просторы гугл щитс безжалостно светят белым.

Ссылка на аддон

Текст прислал наш подписчик Ринат (https://www.facebook.com/planer484)

Оглавление |

ЗАКАЗАТЬ РАБОТУ |

Поддержать нас |

Наш курс на Skillbox |

Обучение, Ренат: @r_shagabutdinov

>>Оглавление нашего канала

Сравнение списков в Таблицах
Способ 1 — функция QUERY и JOIN

Друзья, умение сравнивать списки в Таблицах — это то, что вам точно пригодится. Этим постом мы стартуем серию постов про способы сравнения.

На каждом скриншоте по шагам будет разобран один случай — вывод имён из списка А, если они есть в списке Б.

Помимо этого в Таблице с примерами для каждого способа сравнения есть еще три формулы (ячейки H3:E3):
* для вывода имён, которые есть в списке А и которых нет в Б
* есть в Б и нет А
* для вывода пересекающихся имён из обоих списков

Чтобы разобраться, как работают формулы — копируйте Таблицу с примерами себе и читайте объяснения по каждому шагу. Что-то будет не получаться — приходите к нам в чат, поможем.

Формула, которая разбирается на скриншоте:
=QUERY({B3:B10};"where Col1='" & JOIN("' or Col1 = '";C3:C8)&"'";0)

>> Таблица с примерами

Сравнение списков в Таблицах
Способ 2 — функции FILTER / REGEXMATCH

Формула, которая разбирается на скриншоте:
=FILTER(B3:B10;REGEXMATCH(B3:B10; join("|";C3:C8)))

>> Таблица с примерами

Вдогонку, про функции с регулярными выражениями

Как говорят у нас в деревне, два поинта:

1) Точное совпадение. Регулярное выражение Вася|Петя найдет строку "Вася", строку "Петя", а еще строку "Вася и его частушки под гармонь", так как поиск будет производиться по подстроке.

Для того, чтобы искать только точное совпадение — добавляем перед каждым элементом ^ и $ после.

Например, соберём из С1:D1 регулярное выражение:
="^"&JOIN("$|^";C1:D1)&"$"

// ^Вася$|^Петя$

2) Числа в диапазоне данных. В функции REGEXETRACT / REGEXMATCH / REGEXREPLACE вы можете передавать только текстовую строки. Хотите поработать с числами — предварительно приведите их к тексту, для этого возьмите диапазон в =TEXT(диапазон/ячейка;"@")

Формула со скриншота
=FILTER(A1:A5; REGEXMATCH(TEXT(A1:A5;"@") ; "^"&JOIN("$|^";C1:E1)&"$"))

Таблица

^ $ не обязательно добавлять перед каждым элементом — достаточно добавить по границам перечислений:
^(Вася|Петя)$

@vitalich, спасибо!