Google Таблицы

Небольшой пост от Михаила:

Мы тут вам подготовили табличку с примером, как можно перевести одни символы в другие. Например сделать транслитерацию.

У примера есть и практическая польза – можете проверить написание своих имени и фамилии в загранпаспорте и понять, надо ли бежать исправлять.

Таблица тут.

Сортируем "внутри" текста

Дано: есть текстовая строка с именами/названиями и числами.
Надо: отсортировать и снова получить текстовую строку.

То есть из "Бусинка 37, Лемур 22, Штрудель 75" сделать "Штрудель 75, Бусинка 37, Лемур 22"

Формула:

=JOIN(", ";BYROW(sort(WRAPROWS(SPLIT(A1;", ");2);2;0);LAMBDA(x;JOIN(" ";x))))

Сначала разделяем на отдельные значения — и числа, и имена (SPLIT по пробелу и запятой).
Потом делаем двумерным диапазоном (таблицей) с помощью WRAPROWS и сортируем по столбцу с числами (SORT).
Объединяем с помощью JOIN — сначала в пределах каждой строки (BYROW) имя и число через пробел, а потом эти значения уже в один текст через запятую еще одним верхним JOIN'ом.

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

1. WB, загружаем отчет по реализации и остатки FBO t.me/google_sheets/1143

2. WB, загружаем расходы в Таблицу по рекламе ВБ t.me/google_sheets/1460

3. WB, загружаем топ-100 товаров по запросу t.me/google_sheets/1116

4. WB загружаем характеристики и описание товаров из ВБ t.me/google_sheets/1404

5. WB Достаём ссылки на фотографии товара из карточки WB t.me/google_sheets/992

6. WB, формируем ссылки на изображения товара t.me/google_sheets/1310

7. API OZON ИЗ GOOGLE ТАБЛИЦЫ t.me/google_sheets/583

📌📌 А хотите любую другую автоматизацию по ВБ / ОЗОН / Яндекс Маркет - напишите в нашего бота @vas_mnogo_a_ya_bot

Делаем кнопку группировки более наглядной

вот такой лайфхак от одного из подписчиков Бена Коллинза.

Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (про нее подробнее тут) агрегирует только видимые ячейки.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).

И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима, выдаем другую надпись ("Скрыть").

=IF( SUBTOTAL (103;ячейка)=0; "⬇️Подробнее" ; "⬆️Скрыть")

ИЗБРАННЫЕ СКРИПТЫ НАШЕГО КАНАЛА

Посмотрите, вдруг вы пропустили что-нибудь сочное:

— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →

— 🧞‍♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →

— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →

— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →

— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →

— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →

— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →

— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →

— Скриптами определяем, когда освободится домен →

— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →

— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →

— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →

— Защищаем скрипты от редактирования →

— Регулярный бэкап Таблиц в формате XLSX в телеграм! →

— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →

— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →

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

🏄 Заказ у нас @vas_mnogo_a_ya_bot

INDIRECT / ДВССЫЛ — функция, которая делает текст ссылкой на диапазон

=A1 — это ссылка на ячейку A1
="A1" — это текстовая строка из двух символов, буквы A и единицы
=INDIRECT("A1") — это ссылка на ячейку A1 (причем даже если вы удалите строку 1, это будет ссылка на A1, только теперь это будет другая ячейка; а обычная ссылка =A1 превратится в ошибку в таком случае)
=INDIRECT(A1) — ссылка на диапазон/ячейку, адрес которого(-ой) находится в ячейке A1 🤯

*

Для чего это нужно? Ну, помимо ссылки, которая не слетит никогда (даже при удалении строк):

1 Вы собираете ссылку из нескольких кусочков. Например, название листа берете из столбца или в результате проверки какого-то условия функцией IF / ЕСЛИ. В результате склейки (конкатенации) на выходе уже текст, а не ссылка. ДВССЫЛ превратит текст в ссылку.
Пример (поиск на разных листах)
И более лютый пример (формула, которая ищет при разной структуре листов)

2 Вам нужно ссылаться в условном форматировании на другой лист. Напрямую не получится, а через ДВССЫЛ можно (а в Excel еще пригодится для ссылки на умные таблицы в проверке данных)
Пример

3 Вы формируете ссылку на динамический диапазон — по параметрам. Например, берете строку начала и конца из ячеек:

=INDIRECT("A" & ячейка с номером первой строки & ":A"
& ячейка с номером последней строки)

Такая замена OFFSET для формировании динамической ссылки на диапазон.

Декартово произведение (все возможные комбинации фраз) формулой

Забираем первый список слов (фраз) функцией TOCOL (второй аргумент = 1 исключает пустые значения)

TOCOL(A:A; 1)

Из второго списка тоже забираем все без пустых и вдобавок транспонируем (делаем строкой) — функция TOROW:

TOROW(B:B; 1)

Добавляем между ними пробел и склеиваем:

TOCOL(A:A; 1)& " " &TOROW(B:B; 1)

Результатом этого шага станет двумерный диапазон с нужными значениями. Останется превратить его в список (TOCOL или FLATTEN) и применить ArrayFormula или INDEX сверху, чтобы получить не одно значение, а весь массив:

=ArrayFormula(FLATTEN(TOCOL(A:A; 1)& " " &TOROW(B:B; 1)))

Давненько мы про производственный календарь не говорили

Есть прекраснейший сайт http://xmlcalendar.ru/ — спасибо его создателям — с производственным календарем за разные годы и в разных форматах. XML, TXT, CSV и другие.
Ссылки каждый год отличаются только на номер года.
А значит, можно загружать данные в таблицы.

Например, в формате TXT список всех выходных и праздничных дней года. Ссылка для 2024 года такая:
http://xmlcalendar.ru/data/ru/2024/calendar.txt

Соответственно, можно получить список для текущего года в Google Таблицах следующей формулой:

=IMPORTDATA("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.txt")

Но есть еще такое неприятное явление, как рабочие выходные дни (СБ-ВС).
Например, в 2024 году это 27 апреля и 28 декабря.
Как получить список таких дат?

В XML формате календарь есть тип дня t — единица для выходных, 2 для сокращенных рабочих, 3 — то, что нам нужно (рабочий день в СБ / ВС).
Можно получить этот список такой формулой (спасибо Михаилу Смирнову за более лаконичный вариант в комментариях):

=IMPORTXML("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY())  & "/calendar.xml"; "//day[@t = 3]/@d")

Соответственно, если нужны праздники, то t = 1, если сокращенные рабочие, то t = 2.

Правда, формат дат в XML календаре такой: MM.DD (04.27). Таблицы его не поймут, даже если применить ДАТАЗНАЧ / DATEVALUE.

Поэтому сначала придется поменять день и месяц местами (можно с помощью REGEXREPLACE), а потом добавить DATEVALUE:

=ArrayFormula(DATEVALUE(REGEXREPLACE(IMPORTXML("http://xmlcalendar.ru/data/ru/" &YEAR(TODAY())  & "/calendar.xml"; "//day[@t = 3]/@d");"(\d{2})\.(\d{2})";"$2\.$1")))

Если у вас проблемы с ютубом: перезалили несколько видео на Kinescope. Учитесь на здоровье!

Пользовательские числовые форматы в Google Таблицах:
https://kinescope.io/n8ohYBpQ3w3gufd8Ab4owc

Умные таблицы в Google Spreadsheets:
https://kinescope.io/navBqmzZt1UBqUCgBgxGjx

Видео про относительные и абсолютные ссылки. В интерфейсе Excel, но актуально для всех, кто только начинает работать с формулами и хочет окончательно разобраться со ссылками и долларами:
https://kinescope.io/9tPfY1ZSxa1NFXoTRoqeU2

Выбор нескольких значений в выпадающих списках

Новая возможность (анонсирована Google в июле 2024) в проверке данных (неважно, в обычных ячейках или в "умных" таблицах через выбор типа столбца) — выбор нескольких объектов.

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

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

Чтобы включить опцию, нужно в боковой панели проверки данных включить флажок "Разрешить выбор нескольких объектов" (Allow multiple selections).

Отчет по реализации WB заработал

1) Таблица, с отчетом по реализации ВБ и остатками ФБО t.me/google_sheets/1143

2) Загружаем расходы в Таблицу по рекламе ВБ, API t.me/google_sheets/1460

3) Пост "в поисках идеального дашборда для маркетплейсов" с интересными комментариями t.me/google_sheets/1475

4) WB, достаём топ-100 товаров по запросу t.me/google_sheets/1116

5) Таблица для расчета себестоимости на KazanExpress t.me/google_sheets/1199

6) Достаём характеристики и описание товаров из ВБ, Таблица со скриптом t.me/google_sheets/1404

🏄 Заказ у нас @vas_mnogo_a_ya_bot

Обновили Таблицу загрузки отправлений из OZON.

Теперь данные выгружаются даже если API возвращает больше 1000 строк.

Спасибо нашему подписчику за наводку 🏄‍♂️

ОЗОН, Список отправлений FBO | @google_sheets

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

Таблица с котом кодом.

Страничка из описания API OZON про этот метод.

И если вы совсем новичок, что нужно, чтобы скрипт заработал:

1) делаем копию Таблицы
2) идем в личный кабинет OZON и получаем client_id и client_secret, открываем свою копию Таблицы, редактор скриптов и вставляем эти аргументы на лист main
3) далее в Таблице выбираем диапазон, за который нужны отправления, жмакаем кнопку и получаем список на лист
4) кстати, данные вставляются с помощью sheets api, поэтому, если будете копировать просто код - не забывайте включить эту службу в сервисах

⭐️ У нас много наработок для OZON / WB / Яндекс Маркет, поэтому, нужна любая автоматизация - напишите в нашего бота, а мы к вам оперативно вернемся

СОБИРАТОР 4.0

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

Про что это вообще?
Как-то к нам пришел Дима @IT_sAdmin и сказал – ребята, помогите! Мне очень нужно регулярно брать данные из больших Таблиц, фильтровать, а потом копировать результат в другие Таблицы, других Таблиц будет много (сотни их). Нужно, чтобы был интерфейс и всем этим волшебством можно было управлять из него.

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

В прошлом году мы выпустили свой СОБИРАТОР – интерфейс для сбора других Таблиц через sheets api (самый быстрый скриптовый способ).

Задаёте правила сбора Таблиц в другие Таблицы и собираете, к примеру, 50 Таблиц ваших продавцов в одну сводную Таблицу и 30 Таблиц в другую сводную Таблицу, на лету фильтруя и оставляя только нужные столбцы.

Сегодня выпускаем обновление:
— теперь можно указать частоту загрузки каждой таблицы в минутах;
— можно выбрать тип копирования данных (formula / formatted_value / unformatted_value);
— решена проблема с добавлением новых данных: раньше они (иногда) вставлялись со сдвигом;

Главное:
Решение стало полностью автоматическим – теперь один раз заполняете конфиг сбора, выставляете частоту обновления в минутах, ставите скрипт в расписание и вуаля – данные собираются регулярно и уже без вашего участия.

СОБИРАТОР

ИНСТРУКЦИЯ

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
⭐️ Заказ работы у нас

А вот и ссылки

📗 Чат, где почти 6к активных подписчиков: @google_spreadsheets_chat

📕 Оглавление канала

⭐️ Заказ работы у нас

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

Если бы пропусков не было, можно просто использовать функцию СТРОКА / ROW, возвращающую номер строки, и одной формулой массива получить все номера:

=ArrayFormula(ЕСЛИ (ЕПУСТО(B2:B);""; СТРОКА(B2:B)-1))

А с пустыми строками?
Вот такой вариант — простой , но и без формулы массива (то есть формулы будут в каждой строке листа) — сработает. Берем диапазон с началом в первой ячейке (и ее закрепляем долларами) и с концом в текущей строке (для первой строки таблицы и начало и конец у диапазона совпадают, $B$2:B2.

И считаем количество значений в этом диапазона (СЧЁТЗ / COUNTA). Проверяем на пустоту и в таком случае ячейку с номерами тоже оставляем пустой.

=ЕСЛИ(B2="";"";СЧЁТЗ($B$2:B2))

А можно ли это решить одной формулой? Можно 🤠Но это будет посложнее. Можно через MAP и СМЕЩ (OFFSET). Какие еще предложения будут у вас?

Ключевая ставка ЦБ РФ на дату - или формула имени Эльвиры Сахипзадовны

Ребятки, привет!

Вот формула:

=LET(_data; IMPORTHTML("https://cbr.ru/hd_base/KeyRate/?UniDbQuery.Posted=True&UniDbQuery.From=17.09.2013&UniDbQuery.To=" & TEXT(TODAY(); "dd.mm.yyyy"); "table"; 1); REDUCE({"Дата"\ "Ключевая ставка"}; SEQUENCE(ROWS(_data) - 1; 1; ROWS(_data); -1); LAMBDA(_acc; _i; LET(_date; --INDEX(_data; _i; 1); _value; INDEX(_data; _i; 2) / 100; IF(INDEX(_acc; ROWS(_acc); 2) <> _value; {_acc; _date\ _value}; _acc)))))

Формула достаёт значения ключевой ставки ЦБ РФ с сайта с 17.09.2013 (до этой даты данных нет) по сегодняшний день, приводит строки с датой и значением ставки к числовым значениям (формат столбца с датой надо будет задать руками, чтоб красиво отображалось).

Ещё она оставляет только новые значения: строки с датами, когда ставка не менялась отфильтровываются. Так получается 55 строк вместо 2700+. А пользоваться также: VLOOKUP (ВПР) с 4-м параметром True.

Спасибо за внимание! Всех с 1-м сентября!

🏄 И спасибо нашему прекрасному Михаилу Смирнову, он хоть и не в ЦБ (пока), но многое сделал для извлечения информации про курсы валют и показатели

Продолжаем про маркетплейсы или "в поисках идеального дашборда"

У нас очень много клиентов на разработку всякого и точно бОльшая их часть - это продавцы ВБ, ОЗОН, Яндекс Маркета.

Знаете, что мы за годы работы заметили?

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

Поэтому давайте вместе придумаем нашу народную Табличку по маркетплейсам, действительно полезную.

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

🚨 Исходим из того, что все, что можно загрузить из API маркетплейсов - мы загружаем и превращаем в любой вид.

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

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

💰 А чтобы вам было интереснее - мы выберем 1-2 человек с лучшими вариантами и сделаем для них похожее бесплатно

Наша команда уже пять лет работает с маркетплейсами: с ОЗОНОМ, ВБ, Яндекс маркетом

Что мы делаем:

 1. выгружаем разные отчеты через API прямо в Таблицу, от остатков товаров (и их можно копить) до заказов / продаж / показателей по вашей рекламе;

 2. загружаем данные в маркетплейс, те же остатки ваших складов FBS или новые цены, меняем карточки товаров прямо из Талиц;

 3. и все зз удобного интерфейса Google Таблиц. Никаких проблем с доступом нет, если верно соблюдать тайминги и не стучаться каждую наносекунду - всё летает;

 4. Средний отчет (обращение к одному API с последующей обработкой, скажем, накоплением заказов по ВБ) у нас стоит 10 000 рублей;

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

 6. Ну и конечно, данные можно грузить не только с одного ключа / магазина, а с многих, а если данных много - мы их храним не в Таблицу, а в облачной базе Google Biqquery;

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

Хотите из Таблицы / Локер Студио видеть все по своей империи - пишите мне, @namokonov, по всеми вопросам

Показывает чекбоксы только на строках с данными / снижаем визуальный шум :)

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

Как?
1. создаём целый столбец чекбоксов (выделили столбец > вставка > флажок)

2. применяем белый цвет шрифта на этот столбец

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

ПС а еще чекбоксы называют крыжами, слышали про это?

Наш чат, про Таблицы, скрипты и крыжи: @google_spreadsheets_chat