Флажки можно включать и отключать пробелом

Вот что бы мы делали без Бена Коллинса и его статей/рассылок? Точно бы не узнали, что можно тру-фолсить флажки (переключать в выделенных ячейках состояние флажков, то есть менять значения в ячейках с ними с ИСТИНА / TRUE на ЛОЖЬ / FALSE и наоборот) нажатием пробела!

На самом деле нет, мы писали об этом пару лет назад, но не грех и повторить! К тому же в старом посте было про отключение флажков в скриптах.

А вы знали?😺

Извлекаем числа, едим пончики

Коллеги, сегодня два примера, будем извлекать числа из текстовой строки.

"выпито 580 литров молока", вытаскиваем из строки число 580.

Берём функцию REGEXREPLACE, функция позволяет заменять то, что соответствует регулярному выражению на текст.

Заменим все символы, которые не являются цифрами 0-9 (\D+) на пустоту. И умножим результат на 1, чтобы привести его к числу:
=REGEXREPLACE(A1;"\D+"; ) * 1

"выпито 580 литров молока и съедено 10 пирожков", вытащим 580 и 10 в отдельные ячейки.

Сначала, с помощью REGEXREPLACE заменим все "не числа" на пончик, потом с помощью SPLIT (делит строку по указанному разделителю) разделим нашу строку по этому пончику:
=SPLIT(REGEXREPLACE(A2;"\D+";"🍩") ; "🍩")

🍩 уйдут, а числа останутся и встанут в отдельные ячейки, смотрите скриншот :)

Новый интерфейс на Диске

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

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

Новость в блоге Google Workspace:
https://workspaceupdates.googleblog.com/2023/03/refreshed-ui-google-drive-docs-sheets-slides.html

Выводим все даты текущего месяца формулой

Тут понадобится функция SEQUENCE, которая может вернуть последовательность чисел (ищите эту прелесть в Excel 2021, Microsoft 365, Google Таблицах). У нее задается число строк, столбцов, начальное значение и шаг.
Шаг - самое простое, у нас это единица, один день.
Число строк или столбцов будет равно единице - в зависимости от ориентации списка. Если вы хотите список в один столбец (как в примере на картинке), то столбец будет один, а число строк будет определяться числом дней в месяце.
Начало - первое число месяца.

Единицы добыть легко - их есть на клавиатуре, а вот как получить остальное?

Первое число месяца - это дата, у которой день = единица, месяц текущий (функция МЕСЯЦ / MONTH вернет нам месяц СЕГОДНЯшней даты), год текущий (функция ГОД / YEAR тоже от текущей даты, которую мы получим с помощью функции СЕГОДНЯ / TODAY).
Собрать дату из трех кусочков можно функцией ДАТА / DATE. Ее аргументы - номера года, месяца, дня:
ДАТА(ГОД(СЕГОДНЯ());МЕСЯЦ(СЕГОДНЯ());1)

Последнее число месяца - это функция КОНМЕСЯЦА / EOMONTH. Первый аргумент - дата (мы зададим текущую). Второй - на сколько месяцев вперед (положительное число) или назад от нее отступить. То есть EOMONTH(TODAY();-1) - это последнее число предыдущего месяца. Нам нужен текущий, так что без отступов - второй аргумент равен нулю.
EOMONTH(TODAY();0)

Все вместе (для списка в один столбец):
=SEQUENCE(DAY(EOMONTH(TODAY();0)); 1;
DATE(YEAR(TODAY());MONTH(TODAY());1); 1)

PS Можно и начало месяца получить через EOMONTH - как последнюю дату предыдущего месяца плюс 1. Но мы решили заодно рассказать/напомнить и про функцию DATE.
EOMONTH(TODAY();-1) + 1

Совместная работа в Excel (в Microsoft 365)

Можно ли колдовать с данными вместе в режиме онлайн в Excel, как в Таблицах?

Можно, но нужна подписка Microsoft 365 🤯 И тогда доступны представления (sheet views, своего рода индивидуальные фильтры, как filter views в Таблицах), ветки комментариев, работа над книгой в браузере в Excel Online (но там функционал ограниченный, хоть и обновляется регулярно).

Чтобы работать над документом одновременно с другими пользователями, рабочую книгу Excel нужно хранить онлайн. Это предполагает облачное хранилище, а именно OneDrive, OneDrive for Business или SharePoint Online (в перспективе к списку может добавиться Dropbox).

Подробности - в этом фрагменте из книги "Магия таблиц" - ну а ее выход ожидается через несколько месяцев, а пока можно подписаться о уведомление о выходе тут:
https://www.mann-ivanov-ferber.ru/books/magiia-tablic/

PS А еще мы активно занимаемся обновлением нашей книги про Google Таблицы. Внутри обязательно будет материал и про новые формулы и про Google скрипты. Напишем вам, когда она будет доступна.

WB, достаём топ-100 товаров по запросу

Друзья, продолжаем тему про полезное для продавцов на сайте WILDBERRIES.

Недавно в нашем чате поделились ссылкой, с помощью которой можно достать топ-100 товаров по вашему поисковому запросу:
https://search.wb.ru/exactmatch/ru/common/v4/search?appType=1&curr=rub&dest=-1257786&lang=ru&locale=ru&query=запрос&resultset=catalog

Мы взяли эту ссылку и создали таблицу, которая автоматизирует процесс выгрузки.

1) В ячейку B1 вводите свой запрос (например, веселые костюмы).

2) Запускаете скрипт из меню 🔥 и в Таблицу вставятся результаты запроса.

Одна строка - один товар. Сортировки нет, все вставляется так, как было в полученном объекте.

Мы вставляем все поля, кроме "__sort", "ksort", "time1", "time2", "dist", непонятно, что они означают, если что-то полезное - расскажите и мы их добавим.

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

PS Друзья, если знаете другие полезные ссылки для маркетплейсов - напишите в комментариях 😎

Еще наше:
Обращаемся к внутреннему API WB, получаем остатки на складах и немного информации по артикулам

Непечатаемые символы в Документах

Анонс был давно, и вот наконец подвезли!

Вид - Показывать непечатаемые символы
View - Show non-printing characters
Ctrl + Shift + P

И наслаждайтесь - возможно, именно этого вам не хватало в Документах🤠

Вводите собачку (@) в ячейке (без знака "равно", это не формула) - и получите возможность быстро вставить дату - как значение, а не формулы с функциями ТДАТА / NOW или СЕГОДНЯ / TODAY. Выбор такой - любая дата (откроется календарь для выбора даты), сегодня-вчера-завтра.

Можно щелкнуть по варианту в списке - как на скриншоте.
Можно сразу вводить "@сего..." и потом нажимать Tab, как при вводе формул.

Для вставки сегодняшней даты как значение можно использовать и сочетание клавиш Ctrl + ;.

Еще один новый трюк со старой собачкой (@), пользу которого можно обсуждать. Но вдруг кому-то пригодится: валюта и акции. Вводите валютную пару или тикер, и будет вам чип, при наведении курсора на который появится плашка с информацией. Как GOOGLEFINANCE, но не функция. То есть такими значениями можно оформить таблицу, в которой разные валюты или акции, если хочется. Функция GOOGLEFINANCE на такой значение с собачкой может ссылаться.

Редактируем Google Форму прямо из Таблицы!

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

Как всё работает - на гифке, гифка сегодня со звуком.

Что происходит после запуска скрипта в Таблице:
1) в четвертой строке листа "🔥" Таблицы - вопросы, в пятой строке - тип каждого вопроса, под каждым вопросом и типом - варианты ответа, в ячейке "🔥!B2" - форма, которую будем редактировать
2) запускаем скрипт кликом на кнопку и код ищет каждый вопрос по названию в Google Форме
3) если вопрос найден - то скрипт обновляет варианты ответа
4) если вопрос не найден - скрипт добавляет этот вопрос в Google Форму с заданным типом и вариантами ответа

Типы вопросов, с которыми умеет работать скрипт:
CHECKBOX – выбираем ответ на вопрос чекбоксами, можно выбрать несколько вариантов
LIST – выбираем ответ из выпадающего списка
MULTIPLE_CHOICE – выбираем ответ в переключателе

Чтобы воспользоваться Таблицей - копируйте себе

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Суммируем с условием только видимые строки

Просто суммировать (а также считать среднее и еще несколько базовых операций) скрытые строки - это функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

А сумма с условием — это SUMIFS / СУММЕСЛИМН. Эта функция считает не скрытые, а все.

Так что ни одна из них "в чистом виде" тут не поможет: одна будет обрабатывать видимые строки (SUBTOTAL), другая суммировать по условию (SUMIFS). Нам надо совместить. Это можно сделать со вспомогательным столбцом и без, разными способами. Разбираем два варианта в этом посте!

Подробнее про SUBTOTAL писали здесь.

Статья от нашего подписчика Дмитрия @Mityayka1: про таблицы, про карты и про скрипты: dzen.ru/a/ZCDzfftbZQoEyekf

Самое часто встречающееся текстовое значение

Самое частое число — это мода. Функция МОДА / MODE.

А как найти самое часто встречающееся текстовое значение?
Бен Коллинс в своей книге предлагает решение через MATCH (теперь можно и XMATCH, которая просто по умолчанию ведет точный поиск без обязательного третьего аргумента = 0)

Ищем каждое значение в списке и получаем массив из чисел с порядковым номером каждого элемента (номером той строки, в которой впервые встречается значение). Если диапазон включает пустые ячейки, добавьте IFNA, чтобы заменить ошибки.
IFNA(XMATCH(B2:B;B2:B);)

Потом ищем самое частое число в этом диапазоне:
МОДА(IFNA(XMATCH(B2:B;B2:B);))

И извлекаем по этому порядковому номеру текст ИНДЕКСом / INDEX:
=ArrayFormula(ИНДЕКС(B2:B; МОДА(IFNA(XMATCH(B2:B;B2:B);))))

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

Импорт данных из всех Google Таблиц в списке с помощью формул

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

Решение: пробегаемся по массиву ссылок, и импортируем IMPORTRANGE данные из каждого, последовательно собирая в один массив с помощью REDUCE и LAMBDA. В статье — несколько вариантов формул.

https://teletype.in/@renat_shagabutdinov/IMPORT-LAMBDA

Подборка наших постов про onEdit

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

Ниже подборка скриптов onEdit, про которые мы писали на канале:
1. Выводим в ячейку A1 каждого листа дату и время последнего редактирования: https://tttttt.me/google_sheets/157

2. Если редактируете второй столбец и вводите в нём слово "оплачено" - скрипт автоматически вставляет в соседний столбец дату и время https://tttttt.me/google_sheets/171

3. Простой onEdit скрипт для связанных выпадающих списков: https://tttttt.me/google_sheets/313

4. Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) https://tttttt.me/google_sheets/418

5. Небольшая подборка небольших скриптов onEdit: https://tttttt.me/google_sheets/432

6. Храним данные в Properties и обращаемся к ним. Аналог функции ВПР: https://tttttt.me/google_sheets/483

7. Простой onEdit() скрипт переноса строки: https://tttttt.me/google_sheets/533

8. Пытаемся разобраться в механике onEdit: https://tttttt.me/google_sheets/746

9. Создаём в Таблице триггер и скрипт, который не увидят редакторы и владелец: https://tttttt.me/google_sheets/753

10. Логируем все изменения в Таблице: https://tttttt.me/google_sheets/773

11. Простейший onEdit скрипт накопления с комментариями https://tttttt.me/google_sheets/1061

Получаем из ссылки на Google Диске прямую ссылку на скачивание

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

Формула:
= "https://docs.google.com/uc?export=download&id=" & REGEXEXTRACT(A3;"[-\w]{25,}")

Если сделаете ссылку аргументом IMAGE, то получите изображение в ячейке, как на скриншоте.

Еще:
Формулой загружаем изображения из Яндекса / Google

IMPORTXML: загружаем изображения с веб-страницы

Формируем размеры изображений пропорционально их значениям

Аккаунт Google Диска по умолчанию

Друзья, наверняка у многих из вас несколько аккаунтов на Google Диске. Например, рабочий в Google Workspace и личный.

В ссылке на Google Диск есть номер аккаунта: ноль будет у аккаунта по умолчанию, а далее 1, 2 для последующих.

https://drive.google.com/drive/u/0/my-drive
А значит, можно добавить отдельные ссылки на разные аккаунты на панель закладок в браузере, например.

А еще аккаунт по умолчанию — это тот, в котором создаются новые документы, когда вы используете быстрые ссылки
Таблица: sheet.new / sheets.new
Документ: docs.new / doc.new
Форма: forms.new / form.new
Презентация: slide.new / slides.new
Новая встреча в Google Календаре: cal.new
Новая встреча в Meet: meet.new
Заметка: keep.new

Что если вы хотите изменить аккаунт по умолчанию?
Выходите из всех аккаунтов. Далее входите сначала в тот, который сходите сделать аккаунтом по умолчанию. А потом — в остальные.

---
⭐️ Наш курс по Excel, Таблицам и скриптам: тыц
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

1 мая выходной, а дальше?

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

Есть отличный сайт, на котором есть производственные календари за каждый год: http://xmlcalendar.ru/

Чтобы загрузить все выходные дни за 2023 год пишем формулу:
=IMPORTXML("http://xmlcalendar.ru/data/ru/2023/calendar.xml";"//day/@d")

Получилось так себе – смотрите столбец A на скриншоте, причина в том, что Таблицы неправильно преобразовали наш результат.

Добавим формул, чтобы получить правильные даты:
1) поменяем в том, что выводит IMPORTXML точки на запятые
2) загрузим результат в BYROW, LAMBDA и с помощью REGEXEXTRACT достанем месяц (до запятой), день (после запятой), добавим год и превратим функцией в дату.

Итоговая формула:
=ARRAYFORMULA( BYROW( SUBSTITUTE( IMPORTXML(A1;""//day/@d"");""."";"","");
LAMBDA(a; DATE(2023; REGEXEXTRACT(a;""(.+),""); REGEXEXTRACT(a;"",(.+)"")))))

PS В таблице с примером есть вариант, покороче, а на другом листе - получаем все рабочие дни, все выходные и группируем их по месяцам (спасибо Михаилу Смирнову за пример)

API Wildberries – загружаем остатки FBS

Друзья, привет! Мы уже писали о том, как загрузить в Таблицу остатки и цены по любым артикулам, обращаясь к внутреннему API WB. Также писали про то, как выгрузить ТОП-100 товаров по вашему запросу.

Сегодня начинаем серию постов, в которых будем делиться с вами готовыми решениями для работы с API WB.

С помощью сегодняшней Таблицы вы сможете получить свои остатки. Для работы вам нужно получить API-ключ в личном кабинете WB, скопировать нашу Таблицу и вставить API-ключ в редактор скриптов.

Для инфо, что происходит в коде:
1) Формируем ссылку вида https://statistics-api.wildberries.ru/api/v1/supplier/stocks?dateFrom=2023-04-30, где 2023-04-30 – сегодняшняя дата
2) Выполняем GET-запрос этой ссылки, в заголовках отправляя наш API-ключ (в коде за это отвечает функция Request)
3) Получаем результат в виде строки, превращаем в объект, далее в цикле проходим по этому объекту и делаем из него массив, годный для вставки в Таблицу, добавляем перевод заголовков
4) Очищаем лист и вставляем результат, это делаем с помощью sheets api (так получается быстрее)
5) Если на любом этапе проблемы, то скрипт завершится и на листе "ошибки" вы прочитаете ошибку

Таблица с кодом
Документация

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

PPS Дальше расскажем как загрузить заказы, продажи, отчёт по реализации из API, и что-нибудь еще, оставайтесь с нами :)