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

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

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

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

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

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

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

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

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

Последние новости Google Таблиц

— появилась возможность посмотреть историю редактирования любой ячейки
— в новую Google Форму теперь можно переносить вопросы из старой (новость)
— можно открывать XLSX-файлы (и другие форматы документов Office) и изменять прямо на Google Диске (новость)

​​Заменяем выбранное значение на другое. Сниппет.

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

function sheet_replace(range, search, replace) {
return range.map(function(row) {
return row.map(function(cell) {
return cell == search ? replace : cell
})
})
}

А еще мы добавили наш чат к каналу, теперь вы можете перейти в него просто нажав кнопку "Discuss".

​​Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT.

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

А еще можно заменять и проверять, не только доставать. Напоминаем, в Google Таблицах есть три функции для работы с регулярными выражениями: REGEXEXTRACT для извлечения, REGEXMATCH для проверки соответствия и REGEXREPLACE для замены текста.

Таблица сделана силами нашего чата, спасибо: @IT_sAdmin, @mrykin, @avazbek88, Макс Махров.

Если вы хотите добавить примеры своих выражений — просто напишите мне (@namokonov) в лс.

​​ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)

Если в исходной таблице искомые данные стоят правее тех данных, которые вам нужно возвращать с помощью ВПР / VLOOKUP, можно воспользоваться сочетанием функций ИНДЕКС / INDEX и ПОИСКПОЗ / MATCH (так обычно это решается в Excel).

Но в Google Таблицах можно еще проще решить эту проблему, пересобрав таблицу в правильном порядке в массив внутри ВПР.
Напомним, что для горизонтального объединения массивов их нужно взять в фигурные скобки и поставить между ними обратную косую черту (либо запятую, для других региональных настроек):

=ВПР(A2;{'Прайс-лист'!B:B \ 'Прайс-лист'!A:A};2;0)

А еще с помощью этой конструкции можно создать массив только из двух столбцов (столбца поиска и столбца, который будет возвращаться) и не использовать внутри ВПР таблицу из двадцати столбцов, что может работать медленно.

Скрипт. Прочитаем, что написано на стикерах.
OCR в Google Docs

Недавно нам потребовалось распознать текст, который был написан на нескольких сотнях стикеров. В Google Документах доступна функция OCR (optical character recognition), ей мы и воспользовались.

Мы нашли скрипт, немного модифицировали его и выкладываем в Таблице с примером.

Работает он так:
1) На листе "config" задаете ID папки на Google Диске с PNG / JPG / PDF и задаете лист, на который будет сохранятся результат. ID папки - часть URL адреса после folder/. Папка должна находиться на вашем Google Диске, так как скрипт будет создавать в ней Doc файлы.

2) Скрипт проходится по всем изображениям в заданной папке, сохраняя их копию в Doc. Внутри Doc будет распознанный текст.

3) Дальше этот текст, ID и URL Документа скрипт вставит в Таблицу.

Чтобы скрипт заработал — активируйте Drive Api в редакторе скриптов (Ресурсы → Дополнительные функции Google)

Таблица со скриптом (файл → создать копию)
Папка со стикерами (сделайте копию, если захотите попробовать скрипт на нашей папке)

Про OCR:
https://habr.com/ru/post/114670/
https://habr.com/ru/post/97173/

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

Как сделать такой формат?

Нужен следующий код:
[<2000][Red]#,# * 🔥;[>3000][Green]#,# * 🔥🔥🔥;[Blue]#,# * 🔥🔥

Расшифруем его:
[<2000] - условия. У нас есть формат для чисел меньше 2000, для чисел больше 3000 и для остальных случаев.
[Red] - цвет шрифта
#,# - числовой формат с разделителями разрядов
🔥 - текст, который мы хотим видеть в ячейке помимо числа (вы можете взять любой другой символ или написать разные слова для разных случаев)
* - выравнивает число слева, а текст справа

Файл с примером - по ссылке:
https://docs.google.com/spreadsheets/d/1H_pCMtfCtZZUfLee893v7-Y6-lAGotjWOKdnkAUq9iQ

​​Поговорим еще немного про пользовательские форматы чисел

— добавляем к числам «штуки», не делая строки текстовыми
— создаем свой формат для положительных и отрицательных чисел
— отдельный формат в зависимости от величины числа
— добавляем цвет
— работа с тысячами

Пользовательские форматы

Друзья, мы нашли новое пространство для рекламы.

По ссылке — видео, Маша танцует Frame Up (смесь движений Jazz Funk, Strip, High Hells) в футболке нашего канала «make @google_sheets great again». Спасибо Маше за такую любовь к Таблицам, подписывайтесь на ее Инстаграмм.

Шер, ретвит и Google Sheet!

P. S. А завтра опубликуем скрипт для связанных выпадающих списков, будем брать их из кеша Таблицы, теперь списки могут быть огромными и будут работать быстрее.

Друзья, на нашем канале уже более 200 постов.
Иногда мы и сами не можем сразу вспомнить, о чем писали 🙂

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

Если вы только начинаете работать в Таблицах, вам будет полезно:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel": https://habrahabr.ru/post/331360/

— Советы по оптимизации Таблиц: https://tttttt.me/google_sheets/143

— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц): https://www.youtube.com/watch?v=HOTpjAqdalc

— Видеоурок по фильтрам и режиму фильтрации: https://www.youtube.com/watch?v=kHN5sIFLIjw

— Про виды доступа к документам: t.me/google_sheets/341

— Совместная работа с фильтрами: https://tttttt.me/google_sheets/337

Про функции:
— Функция FILTER. Список условий выбираем диапазоном прямо с листа: https://tttttt.me/google_sheets/102

— Памятка по синтаксису QUERY / FILTER / SUMIFS: https://tttttt.me/google_sheets/283

— Создаем красивое расписание групповых занятий: https://tttttt.me/google_sheets/325

​— Google Форма + Google Таблица для проведения тестирования: https://tttttt.me/google_sheets/292

— Формула, которая достает изображения из поисковой выдачи Яндекса и добавляет их в прайс-лист: https://tttttt.me/google_sheets/367

— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой): https://tttttt.me/google_sheets/355

Про скрипты:
— Простой скрипт копирования / фильтрации (поможет, когда IMPORTRANGE перестает работать): https://tttttt.me/google_sheets/384

— Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу: https://tttttt.me/google_sheets/374

— Скрипт, отправляем письма на электропочты из диапазона, который вы выделяете: https://tttttt.me/google_sheets/212

— И еще один скрипт для отправки писем (отправляем письма только на те адреса, на которые еще не отправляли): https://tttttt.me/google_sheets/339

Важный скрипт. Связанные выпадающие списки из кэша.

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

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

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

Чтобы перенести код списков в свою Таблицу — скопируйте все скрипты и заполните лист скрипта parameters.

Будут вопросы — пишите в наш чат.

有難う

Нас попросили инструкцию попроще, попробуем:
1) скопировать надо все листы скрипта кроме mock_catalogue, вставлять можно на один лист;
2) скопировали - идем на лист parameters и заполняем на каком листе будет работать скрипт, на каких строках и из какого листа возьмёт справочник;
3) всё! перезагружаем таблицу через F5, скрипт должен заработать;

Новый тип диаграммы в Google Таблицах - "Сводка"

Хотя скорее это похоже на некую микроскопическую панель показателей, вернее - показателя.
Она показывает значение показателя и его отклонение от исходного/целевого/планового etc. значения

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

Может пригодиться для создания аккуратных дэшбордов (панелей показателей).

Ленивый скрипт отправки напоминаний.

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

⏩⏩ Смотрите скриншот и открывайте Таблицу с примером.

Скрипт можно поставить в расписание регулярного запуска, например, ежедневно в 10 утра, через меню Изменить > Триггеры текущего проекта.

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

Вам достаточно написать для этого формулу и объединить результат до одной ячейки. А скрипт уже эту ячейку будет регулярно отправлять.

Еще раз - Таблица с примером

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

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

В таком случае можно создать внешний скрипт (не привязанный к этой таблице) и удалить в проблемной Таблице и листе строки или даже лист целиком.

1) идем в наши скрипты: https://script.google.com/home (кстати, там вы можете увидеть все скрипты вашего аккаунта)
2) создаем новый скрипт:

function clear(){
//определяем таблицу и лист в ней
var ss = SpreadsheetApp.openByUrl('url');
var sheet = ss.getSheetByName('имя листа');

//удаляем строки после 100
var rows = sheet.getMaxRows();
if(rows-100>0){sheet.deleteRows(100, rows)}

//или удаляем лист целиком
ss.deleteSheet(sheet);
}

3) всё, проблемная область удалена, ваша таблица снова доступна и вы можете в ней работать

4) а еще в нашем чате уже больше 1000 человек, присоединяйтесь и вы

IF внутри условия FILTER, отбираем ВСЕ значения или конкретное

Друзья, мы нашли полезный хинт в функции FILTER: если в условии написать диапазон условия = диапазон условия, то функция выведет все строки из диапазона вывода.

Можно использовать для выпадающих списков в интерактивных дашбордах, которые вы строите с помощью FILTER.

=FILTER(A2:B25;A2:A25=IF(E1="ВСЁ";A2:A25;E1))

P. S. А вот так в FILTER можно написать ИЛИ:
=FILTER(A2:B25;(A2:A25<>"Майер")+(B2:B25<500000))

Ищу работу аналитиком / продактом в Москве.

Последнее место работы — Яндекс.Такси 🚕, аналитик по ценообразованию: репрайсил тарифы в городах МО, определял города в балансе и придумывал процедуры для них, а еще сделал тысячу разных калькуляторов.

— Перед этим работал аналитиком в компаниях Marriott, Lotte, Автомир

— Совместно с Ренатом уже три года ведем этот канал про Google Таблицы и написали про них книгу: https://www.mann-ivanov-ferber.ru/books/google-tabliczyi-eto-prosto/

— Частенько бываю продакт оунером, мы разрабатываем для внешних заказчиков софт на C++/Python/GAS и строим ERP и CRM-системы в Таблицах (отзыв нашего клиента Григория)

Пишите, пообщаемся: @namokonov

CASE внутри функции QUERY

Коллеги, внутри QUERY на данный момент нельзя использовать доступную в большинстве SQL-диалектов функцию CASE.

Но — вы можете с помощью IF и массива создать виртуальный столбец, присоединить его к своим данным и уже эту конструкцию сделать диапазоном QUERY. И, например, группировать по новому столбцу.

1) Определяем, начинаются имена на «B» или нет (скриншот наверху):
=ARRAYFORMULA(IF(A:A<>"";IF(LEFT(A:A;1)="В";"Имена на В";"Другие имена");""))