Google Таблицы

Про конструктор ботов (полный ноукод)

Его даём вместе с курсом

1) мы вам его даем и даем все обновленные версии
2) научим заполнять так, чтобы все работало
3) и расскажем, как дописать нужные модули, которых в конструкторе еще нет

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

Пишите, отвечу на любые вопросы: @namokonov

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

Бот принимающий заявки – у людей лопнули трубы, нужно помочь

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

Далее покажем еще несколько примеров :)

Ссылка на курс https://t.me/google_sheets/1597

🤖 Вопросы можно либо в комментарии к посту, либо лично мне (@namokonov)

ПС Друзья, звук прерывается - обновим видео в скором времени

Для подписчиков сегодня открыт бесплатный доступ к онлайн-курсу «Power BI для финансиста: от нуля до PRO» в Высшей школе финансового директора.

Cкачайте готовые модели и файлы и повторяйте процесс за автором. По окончании курса - официальное удостоверение о повышении квалификации.  

В программе:  
🔹Расчеты, вычисления, формулы 
🔹Наглядная сквозная аналитика и анализ финансовых показателей
🔹Как превратить 12 отчетов в один
🔹Как быстро рассчитать продажи, прибыль и финансовый результат
🔹Визуализация и разработка дашбордов  

Оставьте заявку на бесплатный доступ на 3 дня>>> 

#реклама
О рекламодателе

Ищем дизайнера

👌 Горшочек, больше не вари, уже нашли несколько человек, которые пробуют.

Ребят, нам нужен дизайнер / верстальщик, который нарисует сайт (практически визитку, никаких сложностей) быстро и симпатично.

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

Референсы дам в личку, работа быстрая, оплата сразу, пишите: @namokonov

Извлекаем из RichUrl собственно текст, ссылку и еще составляем формулу

Таблица

Скрипт

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu("🎃")
    .addItem("превращаем ссылки в данные >", "extract")
    .addToUi();
}

function extract() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getRange("a1:a");
  const richText = range.getRichTextValues();
  const values = richText
    .map(g => [
      g[0].getText(),
      g[0].getLinkUrl(),
      `=HYPERLINK("${g[0].getLinkUrl()}";"${g[0].getText()}")`]);

  sheet
    .getRange(range.getRow(), range.getColumn() + 1, values.length, values[0].length)
    .setValues(values)

  sheet.insertRowBefore(1);
  const h = [['richUrl', 'text', 'url', 'hyperlink']];
  sheet.getRange(1, 1, h.length, h[0].length)
  .clear()
  .setValues(h)
}

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

💵 В лс пришло несколько вопросов – да, рассрочка возможна тоже, а также в следующем посте мы напишем каких именно ботов ВЫ сможете создавать после окончания курса.

Курс по созданию телеграм-ботов от автора канала

Мы запускаем курс по созданию телеграм ботов, после прохождения вы сможете делать ботов на таблицах, для себя и на продажу, соединяя Тelegram Bot Api, развернутое веб-приложение в Google Apps Script, ну и Таблицы и Google Диск.

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

2. помимо базового конфига мы расскажем и покажем, как вам дописать в телеграм бота свои небольшие модули

3. ваш бот/боты будут развернуты на БЕСПЛАТНОЙ платформе apps script, платить за сервер для приложения вам не потребуется

Стоимость курса - 100 000 рублей, продолжительность - 3 недели, будет набрана небольшая група с преподавателем (мной, Евгением Намоконовым) и моими помощниками, которые смогут в случае чего помогать в нашем групповом чатике

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

По любым вопросам пишите мне, @namokonov

ПС группа будет небольшой, поэтому каждому уделим много внимания.

Друзья! Попробуйте, пжл, вариант автоматического "дробления" IMPORTRANGE в своих Таблицах и напишите нам обратную связь прямо в комменты

Нам она важна. Спасибо ❤️

📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

"Внутренняя ошибка загрузки данных" при использовании IMPORTRANGE, решаем проблему

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

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

Формула из таблицы

= IFNA(REDUCE("t.me/google_sheets"; SEQUENCE(A2 / B2 + 1; 1; 1; B2); LAMBDA(acc; z; VSTACK(acc; IMPORTRANGE("ссылка на таблицу";
    "Исходные данные!A" & z & ":J" & z + B2 - 1)))))
  

🌕 Отличная статья про сбор данных от Рената: https://shagabutdinov.ru/blog/tpost/2mzeu919n1-import-dannih-iz-vseh-google-tablits-v-s

🌕 Ну и собиратор, используйте его тогда, когда IMPORTRANGE уже совсем не может, собиратор собирает данные скриптами с помощью sheets api

PS Спасибо нашему формульному маэстро Игорю за формулу. Поделитесь в комментариях, как она будет работать, пожалуйста

РPS Уточню, что дробить IR на отдельные формулы - это гипотеза, это "как будто" помогает в некоторых случаях, но не всегда

Поддержать наш канал
(перевыложили пост с правильными реквизитами)

карта Т-банк:

4377 7237 4010 3902

btc:

1CudE2V46Ws4uXwDnRsNxsaW4U89F1obg4

usdt, trc-20:

TTqPdepoSrXYg8vdHRMWBrHkGHr2cBtRwL

bybit uid

100495670

Яндекс Деньги
Paypal
Patreon

❤️ Спасибо за поддержку! (Last-100) / / чтобы добавиться в список – напишите сумму в чатик

Оглавление нашего канала

⭐ В последние дни регулярно обсуждаем Собиратор, заказчики до сих пор используют, уже и DeepSeek (китайский аналог chatGpt) его упоминает.

Значит, самое время написать следующую версию, а пока ссылки на  актуальное

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

ОТПРАВЛЯТОР, диапазоны в теле GMAIL письма по расписанию →

ОТПРАВЛЯТОР 2, создаём из Таблиц изображения и отправляем в Телеграм по расписанию →

ПС Про ДипСиик подсмотрено на @contributor_pw

Вы еще кипятите создаёте для каждой строки (столбца) отдельное правило условного форматирования градиентом? Тогда мы идем к вам ©

Да! Вы не ошиблись! Мы к Вам с не документированной функцией Google таблиц!
Оказывается в УФ градиентом можно задавать не только конкретные числовые значения, но и формулы❗️
Теперь мы можем не создавать для каждой строки (столбца) отдельное правило УФ, а использовать общее правило УФ градиентом для всего диапазона.

Пример №1. Диапазон B2:F11. УФ по строкам:
- минимальное = MIN($B2:$F2)
- серединное = MEDIAN($B2:$F2)
- максимальное = MAX($B2:$F2)

Пример №2. Диапазон I2:M11. УФ по столбцам:
- минимальное = MIN(I$2:I$11)
- серединное = MEDIAN(I$2:I$11)
- максимальное = MAX(I$2:I$11)

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


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Устали самостоятельно тыкаться в Excel или мучительно изучать SQL и Python?

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

📌 10–15 февраля Changellenge » Education проводят интенсив «Введение в аналитику данных».

Там всё, что нужно, чтобы разобраться с азами в аналитике:
🔹 Познакомитесь с функционалом SQL, Excel и Python
🔹 Будете работать с реальными базами данных
🔹 Построите сводные таблицы с помощью SQL и Python;
🔹 Построите столбчатые диаграммы, boxplot, scatter-плоты с помощью Python;
🔹 Научитесь находить ошибки и применять функцию VLOOKUP (ВПР) в Excel.
🔹 Изучите главные SQL-запросы: освоите GROUP BY, CASE WHEN и фильтрацию данных.

⚡️ Бонусом решите 3 практических кейса, подобные тем, что аналитики решают ежедневно в работе.

5 дней в специальном телеграм-канале вы будете смотреть видео от экспертов и получать задания. И всё это время рядом будет куратор, который подскажет, если возникнуть вопросы.

✅ Можно получить сертификат в конце, пройдя тест.

✅ Каждый участник получает набор бонусов. Бонусы и программа интенсива указаны на сайте.

📌 Переходите, изучайте, регистрируйтесь: https://u.to/bxrAIQ

Реклама. ООО "Высшая Школа Аналитики и Стратегии" ИНН: 7716917009, erid: 2VtzqwMr2TN

Про услугу "ВНЕДРЕНИЕ"

Друзья, часто получаем похожие сообщения (скришот).

Отвечаем:
1) все наши посты, скрипты и решения бесплатны и снабжены полными инструкциями по установке, а последние - даже видео с голосом, в котором мы по шагам показываем, что нужно сделать;

2) но, иногда не хочется ни в чем разбираться, ничего смотреть, а хочется, чтобы пришел человек и настроил на ваших аккаунтах все сам;

Ок, это возможно: мы придумали услугу внедрение, ее стоимость - 2500 рублей, переводите на донатные реквизиты, пишите в чат и в этот же день к вам приходить наш доверенный человек, делает так, чтобы все работало, далее вы его убираете из своих таблиц / скриптов и пользуетесь бесплатным решением самостоятельно и во всю их мощь.

Чатно-канальный бот v6
🚨 теперь в Таблицу попадают и отредактированные сообщения!

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

Мы решили этот момент.

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

❤️ Дорогие подписчики спасибо за интересные идеи, которые позволяют нам улушать наш продукт

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

Друзья, если вы покупали курс "Магия новых функций Excel", заглядывайте в личный кабинет!

Там вас ждет новый урок про функцию TRIMRANGE на 15 минут и файлы-примеры к нему.
В уроке дополнительно покрутим функции FILTER и UNIQUE, поговорим про то, как в старых версиях сделать именованный динамический диапазон с помощью ИНДЕКСа и, собственно, покрутим разные примеры с основным блюдом — TRIMRANGE / УРЕЗДИАПАЗОН

Если вы не покупали — теперь в курсе 15 уроков, и вот они:

1 / Старые и новые формулы. Новые типы ссылок — собачка (@) и решетка (#). Новая ошибка #SPILL. Старые функции в новых формулах. Новые формулы и старые инструменты Excel. Функция UNIQUE
2 / Функции SORT, SORTBY и FILTER
3 / Функция SEQUENCE: формируем последовательность из чисел или дат (или букв? 😉) одной формулой
4 / Функции для разделения текста: TEXTSPLIT и другие
5 / Функции для работы с регулярными выражениями
6 / Функции для перевода и определения языка
7 / Делаем плоское квадратным и наоборот: TOCOL и TOROW, WRAPROWS и WRAPCOLS
8 / Объединяем массивы/таблицы: функции VSTACK и HSTACK
9 / Извлекаем строки и столбцы: функции CHOOSECOLS и CHOOSEROWS, DROP и TAKE
10 / Функция LAMBDA и вспомогательная функция MAP
11 / Функция MAP: собираем данные с разных листов
12 / Функции BYROW и BYCOL: последовательно обрабатываем каждую строку (столбец) в диапазоне
13 / Функция REDUCE: применяем вычисление к каждому элементу и получаем накопленный итог и функция SCAN: рассчитываем нарастающие итоги с разной магией
14 / Функции PIVOTBY и GROUPBY — сводные таблицы… формулами!
15 / Функция TRIMRANGE: удаляем пустые строки и столбцы

https://shagabutdinov.ru/magic-excel

Наш дорогой брат и админ Михаил осваивает новую профессию! Увлекательным приключением поделился с нашими любимыми подписчиками!

Статья с подробностями: Гугл - Плитка

Другие статьи автора


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Чатно-канальный бот v5

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

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

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

Чатно-канальный бот v4
🔥теперь с подключенной библиотекой!

Друзья! Сделали для вас финальную версию нашего бота, теперь вы добавляете его в каналы / чаты

Иии:

Бот сохраняет в Таблицу и в вашу папку на Google Диске буквально всe, что видит:

- сообщения и от кого они
- название чата / канала
- файлы!
- видео сжатые и расжатые!
- картинки, снэпшоты!

☄️ Еще мы разъединили развернутое веб-приложение и рабочий код бота.

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

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

Друзья, мы умеем делать приложения для магазинов

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

По этим приложениям у нас около 100 покупок каждый месяц.

☄️ Поэтому, если вам нужно свое приложение (необязательно для Эвотора) и если в этой системе есть его возможность сделать и выложить - напишите мне, обсудим @namokonov

Комбинированная диаграмма: показываем, где мы находимся на фоне распределения всех

Данные для этой диаграммы — результаты марафонцев и марафонесс в Москве в 2024 году (протокол с сайта организатора).

Как обрабатываем данные для диаграммы?
С помощью функции ЧАСТОТА/FREQUENCY получаем распределение по интервалам.

С помощью функции ТЕКСТ / TEXT делаем заголовки вида "До 05:00":

="До "&ТЕКСТ(H2;"hh:MM")

Функцией ЕСЛИ / IF проверяем, в какой интервал попадает наш результат — и для нужного интервала оставляем его значение, а для остальных — ошибку N/A с помощью одноименной функции НД / NA. Таким образом, будут данные для ряда на диаграмме, состоящие из одного значения, чтобы подсвечивать наш результат, и остальных значений N/A, которые не будут отображаться.

=ЕСЛИ(И(наш результат>граница интервала;наш результат<=граница след интервала);ЕСЛИ(наш пол="Женщины";число женщин в этом интервале;число мужчин);НД())

Как строим такую диаграмму?
Это комбинированная диаграмма. Распределение результатов (два ряда: женщины и мужчины) — это области. А ряд с нашим результатом — это столбик (гистограмма).

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


📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас

Чатно-канальный бот v3
(в видео - как это чудо работает)

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

В видео - как это работает.

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

🍏Хотите уметь делать таких ботов самостоятельно? 

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

Показать защищенные диапазоны

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

Вид — Показать — Защищенные диапазоны
View — Show — Protected ranges

В случае защиты всего листа, в том числе с исключением отдельных диапазонов, опция работать не будет. В таком случае у ярлыка листа будет иконка с замком.


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

🚨 Как снизить риски при работе с внештатниками и автоматизировать бизнес? 

Ручное управление договорами, выплатами и документами с внештатными исполнителями — это не только трудозатратно, но и рискованно.

На бесплатном вебинаре “Как снизить риски и автоматизировать работу с внештатниками” мы:

✔️ Разберем ошибки, которые чаще всего привлекают внимание налоговой.
✔️ Покажем алгоритм минимизации налоговых рисков для бизнеса.
✔️ Поделимся инструментами, которые помогают автоматизировать процессы и исключить ручные ошибки.

📅 Когда: 28 января, 11:00 (МСК)
🎙 Спикер: Александр Поштак, юрист с многолетним опытом сопровождения бизнеса.

Зарегистрируйтесь, чтобы защитить свой бизнес и освободить время для роста:👉 https://konsol.pro/webinars/kak-snizit-riski?utm_source=tgposev28&utm_medium=google 

erid: 2W5zFJEsmaQ

#реклама

Чатно-канальный бот v2

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

В видео показал, как всё теперь работает.

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Совсем немножко осталось, добьем, господа?

https://t.me/google_sheets/1569

Чатно-канальный бот v1

Бот, который сохраняет сообщения из вашего канала в Таблицу
(🚨c видео-инструкцией по установке!)

Друзья, привет! Мы с 2017 года ведём свой канал про Google Таблицы и скрипты.

Выкладываем посты и иногда их удаляем. Как найти пост, который мы удалили? Смотреть в истории изменений - история есть, но она небольшая, слишком далеко её не открутишь.

Поэтому мы сделали телеграм бота для себя и делимся им с вами.

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

Таблица, копируйте
Библиотека, копируйте
Видео по настройке

🧩 Мы делаем ботов посложнее на заказ

⭐️ Через месяц мы запустим курс "как сделать бота для своего бизнеса" :) Следите за анонсами.

⚡ Прокачайте навыки работы в Excel ⚡

Высшая школа Главбух открыла бесплатный доступ к части курсов топовой программы «Excel для бухгалтера. Уровень PRO». На 7 дней вам будут открыты для изучения:
— Инструменты Excel (основные функции, ВПР и многое другое);
— POWER QUERY для быстрого обновления отчетов;
— Профессиональные приемы по работе с Excel от наших экспертов.

Также вам будет доступен тренажер для контроля своих знаний.

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

Оставить заявку на доступ к программе >>

Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347.

Как выделить / найти все формулы на листе?

Можно просто нажать Ctrl + `
Или в меню: Вид - Показать - Формулы (View - Show - Formulae), или Alt+V + S + A.

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

Но если вам нужно выделить ячейки с формулами цветом, чтобы их сразу визуально считывать (или, наоборот, ячейки без формул) - можно воспользоваться условным форматированием и функцией ISFORMULA — как на скриншоте.


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Диаграмма с выбором рядов (флажками)

Добавляем флажки для каждого ряда данных.

Дублируем данные для диаграммы, но формулой, которая будет возвращать значение из исходного диапазона только в том случае, если в соответствующем столбце флажок включен, то есть там значение TRUE / ИСТИНА. А если флажок выключен, то будет возвращать значение N/A с помощью одноименной функции. А такие значения на диаграммах не отображаются. Что нам и надо!

=IF(флажок;ячейка с данными;NA())

Строки с формулами можно скрыть, чтобы не любоваться всеми этими НД-шками, а только любоваться данными да диаграммой волшебной.

P.S. Так как периодически мы видим такое в чатах и таблицах, напомним: если вы проверяете статус флажка, не нужно отдельно прописывать равенство ячейки с флажком значению TRUE.
То есть формулы (где B$1 — ячейка с флажком)

=IF(B$1;...

и

=IF(B$1=TRUE;...

будут работать одинаково. Потому что включенный флажок — это TRUE. Так что в первом варианте будет TRUE. И во втором TRUE = TRUE вернет TRUE. Так что ничего страшного в избыточности нет, но и не нужно :)

P.P.S. В Excel все будет работать аналогично. Правда вот флажки в ячейках есть лишь в 365, а во всех старых версиях можно вставить флажки — элементы управления с вкладки ленты "Разработчик". Подробнее — в статье.

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

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Друзья, с началом нового рабочего года вас!

Чтобы постепенно входить в рабочий режим, начнем с забавной формулы, вдохновленной поздравлением табличного гуру Бена Коллинса. Он там использовал похожую формулу, чтобы получить номер нового года текстом.

Чтобы получать число прописью, обычно используют пользовательские функции. Но если вам нужно без "рублей" и "копеек", можно воспользоваться функцией BAHTTEXT — она превращает число в текст на тайском с добавлением слова "бат", названия валюты (название тоже на тайском, что логично).

А мы это дело переведем на русский или другой нужный язык с помощью GOOGLETRANSLATE.

А потом останется убрать слово "бат" (бата, батов — смотря какое число будет). С помощью REGEXREPLACE.

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

=REGEXREPLACE(GOOGLETRANSLATE(BAHTTEXT(A1);"th";"ru");"бата?\.|батов \.";"")

Как начать работать с SQL?

Пройдите бесплатный мини-курс “SQL для новичков” от Changellenge >> Education и освойте SQL всего за 5 часов и получите практические знания, необходимые для работы с базами данных.

🔥 Преимущества курса:
- Выделите всего 5 часов на обучение,
- Изучите ключевые функции и операторы SQL,
- Получите видео-разборы от эксперта,
- Выполните более 30 практических заданий.

🎁 За прохождение мини-курса вы получите скидку 10 000 рублей на обучение на программе "Аналитик данных" от Changellenge >> Education.

📅 Скорее регистрируйтесь, осталось всего 15 свободных мест: https://u.to/JQVKIQ

Реклама. ООО «Высшая школа аналитики и стратегии». ИНН 7716917009. erid:2Vtzqvtebq5

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

Редактируем Google Форму прямо из Таблицы!

Друзья, мы подготовили Таблицу, с помощью неё вы сможете облегчить себе редактирование Google Формы.

Как всё работает - на гифке, гифка сегодня со звуком.

Что происходит после запуска скрипта в Таблице:
1) в четвертой строке листа "🔥" Таблицы - вопросы, в пятой строке - тип каждого вопроса, под каждым вопросом и типом - варианты ответа, в ячейке "🔥!B2" - форма, которую будем редактировать
2) запускаем скрипт кликом на кнопку и код ищет каждый вопрос по названию в Google Форме
3) если вопрос найден - то скрипт обновляет варианты ответа
4) если вопрос не найден - скрипт добавляет этот вопрос в Google Форму с заданным типом и вариантами ответа

Типы вопросов, с которыми умеет работать скрипт:
CHECKBOX – выбираем ответ на вопрос чекбоксами, можно выбрать несколько вариантов
LIST – выбираем ответ из выпадающего списка
MULTIPLE_CHOICE – выбираем ответ в переключателе

Чтобы воспользоваться Таблицей - копируйте себе

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

Самое часто встречающееся текстовое значение

Самое частое число — это мода. Функция МОДА / MODE.

А как найти самое часто встречающееся текстовое значение?
Бен Коллинс в своей книге предлагает решение через MATCH (можно и XMATCH, которая просто по умолчанию ведет точный поиск без обязательного третьего аргумента = 0)

Ищем каждое значение в списке и получаем массив из чисел с порядковым номером каждого элемента (номером той строки, в которой впервые встречается значение). Если диапазон включает пустые ячейки, добавьте IFNA, чтобы заменить ошибки.

IFNA(XMATCH(B2:B;B2:B);)

Потом ищем самое частое число в этом диапазоне:

MODE(IFNA(XMATCH(B2:B;B2:B);))

И извлекаем по этому порядковому номеру текст ИНДЕКСом / INDEX:

=ArrayFormula(INDEX(B2:B; MODE(IFNA(XMATCH(B2:B;B2:B)))))

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

📊 Excel раньше — какие-то таблички для бухгалтеров.
🚀 Excel сейчас — личный аналитик и оптимизатор.

Превращайте скучные цифры в полезные отчёты, умные таблицы и дашборды без всякой магии на курсе «Excel: от основ до анализа данных».

Вас ждут:
- Продвинутые формулы.
- Визуализация данных.
- Power Query и Power Pivot.

Всё это под руководством Павла Козлова, гуру Excel с 15-летним стажем. Он научил более 5000 человек укрощать эту программу. Теперь ваша очередь.

Неважно, новичок вы или опытный пользователь, курс поможет вам стать тем самым, кто делает красоту в Excel. Присоединяйтесь к курсу и открывайте новые возможности для своей карьеры. - https://netolo.gy/dLPl

Реклама. ООО "Нетология". ИНН 7726464125. Erid: 2VSb5ww3gYG

Клавиши для выделения столбца/строки

Ctrl + пробел — выделение всего столбца.
Первое нажатие: столбец в рамках диапазона (если активна ячейка какого-то диапазона/таблицы);
Второе: весь столбец листа.

Shift + пробел — выделение всей строки (в случае с таблицей сначала ее строки, затем строки всего листа)

Как запомнить? Клавиша Shift обычно длиннее. Как строка 🙂

❤️ Канал @google_sheets
📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Полезные чатики по теме

Помимо нашего чата советуем чат наших друзей по скриптам: @googleappsscriptrc

И по Таблицам: @google_sheets_pro

👾 Делитесь в комментариях чатами по Таблицам, Excel, программированию, добавим в пост

Наш чат, в котором мы уже 6 лет отвечаем на ваши вопросы: @google_spreadsheets_chat

ПС Кто-то напишет в личку и предложит помочь - не отправляйте деньги, вас обманут

Оглавление нашего канала, где перечислены все наши посты: Оглавление канала

Проектная диаграмма с выделением выходных отдельным цветом

(или не выходных, а определенных дней недели — тут вы по аналогии справитесь😉)

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

Для рабочих:

=AND(первая дата с закреплением строки>=начало первого этапа с закреплением столбца; первая дата<=конец первого этапа;WEEKDAY(дата;2)>5)

Для выходных (уже не в общем виде, а с ячейками):

=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)

Таблица

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Парсим данные из веб-страницы в Таблицу скриптами

Добрый День господа!

Я - Василий!

〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️
Сегодня небольшой, но полезный скрипт, о том, как спарсить данные с сайта на примере сайта о проведении Бильярдных турниров https://fairplay.host/.
〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️
Цель:

Собрать список участников указанных турниров и посчитать сколько раз каждый из участников участвовал в этих турнирах, для дальнейшего награждения лучших.
〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️
Решение:

1. Забираем с первого листа таблицы список ссылок на турниры:

const ss = SpreadsheetApp.getActive();
  const spisokTurnirov = ss
    .getSheetByName('Список турниров')
    .getDataRange()
    .getValues()
    .slice(1);

2. В коде страницы находим ссылку на API и проходим по всем ссылкам в цикле, формируя список участников с указание их города:

  var arr = [];
  spisokTurnirov.forEach(x => {
    x = x[0].match(/\d{4,}/);
    const url = 'https://fairplay.host/api/tournament/details?id=' + x[0];
    arr = [...arr, ...pars(url)]
  })

3. Создаём объект и считаем количество участником и далее вставляем их на другой лист:

  let spr = {}; //создаём пустой объект

  arr.forEach(z => {

    let iii = spr[z[0]];
    !iii ? spr[z[0]] = 1 : spr[z[0]] = iii + 1; // тернарный оператор
  });

Делайте копию таблицы, пробуйте, смотрите код, пишите вопросы, с радостью на них ответим!

Полный код в таблице с примером.
Таблица с примером

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Запускаем скрипт ровно в 55 минут

Друзья, мне написал Владислав - у него есть скрипт и он хочет его запускать каждый час в конкретной время (например, в 55 минут каждого часа)

Мы решили помочь и написали скрипт, который

1) запускает нужный нам скрипт;
2) далее проверяет, есть ли скрипт в триггерах проекта, если есть - удаляет этот триггер
3) далее создаёт новый триггер, на следующий час, чтобы тот запустился в введенные в коде минуты (аргумент mins)

function googleSheets() {
  setDate(); // запускаем нашу функцию

  let fName = arguments.callee.name; // получаем имя текущей функции
  let triggers = ScriptApp.getProjectTriggers(); // удаляем триггер с текущей функцией, если он есть
  for (let i = 0; i < triggers.length; i++) {
    let trigger = triggers[i];
    if (trigger.getHandlerFunction() == fName) {
      ScriptApp.deleteTrigger(triggers[i]);
    };
  };

  let dt = new Date(); //рассчитываем время для запуска функции в следующий раз
  let h = dt.getHours();
  let mins = 44; //устанавливаем триггер на следующий час на это время
  let newDt = new Date(dt.setHours(h, mins, 0) + 60 * 60 * 1000);
  Logger.log(newDt);

  ScriptApp.newTrigger(fName) // ставим функцию на триггер
    .timeBased()
    .at(newDt)
    .create();
};

function setDate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  sh.getRange((sh.getLastRow() || 0) + 1, 1)
    .setValue(new Date());
};

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

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Примеры применения ссылок на таблицы в формулах
начало про ссылки в "умных" таблицах тут

Пара примеров того, как можно использовать ссылки на таблицы на практике.

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

=VSTACK(ПерваяТаблица[#ALL]; [Вторая]; ... [ЕщеТаблица])

Если нам надо загрузить умную таблицу из другого файла — засунем ее во второй аргумент IMPORTRANGE. Если нужно с заголовками — опять-таки, добавляем #ALL:

=IMPORTRANGE("ID исходной таблицы"; "УмнаяТаблица[#ALL]")

Только в случае с IMPORTRANGE мы берем название таблицы в кавычки, т.к. это не активная ссылка, а текстовый аргумент этой функции.

И вот в обоих случаях нам не надо беспокоиться о том, сколько будет строк в таблицах-источниках. Все новые строки соберутся в одну пачку функцией VSTACK или будут импортированы IMPORTRANGE.

Сопоставляем список фамилий в Таблице и список файлов в папке, скрипт

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

У нас есть список документов в папке google диска, пусть это военные билеты или паспорта. 

Каждый документ лежит в папке диска и имеет название в формате `Фамилия Имя Отчество`.

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

Таблица с примером (делайте копию)

Код отдельно:

function ссылки_ссылки() {
  const idFolder = idFromUrl('https://drive.google.com/drive/u/0/folders/17uqPgg09Y3EaJJnkbutmzQfOZqIEMi0b'); // ID папки с файлами
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  const range = sh.getRange(1, 1, sh.getLastRow(), 1);
  let values = range.getValues();
  const files = DriveApp.getFolderById(idFolder).getFiles(); // забираем все файлы из папки

  var filesM = [];

  while (files.hasNext()) { // создаём массив [Имя, Url] каждого файла
    var file = files.next();
    filesM = [...filesM, [file.getName(), file.getUrl()]];
  };

  values = values.map((x, i) => {
    const fileUrl = i == 0 || !x[0] ? '' : filesM.filter(z => new RegExp(x[0]).test(z[0]))[0]?.[1] || 'не найдено';
    return [x[0], fileUrl];
  });

  range.offset(0, 0, values.length, values[0].length).setValues(values);
};

function idFromUrl(url) { // функция для извлечения ID из URL
  return url.match(/[-\w]{25,}/)[0];
};

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Комбинированная диаграмма: добавляем фон для периода

Хотим подсветить определенный период на диаграмме фоном.

Для этого:
1 к данным добавляем столбец и в те дни, которые должны быть с фоном, добавляем число — в районе максимального значения наших данных. В примере 14 500, потому что максимальная выручка в районе 14 тысяч, то есть 14 500 — высота нашего будущего фона — точно перекроет все значения.

2 Вставляем диаграмму и выбираем тип "Комбинированная".

3 Для второго ряда данных (фона) выбираем тип "с областями". Для самих данных — по вкусу (можно график или — как на скриншоте — столбики).

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

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

И ответ на задачку от её автора, Алексея:

Приведу пример оптимального метода расчета для самого простого варианта.

Дано. 2 временных интервала, заданные началами и концами.
start1 - finish1 и start2 - finish2. Найти начало и конец пересечения интервалов, обозначим их как begin и end.

При условии: start1 < finish1 и start2 < finish2

begin = max (start1; start2)
  end = min (finish1; finish2)

На примерах с иллюстрации.

1. start1 = 6; finish1 = 18
    start2 = 9; finish2 = 15

begin = max (6; 9) = 9
end = min (18; 15) = 15

2. start1 = 6; finish1 = 18
    start2 = 3; finish2 = 15

begin = max (6; 3) = 6
end = min (18; 15) = 15

3. start1 = 6; finish1 = 18
    start2 = 9; finish2 = 21

begin = max (6; 9) = 9
end = min (18; 21) = 18

Пост от нашего читателя Алексея Odysseus 

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

Есть тарифы на электроэнергию: дневной и ночной. Задан период работы электроприбора. Максимальное время работы прибора - 24ч. Посчитать, сколько нужно заплатить за потребленную электроэнергию.

Сразу скажу, задача не на знание редких функция таблиц, а на логическое математическое мышление.

#Лирическое отступление. В чате часто спрашивают:
▸ есть ли какие-то хорошие курсы по таблицам?
▸ как научится писать такие формулы, как Вы?
▸ невероятно, а так можно было?
Из своего опыта, ответственно заявляю:
1. Курсы нужны чтоб быстро и грамотно и усвоить базовые принципы. Если Вы никогда не работали в таблицах, не делали курсовые с большим количеством расчетов, Вам обязательно нужно пройти "базовый курс".
2. Никто и никогда не становится специалистом за пару месяцев. Нужен опыт и практика. Наш чат предоставляет возможность получить и то, и другое!
3. Все сложные механизмы, в том числе и формулы, состоят из сочетания множества простых. Но чтобы уметь сочетать даже простые функции нужно уметь мыслить логически! И сегодня задачка именно на умение думать!

От авторов канала - Алексей, задачка сложная, помоги / подскажи в комментариях, в случае чего

Эксперты Системы Финансовый директор выяснили, какие горячие клавиши в Excel используют финансисты, и собрали в одну таблицу. Например, сочетание Alt + = вставляет формулу автосуммы, при этом складывает все числовые значения в ячейках сверху.

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

👉Сегодня для подписчиков канала мы открыли бесплатный доступ к справочнику — чтобы получить его, оставьте заявку по ссылке: https://clck.ru/3Esz3L?erid=LjN8KXcYQ

#реклама
О рекламодателе

Итак, до Google таблиц доезжают ссылки на умные таблицы, которые ранее существовали в Excel.

Изначально с появлением таблиц в Google Spreadsheets можно было ссылаться на все данные в столбце:

Таблица[Столбец]

Теперь как в Excel — можно ссылаться на весь столбец вместе с заголовками и итогами:

Таблица[[#ALL],[Столбец]]

На данные и заголовки, но без итогов:

Таблица[[#HEADERS],[#DATA],[Столбец]]

Можно на итоги или заголовки конкретного столбца

Таблица[[#HEADERS],[Столбец]]
Таблица[[#TOTALS],[Столбец]]

На всю строку заголовков или итогов:

Таблица[#HEADERS]
Таблица[#TOTALS]

И все это актуально для ссылок на несколько столбцов, которые теперь тоже работают в Google. Указываем первый и последний столбец через двоеточие:

Таблица[[Первый]:[Последний]]

Строка итогов появится, как только введете какую-нибудь формулу под последней строкой таблицы. В Excel там автоматом появлялись функции SUBTOTAL для расчета по видимым строкам, но можно и обычные функции вроде SUM тоже.
Новость тут

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

Excel на отлично и куда идти дальше

Погружаться в Google-таблицы/Excel можно очень долго, но когда-нибудь они закончатся. Куда в карьерном плане двигаться дальше? С одним прокачанным навыком в резюме погоду, то есть карьеру и зарплату, не сделаешь.

На рынке трудоустройства первую скрипку начинают играть специалисты со структурным подходом к решению задач бизнеса, а еще — с полным набором ключевых навыков. И таблицы — только один из них.

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

Значит, начинаем анализировать данные по-серьезному!

И начать предлагаем с программы, где вы:

➔ освоите 11 ключевых аналитических навыков (продвинутый Excel, Python, SQL, BI-системы, финмодели и др.), которые нужны не только аналитикам, но и специалистам других областей;
➔ решите 13 индивидуальных и 7 командных проектов и получите обратную связь, чтобы видеть свои сильные стороны и зоны роста;
➔ прокачаете soft skills, необходимые для работы в любой аналитической роли;
➔ узнаете лайфхаки от экспертов Яндекса, VK, Сбера и Avito;
➔ получите консультации с HR-специалистами и подготовитесь к собеседованиям.

Что будет после?
Прокачанные навыки для штурма карьерных вершин, диплом о профессиональной переподготовке, доступ к сообществу выпускников… А главное — резюме, в котором будет не только Excel, но полноценный набор навыков аналитика данных или другого специалиста из смежной области. Ну и соответствующая зарплата 😉

Не останавливайтесь на базовом уровне — пусть Excel станет только началом вашей карьеры! 🚀

В Changellenge >> Education проходит черная пятница со скидками до 50% до 30 ноября. При покупке «Аналитик PRO» вы в подарок получите 2 мини-курса.
Оставьте заявку, чтобы забронировать место со скидкой >> https://u.to/6ywJIQ

Реклама. ООО «Высшая школа аналитики и стратегии». ИНН 7716917009. erid: 2Vtzqx2FvHD

Распознаем текст рассказа О`Генри, взяв PDF из интернета с помощью Таблицы

Друзья, в Google Docs есть технология OCR (оптическое распознавание текста). Мы подготовили для вас Таблицу со скриптом и в видео показали, как она умеет "на лету" распознавать текст из pdf / изображений, ссылку на файл мы взяли прямо из интернета.

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

Чтобы все заработало у вас:

1) Делаем копию Таблицы

2) В ячейку листа "config" вставляем URL файла (пойдет PDF, картинка, ссылка может быть как на Google Диск, так и на файл в интернете);

3) Запускаем скрипт кнопкой "GO!", скрипт создаст из заданного файла новый Google Документ с текстом и вставит весь текст из него на лист "OCR";

4) Хотите сразу удалять временный файл – уберите комментарий (//) с 29 строки кода;

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Нумеруем только видимые строки

Задача: хотим, чтобы при скрытии строк нумерация менялась: чтобы номера были по порядку только для видимых в текущий момент строк.

Решение:

=SUBTOTAL(103;$B$2:B2)

(если начало в строке два)

Считаем (103 = СЧЁТЗ / COUNTA) значения в каком-то столбце таблицы, но считаем только видимые (не скрытые), так как используем SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ.

Ссылка вида $B$2:B2 — то есть всегда считаем с начала таблицы до текущей строки (при смещении формулы будет меняться только конец диапазона).

Создаем пакет документов для оформления нового сотрудника прямо из Google Таблицы

Коллеги, привет! Наш подписчик Василий поделился с нами своим решением, которое он написал на GAS-скриптах.

Василий решал задачу "у меня есть шаблон в Google Документе, в нём несколько документов, я хочу заполнять нужные мне документы данными из таблицы и сохранять результат в папку, а потом распечатать это и дать сотруднику подписать".

Чтобы все заработало, вы:

1) копируете Таблицу себе
2) вводите на лист "настройки" ссылку на папку, куда будут сохраняться созданные документы
3) копируете себе шаблон, меняете его под себя (если требуется) и также вносите ссылку на него на лист "настройки"
4) далее отмечаете чекбоксами какие документы вы хотите из шаблона оставить и заполнить
5) далее переходите на лист "ввод данных" и заполняете данные по сотруднику и смело нажимаете на кнопку "создать документы!"

И скрипт:

1) копирует ваш шаблон 
2) оставляет внутри него только выбранные документы
3) далее меняет теги в шаблоне на то, что вы занесли на лист "ввод данных"
4) переносит шаблон в указанную вами папку и вставляет ссылку на документ, который получился на лист "ввод данных"

Василий оставил комментарии по в скрипте по каждой строке кода, поэтому, если захотите - можно попробовать разобраться, как все работает

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

🔥 Если решение понравится – можете сделать донат Василию по реквизитам, мы передадим.

😈 А будут вопросы – пишите в наш чат.

⚠️ 10 лайфхаков работы в 1С, которые упростят жизнь любому бухгалтеру и позволят сэкономить сотни часов рабочего времени.

Как бы вы оценили свой уровень владения обычной 1С:Бухгалтерией по 10-ти бальной шкале? Даже если на максимальную оценку — всегда интересно освоить новые трюки, которые облегчают работу.

Рекомендую подписаться на канал Мастер CFO. Авторы — Софья и Натали — специалисты в области фин. и бух. учёта со стажем 15 лет. Создали мотивирующее сообщество для бухгалтеров, финансистов, экономистов и аналитиков.

➡️ На канале вас ждут:
– уникальные таблицы, которые упростят вашу работу
– лайфхаки, которые позволят зарабатывать в 2 раза больше
– готовые инструкции по автоматизации фин.учёта
– экспертные кейсы и советы
– ежедневное живое общение с коллегами

📎Чек-лист «10 лайфхаков работы в 1С.

✅ подписывайтесь на канал и забирайте файл в закреплённом сообщении канала бесплатно.
@findir_pro

#Лайфхак❗️Как в Google таблице с помощью условного форматирования сделать границы ячеек

Пост от активного члена нашего сообщества Алексея:

1. Создаём/наполняем данными нашу гугл таблицу как обычно.
2. Сохраняем (скачиваем) нашу таблицу в Excel-формате (меню Файл > Скачать > Microsoft Excel (.xlsx)).
3. Открываем таблицу из п.2 в Excel.
4. Создаём правила Условного форматирования в Excel.
5. Сохраняем (загружаем) таблицу из п.3 в Google Drive.
6. Открываем таблицу из п.4 и сохраняем в формате Гугл Таблиц (меню Файл > Сохранить как таблицу Google)

✅ Профит! Условное форматирование сохранилось!
Конечно, не в полном объёме, например, цвет и толщина границ не сохраняется.

⚠️И главный минус. Данное правило нельзя изменять!

Пример. Мы сделали с помощью УФ несколько границ: между разными группами, слева, справа и внизу...

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала

Напоминаем, друзья: флажки (Checkboxes) в Google Таблицах (и в Excel 365, кстати) можно использовать не только как переключатели значений TRUE / FALSE, но и как визуальное представление этих значений, возвращаемых формулами.

Иначе говоря, если у вас формулы, что-то проверяющие, и выдающие ИСТИНА / ЛОЖЬ, можно вставить "поверх" флажки. Такие флажки уже нельзя переключить пробелом или мышкой.

Они будут переключаться при изменении значения, возвращаемого формулой.

Что это за формулы могут быть? Какие-нибудь проверки значений в ячейке (функции ISEMAIL, ISURL, ISBLANK, ISTEXT и другие), просроченных платежей и прочего, как на скриншоте, нескольких условий сразу через AND / OR.

Или что-нибудь многоэтажное через LAMBDA — например, был ли у сотрудника отпуск в 14 дней подряд (про это читайте тут)

У вас есть дата, а вы хотите номера/названия месяцев в отдельном столбце (допустим, для сводной, для отчетов, для фильтрации) — 7 вариантов

Самый простой вариант — функция MONTH / МЕСЯЦ. Это число, порядковый номер (3 для марта, 11 для ноября).

С помощью функции TEXT / ТЕКСТ можно получить также вариант с нулем для коротких номеров (03 для марта, но 11 для ноября). С помощью нее же — текстовые варианты (мар. и марта для российских региональных настроек).

Если "марта" вам не нравится и вы хотите использовать абсолютно любые варианты, то можно брать их из диапазона с помощью INDEX. Или из виртуального массива внутри формулы с помощью того же INDEX или VLOOKUP или CHOOSE.

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

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

Сделали доработку и теперь скрипт возвращает в ячейки:

- ссылку на загруженный файл
- тип файла
- название

PS Если нужна только ссылка - то закомментируйте 10 строку и раскомментируйте 9 строку.

Загружаем файлы прямо из Таблицы

Друзья, привет! Презентуем вам Таблицу со скриптом, с помощью нее вы сможете загружать файлы на Google Диск и возвращать в Таблицу ссылку на загруженный файл

Как вы можете это использовать? Допустим, вы торгуете на WB / OZON, делаете в Таблице каталог своих товаров и сразу же загружаете через эту Таблицу фотографии этих товаров.

Таблица (делаем копию)

Ниже просто код:

<form>
  <input type="file" name="file" onchange="upload(this.parentNode)" />
</form>
<script>
  function upload(e){
  const file = e.file.files[0];
  const fr = new FileReader();
  fr.onload = e => google.script.run.withSuccessHandler(function() { google.script.host.close() })
  .upload([[...new Int8Array(e.target.result)], file.type, file.name]);
  fr.readAsArrayBuffer(file);
};

</script>

const folderUrl = 'https://drive.google.com/drive/folders/1N9ztzVnG4Xz6AymLN5nByQXc9JOTWdou';

function openSidebar() {
  SpreadsheetApp.getUi().showSidebar(HtmlService.createHtmlOutputFromFile("index"));
};

function upload(e) {
  const url = DriveApp.getFolderById(getIdFromUrl(folderUrl)).createFile(Utilities.newBlob(...e)).getUrl();
  // SpreadsheetApp.getActiveRange().setValue(url);
  SpreadsheetApp.getActiveRange().offset(0, 0, 1, 3).setValues([[url, e[1], e[2]]]);
};

function getIdFromUrl(url) {
  return url.match(/[-\w]{25,}/)[0];
}
}

📗 Чат @google_spreadsheets_chat

📕 Оглавление канала

🏄 Заказ работы у нас

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

Посмотрите, вдруг вы пропустили что-нибудь сочное:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

🏄 Заказ у нас @vas_mnogo_a_ya_bot

Магия новых функций Excel: революция в формулах 🔥

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

Это функции, которые могут решать задачи, ради которых раньше пришлось бы писать макросы или создавать запросы в Power Query!

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

Некоторые из них кажутся бесполезными. Но их просто нужно уметь готовить — и вся их мощь раскрывается зачастую только при сочетании нескольких в одной формуле. Мы рассмотрим мно-о-ого таких комбинаций.

В честь запуска новой версии сайта и этого курса — зверская цена от Лемура — 890 рублей! 🔥
Никаких уловок и вечных скидок не будет: 18 ноября цена вырастет до 2300 и больше уже никогда не вернется к старой.

Внутри:
14 видео от 5 до 25 минут с качественным монтажом
Дополнительные текстовые материалы
Файлы со всеми примерами и формулами — исходные и готовые.

Покупать тут — и там же подробная программа и примеры:
https://shagabutdinov.ru/magic-excel

В Excel можно вставлять в диаграммы иконки и рисунки вместо столбиков / горизонтальных столбиков (в линейчатых диаграммах)

В Таблицах нельзя, но мы сделаем такое формулой.

Изображение будем получать с помощью IMAGE. Ссылку на изображение — с помощью IMPORTXML с одного из сайтов с иконками, куда будем вставлять название нужной категории (у нас статистика по домашним животным в одной из стран). Если сайт иностранный, то название переводим GOOGLETRANSLATE.

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

Клонируем изображение в ячейки с помощью MAKEARRAY. Параметры: строка одна (если имитируем линейчатую диаграмму), а число изображений можно по такой логике:
выясняем максимальную разрядность у наших чисел (в примере 7), возводим 10 в СТЕПЕНЬ на единицу меньше, то есть в нашем примере миллион, и на это число делим.

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

Округление вверх — тогда даже маленькое значение будет отображаться как одна картинка.

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

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

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

Для подписчиков канала сегодня открыт бесплатный доступ к курсу "Excel и Google таблицы в работе финансового директора" в Высшей школе финансового директора. По окончании курса - официальное удостоверение о повышении квалификации!

В курсе вы научитесь: 
🔸 Консолидировать данные разными способами в Excel и Google таблицах;
🔸 Использовать инструменты для построения моделей сценарного анализа;
🔸 Быстро форматировать данные для отчетов;
🔸 Объединять данные из разных источников;
🔸 Автоматизировать работу с отчетностью, бюджетами и их анализом;
🔸 Сделать из бюджета интерактивную финмодель;

Скачайте Excel-модель для тренировки и используйте ее в своей работе!

Заказать бесплайтный доступ к курсу на 3 дня>>>

#реклама
О рекламодателе

Пост от нашего читателя Алексея, передаем слово автору:

Сегодня случайно увидел у Бена Коллинса пост, решил попробовать сделать.
Получилось вроде не хуже 🙂
Может будет актуально после предыдущего поста про множественный выбор.

Описание такое же простое, как сама формула 🙂

ArrayFormula(SPLIT(C:C; ", "; ))

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

TOCOL(... ; 3)

Преобразуем двумерный массив (полученный после SPLIT) в один столбец, игнорируя пустые значения и ошибки.

С помощью хорошо знакомой всем QUERY считаем наши фрукты.

Автоматизация отчетов для торгующих на Вайлдберриз и Озон

К нам часто обращаются селлеры для автоматизации их задач на маркетплейсах.

Что обычно нужно селлеру?
- Понять, сколько он заработал из недельного отчета от ВБ
- Знать, сколько у него остатков товаров и надолго ли их хватит - не будет ли 0 остатка
- Знать, какие товары приносят прибыль, а какие ее наоборот сливают
- Понять, результаты по рекламе на ВБ и Озон - в плюс она вообще или в минус

Что делает селлер или финансист:
1. Берет нашу таблицу по реализациям ВБ, или по Озон, таблицу по рекламе ВБ.
2. Через IMPORTRANGE сводит данные оттуда в другую таблицу, чтобы все было в одном месте.
3. Используя QUERY, INDEX+MATCH, SUMIFS, COUNTIFS агрегирует данные, чтобы понимать общие цифры по кабинету
4. Разбирается с форматами и условным форматированием, чтобы это выглядело хоть как-то понятно.

А потом ВБ что-то поменял, данных стало чуть больше или меньше, возникают сомнения что цифры в таблицах вообще что-то значат.
Да и время тратится на ковыряние в формулах, скриптах, чувствуешь себя на паре по экселю, а не предпринимателем или финансистом, знакомо такое?

При этом есть готовые решения, одно из которых мы рекомендуем как минимум посмотреть.

Андрей Яценко еще в 2021 году сделал первую гугл таблицу, которая автоматизирует учет на ВБ.
За 3 года уже 1 366 внедрений, команда выросла до 35 человек, оборот селлеров, который обрабатывают таблицы - 23 млрд. рублей за 23 год.

Для ВБ и Озон:
⁃ Отчет о прибылях и убытках с загрузкой данных по API
⁃ Учет себестоимости по партиям
⁃ ABC-XYZ анализ с 50 разными показателями
⁃ Отчет о продажах по регионам
⁃ Отчет по рекламе
⁃ Планирование поставок исходя из скорости заказов и продаж
⁃ План/Факт на месяц, неделю, день
⁃ и еще с десяток отчетов

Да, это коммерческий проект и решение стоит денег.

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

Ну и как минимум посмотреть-то точно нужно, чтобы знать как выглядит профессиональный продукт по автоматизации учета на ВБ и Озон, к чему стремиться.

Если для вас маркетплейсы - это бизнес, то посмотрите канал Андрея «Финансы на маркетплейсах» и запишитесь на бесплатную встречу, чтобы посмотреть его Продукт.

👉 Открыть канал «Финансы на маркетплейсах» и посмотреть решение по автоматизации отчетов ВБ и Озон

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

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

Как аналитика может спасти вам жизнь. Ну и построить карьеру 😉

Представим: у вас обнаружилось редкое заболевание, и вероятность летального исхода высока. Вы должны выбрать один из двух экспериментальных методов лечения и знаете, что в клинических исследованиях каждого метода участвовало по 20 тысяч пациентов.

Что выберете:
— Метод А, который, увы, привел к смерти 4800 человек, или
— Метод В, который в 75% случаев спасает жизнь?

Несложные подсчеты показывают, что метод B не помог 25% от 20 000, то есть 5000 человек. Метод А спас больше жизней, и это различие статистически значимо.

Но в описании метода А есть слово «смерть», а в описании метода B говорится о спасении, поэтому этот вариант звучит предпочтительнее. Это типичное «когнитивное искажение», ошибка мышления, которая приводит к неправильным выводам — и в жизни, и на работе. Для аналитика когнитивные искажения недопустимы.

Как не поддаваться? Прокачивать критическое мышление.
Как прокачивать? На курсе «Аналитик PRO» онлайн-школы Changellenge >> Education.

Что вас ждет:
— обучение по принципу «от теории к практике», причем практика составляет 75% курса;
— 11 ключевых аналитических навыков от Python до BI-систем и прокачка soft skills на командных проектах;
— эксперты из Яндекса, VK, Сбера и Росатома, с которыми можно общаться и получать ответы на вопросы;
— официальный диплом и упрощенный отбор в компании-партнеры: Kept, ДРТ, Arthur Consulting, SBS Consulting и Ozon.

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

В Changellenge >> Education стартовала акция в честь Хэллоуина, где вы можете купить курсы с жуткими скидками до 60 000 рублей до 28 октября 23:59.
Оставьте заявку по ссылке на бесплатную консультацию.
С Changellenge >> Education переходить в аналитику не страшно!

Реклама. ООО «Высшая школа аналитики и стратегии». ИНН 7716917009. erid: 2VtzqwvR4xB

📊 Excel раньше — какие-то таблички для бухгалтеров.
🚀 Excel сейчас — личный аналитик и оптимизатор.

Превращайте скучные цифры в полезные отчёты, умные таблицы и дашборды без всякой магии на курсе «Excel: от основ до анализа данных».

Вас ждут:

- Продвинутые формулы.
- Визуализация данных.
- Power Query и Power Pivot.

Всё это под руководством Павла Козлова, гуру Excel с 15-летним стажем. Он научил более 5000 человек укрощать эту программу. Теперь ваша очередь.

Неважно, новичок вы или опытный пользователь, курс поможет вам стать тем самым, кто делает красоту в Excel. Присоединяйтесь к курсу и открывайте новые возможности для своей карьеры.

https://netolo.gy/dAxO
Реклама. ООО "Нетология". ИНН 7726464125 Erid: 2SDnje8pGqZ

Небольшой пост от нашего админа Михаила:

Мы тут вам подготовили табличку с примером, как можно перевести одни символы в другие. Например сделать транслитерацию.

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

Таблица тут.

Бесплатно об аналитике изнутри: кому подходит, что нужно уметь и сколько платят

Данные — это новая нефть, и мировые цены на них постоянно растут. От старой нефти отличается тем, что сидеть нужно не на вышке на Ямале в -30°, а в уютном кресле дома (или в кафе на Пхукете), потягивая латте на кокосовом.

На бесплатном вебинаре от Changellenge >> Education «Профессия аналитика данных: навыки, перспективы, зарплаты» Head of Data Science & Analytics крупной международной Fintech-компании Иван Горшков расскажет, как перейти в аналитик уже сейчас!

Обсудим:
- кто такой аналитик данных и какие навыки ему нужны;
- как оставаться востребованным специалистом и за 3-4 года из джуна дорасти до сеньора;
- сколько зарабатывают аналитики и от чего это зависит;
- как проходит отбор и как искать работу на начальном этапе.

Ответим на вопросы в прямом эфире и всем участникам подарим скидку 20% на наши комплексные онлайн-курсы по аналитике, чтобы перейти в профессию уже в первой половине 2025 года.

Сделайте этот первый шаг в новую карьеру 21 октября в 19:00 Мск из любой точки мира! 🏃

Регистрируйтесь по ссылке >>

Реклама. ООО «Высшая школа аналитики и стратегии», ИНН 7716917009, erid: 2VtzqwqgrXu

Сортируем "внутри" текста

Дано: есть текстовая строка с именами/названиями и числами.
Надо: отсортировать и снова получить текстовую строку.

То есть из "Бусинка 37, Лемур 22, Штрудель 75" сделать "Штрудель 75, Бусинка 37, Лемур 22"

Формула:

=JOIN(", ";BYROW(sort(WRAPROWS(SPLIT(A1;", ");2);2;0);LAMBDA(x;JOIN(" ";x))))

Сначала разделяем на отдельные значения — и числа, и имена (SPLIT по пробелу и запятой).
Потом делаем двумерным диапазоном (таблицей) с помощью WRAPROWS и сортируем по столбцу с числами (SORT).
Объединяем с помощью JOIN — сначала в пределах каждой строки (BYROW) имя и число через пробел, а потом эти значения уже в один текст через запятую еще одним верхним JOIN'ом.

Посчитайте, сколько стоит ваша лень

🔹 Лень начать ходить в зал — через год проблемы со здоровьем.
🔹 Лень пойти на обучение — через полгода бывшие коллеги зарабатывают вдвое больше.
🔹 Лень начать свое дело — через год выгорание на работе и нет желания работать и развиваться.

Лень обходится дорого!
Узнайте что нужно делать, когда вам лень, на канале PRO коучинг и психологию | 5 Prism!

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

@academ5prism

Что у нас есть для работы с маркетплейсами, бесплатно, без смс и помесячных оплат, дорогие продавцы:

1. WB, загружаем отчет по реализации и остатки FBO t.me/google_sheets/1143

2. WB, загружаем расходы в Таблицу по рекламе ВБ t.me/google_sheets/1460

3. WB, загружаем топ-100 товаров по запросу t.me/google_sheets/1116

4. WB загружаем характеристики и описание товаров из ВБ t.me/google_sheets/1404

5. WB Достаём ссылки на фотографии товара из карточки WB t.me/google_sheets/992

6. WB, формируем ссылки на изображения товара t.me/google_sheets/1310

7. API OZON ИЗ GOOGLE ТАБЛИЦЫ t.me/google_sheets/583

📌📌 А хотите любую другую автоматизацию по ВБ / ОЗОН / Яндекс Маркет - напишите в нашего бота @vas_mnogo_a_ya_bot

Делаем кнопку группировки более наглядной

вот такой лайфхак от одного из подписчиков Бена Коллинза.

Функция SUBTOTAL / ПРОМЕЖУТОЧНЫЕ.ИТОГИ (про нее подробнее тут) агрегирует только видимые ячейки.
Соответственно, мы можем проверять, раскрыта ли ячейка строкой ниже — если применим функцию COUNTA / СЧЁТЗ, то есть подсчет любых значений. В SUBTOTAL это функция номер 3 (номер функции в первом аргументе).

И если строка скрыта, то функция вернет 0. Тогда можно выдать текст в духе "Подробнее" или "Раскрыть". Если не 0 — значит, строка ниже видима, выдаем другую надпись ("Скрыть").

=IF( SUBTOTAL (103;ячейка)=0; "⬇️Подробнее" ; "⬆️Скрыть")

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

Посмотрите, вдруг вы пропустили что-нибудь сочное:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

🏄 Заказ у нас @vas_mnogo_a_ya_bot

INDIRECT / ДВССЫЛ — функция, которая делает текст ссылкой на диапазон

=A1 — это ссылка на ячейку A1
="A1" — это текстовая строка из двух символов, буквы A и единицы
=INDIRECT("A1") — это ссылка на ячейку A1 (причем даже если вы удалите строку 1, это будет ссылка на A1, только теперь это будет другая ячейка; а обычная ссылка =A1 превратится в ошибку в таком случае)
=INDIRECT(A1) — ссылка на диапазон/ячейку, адрес которого(-ой) находится в ячейке A1 🤯

*

Для чего это нужно? Ну, помимо ссылки, которая не слетит никогда (даже при удалении строк):

1 Вы собираете ссылку из нескольких кусочков. Например, название листа берете из столбца или в результате проверки какого-то условия функцией IF / ЕСЛИ. В результате склейки (конкатенации) на выходе уже текст, а не ссылка. ДВССЫЛ превратит текст в ссылку.
Пример (поиск на разных листах)
И более лютый пример (формула, которая ищет при разной структуре листов)

2 Вам нужно ссылаться в условном форматировании на другой лист. Напрямую не получится, а через ДВССЫЛ можно (а в Excel еще пригодится для ссылки на умные таблицы в проверке данных)
Пример

3 Вы формируете ссылку на динамический диапазон — по параметрам. Например, берете строку начала и конца из ячеек:

=INDIRECT("A" & ячейка с номером первой строки & ":A"
& ячейка с номером последней строки)

Такая замена OFFSET для формировании динамической ссылки на диапазон.

Декартово произведение (все возможные комбинации фраз) формулой

Забираем первый список слов (фраз) функцией TOCOL (второй аргумент = 1 исключает пустые значения)

TOCOL(A:A; 1)

Из второго списка тоже забираем все без пустых и вдобавок транспонируем (делаем строкой) — функция TOROW:

TOROW(B:B; 1)

Добавляем между ними пробел и склеиваем:

TOCOL(A:A; 1)& " " &TOROW(B:B; 1)

Результатом этого шага станет двумерный диапазон с нужными значениями. Останется превратить его в список (TOCOL или FLATTEN) и применить ArrayFormula или INDEX сверху, чтобы получить не одно значение, а весь массив:

=ArrayFormula(FLATTEN(TOCOL(A:A; 1)& " " &TOROW(B:B; 1)))

Давненько мы про производственный календарь не говорили

Есть прекраснейший сайт http://xmlcalendar.ru/ — спасибо его создателям — с производственным календарем за разные годы и в разных форматах. XML, TXT, CSV и другие.
Ссылки каждый год отличаются только на номер года.
А значит, можно загружать данные в таблицы.

Например, в формате TXT список всех выходных и праздничных дней года. Ссылка для 2024 года такая:
http://xmlcalendar.ru/data/ru/2024/calendar.txt

Соответственно, можно получить список для текущего года в Google Таблицах следующей формулой:

=IMPORTDATA("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY()) & "/calendar.txt")

Но есть еще такое неприятное явление, как рабочие выходные дни (СБ-ВС).
Например, в 2024 году это 27 апреля и 28 декабря.
Как получить список таких дат?

В XML формате календарь есть тип дня t — единица для выходных, 2 для сокращенных рабочих, 3 — то, что нам нужно (рабочий день в СБ / ВС).
Можно получить этот список такой формулой (спасибо Михаилу Смирнову за более лаконичный вариант в комментариях):

=IMPORTXML("http://xmlcalendar.ru/data/ru/" & YEAR(TODAY())  & "/calendar.xml"; "//day[@t = 3]/@d")

Соответственно, если нужны праздники, то t = 1, если сокращенные рабочие, то t = 2.

Правда, формат дат в XML календаре такой: MM.DD (04.27). Таблицы его не поймут, даже если применить ДАТАЗНАЧ / DATEVALUE.

Поэтому сначала придется поменять день и месяц местами (можно с помощью REGEXREPLACE), а потом добавить DATEVALUE:

=ArrayFormula(DATEVALUE(REGEXREPLACE(IMPORTXML("http://xmlcalendar.ru/data/ru/" &YEAR(TODAY())  & "/calendar.xml"; "//day[@t = 3]/@d");"(\d{2})\.(\d{2})";"$2\.$1")))

Если у вас проблемы с ютубом: перезалили несколько видео на Kinescope. Учитесь на здоровье!

Пользовательские числовые форматы в Google Таблицах:
https://kinescope.io/n8ohYBpQ3w3gufd8Ab4owc

Умные таблицы в Google Spreadsheets:
https://kinescope.io/navBqmzZt1UBqUCgBgxGjx

Видео про относительные и абсолютные ссылки. В интерфейсе Excel, но актуально для всех, кто только начинает работать с формулами и хочет окончательно разобраться со ссылками и долларами:
https://kinescope.io/9tPfY1ZSxa1NFXoTRoqeU2

Выбор нескольких значений в выпадающих списках

Новая возможность (анонсирована Google в июле 2024) в проверке данных (неважно, в обычных ячейках или в "умных" таблицах через выбор типа столбца) — выбор нескольких объектов.

Визуально это как несколько чипов (выбрать другой стиль отображения, как классическую стрелку, уже будет нельзя), но в ячейке это будет текстовая строка со значениями, разделенными запятыми. Значит, для последующей обработки их нужно будет извлекать функцией SPLIT.

Не самый лучший вариант для хранения данных и последующей работы, но с формулами массива и LAMBDA не будет великой проблемы обработать отдельные значения одной формулой.

Чтобы включить опцию, нужно в боковой панели проверки данных включить флажок "Разрешить выбор нескольких объектов" (Allow multiple selections).

Отчет по реализации WB заработал

1) Таблица, с отчетом по реализации ВБ и остатками ФБО t.me/google_sheets/1143

2) Загружаем расходы в Таблицу по рекламе ВБ, API t.me/google_sheets/1460

3) Пост "в поисках идеального дашборда для маркетплейсов" с интересными комментариями t.me/google_sheets/1475

4) WB, достаём топ-100 товаров по запросу t.me/google_sheets/1116

5) Таблица для расчета себестоимости на KazanExpress t.me/google_sheets/1199

6) Достаём характеристики и описание товаров из ВБ, Таблица со скриптом t.me/google_sheets/1404

🏄 Заказ у нас @vas_mnogo_a_ya_bot

Обновили Таблицу загрузки отправлений из OZON.

Теперь данные выгружаются даже если API возвращает больше 1000 строк.

Спасибо нашему подписчику за наводку 🏄‍♂️

ОЗОН, Список отправлений FBO | @google_sheets

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

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

Страничка из описания API OZON про этот метод.

И если вы совсем новичок, что нужно, чтобы скрипт заработал:

1) делаем копию Таблицы
2) идем в личный кабинет OZON и получаем client_id и client_secret, открываем свою копию Таблицы, редактор скриптов и вставляем эти аргументы на лист main
3) далее в Таблице выбираем диапазон, за который нужны отправления, жмакаем кнопку и получаем список на лист
4) кстати, данные вставляются с помощью sheets api, поэтому, если будете копировать просто код - не забывайте включить эту службу в сервисах

⭐️ У нас много наработок для OZON / WB / Яндекс Маркет, поэтому, нужна любая автоматизация - напишите в нашего бота, а мы к вам оперативно вернемся

PS И не могу не порекомендовать пост от моей знакомой "про кайф от работы и почему мы не работаем с м*даками" Подписывайтесь на custдевицу.

СОБИРАТОР 4.0

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

Про что это вообще?
Как-то к нам пришел Дима @IT_sAdmin и сказал – ребята, помогите! Мне очень нужно регулярно брать данные из больших Таблиц, фильтровать, а потом копировать результат в другие Таблицы, других Таблиц будет много (сотни их). Нужно, чтобы был интерфейс и всем этим волшебством можно было управлять из него.

Мы ответили – окей, будет тебе интерфейс. Так и появился наш скрипт для копирования Таблиц, в этом посте – его третья версия.

В прошлом году мы выпустили свой СОБИРАТОР – интерфейс для сбора других Таблиц через sheets api (самый быстрый скриптовый способ).

Задаёте правила сбора Таблиц в другие Таблицы и собираете, к примеру, 50 Таблиц ваших продавцов в одну сводную Таблицу и 30 Таблиц в другую сводную Таблицу, на лету фильтруя и оставляя только нужные столбцы.

Сегодня выпускаем обновление:
— теперь можно указать частоту загрузки каждой таблицы в минутах;
— можно выбрать тип копирования данных (formula / formatted_value / unformatted_value);
— решена проблема с добавлением новых данных: раньше они (иногда) вставлялись со сдвигом;

Главное:
Решение стало полностью автоматическим – теперь один раз заполняете конфиг сбора, выставляете частоту обновления в минутах, ставите скрипт в расписание и вуаля – данные собираются регулярно и уже без вашего участия.

СОБИРАТОР

ИНСТРУКЦИЯ

=
🔥НАШ КУРС НА SKILLBOX: Таблицы и GAS, Excel и VBA
📗ОГЛАВЛЕНИЕ КАНАЛА, категории | всё оглавление
⭐️ Заказ работы у нас

А вот и ссылки

📗 Чат, где почти 6к активных подписчиков: @google_spreadsheets_chat

📕 Оглавление канала

⭐️ Заказ работы у нас

Задача: пронумеровать список, но так, чтобы пропуски игнорировались.

Если бы пропусков не было, можно просто использовать функцию СТРОКА / ROW, возвращающую номер строки, и одной формулой массива получить все номера:

=ArrayFormula(ЕСЛИ (ЕПУСТО(B2:B);""; СТРОКА(B2:B)-1))

А с пустыми строками?
Вот такой вариант — простой , но и без формулы массива (то есть формулы будут в каждой строке листа) — сработает. Берем диапазон с началом в первой ячейке (и ее закрепляем долларами) и с концом в текущей строке (для первой строки таблицы и начало и конец у диапазона совпадают, $B$2:B2.

И считаем количество значений в этом диапазона (СЧЁТЗ / COUNTA). Проверяем на пустоту и в таком случае ячейку с номерами тоже оставляем пустой.

=ЕСЛИ(B2="";"";СЧЁТЗ($B$2:B2))

А можно ли это решить одной формулой? Можно 🤠Но это будет посложнее. Можно через MAP и СМЕЩ (OFFSET). Какие еще предложения будут у вас?

Ключевая ставка ЦБ РФ на дату - или формула имени Эльвиры Сахипзадовны

Ребятки, привет!

Вот формула:

=LET(_data; IMPORTHTML("https://cbr.ru/hd_base/KeyRate/?UniDbQuery.Posted=True&UniDbQuery.From=17.09.2013&UniDbQuery.To=" & TEXT(TODAY(); "dd.mm.yyyy"); "table"; 1); REDUCE({"Дата"\ "Ключевая ставка"}; SEQUENCE(ROWS(_data) - 1; 1; ROWS(_data); -1); LAMBDA(_acc; _i; LET(_date; --INDEX(_data; _i; 1); _value; INDEX(_data; _i; 2) / 100; IF(INDEX(_acc; ROWS(_acc); 2) <> _value; {_acc; _date\ _value}; _acc)))))

Формула достаёт значения ключевой ставки ЦБ РФ с сайта с 17.09.2013 (до этой даты данных нет) по сегодняшний день, приводит строки с датой и значением ставки к числовым значениям (формат столбца с датой надо будет задать руками, чтоб красиво отображалось).

Ещё она оставляет только новые значения: строки с датами, когда ставка не менялась отфильтровываются. Так получается 55 строк вместо 2700+. А пользоваться также: VLOOKUP (ВПР) с 4-м параметром True.

Спасибо за внимание! Всех с 1-м сентября!

🏄 И спасибо нашему прекрасному Михаилу Смирнову, он хоть и не в ЦБ (пока), но многое сделал для извлечения информации про курсы валют и показатели

Продолжаем про маркетплейсы или "в поисках идеального дашборда"

У нас очень много клиентов на разработку всякого и точно бОльшая их часть - это продавцы ВБ, ОЗОН, Яндекс Маркета.

Знаете, что мы за годы работы заметили?

Как правило, даже те, кто понял, что нужна автоматизация - приходят без конкретного понимания, какие показатели выводить, как это должно выглядеть в целом и какие решения по всему этому можно принимать в своем бизнесе.

Поэтому давайте вместе придумаем нашу народную Табличку по маркетплейсам, действительно полезную.

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

🚨 Исходим из того, что все, что можно загрузить из API маркетплейсов - мы загружаем и превращаем в любой вид.

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

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

💰 А чтобы вам было интереснее - мы выберем 1-2 человек с лучшими вариантами и сделаем для них похожее бесплатно