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

Функции:
— Учимся извлекать из текста нужное. База знаний регулярных выражений для 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
- еще один перевод строки

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

ВПР

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

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

— Последний аргумент ВПР или ВПР с интервальным просмотром = 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);
}
})
};

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