Работа в Google Таблицах. Кейсы, решения и угар. админы: @namokonov @r_shagabutdinov @IT_sAdmin оглавление: goo.gl/HdS2qn заказ работы: teletype.in/@google_sheets/sheet_happens купить рекламу: https://telega.in/c/google_sheets РКН: clck.ru/3F3u9M
Лабубу. Превращаем Таблицы в поисковую систему
Друзья, результаты поиска в Яндексе и Google, к сожалению, спарсить в Таблицу не получится.
Зато пока ещё работает https://html.duckduckgo.com/ – и его можно обработать с помощью функции IMPORTXML.
В видео я показываю, как найти нужный элемент в коде сайта и составить к нему XPath-запрос (будем вытаскивать сниппеты из результатов поиска).
Таблица поисковик
📌 Формула, которая парсит все сниппеты по запросу «Лабубу»:
=IMPORTXML("https://html.duckduckgo.com/html?q=Лабубу";"//*[@class='result__snippet']")
=LET(
a, IMPORTXML(...),
BYROW(
a,
LAMBDA(row,
TEXTJOIN(" ", TRUE, row)
)
)
)
Задача для вас
Привет! Звучит задачка так – у нас есть столбец и мы хотим вывести номер последней заполненной строки по этому столцбу
Решений несколько, давайте соберем их все в комментариях 👇
1С-аналитик — следующий шаг в карьере бухгалтера
В этих профессиях много перекрёстных знаний. К тому же, у бухгалтеров уже есть опыт в 1С. Из-за этого путь в новую сферу становится короче ⬇️
Освойте востребованную специальность на расширенном курсе «1С-аналитик» в Нетологии. Вы будете много практиковаться, добавите 7 проектов в портфолио и сможете искать работу через 4 месяца.
На курсе вы:
🔹 научитесь выполнять задачи 1С-аналитика: моделировать бизнес-процессы, вести документацию и настраивать 1C:ERP;
🔹 получите бесплатный доступ к 1С:Фреш и 1С:ИТС;
🔹 поймёте, как использовать продвинутые инструменты аналитики;
🔹 подготовитесь к экзамену «1С:Профессионал».
🧑🎓 Нетология — это Центр сертифицированного обучения «1С». После обучения вы получите сертификат «1С», который может стать преимуществом при трудоустройстве. Записаться
Реклама. ООО "Нетология". ИНН 7726464125 Erid 2VSb5yHEvj2
🎓 Программа нашего курса по формулам
Старт — 1 июля
Готовы освоить все эти ЛЯМБДЫ, СКАНЫ, КВЕРИ и МАПЫ и стать тем самым человеком, к которому все бегут с вопросами по Таблицам в офисе?
🚀 Мы собрали для вас подробную программу по каждому уроку нашего курса — переходите по ссылке: t.me/google_sheets1/3
Курс для тех, кто хочет не просто уметь, а понимать, как работают формулы и использовать их по максимуму. Ждём вас!
Расставляем точки на карте с помощью Maps JavaScript API
Друзья, сделали для вас карту со стадионами Москвы — она работает как веб-приложение.
Переходите, смотрите :)
Код GAS и HTML, как получить API ключ и разврнуть приложении оставлю в комментариях.
И напомню: сегодня стартует мой интенсив по JavaScript и Google Apps Script — 3 недели обучения.
В программе — база программирования и работа с популярными API: WB, OZON, Telegram Bot API.
Группа небольшая, так что будет комфортно и все сможем уделить внимание. Детали @namokonov
Получаем новые файлы и папки
Выводим новые документы, которые появились в них за последние n дней.
Наша подписчица Татьяна задала отличный вопрос: у нас есть несколько папок — как обойти их все и вытащить только те файлы, которые были созданы недавно?
Мы подготовили для вас Таблицу со встроенным скриптом (сделайте себе копию!).
Код — в комментариях к таблице.
Татьяне — большое спасибо за полезный вопрос!
🔥 Кстати, завтра стартует мини-курс по скриптам на Google Apps Script!
Будет всего два участника, в программе база и реальные кейсы работы с API Ozon, Wildberries и Telegram Bot Api.
Курс рассчитан на 2 недели — без воды, всё по делу.
ВПР-им с разных листов
Если вам нужно "подтягивать" данные с помощью ВПР
/ VLOOKUP
с разных листов (например, на каждый город/месяц/склад у вас отдельный лист с данными), можно собрать ссылку с помощью INDIRECT
/ ДВССЫЛ
.
Обычная ссылка на другой лист выглядит так:
='Москва'!A:B
="'" & ячейка с названием листа & "'!диапазон"
=INDIRECT("'" & ячейка с названием листа & "'!диапазон")
ВПР'а
как второй аргумент:
=VLOOKUP(значение для поиска; INDIRECT("
) ; номер столбца ; 0)
Панграмма
Спасибо за участие, друзья! У всех почти всё получилось, если не считать букву ё, которую так просто не достать.
Поэтому – по хардкорным вариантам:
Панграмма – это предложение, в котором есть все буквы, в данном случае русского алфавита, например:
«Любя, съешь щипцы, — вздохнёт мэр, — кайф жгуч. Шеф взъярён тчк щипцы с эхом гудбай Жюль. Эй, жлоб!»
=REGEXREPLACE(A1; "[^А-Яа-яЁё]"; "")
=SORT( UNIQUE( MID(UPPER(A3);SEQUENCE(LEN(A3));1)))
Как использовать множественный выбор в формулах Google Таблиц
Недавно в Google Таблицах появилась возможность множественного выбора значений из выпадающего списка. Рассказываем, как это можно применить в формулах.
Для примера возьмём список всех городов России, который мы выгрузили в предыдущем посте. На его основе покажем, как с помощью функции FILTER можно отобрать только те города, в названии которых встречаются выбранные буквы.
Мы добавили выпадающий список с буквами русского алфавита (от А до Я). При этом:
• если включён чекбокс, поиск идёт без учёта регистра (буквы в любом виде),
• если чекбокс выключен — ищем только заглавные буквы.
Таблица
А вот как выглядит формула:
=IFNA(
SORT(
FILTER(
'Россия'!A1:AF;
REGEXMATCH('Россия'!A1:A; IF(B2; "(?i)"; "") & JOIN("|"; SPLIT(B1; ", ")))
);
2;
FALSE
);
"—"
)
Собираем данные из нескольких листов сразу с помощью Sheets API Batch
бета-версия нового собиратора
Загружаем в Таблицу все города России
Друзья, подписчики — нашли мы на просторах интернета очень полезную штуку:
🔗 Открытая ссылка без авторизации
А там — весь список городов России! И не просто список, а с координатами, населением, округом и субъектом. Всё как мы любим:
Пример объекта:
{
"coords": {
"lat": "68.96667",
"lon": "33.08333"
},
"district": "Северо-Западный",
"name": "Мурманск",
"population": 270384,
"subject": "Мурманская область"
}
Решаем список формул!
У нас есть 606 названий — а значит, 606 потенциальных формул.
Что с ними делать?
Конечно же:
1. Добавить = и ()
2. Вставить всё это в Google Таблицу скриптами
3. Посмотреть, что эти формулы будут выводить на самом деле
Извлекаем все формулы из кода Таблицы! Формулы!
Друзья, Google Таблицы – это на самом деле полноценный веб-сайт, только большой и сложный.
Мы уже показывали, как извлекать из текста страницы все формулы. Давайте повторим:
Откройте любую таблицу Google.
Откройте консоль браузера (нажмите F12).
Вызовите поиск по консоли: на macOS – CMD + OPT + F, на Windows – CTRL + SHIFT + F.
Введите название нужной функции, чтобы найти её в коде.
В моём видео я подробно показывал, как дальше работать с найденным.
Параллельный запрос данных с помощью fetchALL в Google Apps Script
В этом примере мы будем одновременно запрашивать 10 стихотворений с сайта. Используем UrlFetchApp.fetchAll
, чтобы ускорить процесс за счёт параллельных запросов.
📹 В видео — подробный разбор скрипта, объяснение каждого шага и демонстрация результата.
💡 Также:
Продолжаю набор на личное обучение по программированию. Заинтересованные уже есть, мест немного, кто действительно хочет – пишите.
Спасибо огромное! Осталось буквально 8–10 бустов — дожмём чуть-чуть 💪
По контент-плану дальше такие идеи:
– Пишем бота, который отслеживает, сколько днем человек пьет, а сколько пропускает
– Продолжаем разбираться с ограничениями fetchAll в Google Apps Script:
цель — загрузить канал среднего размера за пару часов программно
ПУШКИН, Таблицы и Importdata
Друзья, в этом видео рассказываю про чудесную функцию IMPORTDATA
. Она позволяет загружать файлы в формате TXT, CSV, TSV из интернета и вставлять их как значения в нашу Таблицу.
Импортом мы, конечно, не ограничились. Далее мы соединили каждую строчку Поэтма (некоторые расползлись на несколько столбцов) прямо внутри формулы — с использованием LET
, BYROW
и LAMBDA
.
Таблица с тем, что получилось
Формула для Пушкина:
=LET(
a, IMPORTDATA("https://lib.ru/LITRA/PUSHKIN/p2.txt"),
BYROW(
a,
LAMBDA(row,
TEXTJOIN(" ", TRUE, row)
)
)
)
LET ME QUERY
Привет-привет!
Задумывались ли вы, что функции QUERY
порой не хватает финальной строки с общей суммой?
В этом видео я показываю, как на основе массива с населением городов России мы:
– сгруппировали данные по субъектам РФ,
– посчитали сумму по каждому субъекту,
– и добавили внизу таблицы общую сумму по всем субъектам
- и всё одной формулой.
Вот что получилось:
=LET(
a; 'Города'!A:F;
{
QUERY(a; "select C, sum(B) group by C"; 1);
{"Сумма" \ SUM(CHOOSECOLS(a; 2))}
}
)
🔥 Считаем сумму по каждой строке с помощью формул массива!
Друзья, у нас есть таблица с именами и суммами, и задача — одной формулой массива получить общую сумму по каждому имени для каждой строки.
В видео я показываю два способа: "классический" через ARRAYFORMULA
с SUMIF
и более продвинутый — с добавлением функции LET
.
={"Классика" ; ARRAYFORMULA(IF(A2:A=""; ; SUMIF(A2:A; A2:A; B2:B)))}
={"С LET"; LET(
names; A2:A;
sums; B2:B;
ARRAYFORMULA(IF(names = ""; ""; SUMIF(names; names; sums)))
)}
Что на самом деле нужно уметь, чтобы стать успешным CFO?
(Для бухгалтеров, финансистов и финансовых директоров)
Наши спикеры — действующие финансовые директора с опытом работы 15+ лет в крупнейших компаниях раскроют секреты профессии, которые не найти в сети.
📍 Что вы получите на тренинге "Финансовый директор будущего":
→ Авторский тест на тип личности: узнайте свои сильные стороны и построите план карьерного роста.
(Старая цена: 1990 руб. Новая цена: 0 руб.)
→ Путь по карьерному росту: как перейти к роли ведущего CFO или монетизировать навыки через проекты.
→ Интервью с успешными CFO: их ошибки, решения и кейсы из практики
→ 35 бонусных материалов: шаблоны Google-таблиц, финансовый анализ, автоматизация УУ, нейросети в работе, упаковка финансиста.
❗️ Количество бесплатных мест 300
Ссылка на участие ➡️https://fin-academy.pro/
🚀 С 2017 года рассказываю про Google Таблицы и формулы на нашем канале — теперь запускаю полноценный курс!
Хотите упростить и ускорить до 90% рутинных задач в своей компании?
📊 Записывайтесь на курс по формулам в Google Таблицах — от базы до продвинутых функций.
Всего 12–14 уроков за 3 недели, по 2–3 занятия в неделю.
Что вас ждёт:
🔹 Базовые формулы
🔹 Современные инструменты — MAP, REDUCE, LAMBDA и работа с массивами
🔹 Много практики — с домашкой, разбором ошибок и персональной обратной связью
💬 У каждого участника будет:
– личный чат для сдачи и обсуждения ДЗ
– общий чат курса с поддержкой и общением
💰 Стоимость — 50 000₽, всего 20 мест, из них 5 уже забронированы
🗓 Старт — начало июля
Программа — в следующем посте.
Вопросы — пишите в личку: @namokonov
Вопрос в студию 🙂
Друзья, это Google Таблица, и у нас в ней есть формула.
Напишите в чате, кто понимает, как это сделано.
Тот, кто первым ответит правильно — получит 15 кармы в чате!
Хардкорный вариант №2: получаем алфавит с внешнего сайта
Я нашёл сайт со всеми буквами русского алфавита. С помощью fetch получаю код страницы, затем с помощью регулярных выражений достаю буквы – они заключены в кавычки «».
После этого уникализирую список и вывожу в таблицу как заглавные, так и прописные буквы.
Что интересно – функция будет работать как пользовательская, то есть её можно просто ввести в таблицу Google Sheets как формулу.
Скрипт
function KIRILL() {
// URL страницы с русским алфавитом
const url = 'https://znanierussia.ru/articles/%D0%A0%D1%83%D1%81%D1%81%D0%BA%D0%B8%D0%B9_%D0%B0%D0%BB%D1%84%D0%B0%D0%B2%D0%B8%D1%82#:~:text=%D0%A0%D1%83%D1%81%D1%81%D0%BA%D0%B8%D0%B9%20%D0%B0%D0%BB%D1%84%D0%B0%D0%B2%D0%B8%D1%82%20%D1%81%D0%BE%D1%81%D1%82%D0%BE%D0%B8%D1%82%20%D0%B8%D0%B7%2033,%D1%8E%C2%BB%2C%20%C2%AB%D1%8F%C2%BB.';
// Выполняем HTTP-запрос, получаем содержимое страницы в виде строки
const response = UrlFetchApp.fetch(url);
let html = response.getContentText();
// Используем регулярное выражение для поиска всех вхождений "один символ внутри кавычек"
let letters = html.match(/«(.?)»/g);
console.log(letters);
// Удаляем кавычки « и » из каждого элемента массива, оставляя только буквы
letters = letters.map(l => l.replace(/«|»/g, ''));
// Преобразуем все буквы к верхнему регистру и удаляем дубликаты с помощью Set
let upper = [...new Set(letters.map(l => l.toUpperCase()))];
// Для каждой буквы в верхнем регистре создаём пару [верхний регистр, нижний регистр]
let all = upper.map(l => [l, l.toLowerCase()]);
// Возвращаем массив пар букв (верхний и нижний регистр)
return all;
};
Конкурсы? Конкурсы!
Друзья, кто выведет английский алфавит и кириллицу формулой (или пользовательской функцией) получит в нашем чате +10 кармы.
Давайте свои варианты в комментарии.
Наш чат: @google_spreadsheets_chat
🤖 Боты, деньги, n8n — канал, где на реальных кейсах показываем, как зарабатывать и экономить с помощью автоматизации.
Если вы используете Google Таблицы и Apps Script — значит, уже в теме автоматизации. А n8n поможет усилить этот подход: подключать CRM, платёжки, формы, Telegram, Notion и другие сервисы в одной цепочке — без кода и бесплатно.
Это как визуальный конструктор процессов, где без кода можно собирать связки из десятков сервисов.
📌 Автоматическая выгрузка платежей
📌 Обновление остатков и прайсов
📌 Уведомления, рассылки, отчёты
📌 И даже боты, которые приносят доход
В канале:
— адаптированные треды с Reddit
— рабочие схемы на русском
— фокус на деньги, время и простоту
👉 Подпишитесь на «Боты, деньги, n8n» — чтобы получить максимум пользы от данных и процессов.
Реклама. ООО "ЭР Софт". ИНН 7716509296. Erid:2VtzqvWZa6L
Книга "Магия таблиц" — третье издание в продаже!
— Более 100 функций с индексом (два языка, где работает — Excel/Google, на каких страницах встречается)
— 1 фотография кота
— Сотни скриншотов
— 50 файлов с примерами
— Издание №3
— Уже более 500 отзывов на разных площадках со средними оценками 4.8-5.0
— 528 страниц
Третье издание уже в продаже:
Озон
WB
Издательство
6 из 13 уроков второго потока позади!
Друзья, прошли уже 6 из 13 уроков второго потока обучения скриптам на Google Apps Script. Спасибо каждому участнику - именно ваша вовлечённость и интерес делают курс живым и насыщенным!
Покажем лучшие работы наших студентов на канале.
Пока что открыт приём заявок на:
– разработку скриптов (под Ozon, Wildberries, другие API),
– генерацию ответов с помощью скриптов,
– персональное сопровождение (обучение) по интересующим вас темам.
Пишите @namokonov
Ссылка на формулы из кода Таблицы, их 606
🎁 Если кто-то найдет, какая формула работает кроме известных и классических – начислим +10 кармы в нашем чате
На картинке формула =DUCKHUNT()
Наши ребята, коллеги, друзья — со многими мы общаемся ещё с тех времён, когда был жив мессенджер от Google (давно и благополучно закрывшийся).
Вместе осваивали Telegram, когда он ещё не был в мейнстриме, когда это казалось чем-то экспериментальным и новым.
А вот и каналы, которые выросли из этого опыта:
@gasru — про скрипты от Иванова и Митяйки
@GoogleSheets_ru — про Таблицы и скрипты
@STMSol — тоже про Таблицы и скрипты
@MaksymStoianov — авторский канал от Максима
@contributor_pw — разработка и автоматизация
@ZiGoomul, @ZiGoomul_GoogleApps, @ZiGoomul_GAS — Таблицы, боты и немножко магии
@GDS_chat — всё про Google Data Studio
@BigQuery — когда название говорит само за себя
@lemur_excel — канал нашего любимого соавтора Рената, который ведёт его вместе с котом редкой (и продуктивной!) породы
@WorkSmartPartyHard — пока Ренат отдыхает, мудрый Лемур делится лайфхаками продуктивности
👀 И, конечно же, наши:
@google_sheets — канал про Таблицы и скрипты, с вами с 2017 года
@google_spreadsheets_chat — наш уютный чат, велком!
Открыли подписчикам канала доступ к новой книге Системы Финансовый директор — сборнику «Мастер-классы по Ехсеl для финансиста: по задачам и должностям». В одной книге собрали самые популярные приемы — короткие и четкие видео помогут быстро освоить новые навыки и «прокачать» имеющиеся:
✅работа с формулами
✅преобразование данных
✅фильтры и сортировка
✅сводные таблицы
✅динамические и связанные списки
✅нестандартные способы визуализации отчетов, кастомные графики и диаграммы
✅приемы для прогнозирования
✅приемы план-факт и сценарного анализа в Excel.
Для удобства добавили приемы в Excel по должностям: для главного бухгалтера, руководителя ПЭО, финансового менеджера и контролера.
🔔 Чтобы получить сборник, оставьте заявку по ссылке: https://clck.ru/3MQEuz?erid=2W5zFHWBJdN
#реклама
О рекламодателе
Друзья, а вас больше!
А у нас в канале больше полезного контента :)
Пробусти, нас, пожалуйста t.me/google_sheets?boost