Данные с разных листов, на которых разная структура

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

Тут сложнее. Можно решить эту задачу так: через ИНДЕКС+ПОИСКПОЗ (INDEX + MATCH) вместо ВПР (про эту комбинацию мы уже писали), чтобы все работало при любом порядке столбцов.

Через СМЕЩ / OFFSET будем формировать ссылку на столбец для поиска и на столбец с нужными данными. Находить их будем по заголовкам (заголовок будет находиться через ПОИСКПОЗ / MATCH, и это будет использоваться в функции СМЕЩ / OFFSET для смещения по столбцам, чтобы попасть на нужный).

Вот ингредиенты нашего коктейля:
INDIRECT("'"&название листа&"'!диапазон") - ссылка на ячейку или диапазон на нужном листе, с которого тянем данные

MATCH(заголовок;INDIRECT("'"&название листа&"'!диапазон");0) - поиск нужного нам заголовка (столбца, из которого нужно тянуть данные)

ROWS(INDIRECT("'"&название листа&"'!A:A") - число строк на листе, с которого нужно тащить данные)

OFFSET(INDIRECT("'"&название листа&"'!A1"); 0;MATCH (ищем заголовок, как выше) - 1; число строк, как выше;1) - ссылка на диапазон на нужном листе шириной 1 столбец со всеми строками, с отступом от A1 до нужного нам заголовка.

А логика формулы в общем виде такая:

=ИНДЕКС(СМЕЩ(который дает ссылку на диапазон на нужном листе в нужном столбце, с которого нужно тащить данные;
ПОИСКПОЗ(ключ для поиска;СМЕЩ(который дает ссылку на диапазон на нужном листе в столбце, в котором находятся ключи для поиска, например, названия товаров);0))

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

Отправлятор / Удалятор постов и сообщений Телеграм, апдейт

Наш подписчик Михаил сделал новую версию Таблицы с Отправлятором.

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

Таблица

Всё крутится на библиотеке на GAS для Telegram Bot API: github.com/Guf-Hub/TGBot

@nosaev_m, спасибо! 😎

Сообщение в Телеграм чат при любом редактировании столбца.

Посмотрите на скриншот - такой вопрос пришел в наш чат.

Показываем минимальный код, с помощью которого задачку можно решить. Код вставляется в редактор скриптов Таблицы (Расширения > Apps script), после функцию send нужно положить на триггер изменения Таблицы (Расширения > Apps script > Триггеры > создать Триггер > при редактировании Таблицы > функция send).

В коде нужно заполнить botToken, ввести один или несколько chatIds, на которые будут отправляться сообщения (можете ввести и username канала). Чтобы узнать chatId - используйте @idBot в телеграм.

Бот сможет отправлять сообщение только если он в чате или на канале с правами отправлять сообщения либо если пользователь уже ему писал и после этого не блокировал.

Как искать ошибку, если код не работает: Расширения > Apps script > Количество выполнений > кликаем на неудачное выполнение, читаем ошибку и исправляем.

Код: pastebin.com/dBevx4L3

Чат: @google_spreadsheets_chat
Канал: @google_sheets

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

Функции:
— Учимся извлекать из текста нужное. База знаний регулярных выражений для REGEXEXTRACT →→
— Памятка по синтаксису QUERY / FILTER / SUMIFS →→
— Полный справочник по QUERY →→
— Функция FILTER. Список условий выбираем диапазоном прямо с листа →→
— Обоюдоострый FILTER. Убираем с помощью одной формулой из таблицы пустые строки и столбцы →→
— SUMIF для нескольких условий в формуле массива →→
— Формулой достаем изображения из поисковой выдачи Яндекса →→
— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце) →→
— ВПР в массиве вместо тысячи CУММЕСЛИМН (заполняем весь лист одной формулой) →→
— ВПР-им с разных листов →→
— Автоматически создаем фразы по определенным шаблонам в Google Таблицах →→
— Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥 →→
— Создаём в QR-код прямо в ячейке →→
— Флаг вам в руки — суммируйте что хотите. SUMIFS с флажком →→

Проекты:
— Telegram bot + Google Sheets (используя вебхуки) →→
— Эмоджи форматирование выполнения плана (IMAGE, IFS) →→
— Создаем красивое расписание групповых занятий →→
— Создание оглавления в телеграм-канале: как автоматически загружать и сортировать публикации из вашего рабочего файла →→
— Google Форма + Google Таблица для проведения тестирования →→
— Применение IFTTT для отправки сообщений с данными из Таблиц →→
— Googlefinance, Парето и графики (#готовое решение) →→
— Создаем инфографику с помощью IMAGE. Размер картинок пропорционален значениям →→
— Импортируем таблицу из веб-страницы и оставляем только нужные нам столбцы (IMPORTHTML+QUERY) →→

Для новичков:
— Мы на Хабре: "Функции Таблиц, которых нет в Excel" →→
— Советы по оптимизации Таблиц →→
— Видеоурок по функции IMPORTRANGE (для связывания нескольких таблиц) →→
— Видеоурок по фильтрам и режиму фильтрации →→
— Видеоурок: Пользовательские числовые форматы в Google Таблицах →→
— Про виды доступа к документам →→
— Совместная работа с фильтрами →→
— Данные с другого листа в правиле условного форматирования →→
— Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела →→
— Схватка двух ёкодзун. Сравнение Google Таблиц и Excel →→

Полное оглавление нашего канала: http://goo.gl/HdS2qn
Наш чат: @google_spreadsheets_chat

Разрешаем доступ для IMPORTRANGE автоматически

Друзья, когда я начал заниматься Таблицами у меня был вопрос – как не кликать каждый раз на "allow access / разрешить доступ", как расшаривать доступ к Таблицам автоматически?

Тогда я не смог найти ответ, а сейчас мы умеем это делать — напоминаю про отличную статью от Михаила Смирнова 🔥

скрипт onOpen, который создаёт автофильтр

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

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

Код:
function onOpen() {
const sheetNames = [
'Касса 1',
'Касса 2',
'ИТОГО',
];

const ss = SpreadsheetApp.getActive();
const criteria = SpreadsheetApp.newFilterCriteria()
.whenFormulaSatisfied('=$A2 > TODAY() - 3')
.build();

for (let sheetName of sheetNames) {
let sheet = ss.getSheetByName(sheetName);

let filter = sheet.getFilter();

if (filter == null) {
filter = sheet.getRange('A1:F').createFilter();
}

filter.setColumnFilterCriteria(1, criteria);
}
};

Оглавление нашего канала: тут
Наш чат: @google_spreadsheets_chat

За сниппет спасибо Михаилу Смирнову! 😎

Шаблоны в Google Таблицах и Excel

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

Но можно просто сделать ссылку на копирование часто используемой таблицы и добавить в избранное.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Удаляем лишние пробелы

Для этого можно использовать функцию TRIM / СЖПРОБЕЛЫ - она удаляет пробелы в начале строки (все до первого слова), в конце и оставляет только по одному пробелу между словами.

А если нужно удалять переносы строк, воспользуйтесь CLEAN / ПЕЧСИМВ - эта функция удаляет непечатаемые символы (первый 31 символ ASCII, в том числе и перенос строки, с которым мы можем столкнуться в Таблицах).

Обе функции есть и в Excel.

А в Таблицах еще есть инструмент для удаления пробелов без формул, если вам нужно сделать это разово (без пересчета).

Данные - Очистка данных - Удалить пробелы
Data - Data cleanup - Trim whitespace

Или клавиши: (Alt+D) + U + I

ДЛЯ ПРОДАВЦОВ НА WILDBERRIES

Друзья, привет! Мы подготовили дополнение для продавцов на сайте WB.

Какие модули есть сейчас:
— выгрузка ваших данных из API WB: остатки, заказы, продажи, отчет по реализации (2000 рублей / месяц)

— выгрузка всех карточек продавца (можно выгрузить как свои, так и чужие карточки) в Таблицу + выгрузка остатков и размеров по выбранным карточкам (2000 рублей / месяц)

— редактирование карточек из Таблицы (2500 рублей / месяц)

Какие модули будут скоро: мы добавим более широкий парсинг сайта WB, инструмент для расчёта автозаказа, а также для изменения цен.

Про процесс: с каждым из первых клиентов мы создаём отдельный чат в Телеграм, в нём вы сможете задавать вопросы, прийти если что-то не работает и дать фидбек по развитию приложения.

Как купить: напишите мне, @namokonov, далее я вас сориентирую

Какие данные вы увидите: teletype.in/@google_sheets/wb_headers

PS. На ГИФ – выгрузка всех товаров с розничными ценами одного продавца 😎

Sheets Api, берём данные

*библиотека для работы с Таблицами, использовать её можно в том числе из скриптов этих самых Таблиц

Рома Игнатов написал материал про Sheets Api. Он берёт объемную Таблицу с данными о российских библиотеках и показывает примеры использования Sheets Api: забирает данные из Таблицы, вставляет, вставляет батчем (одновременно в несколько мест).

ignatov-script.blogspot.com/2022/06/sheets-api-1.html

Спасибо автору! 😎

Функция ТЕКСТ / TEXT: превращаем число в текстовое значение в заданном числовом формате

Давненько мы про эту славную функцию не напоминали. Она возвращает текстовую строку со значением (первый аргумент), оформленным в заданном числовом формате (второй аргумент).

Для чего нужна?
Допустим, вы хотите "склеить" в одну текстовую строку текст и число.
Чтобы получить в таблице надпись вида "По состоянию на: 30.06.22" или "Сумма продаж: 20 500". То есть текст из фиксированной части и какого-то вычисления/функции, как-то суммы чисел или текущей даты.

Проблема в том, что если сделать это "в лоб" без функции ТЕКСТ / TEXT, форматирование потеряется. Число будет без разделителей разрядов, со всеми знаками после запятой; дата будет в виде числа ("По состоянию на: 44742") - потому что вот так даты хранятся в Excel и Таблицах.

И функция ТЕКСТ позволяет это исправить - укажите нужный формат во втором аргументе, как если бы вводили его в окне "Настройка формата чисел". Напоминаем, что мы писали про пользовательские форматы подробно - ровно такие же коды используются в этой функции, за исключением цветов, их с помощью нее изменять не получится:
Пользовательские числовые форматы, добавляем к числам 🔥🔥🔥
Видеоурок: пользовательские числовые форматы
Статья: пользовательские форматы

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

P.S. ТЕКСТ / TEXT работает и в Excel тоже.

Sheets Api 2, вставляем данные

Вторая часть статьи от Романа про Sheets Api.

Внутри автор обращается к сайту Wildberries и достаёт все пункты выдачи заказов (21 229). Далее парсит этот JSON и вставляет и на один лист и на два листа (с помощью batchUpdate), попутно сравнивая время исполнения скрипта с классическим методом вставки данных с помощью SpreadsheetApp.

Спойлер: Sheets Api действительно немного быстрее 🔥.

Статья с примерами кода: ignatov-script.blogspot.com/2022/07/c-sheets-api-2-wildberries.html

Оглавление нашего канала: тут
Наш чат: @google_spreadsheets_chat

Поиск и окно "Найти и заменить" в Excel и Google Таблицах

Казалось бы, все просто - нажимай Ctrl+F да ищи, нажимай Ctrl+H да заменяй/удаляй.
Но есть приятные опции:
- Можно искать/заменять в диапазоне/на листе/на всех листах
- Можно искать/заменять с учетом регистра
- В Google Таблицах в окне "Найти и заменить" можно использовать регулярки (смотрите примеры в статье: можно поменять формат дат на другие или сделать еще какую-нибудь магию), а в Excel символы подстановки
- В Google Таблицах можно искать по ссылкам (а в обоих редакторах - по формулам);
- В Excel можно менять формат ячеек - достаточно выбрать образец для поиска и образец для замены.

Достаём остатки и планируемые пополнения складов ИКЕА по артикулу

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

Друг нашего канала Виталий написал скрипт, который позволяет получить из кода страниц ИКЕА остатки / планируемые пополнения складов по выбранным артикулам.

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

Инструкция от автора:
1. Меню IKEA - Выдать скрипту права. Согласится на выдвигаемые требования (скрипту нужен доступ к таблице и запросам на внешние узлы)
2. Проставьте галочки у нужных магазинов
3. Вставляйте ссылки по одной, количество будет подгружаться автоматически
4. Данные перегружаются при редактировании ссылки (например, можно удалить букву из названия)
5. Очищение строки удалит загруженные данные

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

PS Ссылка-избранное позволяет сильно сэкономить время на штучном добавлении в корзину

Приложение для продавцов WB

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

На ГИФКЕ:
Показываем, как работает выгрузка всех товаров по выбранному бренду из сайта WB в Google Таблицу.

Сейчас в приложении три модуля:
1) можно выгружать "сырые" данные о ваших продажах, заказах, остатках и отчёт по реализации по вашему ключу API;

2) можно выгружать остатки на складах по любым артикулам из сайта WB, в этот же модуль входит запрос всех товаров по бренду, который показан на ГИФ;

3) можно редактировать карточки товаров из Таблицы (из-за политики WB редактирование работает не на всех аккаунтах, работает или нет у вас – надо проверять);

Внутри Таблицы:
Данные, которые вы получите вы можете посмотреть здесь: teletype.in/@google_sheets/wb_headers

Для консультации по стоимости и функционалу пишите @namokonov

Выводим номер вхождения строки и удаляем дубликаты

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

Для этого выведем номер вхождения строки в Таблицу. Тут поможет функция countifs в массиве, в аргументах перечисляем все столбцы данных и обязательно, добавим магическое условие: row(D2:D20);"<=" & row(D2:D20). Получится что-то такое:

=ARRAYFORMULA(if(A2:A20<>"";COUNTIFs(A2:A20;A2:A20;B2:B20;B2:B20;C2:C20;C2:C20;D2:D20;D2:D20;row(D2:D20);"<=" & row(D2:D20));))

Получаем номера вхождения строк:
1 - первое вхождение,
2-3-4-... последующие (то есть, дубликаты)

Применяем автофильтр и оставляем строки, у которых номер вхождения не единица, выделяем эти строки и смело удаляем.

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

— Таблица с примером: здесь
— Как выделить дубликаты с помощью УФ: тут
— Про магические условия вида row(F2:F20),"<=" & row(F2:F20) и как с помощью них сделать автоматическую нумерацию списков: вот здесь

Экзотический прием от Бена Коллинза, но вдруг кому-то пригодится!

Объединяем два заголовка в одной ячейке.
Для этого можно воспользоваться формулой:
Склеиваем два текстовых значения (заголовка), добавляя между ними:
- перевод строки (функция СИМВОЛ/CHAR с кодом 10)
- несколько нижних подчеркиваний, которые мы повторяем с помощью функции ПОВТОР/REPT
- еще один перевод строки

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