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

Друзья, сегодня – отличный пример использования Таблиц и скриптов как 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
- еще один перевод строки

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

ВПР

Друзья, ВПР - могущественная функция, чего только с помощью неё нельзя сделать.

На нашем канале мы написали много постов про ВПР, давайте вспомним их:

— Последний аргумент ВПР или ВПР с интервальным просмотром = 1: t.me/google_sheets/13

— ВПР с ПОИСКПОЗ в массиве: t.me/google_sheets/78

— ВПР по нескольким диапазонам: t.me/google_sheets/145

— Видео про функцию ВПР в Google Таблицах: t.me/google_sheets/182

— ВПР (VLOOKUP) по нескольким условиям: t.me/google_sheets/265

— ВПР в массиве вместо тысячи CУММЕСЛИМН: t.me/google_sheets/355

— ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце): t.me/google_sheets/397

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

— ВПР, достаём последнее значение по ключу: t.me/google_sheets/558

— Массивный ВПР: t.me/google_sheets/577

— ВПР / VLOOKUP со звездочкой: t.me/google_sheets/640

— Условное форматирование + ВПР: выделяем цветом имена сотрудников из определенного отдела: t.me/google_sheets/925

— ВПР-им с разных листов: t.me/google_sheets/945

— Объединяем ВПР и СУММПРОИЗВ (SUMPRODUCT): t.me/google_sheets/253

🔥 Посты про другие функции и скрипты в оглавлении нашего канала: перейти

Друзья, вы знаете, что сводные в великих Google Таблицах обновляются автоматически при изменении исходных данных. А вот в Excel, увы, этого не происходит.

Там для обновления сводной (а точнее, ее кэша - при создании сводной данные загружаются в память и крутится этот "мгновенный снимок", а не то, что сейчас в ячейках) нужно:
- Щелкнуть правой кнопкой на сводной и нажать "Обновить" (Refresh)
- Нажать Alt + F5 для обновления активной сводной (одна из ячеек которой активна в моменте)
- Нажать на ленте на вкладке "Данные" (Data) "Обновить" (Refresh) для обновления активной сводной

Для обновления всех сводных в книге можно:
- Нажать Ctrl+Alt+F5
- Нажать на ленте на вкладке "Данные" (Data) "Обновить все" (Refresh All).

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

На уровне рабочей книги может подойти событие SheetActivate - активизация любого листа (можно создать и событие для конкретного листа, если на нем одном нужно обновлять сводные).
Alt+F11, далее выбираем рабочую книгу и добавляем код для этого события (его можно выбрать в выпадающем списке).
Пробегаемся по коллекции сводных таблиц на активированном листе и обновляем кэш у каждой:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim Pivo As PivotTable
For Each Pivo In Sh.PivotTables
    Pivo.PivotCache.Refresh
Next Pivo
End Sub

при открытии таблицы вставим на каждом листе его название
(триггер onOpen)

Друзья, привет!

Сегодня простой летний скрипт – вставим при загрузке Таблицы в ячейку A1 каждого листа название этого листа:

function pasteNames() {
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();

sheets.forEach(sheet => {
const sheetName = sheet.getSheetName();
sheet.getRange("A1").setValue(sheetName);
})
};

Чтобы скрипт отрабатывал при каждом открытии Таблицы пользователем – добавьте его в функцию onOpen:

function onOpen(){
pasteNames();

SpreadsheetApp.getUi()
.createMenu("тут может быть меню со скриптами")
.addItem(скрипт, '...')
.addToUi();

};

Если нам нужно игнорировать некоторые листы и ничего не вставлять в них:

function pasteNames() {
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
const ignoreNames = ['Sheet1', 'Sheet2']

sheets.forEach(sheet => {
const sheetName = sheet.getSheetName();
if (!ignoreNames.includes(sheetName)) {
sheet.getRange("A1").setValue(sheetName);
}
})
};

🔥 оглавление нашего канала: перейти

Извлекаем номера телефонов из массива

Не всё же писать про сложные скрипты, про телеграм ботов и про развернутые приложения с библиотеками.

Сегодня у нас простой, и, надеемся полезный для вас пост.

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

Уберем лишнее и поместим каждый телефонный номер в отдельную ячейку.

Решение:
1) С помощью REGEXREPLACE заменим все не цифры и не запятые на пустоту:
=ARRAYFORMULA(REGEXREPLACE(A2:A;"[^\d,]";""))

2) Добавляем SPLIT и разделим строки по "," (для ячеек, где перечислены несколько телефонов через запятую):
=ARRAYFORMULA(SPLIT(REGEXREPLACE(A2:A;"[^\d,]";"");","))

3) Поместим весь результат в один столбец, добавив FLATTEN
=ARRAYFORMULA(FLATTEN(SPLIT(REGEXREPLACE(A2:A15;"[^\d,]";"");",")))

4) И последний штрих– отфильтруем от пустых ячеек, добавив QUERY с фильтрацией по первому столбцу

Итог:
=QUERY(ARRAYFORMULA(flatten(SPLIT(REGEXREPLACE(A2:A15;"[^\d,]";"");",")));"where Col1 is not null";0)

Чат нашего канала: @google_spreadsheets_chat

Пользоваться просто:
1) пройдите капчу, чтобы получить возможность писать в чат;

2) если задаёте вопрос - приложите к нему Таблицу с примером, Таблица должна быть открыта на редактирование;

3) перед тем как задать вопрос – поищите его в оглавлении нашего канала;

😎

Сортировка не по содержанию, а по номеру строки

Друзья, чтобы "перевернуть" данные в A1:B6 (чтобы последняя строка данных – стала первой, предпоследняя – второй, ...) мы можем воспользоваться формулой
=SORT(A1:B6;ROW(A1:A6);FALSE)

Разберём её по частям:
=SORT(A1:B6;ROW(A1:A6);FALSE) – диапазон данных для сортировки.

=SORT(A1:B6;ROW(A1:A6);FALSE) – хитрость, мы создаём виртуальный столбец с номерами строк исходного диапазона данных {1;2;3;4;5;6}. Виртуальность столбца означает, что он никуда не выводится и используется только внутри формулы.

=SORT(A1:B6;ROW(A1:A6);FALSE) – сортировка по возрастанию FALSE/ЛОЖЬ, то есть, сортировка по виртуальному столбцу пойдет по по убыванию.

PS А если вы решите посмотреть на номера строк и просто напишите формулу =ROW(A1:A6) в ячейку, то увидите значение только для первой ячейки диапазона, A1 (смотрите ячейку C1 на скриншоте).

Чтобы увидеть всё – нужна формула массива:
=ARRAYFORMULA(row(A1:A6))

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Импорт писем из Яндекс Почты в Google Таблицу?

Недавно к нам пришел клиент с таким запросом. К сожалению, в лоб это реализовать нельзя – у ЯП нет http-API, к которому можно обратиться, используя Google Apps Script.

Но задачу можно решить, простой способ:
1) Создаём правило для пересылки писем из ящика ЯП на наш ящик Gmail. К сожалению, правило будет работать только для входящих сообщений.

2) Далее загружаем письма из Gmail в Таблицу с помощью скрипта t.me/google_sheets/802

И еще способ (от Александра Иванова, он же @oshliaer). Подключаем ящик Яндекс Почты в Gmail через POP3:
1) Активируем POP3 в Яндекс Почте (видео)
2) Производим настройки в веб-клиенте Gmail (видео)
3) Письма приходят в наш ящик в Gmail. Загружаем их в Таблицу с помощью скрипта: t.me/google_sheets/802

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

Достаём ссылки на фотографии товара из карточки WB

Привет, друзья! Чтобы получить ссылки на фотографии товара – берём ссылку на карточку WB

wildberries.ru/catalog/86123932/detail.aspx?targetUrl=XS

и превращаем её в

img1.wbstatic.net/big/new/86120000/86123932-1.jpg (-2.jpg, -3.jpg, ...)

Разберём по пунктам:
1) Достаём из ссылки на карточку – артикул товара, отбросив последние четыре цифры:
wildberries.ru/catalog/86123932/detail.aspx?targetUrl=XS
=REGEXEXTRACT($A1;"(\d+)\d{4}")
//8612

2) Добавим слева "https://img1.wbstatic.net/big/new/" и добавим справа "0000/", а также артикул полностью, а также {"-1.jpg" \ "-2.jpg" \ "-3.jpg"}, чтобы получить три ссылки.

Итоговая формула:
=ARRAYFORMULA(
"https://img1.wbstatic.net/big/new/"
& REGEXEXTRACT($A1;"(\d+)\d{4}") & "0000/"
& REGEXEXTRACT($A1;"\d+")
& {"-1.jpg" \ "-2.jpg" \ "-3.jpg"})

Формула возращает:
https://img1.wbstatic.net/big/new/86120000/86123932-1.jpg
https://img1.wbstatic.net/big/new/86120000/86123932-2.jpg
https://img1.wbstatic.net/big/new/86120000/86123932-3.jpg

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

📌 А еще мы сделали своё дополнение для продавцов WB

🚜 Делитесь своими трюками в WB в комментариях :)

Наш чат, где могут помочь с Таблицами: @google_spreadsheets_chat

Несколько 🔥 клавиш Google Диска

Создать новые документы/папки в текущей папке
Документ: Shift + t
Презентация: Shift + p
Таблица: Shift + s
Форма: Shift + o
Папка: Shift + f

Переместить документ: z
Переименовать: n
В Избранное: s

Открыть выделенную папку/документ в новой вкладке браузера: Ctrl + Enter

Открыть настройки доступа: . (точка)

Поменять режим просмотра (сетка/список): v

Скопировать ссылку на выделенную папку/документ: Ctrl+C
Скопировать название: Ctrl+Shift+C

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

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Видеоурок для новичков: текстовые функции

Друзья, привет! Делимся с вами уроком из курса "Драйв. Гугл Драйв" - про работу с текстовыми значениями: окно "Найти и заменить", функция TRIM, изменение регистра, разделение текста (командой "Разделить текст на столбцы" и функцией SPLIT), объединение текстовых значений, извлечение фрагментов из текста.

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

В курсе таких видео 90 - про диск, таблицы (конечно, ко всем урокам прилагаются исходные и заполненные таблицы), документы, календарь, почту, презентации (от ведущего дизайнера МИФа Ольги Королёвой).

Слушатели уже поставили 249 оценок урокам курса. 244 из них (98%) - пятерки 5️⃣

Коллеги из МИФа сделали промокод на скидку для вас - 40%DGD. Действует до 29 августа (12-00).

https://www.mann-ivanov-ferber.ru/courses/gdrive/

Кликаем – строки выделяются

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

Скрипт из ГИФки проверяет, какой диапазон был сохранён в ScriptProperties в предыдущий раз, убирает с этих строк заливку, далее закрашивает строки, которые были выделены сейчас и сохраняет этот диапазон в ScriptProperties (чтобы при следующем запуске убрать заливку с него).

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

🤓 Если найдёте триггеру применение в своем Табличном хозяйстве – напишите нам в комментариях.

const pr_key = 'lr';
const colour = '#800080'; //en.wikipedia.org/wiki/Web_colors

function onSelectionChange(e) {
const range = e.range;
const sh = e.source.getActiveSheet();
const range_a1 = ${range.rowStart}:${range.rowEnd};
const last_range_a1 = ScriptProperties.getProperty('lr');
if (last_range_a1) {
sh.getRange(last_range_a1)
.setBackground(null);
};
sh.getRange(range_a1)
.setBackground(colour);
ScriptProperties.setProperty('lr', range_a1);
};

Оглавление нашего канала: тыц
Наш чат: тыц-тыц

Нарастающий итог: закрепляем только первую ячейку в диапазоне

Если вы хотите считать в отдельном столбце накопительный итог, никто не помешает вам закрепить только начало диапазона, но не его конец.

1. Ссылаемся на первую ячейку, эфчетырим ее (то есть нажимаем F4, чтобы сделать ссылку абсолютной, "закрепить")

2. Вводим двоеточие и ту же самую ячейку, но уже оставляем относительной. Получается диапазон с началом и концов в одной ячейке, но конец не закреплен - так что при протягивании/копировании формулы будет меняться.
=СУММ($B$2:B2)

3. Протягиваем и получаем диапазон с началом в одной и той же ячейке и концом в текущей строке.

Альтернативное решение, одна формула массива:
=ARRAYFORMULA(SUMIF(row(B1:B16),"<="&row(B1:B16),B1:B16))

Про такую формулу мы писали вот здесь

TOO MUCH IMPORTRANGE

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

Мы подготовили скрипт, который решит эту проблему (скрипт и короткое описание: pastebin.com/EcRZwhg0).

А зачем это может понадобиться?
Расскажу про проблему наших клиентов – одна Таблица была использована внутри IMPORTRANGE слишком много раз (>500) и после очередного раза всё перестало работать, данные из Таблицы перестали передаваться, пользователи видели ошибку как на картинке, а еще в эту несчастную Таблицу стало нельзя добавлять новых пользователей с правами чтение или редактирование.

Наше решение:
1) Мы выяснили, какие Таблицы у нас лежат внутри рабочей папки нашим скриптом Drive Columbus;

2) Прошлись по этому списку Таблиц скриптом и узнали, какие Таблицы указаны внутри IMPORTRANGE в этих Таблицах;

3) Сделали копию Таблицы-донора и написали скрипт, который вставил формулу IMPORTRANGE с этой Таблицей в ряд Таблиц по списку;

4) Использовали скрипт от Михаила Смирнова, который прошелся по списку Таблиц с обновленной формулой IMPORTRANGE и раскрыл доступ к Таблицам автоматически, кликать на "расшарить доступ" не пришлось;

В итоге достаточно неприятная проблема была решена и сейчас сотни Таблиц по-прежнему работают.