Выдёргиваем аргументы из функций QUERY, IMPORTRANGE, HYPERLINK

Функция FORMULATEXT - извлекаем текстовую строку с формулой

Эта функция выдает вам формулу из ячейки в виде текста. Единственный аргумент – ссылка на ячейку с формулой.

Может пригодиться, чтобы вытащить что-нибудь из формулы. Когда у нас есть текст и нам нужно получить из него фрагмент (например, ссылку из функции ГИПЕРССЫЛКА / HYPERLINK или запрос из QUERY), можно дальше использовать и обычные текстовые функции типа ПСТР / MID, ПОИСК / SEARCH и ДЛСТР / LEN, чтобы с теми или иными костылями извлечь нужное, и регулярные выражения в функции REGEXEXTRACT.

Например, если нам нужно вытащить ссылку из ГИПЕРССЫЛКИ:
=REGEXEXTRACT (FORMULATEXT(ссылка на формулу) ; """(.+?)""" )

FORMULATEXT выдаст текст с формулой из ячейки, а REGEXEXTRACT - извлечет текст из первых кавычек.

А если нужно вытащить из IMPORTRANGE ссылку на источник и превратить ее в кликабельную ссылку в отдельной ячейке:
=HYPERLINK(REGEXEXTRACT(FORMULATEXT(ссылка на формулу) ; """(.+?)""") ; "Ссылка")

Здесь достаем текст формулы из ячейки с IMPORTRANGE (это делает FORMULATEXT), затем из этой формулы достаем ссылку на исходную таблицу (это делает REGEXEXTRACT) и затем превращаем ее в кликабельную (HYPERLINK) и не показываем в ячейке саму ссылку, а только слово "Ссылка" (последний аргумент HYPERLINK).

Какие у вас идеи / примеры применения FORMULATEXT? Что бывает необходимо доставать из формул вам?

Ссылка на примеры
Сделать копию

P.S. В Excel эта функция называется Ф.ТЕКСТ на русском и FORMULATEXT на английском

Пользовательские списки в Excel

Сегодня — небольшой совет для пользователей Excel.

Скорее всего, вы знаете, что можно ввести, например, “Январь” в Excel, потянуть мышкой за маркер в правом нижнем углу ячейки — и список месяцев продолжится. Excel знает порядок месяцев на русском только потому, что есть такой встроенный список — но мы можем добавлять и другие, свои списки. Допустим, у вас в компании есть список из городов/филиалов/фамилий — да чего угодно, что нужно вводить списком и в определенном (не обязательно алфавитном) порядке:

Москва
Санкт-Петербург
Казань
Саратов
Екатеринбург

Следуем по адресу:
Файл - Параметры - Дополнительно - Изменить списки
File - Options - Advanced - Edit Custom Lists

Вводим свой список и нажимаем Добавить / Add.
Все: можно ввести в ячейку первое значение списка и потянуть мышкой.

Таких полезных мелочей в Excel очень и очень много. О десятке (а скорее всего и больше) поговорим уже 5 октября во вторник на открытом уроке в МИФе. Но об этом уже в следующем посте :)

Решим формулами задачу нашего подписчика Андрея

Дано:
На склад завезли 1000 шт. велосипедов. Стоимость хранения 1 шт - 3 рубля / день. Каждый день продается по 10 штук товара. Как посчитать итоговую стоимость хранения товара, к моменту, когда он кончится?

Решение 1
Если товар продаётся по 10 штук в день, то мы его продадим за 100 дней (1000 / 10), сформируем уменьшающийся склад (1000, 990, 980) за каждый день с помощью функции SEQUENCE:
SEQUENCE( 100 [количество строк] ; 1 [количество столбцов] ; 1000 [первое значение] ; -10 [шаг] )

Далее суммируем все строки и умножим на стоимость хранения товара — 3 рубля / шт / день. Итоговая формула:
=СУММ( SEQUENCE(1000/10;1;1000;-10) ) * 3

(на скриншоте a6:a и b6)

Решение 2
Вариант от Михаила Смирнова, без создания массива со складом за каждый день. Сумма арифметической прогрессии и остаток:
=3 * ((10 + MOD(1000; 10) + 1000) * INT(1000 / 10) / 2 + MOD(1000; 10))

(на скриншоте d5:d7)

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

Наш чат, не заходите: @google_spreadsheets_chat

Письмошная — выгрузка файлов и писем по фильтрам

Друзья, сегодняшний пост – коллаборация нашего канала и Дамира Х., передаём слово автору:

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

На листе настройки – прописываем свои фильтры в зелёных ячейках.

На листе "Отчет по выгрузке" будут сохраняться письма, ссылки на скачанные файлы и на папки к ним.

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

При необходимости скрипт getMailsToday() - можно поставить на ежедневный триггер для автоматизации загрузки.

Таблица (делайте копию)

Скрипт отдельно в pastebin

==
★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat

Загружаем из своей почты 20 xlsx-файлов с помощью письмошной →

Итак, пример использования утреннего скрипта: в моей почте 20 писем с темой HP REPORT, в каждом письме – xlsx-файл.

Чтобы выгрузить текст писем в Таблицу, а все файлы на Google Диск —

1) делаем копию нашей Таблицы (если не скопировали еще);
2) заполняем только тему, все остальные фильтры не заполняем;
3) запускаем скрипт!

😮😎

★ Наш чат: @google_spreadsheets_chat

ПЕРЕМЕЩАТОР — продолжаем показывать магию с Google Диском

Привет, недавно в наш чатик пришли с вопросом – есть список ссылок на файлы на Google Диске, как каждый из них скопировать в 10 папок?

Наш сегодняшний скрипт отвечает на этот вопрос, а еще он может не только копировать, но и перемещать файлы.

Инструкция, как пользоваться:
1) копируйте Таблицу со скриптом себе;
2) заполняйте URL файла и URL папки для перемещения (столбцы A и B);
3) выбирайте: копируем или перемещаем (столбец C);
4) запускайте скрипт из меню с 🔥 (при первом запуске скрипт попросит авторизацию – это нормально, этим вы разрешаете вашему скопированному скрипту обратиться к вашему диску – разрешаем);

Скрипт запустится и скопирует / переместит файлы исходя из настроек, попутно заполнит информацию в Таблице (название файла, название папки, результат и время), если вы запустите скрипт еще раз – скрипт обработает строки только без результата copy / move done.

Да и чтобы копировать файлы - у вас должен быть доступ и к файлу и к папке назначения 😉

Перемещатор (посмотреть на код в pastebin)

==
★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat

Developer MetaData

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

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

Есть отличное решение, про которое почти никто не знает, хоть оно и описано в документации – Developer MetaData.

С помощью DM вы можете соотнести (положить, приклеить) пару ключ и значение на Таблицу, на лист Таблицы, на строку или столбец. Далее вы сможете извлекать по заданному ключу как значение, так и саму "коробку" куда вы положили это значение, обращаться к ней и выводить еë текущие аргументы: номер строки / столбца для строки / столбца, название для листа / таблицы.

Примеры кода:
//создаём DM для листа "Лист с таким названием"
function createDM() {
SpreadsheetApp.getActive().getSheetByName('Лист с таким названием')
.addDeveloperMetadata("ключ для листа", "какое-то значение", SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT)
}

//обращаемся к DM по ключу и возвращаем актуальное название листа:
function getDM() {
Logger.log(SpreadsheetApp.getActive()
.createDeveloperMetadataFinder().withKey('ключ для листа').find()[0].getLocation().getSheet().getName());
}

Код в пастебин (пример с гифки тоже там)

PS Обратите внимание:
— DM не проверяет ключ на уникальность и вы можете создать несколько ключей с одинаковыми названиями (в коде же мы обращаемся к первому);
— Есть лимиты - это 30к символов на файл + 30к на каждый лист. Т.е. файл с 3 листами может суммарно сохранить 120к символов. В квоте считается каждый символ и ключа и значения;

Регулярные выражения — компактная памятка от @vitalich

В Таблицах можно использовать регулярные выражения
1) для проверки строки на соответствие выражению (функция REGEXMATCH)
2) для замены части строки на другой текст (REGEXREPLACE)
3) для извлечения строки, которая соответствует регулярному выражению (REGEXEXTRACT)

Для Таблиц в регулярках достаточно знать (или самые часто используемые части RE2):
Классы символов: . \d \D \s \S \w \W \b \B (для \w нужно понимать, что не во всех средах будут матчится не-латинские буквы)
Пробельные спецсимволы: \n \r \t
Границы ^ $
Выбор или-или |
Выбор из набора [ ] и выбор всего, кроме символов из набора [^ ]
Квантификаторы жадные ( * + {n} {n,m} {n,} ) и как управлять их "жадностью" (*? +? {n,m}? {n,}?)
Извлекаемые группы ( )
Неизвелекаемые группы (?: )

Для отладки табличных выражений на regex101 слева надо включать Golang

Таблица с большим количеством примеров

PS 🙋‍♂️ Делитесь в комментариях тем, что помогает вам.

#regexp

Пользовательские форматы: мини-памятка

Еще одна памятка - на этот раз по символам, используемым в кодах пользовательских (Custom) форматов.
Напомним, что свои форматы создаются по адресу:

Формат → Числа → Другие форматы → Другие форматы чисел
Format → Number → More Formats → Custom number format

0 — незначащие нули (отображаются всегда). Если в формате указан один ноль, числа любой разрядности будут отображаться (то есть никакое число не будет «обрезаться»). Но если в формате указано несколько нулей, а числа в ячейках меньшей разрядности — нули все равно будут отображаться.

0% — процентный формат. Соответствует стандартному процентному формату, который формируется в ячейках автоматически при вводе знака процента. Но в пользовательских форматах вы можете его модифицировать — например, добавить разделители групп разрядов, знак "+" для положительных значений или текст.

. (точка) — десятичная запятая.

, (запятая) — запятая после числа отображает числа в тысячах, миллионах и так далее. Одна - тысячи, две - миллионы и так далее. Например, 0,, — число будет отображаться в миллионах.

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

#,## — разделители групп разрядов.

* (звездочка) — звездочка с указанным после нее символом будет заполнять ячейку этим самым символом. Например, 0*- — число и затем повторяющиеся дефисы до конца ячейки.

\* — если нужно показывать в формате именно символ звездочки, то добавляем обратную косую черту.

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

[ColorN] — цвет. Помимо номеров (от 1 до 56) можно указывать цвет и текстом. Например, [Red].

@ — текст в ячейке. Например, @@@ — повторение текста трижды.

"текст" — текст в кавычках будет отображаться в формате. Например, если нужны числа с пробелом и сокращением "руб.": 0" руб."

; (точка с запятой) — Разделяет разные форматы. Есть 2 структуры: можно перечислить форматы (все или некоторые) для положительных, отрицательных чисел, нуля и текста. Указываются именно в таком порядке и через точку с запятой. Либо форматы для одного или двух условий (и всех остальных случаев). Условия задаются в квадратных скобках [Условие1] Формат ; [Условие 2] Формат; формат для остальных случаев.

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

Цвета в пользовательских форматах: табличка-памятка

Напомним, что в пользовательских форматах можно не только задавать несколько стандартных цветов словами:
[Red] [Green] [Blue] [Yellow] [Magenta] [Green] [Yellow] [Cyan]

Но и по номерам:
[Color1] [Color2] ... [Color55]

Выкладываем для вас табличку с кодами всех цветов!
(синяя заливка использовалась только чтобы было видно некоторые "бледные" цвета)

Обновляем Таблицу по ключу скриптом onEdit()

Привет, друзья! Сегодня рубрика – простой скрипт с комментариями по каждой строке кода.

Задача такая: есть таблица, имена и номера договоров, нужно автоматически менять все вхождения номера по выбранному имени.

Вот здесь (скрипт и комментарии), показываем, как задача решается с помощью триггера onEdit(). Как вы помните – onEdit() не надо запускать, он реагирует на все изменения Таблицы.

Таблица с решением (скопировать >)

=
Для заминки: в ячейке "H2" на листе "ЛистЛист" наш чекбокс, отследим с помощью onEdit(), что ячейка изменилась (чекбокс нажали или отжали) и выведем окно со словом "привет":

function onEdit(e) {
//определяем текущий лист, который редактируется и диапазон
let sheet = e.source.getActiveSheet();
let range = e.range;

//проверяем, редактируется ли ячейка "H2" на "ЛистЛист"
if (range.getA1Notation() == 'H2' && sheet.getName() == 'ЛистЛист!') {
//если да — что-то запускаем
Browser.msgBox('привет!')
}
}

Надо бы диапазон проредить.

Друзья, ниже прекрасная статья от Михаила Смирнова. Слово автору:

Дальше вместо ... вставляем диапазон (из таблицы или результат каких-то формул).

Как убрать N первых строк диапазона?

=QUERY(...; "OFFSET N";)

Как убрать N первых столбцов диапазона?

=TRANSPOSE(QUERY(TRANSPOSE(...); "OFFSET N";))

Как убрать чётные строки диапазона?

=QUERY(...; "SKIPPING 2";)

Как убрать нечётные строки диапазона?

=QUERY(QUERY(...; "OFFSET 1";); "SKIPPING 2";)

Как убрать чётные столбцы диапазона?

=TRANSPOSE(QUERY(TRANSPOSE(...); "SKIPPING 2";))

Как убрать нечётные столбцы диапазона?

=TRANSPOSE(QUERY(QUERY(TRANSPOSE(...); "OFFSET 1";); "SKIPPING 2";))

Ну, вы поняли: для столбцов всё то же, что и для строк, только в начале и в конце делаем TRANSPOSE.

Ещё всё это можно совмещать.

Убираем чётные столбцы и строки:

=TRANSPOSE(QUERY(TRANSPOSE(QUERY(...; "SKIPPING 2";)); "SKIPPING 2";))

Оставляем каждую N-ю строку, начиная с первой:

=QUERY(...; "SKIPPING N";)

Оставляем каждую N-ю строку, начиная с M-ой (вместо M-1 надо вставить конкретное число):

=QUERY(QUERY(...; "OFFSET M-1";); "SKIPPING N";)

Полная версия статьи с примерами: telegra.ph/Kak-proredit-kolonki-i-stolbcy-diapazonov-v-Google-Sheets-10-20

Удаляем строки в определенном порядке в Excel

Так, а что делать в Excel, где хоть и появились уже и ФИЛЬТР/FILTER с УНИК/UNIQUE (только в Office 365), но великая QUERY все еще отсутствует?
Вот лайфхак от автора книги "Эффективная работа в Microsoft Excel" Алана Мюррея.

1 В соседнем с данными пустом столбце вводим какой-нибудь символ (например, смайлик) для тех строк, которые нужно оставить, и другой - для тех, что нужно удалить (например, @).

2 Протягиваем это дело с помощью маркера заполнения (в правом нижнем углу ячейки) на весь столбец.

3 Идем в "Найти и заменить" (FInd and Replace) - Ctrl + F или Ctrl + H - в поле "Найти" (Find what) вставляем символ из удаляемых строк и нажимаем "Найти все" (Find All).

4 Нажимаем Ctrl + A, чтобы выделить все найденные ячейки, закрываем окно, нажимаем Ctrl + - (вызов диалогового окна "Удалить") и выбираем "строку" (Entire Row).

Проблемы с IMPORTRANGE()

Уже более 10 дней люди массово жалуются на ошибку "Import Range internal error" при использовании IMPORTRANGE() там, где она раньше работала.

Это проблема на стороне Google.

Известны два костыля для временного использования.

1-й костыль:

Необходимо поменять регистр одной из букв (сделать из прописной строчную, или наоборот) во втором параметре IMPORTRANGE(). Второй параметр - это импортируемый диапазон.

Было так:

IMPORTRANGE("id"; "A:A")

Меняем одну букву A на a:

IMPORTRANGE("id"; "A:a")

Должно отпустить. Но потом проблема может вернуться. На этот случай есть 2-й костыль.

2-й костыль:

Дублируем IMPORTRANGE() внутри IFERROR(). Первый - оригинальный, второй - с изменённым регистром одной буквы. Например, так:

IFERROR(IMPORTRANGE("id"; "A:A"); IMPORTRANGE("id"; "A:a"))

Сколько у вас в формуле IMPORTRANGE() - все надо так оформить.

---

Лучей добра @Vitalich за 2-й костыль.

---

‼️Чтобы проблема была быстрее решена, не поленитесь зайти в созданный для Гугловских разработчиков баг и поставить ему звёздочку. ⭐️ Баг здесь.

‼️Так же проголосуйте за вопрос на Stackoverflow, гугловцы к ним присматриваются. 🔼 Вопрос на SO здесь.

🎓А ранее писали про вариант с флажком, щелчок на который будет инициировать обновление: t.me/google_sheets/362

‼️Чтобы проблема была быстрее решена, не поленитесь зайти в созданный для Гугловских разработчиков баг и поставить ему звёздочку. ⭐️ Баг здесь.

⬆️ Проблемы с IMPORTRANGE()

‼️Так же проголосуйте за вопрос на Stackoverflow, гугловцы к ним присматриваются. 🔼 Вопрос на SO здесь.