Подборка наших постов про 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, и что-нибудь еще, оставайтесь с нами :)

Друзья, собрали для вас всякую пользу про функции для вычислений с условиями: SUMIF(S), AVERAGEIF(S), COUNTIF(S), COUNTUNIQUEIFS, MAX/MINIF(S).

Про условия (прикрепляем также картинку с примерами):
— Регистр в условиях не учитывается.
— Важно, чтобы все диапазоны условий и диапазоны суммирования/усреднения были одинаковой размерности. Это могут быть и столбцы целиком (E:E), и диапазоны (E2:E40), и открытые диапазоны (E3:E). Например, если один аргумент — это столбец целиком (D:D), то и другой должен быть в таком же формате (такого же размера — E:E, а не E2:E120, например).
— Диапазоны могут быть со смещением (например, суммируем E2:E50, а условие ищем в D3:D51). Тогда при соответствии ячейки в столбце D условию суммироваться будет число из строки выше. Если вдруг применяли такое (или придумали, где применить, когда узнали пару секунд назад) — напишите в комментариях!
— Условия можно вводить в кавычках внутри функции — любые текстовые значения в формулах вводятся в кавычках. Либо ссылаться на ячейки, где хранится текст условия.
— В условиях можно использовать символы подстановки (* — любой текст любой длины, в том числе нулевой; ? — один любой символ). Например, "*сайт*" — это ячейка со словом "сайт" и любым другим текстом до и после, а не только ячейка со словом "сайт".
— В условиях можно использовать знаки сравнения (<, >, <=, >=, <> — "не равно"). Например, "<>Москва" — все, кроме ячеек, в которых текст "Москва".

Еще польза по теме:
Примеры условий с символами подстановки

Памятка по условиям в FILTER, QUERY и SUMIF

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

Телеграм + Google Таблицы, наши решения

Друзья, привет. Мы с 2017 года ведём наш канал в Телеграм и иногда публикуем свои скрипты, которые с этой платформой взаимодействуют. Все посты – готовые и бесплатные решения, которые можно скопировать, прочитать инструкцию и начать использовать.

Простой Телеграм-бот (гифка установки), логирует все, что ему пишут в Google Таблицу, также из Таблицы можно отправлять сообщения в Телеграм-чаты и каналы

Еще один Телеграм-бот, извлекает из Таблицы данные по запросу и отправляет в Телеграм

Отправлятор 2, отправляет по расписанию диапазоны из Таблицы в Телеграм (pdf / jpg)

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

ВЫГРУЖАТОР, достаёт скриптом количество подписчиков чатов и каналов в Телеграм

Скрипт внутри Google Формы,
— берёт последний ответ и отправляет его в Таблицу
— отправляет в Телеграм чат
— отправляет на электропочты

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

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

Ну а COUNTUNIQUEIFS позволяет считать уникальные значения с условиями — например, посчитать, сколько клиентов приобретали у нас консультации.

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

Обновление нашей Таблицы WB: загружаем отчет по реализации из API

Продолжаем разговор и продолжаем добавлять полезное в нашу Таблицу WB.

Отчёт по реализации – главный отчёт продавца Wildberries. Внутри отчёта – прибыль продавца за каждый товар, также те комиссии, которые продавец заплатил площадке и другие поля.

Мы добавили возможность загрузки этого отчёта в нашу Таблицу WB – вводите диапазон дат, за который хотите получить отчёт на листе "Реализация" и нажимайте на кнопку "отчёт по реализации" в меню.

❗Обратите внимание, WB вернёт вам ошибку, если запросите слишком большой диапазон дат, да и в Таблицу вы не сможете вставить слишком много.

Наша Таблица WB (уже умеет загружать остатки FBO и отчёт по реализации)

Краткое описание работы скрипта

Документация метода

Если у вас есть интересные решения с WB, которыми можно поделиться - поделитесь в комментариях. Ждите обновлений и они будут :)

Видеоурок: подсчет и суммирование по условиям

В продолжение темы функций SUMIF(S) и подобных предлагаем вашему вниманию видео по теме. В нем и про основы работы с этими функциями, и про символы
подстановки.

https://www.youtube.com/watch?v=HsBt0_IWoyA

Это один из 90 уроков курса "Гугл Драйв" в МИФе.

Если вам нужно посчитать сумму (или еще что) только в строках, которые отличаются от других выравниванием (печально, но вдруг?) — можно использовать функцию CELL / ЯЧЕЙКА.

Первый аргумент — параметр, второй — ссылка на ячейку.

Параметр "prefix" показывает выравнивание. Функция с таким параметром будет возвращать одно из трех значений:
^ — по центру
' — по левому краю
" — по правому краю

Распознаем текст на изображениях прямо в Google Таблицах

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

Таблица со скриптом для распознавания текста

Как это работает - подаёте на вход PDF, Google Документ, изображение, файл должен лежать на вашем Google Диске.

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

На скриншоте слева изображение, а справа – распознанный результат.

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Добавляем комментарий к формуле

Немного экзотики. Функция с очень коротким названием N / Ч превращает ИСТИНА / TRUE в единицу, ЛОЖЬ / FALSE в ноль, числа оставляет как есть, текст превращает в ноль.

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

Например:
=E2*15% + Ч("Вычисляем комиссию как 15% от суммы сделки")

Первая часть (E2*15%) здесь — это вычисление комиссии, а вторая — текст внутри функции Ч, которая превратит его в ноль. Так что внутри формулы текст есть, а к результату эта часть ничего не добавляет.

Еще один вариант для комментариев в формуле — функция LET.

Комментарии — лишь повод про нее напомнить, так как функционал у нее шире.

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

LET(имя1; значение_имени1; [имя2; значение_имени2]; …; вычисление)

Давайте посмотрим на совсем простой пример — зададим две переменных a и b, присвоим им значения 50 и 10 и вычислим их произведение в последнем, единственном непарном, аргументе функции LET:
=LET(a;50;b;10;a*b)
На выходе будет 500.

В выражениях для вычисления переменных можно использовать предыдущие переменные. В следующем случае мы вычисляем b как 10*a:
=LET(a;50;b;10*a;a*b)
На выходе будет 25000.

Конечно, на практике для таких простых выражений функция LET не нужна. Но если у вас сложная формула, в которой одно и то же промежуточное выражение нужно вычислять несколько раз или вы хотите в итоговой формуле ссылаться на промежуточные шаги по имени для лучшей читаемости — LET поможет.

Возвращаясь к нашей теме с "комментариями": можно задать переменную (с любым названием) и присвоить ей текстовое значение.
=LET(переменная; "комментарий"; [другие переменные для вычислений]; ... ; вычисление)

P.S. Функция LET появилась не так давно — что в таблицах, что в Excel. И это значит, что в отличие от Ч/N при скачивании таблицы на локальный диск функция будет работать только в Excel 2021 и Microsoft 365.

У вас есть список событий/записей с датами и именами/названиями событий.
Например, записи клиентов на услугу; пациентов на госпитализацию и т.п.

И вы хотите собрать расписание, склеить все события/записи, которые будут в один день, в одну строку.

Сначала собираем все значения, соответствующие каждой очередной дате — с помощью функции FILTER:
=FILTER(имена/события;столбец с датами=очередная дата нашего расписания)

А потом полученный список остается склеить в одну ячейку с помощью TEXTJOIN. В качестве разделителя выбирайте любой по вкусу. Если хотите, чтобы все было в одной ячейке, но в разных строках, используйте перенос строки, который можно добыть функцией CHAR / СИМВОЛ с кодом 10.
=TEXTJOIN(CHAR(10);;FILTER(...))

Остается добавить сверху IFNA, чтобы заменить ошибки N/A (в случаях, когда ни одного события на дату не нашлось) на ничего.
=IFNA(TEXTJOIN(CHAR(10);;FILTER(...));)

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

PS А вот тут мы писали, как сделать TEXTJOIN по каждой строке с помощью LAMBDA

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

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

Правда, на встречах слушатели этих слайдов не увидят. Вот еще — время тратить на презентации на уроках :)
Все время (3 по 2 часа) проведем в Excel (ну и малость в Google Таблицах), а слайды — это как мини-методичка для участников, чтобы потом освежить в памяти знания.

Еще будут домашки, их разбор (и подарки авторам лучших работ), файлы-примеры до и после, ответы на вопросы.

Приходите, вебинары будут 14, 20 и 23 июня.

Для вас — скидка 37% по промокоду Excel23, которая будет действовать до 5 июня включительно.
https://www.mann-ivanov-ferber.ru/courses/practicum-excel/

Система учета рабочего времени по локации из телеграм бота.

Сегодня - пост от старожила нашего чата Каната (@akanat), передаём слово ему:

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

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

https://youtu.be/olwb5SFsMVg

Новые формулы в Таблицах

Недавно в Google Таблицы добавили новые мощные формулы, про них у нас есть статья от Михаила Смирнова.

Напомним и вам и себе про эти формулы (ссылки на Таблицу с примером).

TOROW — превращает диапазон в строку
ТОCOL — превращает диапазон в столбец
CHOOSEROWS — позволяет выбрать из диапазона нужные строки
CHOOSECOLS — позволяет выбрать из диапазона нужные столбцы
WRAPROWS — разрывает столбец на строки
WRAPCOLS — разрывает столбец на столбцы
VSTACK — соединяет диапазоны в один по вертикали. Напоминаем про пример применения VSTACK для сбора данных из нескольких таблиц, ссылки на которые хранятся в ячейках
HSTACK — соединяет диапазоны в один по горизонтали
LET :)

Запрашиваем из Таблиц ИНН и получаем название компании

Привет, сотабличники! Сегодня мы для вас подготовили простой летний скрипт.

Кликаете на ячейку с ИНН, запускаете скрипт из меню и видите, что в ячейку с ИНН, в примечание, подставилось название компании из сайта rusprofile.

Весь код с комментариями в комментариях к этому посту :)

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

Как достать иконки доменов?

Ребята, это старая тема, но мы про неё, вроде, не писали.

Берём ссылку

https://www.google.com/s2/favicons?sz=256&domain_url=

и добавляем в конец домен, например, avito.ru. Получившееся помещаем в функцию IMAGE().

Результат на скрине. И пример таблицы: там на одном листе справочник с доменами и иконками, а на другом по домену достаётся иконка с помощью VLOOKUP() (это быстрее, чем каждый раз использовать IMAGE()).

Наш дорогой Беня твитнул несколько лет назад этот способ. Способу уже больше 7 лет. Ну, вот и мы про него написали.

Ещё можно попробовать дописать к вашему домену /favicon.ico и уже это https://my-domain/favicon.ico напрямую вставить в IMAGE(), чтобы получить более актуальную картинку. Этот способ в том же примере в соседней колонке.

У гугла картинки не все актуальные, а второй способ не найдёт иконку, если она не в favicon.ico. Выбирайте.