​​Друзья! Если вы работаете с онлайн-кассой и хотите нам немного помочь, то пожалуйста, заполните форму (там всего два вопроса).

​​​​Очищаем текст от HTML-тегов c помощью функции REGEXREPLACE.

Функция с регулярным выражением:
=REGEXREPLACE(A1;"<.+?>";"")

Раздел про жадную и ленивую квантификацию на wiki.

Друзья, идею поста подал Павел Мрыкин, его канал: @eWorker. Спасибо ему!

​​Незадокументированая особенность QUERY.

Друзья, привет! Мы нашли для вас что-то, чего нет в официальной документации. Кляуза skipping n позволяет оставить в диапазоне вывода каждую n-нную строку

Например,
skipping 3 - выводит каждую третью строку
skipping 5 - каждую пятую

Кляуза пишется в конце запроса - как limit (количество строк, которые выведет запрос) и offset (количество строк сверху, которые мы пропускаем и не выводим).

​​SUMIF для нескольких условий в формуле массива. Объединяем диапазоны с помощью амперсанда (&).

(Пример 1) Считаем сумму по нескольким условиям (одна формула будет работать сразу для ряда ячеек):
=ARRAYFORMULA(SUMIF(A2:A15&B2:B15;E2:E4&F2:F4;C2:C15))

(Пример 2) Сумма по нескольким условиям, заполняем табличку с двумя измерениями:
=ARRAYFORMULA(SUMIF(A2:A&B2:B;E8:E13&F7:H7;C2:C))

Напоминаем синтаксис SUMIF: (диапазон условия; условие; диапазон суммирования).

P. S. Если будете внедрять - напишите нам, как будет работать формула на ваших больших датасетах, нам интересно.

​​Пересчет QUERY по запросу. Лайфхак.

Уже несколько раз мы сталкивались с тем, что при загрузке данных из других документов с помощью QUERY и вложенной в нее функции IMPORTRANGE бывают задержки с загрузкой.
Особенно если исходный файл очень тяжелый - обновления в нем могут отразиться лишь через некоторое время.

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

=ЕСЛИ(ячейка с флажком; QUERY(IMPORTRANGE(1)) ; QUERY(IMPORTRANGE(2)))

Главное - чтобы первая и вторая формулы в ЕСЛИ отличались. Например, можете в первом IMPORTRANGE указать диапазон вида A:E, а во втором - A1:E. Результат будет одинаковый. Но при щелчке на флажок данные будут сразу пересчитываться.

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

Поехали, разбираем формулу поэтапно:
1) Объединяем две строки в массив:
={A1:F1\A2:F2}

2) Разворачиваем массив в столбец функцией TRANSPOSE / ТРАНСП:
=TRANSPOSE({A1:F1\A2:F2})

3) Добавляем результат в функцию QUERY, фильтруем от пустых ячеек и разворачиваем обратно с помощью TRANSPOSE / ТРАНСП:
=TRANSPOSE(QUERY(TRANSPOSE({A1:F1\A2:F2});"where Col1 is not null"))

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

P.S. В региональных настройках США, Канады и некоторых других создание массива будет выглядеть так: ={A1:F1,A2:F2}

​​Флажки не обязательно использовать активно, меняя в них значение с TRUE / ИСТИНА (флажок есть) на FALSE / ЛОЖЬ (флажка нет).
Их можно использовать и для индикации, а не изменения.

Пример (из практики, хоть и сильно упрощенный): у нас есть список регламентов, с которыми должен ознакомиться сотрудник. Он ставит свою фамилию, когда знакомится с документом, а в соседнем столбце указывается фамилия руководителя, принявшего зачет на знание регламента.

Добавим формулу, которая будет возвращать значение TRUE / ИСТИНА, когда оба столбца заполнены:
=СЧЁТЗ(B2:C2)=2
(эта формула для примера. Главное - речь о формуле, которая возвращает логическое значение, да/нет, ИСТИНА/ЛОЖЬ)

А далее выделяем диапазон и вставляем флажки (Вставка -> Флажок)
И после этого появятся флажки, которые будут "включены" при выполнении условия. заложенного в формулы. При этом щелчком такие флажки, "под которыми" есть формула, не будут изменяться.

Ссылка на Таблицу с примером.

​​Сегодня гостевой пост:
Изменяем наш Google Calendar с помощью скриптов.

Передаем слово автору:

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

Также и с юбилеями контактов - вроде бы юбилей есть, а сколько времени прошло с этого события непонятно.

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

Решил сам себе упростить жизнь и написал Google Apps Script, который сначала ищет эти события в специальном календаре, который по умолчанию есть у всех и выводит эти события. А зачем на втором шаге рассчитывает возраст для конкретных контактов и уже создает события в основном календаре.

Вот ссылка на скрипт:
https://script.google.com/d/1oDswSXp_UleDXiTFushWnqziHi1Dlkb6x_neteWWKfEnjXttUUIPyRTd/edit?usp=sharing

​​Автоматически создаем фразы по определенным шаблонам в Google Таблицах

Разберем, как с помощью формул в Google Таблицах автоматически создавать фразы по определенным шаблонам, в которые будут подставляться параметры из таблицы.

Статья в Medium.

​​Друзья, у нас есть прайс-лист с наименованиями товаров. К каждому товару мы хотим подобрать картинку и вставить ее в Таблицу. Первый путь - можно загуглить каждое изображение руками, выгрузить и вставить эту картинку в Таблицу. Вариант рабочий, но придется потратить значительное время, особенно, если наименований у вас много.

Второй путь - вы можете вообще ничего не гуглить, а воспользоваться функцией IMPORTXML, обратиться к поиску картинок Яндекса (или Google), достать ссылку на изображение оттуда и с помощью IMAGE превратить его в картинку.

Разберем все по пунктам:
1) Формируем ссылку для поиска по картинкам Яндекса:
"https://yandex.ru/images/search?text="&a4 (a4 - ссылка на ячейку с названием товара)

2) Вытаскиваем из веб-страницы поиска все ссылки на изображения:
importxml("https://yandex.ru/images/search?text="&A4;"//img/@src")

3) Оставляем одну ссылку (например, вторую сверху):
index(importxml("https://yandex.ru/images/search?text="&A4;"//img/@src");2)

4) Превращаем ее в картинку (и это итоговая формула):
=IMAGE(index(importxml("https://yandex.ru/images/search?text="&A4;"//img/@src");2))

Ссылка на таблицу.

​​​​​​Подсчет ингредиентов на лету.

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

Формула целиком: =ARRAYFORMULA(QUERY({{F3:F9}\{VLOOKUP(E3:E9;B3:C6;2;0) * G3:G9}};"select Col1, sum(Col2) where Col2>0 group by Col1"))

Расшифровка каждого этапа - на скриншоте.
Таблица с примером

​​Друзья, привет!

Допустим, у вас две таблицы: одна с приходом товара, а другая с его расходом.
Построим формулу, которая отобразит разницу этих таблиц по каждому наименованию.

1) умножаем таблицу с расходом на -1,
=ARRAYFORMULA(F7:G13*-1)
2) текстовые поля вернут ошибку, поэтому оборачиваем умножение в ЕСЛИОШИБКА (IFERROR), возвращая в случае ошибки исходный диапазон: =ARRAYFORMULA(IFERROR(F7:G13*-1;F7:G13))
3) объединяем таблицы с расходом и приходом: =ARRAYFORMULA({B7:C12;IFERROR(F7:G13*-1;F7:G13)})
4) и наконец: полученный массив используем в QUERY, группируем по первому столбцу, суммируя второй: =QUERY(ARRAYFORMULA({B7:C12;IFERROR(F7:G13*-1;F7:G13)});"select Col1, sum(Col2) group by Col1")


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

Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу

Друзья, привет! Мы подготовили для вас скрипт. Он умеет переносить письма из gmail-аккаунта юзера, запустившего скрипт в Таблицу. 

Одна строка — одно письмо, при переносе скрипт делает перенесенные письма прочитанными в почтовом ящике.

Чтобы заработало, вам нужно:
1. скопировать Таблицу со скриптом
2. открыть редактор скриптов > дополнительные функции Google и включить Gmail API
3. заполнить первые три строки в коде: 
— ID таблицы и название листа, туда будут вставляться письма
— Поисковый запрос к почтовому ящику, например 
'after:2019/05/12' - письма после 12 мая
'after:2019/05/12 and is:unread' - после 12 мая и непрочитанные
4. запустить функцию run(), ее можно запускать как руками, так и ставить в расписание на регулярный запуск (изменить > триггеры текущего проекта в редакторе скриптов), письма по вашему запросу будут добавляться в Таблицу

P.S. Синтаксис поискового запроса: https://support.google.com/mail/answer/7190, созданный запрос можете проверить в веб-интерфейсе почты gmail.

Хорошие новости. Теперь в Таблицах можно обрезать пробелы в строках штатными средствами. Т.н. операция trim доступна из меню https://gsuiteupdates.googleblog.com/2019/05/new-sheets-features-may.html?utm_source=contributor.pw

Действительно, функция удаления дубликатов в Таблицах должна быть встроенной https://gsuiteupdates.googleblog.com/2019/05/new-sheets-features-may.html?utm_source=contributor.pw

​​ARRAYFORMULA для автоматической нумерации списков.

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

За накопление (за увеличение каждого счетчика на 1) отвечает часть ROW(A:A);"<="&ROW(A:A)

1) Нумерация по одному условию (столбцу):
=ARRAYFORMULA(
COUNTIFs(A3:A9;A3:A9;ROW(A3:A9);"<="&ROW(A3:A9))
)

2) Нумерация по двум условиям (столбцам):
=ARRAYFORMULA(
COUNTIFS(D3:D8;D3:D8;E3:E8;E3:E8;ROW(A3:A8);"<="&ROW(A3:A8))
)

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

Сегодня у нас небольшой лайфхак про применение IFTTT для отправки сообщений с данными из Таблиц от нашего читателя Романа Моисеева. Спасибо Роману!

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

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

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

Google Документ с примером

​​Скрипт копирования / фильтрации.

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

Мы написали простой скрипт, который поможет в такой ситуации: 
1) скрипт открывает выбранную Таблицу / лист в ней
2) берет диапазон и фильтрует его по первому столбцу по дате (мы это сделали, чтобы показать, что так можно)
3) очищает лист в другой Таблице
4) и вставляет данные

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

Таблица со скриптом, которую нужно скопировать

P. S. Чтобы скрипт выполнялся регулярно, например, раз в сутки — идем в Правка → Триггеры текущего проекта и задаем расписание запуска.

Друзья, в Medium рассказываем про третий аргумент в QUERY (заголовки).

=QUERY(диапазон данных;"запрос";заголовки)

P.S. И показываем, как вы можете объединить все ячейки внутри каждого столбца