Google Таблицы

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

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

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

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

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

Дано: есть список контактов с адресами электропочты в 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 Теперь по щелчку на ячейку с формулой будет формироваться письмо по нужному списку адресатов, исходя из выбранного региона, с заданными темами и телом письма.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Таблица IMPORTXML

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

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

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

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

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

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

Диаграмма Ганта с помощью одной формулы

Друзья, посмотрите на диапазон A1:D12 на скриншоте. Из этих данных можно построить диаграмму Ганта, написав формулу в одной ячейке, сейчас расскажем как.

ПОЕХАЛИ СТРОИТЬ:
1) Мы написали для вас пользовательскую функцию RASPARCE — она превратит диапазон в плоскую таблицу, в которой одна строка будет одним днём отсутствия (смотрите пункт 1. на скриншоте).

2) Дальше используем полученный массив данных в QUERY. В функции с помощью WHERE отбираем тип отсутствия, выбранный в выпадающем списке, группируем по сотрудникам и создаём сводную таблицу кляузой PIVOT, результат — диаграмма Ганта (1. / 2. на скриншоте)

Итоговая формула, которая строит Ганта по дням:
=QUERY(rasparce($A$1:$D$12);"select Col1, count(Col2) where Col2='"&G4&"' group by Col1 pivot Col3")

Или так, чтобы построить по месяцам:
=QUERY( rasparce($A$2:$D$13);"select Col1, count(Col2) where Col2 = '"&G21&"' group by Col1 pivot month(Col3)+1, year(Col3)")

>> Таблица с примером
(файл > создать копию)

Видеоурок: добавляем изображение товара в ячейку Google Таблиц по его названию

По шагам разбираем формулу, которая:
- ищет название товара в поисковике по картинкам
- берет первое изображение
- вставляет его в ячейку Google Таблиц.

https://www.youtube.com/watch?v=9NmZS8PfJuQ

А текстом с формулами мы писали про это здесь.

Собираем данные в виде ФИО+телефон по сотрудникам компании в одной ячейке

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

Многие в такой ситуации вводят всю информацию в одну ячейку руками, благо можно переходить на следующую строку в ячейке с помощью Alt+Enter.

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

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

Выбираем чекбокс — получаем код ответа сервера по URL.

(или о том, как запустить любой скрипт изменяя ячейки на листе)

Привет, друзья. Сегодня у нас скрипт, который вытягивает в Таблицу код ответа сервера (смотрите ГИФ).

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

Скрипт «дождётся» того, что описано в условии оператора IF (буквально — редактирования первого столбца листа «чек») и запустит то, что внутри оператора — передаст в функцию URL страницы и вставит результат в соседнюю ячейку.

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

Таблица с примером (файл — создать копию, чтобы увидеть скрипт)

Чтобы Таблица у вас работала — откройте редактор скриптов, триггеры и поставьте функцию go на триггер редактирования Таблицы.

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

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

>> @google_spreadsheets_chat

Как работать с Таблицами из R

Друзья, привет. Алексей Селезнев записал небольшой урок о том, как работать с API Google Таблиц с помощью языка программирования R.

Из урока можно научиться:
1. Проходить авторизацию для работы с API.
2. Считывать данные с листов Google Таблиц.
3. Создавать Google Таблицы.
4. Создавать новые листы в Google Таблицах.
5. Записывать данные на листы Google Таблиц.
6. Дописывать данные в созданные ранее листы Google Таблиц.

https://youtu.be/no75MyT87l0

Канал Алексея про R, его библиотеки и маркетинг: @R4marketing

Скрипт для работы с Google Контактами
Сохраняем контакты из Таблицы

Друзья, привет! Сегодня — гостевой скрипт от нашего подписчика Дмитрия @Mityayka1. Скрипт синхронизирует Таблицу и Контакты, добавляя в Контакты только те контакты, которых там не было, проверяя по номеру телефона. Принцип работы скрипта - на гифке.

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

Дима — спасибо за полезный материал 👍👌🔥

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

Приходите в наш чат: https://t.me/google_spreadsheets_chat

Дата и время в Google Таблицах

Сегодня хороший день - 43 873.
Во всяком случае, так говорят Таблицы. Мы, люди, называем это 12 февраля 2020 года.

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

В нем:
- Обсуждаем, как отображаются дата и время в Google Таблицах
- Знакомимся с функциями для отображения текущих даты и времени:
Для расчета разницы между двумя датами в разных единицах измерения, включая рабочие дни
Для определения номера года, месяца, дня и недели по ячейке с датой
Для расчета того, какой день наступит по истечению X рабочих дней

https://www.youtube.com/watch?v=Lbj5kh0q5KQ

Отображаем функцией =IMAGE() картинки из Google Диска в ячейке

Друзья, привет! Поменялась ссылка, которую нужно добавлять к ID файла на Google Диске, чтобы вывести изображение в ячейку. Старый вариант не работает, публикуем работающий.

Ссылка теперь такая: "https://docs.google.com/uc?export=download&id="

Целиком формула для ячейки A1, с регулярным выражением, достающим из ссылки ID файла будет выглядеть так:
=IMAGE("https://docs.google.com/uc?export=download&id="& REGEXEXTRACT(A1;"[-\w]{25,}"))

Чтобы все работало и изображение должно быть расшарено для всех по ссылке.

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

P.S. Регулярное выражение [-\w]{25,} матчит ровно 25 символов, каждым из которых может быть либо буква-цифра-подчёрк (\w, word character), либо дефис.

Друзья, вашему вниманию - еще один видеоурок для начинающих!

Рассматриваем функции для расчета сумм, средних и количества в Google Таблицах:
- по всему диапазону (СЧЁТ, СЧЁТЗ, СУММ, СРЗНАЧ)
- с одним условием на число, дату или текст (СЧЁТЕСЛИМН, СУММЕСЛИМН, СРЗНАЧЕСЛИМН)
- с двумя и более условиями (СУММЕСЛИМН, СЧЁТЕСЛИМН, СРЗНАЧЕСЛИМН)

https://www.youtube.com/watch?v=sqBhegW-JYU

Достаём просмотры и лайки статей прямо из веб-страниц. Волшебство =IMPORTXML.

Друзья, сегодня публикуем статью нашего подписчика Михаила. В своём материале на habr он рассказывает о том, как собрал все свои публикации в одной Google Таблицe и с помощью формул подтягивает к каждой публикации количество просмотров, лайков и другую информацию.

В функции =IMPORTXML два аргумента: адрес веб-страницы и запрос Xpath для поиска данных. Чтобы понять, что писать в Xpath, чтобы он вернул то, что вам нужно — можете воспользоваться бесплатным дополнением для браузера ChroPath.

А на гифке — пример, подтягиваем просмотры нашей статьи про ключевые отличия Ms Excel от Google Таблиц.

Статья Михаила на habr
Таблица LynxReport: учёт публикаций

Сапер и морской бой в Таблицах
Уже пошли игры

Друзья, вокруг нашего канала собирается сообщество людей, которые по-настоящему увлечены Таблицами и всем, что в них можно сделать. Поэтому сегодня мы выкладываем целых две игры — Сапёр и Морской Бой, их написал наш подписчик Лео. Он прошёл путь от «ночью заснуть не мог, думал-думал, но не придумал» до «ловите парни, я сделал».

Сапёр
Морской бой

Пока что наш лучший скрипт для сбора. С интерфейсом для ввода.

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

Просто скопируйте Таблицу себе и укажите в диапазоне «A2:C7» ссылки на Таблицы, листы и номера столбцов, которые нужно скопировать.

После запука скрипт в цикле пробежится по Таблицам, соберёт данные в массив и вставит их начиная с десятой строки.

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

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

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

Будут вопросы, пишите в наш чат: @google_spreadsheets_chat

Объединяем любое количество диапазонов Таблицы в одной функции.

Друзья, ДВССЫЛ / INDIRECT в массиве не работает, но очень часто вам нужно сослаться на ряд ячеек с диапазонами и собрать их в один массив. Например, у вас в Таблице двенадцать листов с расходами за разные месяца и вы хотите собрать все строки этих листов с помощью одной формулы, просто выделив диапазоны мышкой.

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

Чтобы функция работала в вашей Таблице - скопируйте код и вставьте его в редактор скриптов этой Таблицы.

Функция отдельно:
function indirects(ranges){
var ss = SpreadsheetApp
.getActiveSpreadsheet();

if(ranges.map){
return [].concat.apply([], ranges.map(function(i){return ss.getRange(i).getValues()}));}
else{return ss.getRange(ranges).getValues();}
}

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