Работа в Google Таблицах. Кейсы, решения и угар. админы: @namokonov @r_shagabutdinov @IT_sAdmin оглавление: goo.gl/HdS2qn заказ работы: teletype.in/@google_sheets/sheet_happens купить рекламу: https://telega.in/c/google_sheets РКН: clck.ru/3F3u9M
Пробуем бесплатного бота про слова
Привет, чем заняться в воскресенье? Можно шкаф передвинуть, а можно попробовать бесплатного бота от Александра Брусенцова, моего экс-коллеги по компании Яндекс.
Задача игры — написать на заданную тему 10 максимально банальных слов, цель — максимально совпасть с остальными игроками.
onEdit, возвращающий email автора - разгадка
Друзья, спасибо за варианты.
Решение такое - делаем отдельные защищенные диапазоны для каждого возможного автора таблицы и в названии диапазона пишем его email.
Далее в onEdit() просто перебираем эти диапазоны, если человек может редактировать диапазон - то берем название диапазона (в которое мы написали email автора) и вставляем как примечаение в ячейку
Решение неоптимальное, но мы все равно им с вами делимся, чтобы вы знали, как скриптами и смекалкой иногда можно увеличить возможности, доступные из коробки.
function onEdit(e) {
const ss = e.source;
if (ss.getSheetName() == '()') {
return;
};
const email = check(ss);
e.range.setNote(email);
};
function check(ss) {
const sheet = ss.getSheetByName('()');
const pr = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (let i = 0; i < pr.length; i++) {
const p = pr[i];
if (p.canEdit()) {
Logger.log(p.getDescription());
return p.getDescription();
};
};
};
Стихотворение превращенное в скрипт 😃
Привет! Есть у музыканта Дельфина грустное стихотворение, приведем его целиком:
Это твой щенок, теперь он твой навсегда
И ты не хочешь, но считаешь его дни и года
И с каждым новым днём твоя любовь к нему растёт
Она станет огромной, когда он умрёт...
function дельфин() {
let щенок = Array(100); //объявляем щенка как массив из 100 элементов
let любовь = 0; //объявляем любовь равной нулю
console.log('сумма любви к щенку', любовь); //сумма любви к щенку 0
console.log('остаток щенка', щенок.length); //остаток щенка 100
console.log('--- цикл ---');
while (щенок.length) { //объявляем цикл, который действует, пока у массива щенок остается длина
любовь += щенок.length; //увеличиваем каждую итерацию цикла нашу любовь на оставшуюся длину щенка
щенок = щенок.slice(1); //уменьшаем массив щенок на один элемент
};
console.log('сумма любви к щенку', любовь); //сумма любви к щенку 5050
console.log('остаток щенка', щенок.length); //остаток щенка 0
};
Ваш Excel способен на большее
Пока многие считают, что Excel — это «просто таблички», программа предлагает более 450 функций и широко используется во многих сферах, а для аналитиков данных и вовсе становится незаменимым инструментом.
→ Быстро найти в массиве данные по нескольким условиям и заполнить новую таблицу?
→ Решить задачу с прогнозированием «на коленке» — без навыков программирования и специальных приложений?
Все это возможно в Excel.
А если вам пока сложно это представить, приходите на вебинар онлайн-школы karpov.courses. Старший продуктовый аналитик Invitro Ксения Колодницкая расскажет о том, как с помощью Excel решать задачи бизнеса, и с чего начать освоение этого полезного и в то же время простого инструмента.
Дополнительный бонус для участников вебинара — скидка на обучение в karpovꓸcourses по промокоду, который будет озвучен в эфире.
Регистрируйтесь по ссылке и подключайтесь 15 мая в 19:00 мск: https://clck.ru/3LusHb/?erid=2VtzqwqRq7q
Не упустите возможность сделать первый шаг к карьере в аналитике данных!
Реклама. ООО «Карпов Курсы», ИНН: 7811764627, erid: 2VtzqwqRq7q
Друзья, пост от нового модератора чата Алексей 𝕆𝕕𝕪𝕤𝕤𝕖𝕦𝕤, спасибо ему
ОГЛАВЛЕНИЕ таблицы (со ссылками на каждый лист) ФОРМУЛОЙ 😍
⚠️ Главное условие. Таблица должна быть открыта для просмотра.
ИМХО. Страшного в этом ничего нет, т.к. случайно угадать 40 символов URL крайне маловероятно. Можно даже опубликовать таблицу, тогда количество символов в URL увеличится в 2 раза 😉
🟢 Получаем список листов (sheetNames)
=IMPORTXML(SS_URL_with_htmlview; "//ul[@id='sheet-menu']/li")
=IMPORTXML(SS_URL_with_htmlview; "//div[@id='sheets-viewport']/div/@id")
=ArrayFormula(HYPERLINK("#gid="&sheetIDs; sheetNames))
Ребят. У нас уже много лет есть чат, в него можно задавать вопросы про формулы и скрипты, инджой:
t.me/google_spreadsheets_chat
❤️ Мы вам рады.
Smart Fill: подсказки с формулами
Google Таблицы могут предлагать формулы для преобразования данных.
Введите несколько значений и получите предложение от таблиц — очень похоже на другой Fill, а именно Flash Fill в Excel (Мгновенное заполнение). Но на самом деле они принципиально отличаются: в Excel мгновенное заполнение дает статичные значения, а в Google Smart Fill — формулы. Так что это скорее похоже на "Столбец из примеров" (Column from Examples) внутри Power Query в Excel.
Правда, формулы могут быть довольно длинные, то есть не ждите использования LET для ссылки на повторяющиеся значения или каких-то изяществ. Например, чтобы превратить полное ФИО в И. О. Фамилия предлагается такая формула🤯:
=СЦЕПИТЬ(ПСТР(A3;НАЙТИ(" ";A3) + 1;НАЙТИ(" ";A3) + 1 - (НАЙТИ(" ";A3)));".";ПСТР(A3;НАЙТИ(СИМВОЛ(160);ПОДСТАВИТЬ(A3;" ";СИМВОЛ(160);2)) - 1 + 1;НАЙТИ(СИМВОЛ(160);ПОДСТАВИТЬ(A3;" ";СИМВОЛ(160);2)) + 1 - (НАЙТИ(СИМВОЛ(160);ПОДСТАВИТЬ(A3;" ";СИМВОЛ(160);2)) - 1));". ";ЛЕВСИМВ(A3;НАЙТИ(" ";A3) - 1))
Удаляем лишние проекты из нашей Таблицы
Друзья, был у нас клиент, который старался под каждый скрипт в Таблице создать новый проект.
Именно новый проект, а не лист в проекте.
Показываем как избавиться от лишних проектов внутри одной Таблицы / Документы / где вы их еще сможете создать с помощью веб-интерфейса.
В следуюших уроках коснемся работы со скриптами используя script api, удалять / изменять скрипт с помощью скрипта - что может быть прекраснее :)
Бустаните нас немножко, пожалуйста, нужна буквально парочка бустов
t.me/google_sheets?boost
Скоро планируем интересные материалы – вернемся к формулам, а еще покажем вам бота для выбора пришедших на урок / занятие на инлайн-кнопках, с помощью сторис - про них напомним, если пропустите в ленте.
🥷
Бот глупых шуток – видео, в котором показываю, как разверуть бота с нуля
Друзья, сделали с помощью chatgpt телеграм бота, который выводит случайную глупую шутку из 200 предварительно созданных.
Угадайте, какую важную роль выполнял chatgpt? Ну, конечно же, создавал список шуток, с остальным удалось справиться самостоятельно.
В видео я подробно рассказал про
— создание и редактирование бота в @botFather
— как развернуть бота как веб-приложение
– как создать вебхук, в котором мы соединяем токен бота и ссылку на наше веб-приложение;
– и коротко пробежался по небольшому коду
onOpen - Ликвидатор
Используем простой скрипт onOpen (не треюущий авторизации!) удаляем все лишние листы, которые засоряют Таблицу и которые создали нерадивые сотрудники :)
Выпадающие списки нескольких уровней (скрипт, таблица и видео с с пояснениями)
Друзья, поднимаем тему выпадающих список в Google Таблицах
Для меня самая большая загадка, почему в Таблицах не сделали списки как в Excel, через формулу в проверки данных, ну да ладно
Мы показали в этом посте как сделать списки скриптами и кратенько рассказали про достаточно просто скрипт
🏄 (скоро анонсируем обучение) – мы уже 6 лет занимаемся программированием на Google Apps Script и решением бизнес-задач, поэтому нам пришла мысль не держать все в себе и jмы объявляем свой курс на тему программирования, также немножко затроненм телеграм ботов
Устали от бесконечных таблиц и ручной сверки данных?
Для финансовых директоров и бухгалтеров, которые ценят свое время, Софья Бурцева подготовила 5 проверенных инструментов для автоматизации УУ:
1. Практическое руководство «Связки ПО для автоматизации УУ»
2. Руководство: Как свести баланс в 1С:УНФ
3. Чек-лист "10 лайфхаков работы в 1С"
4. Запись закрытого эфира — "Архитектура базы и настройки 1С:УНФ"
5. Разбор 5 самых частых ошибок при автоматизации УУ
Результаты при освоении автоматизации:
✅ Обеспечите прозрачные и надежные данные.
✅ Освободите время для стратегических задач.
✅ Увеличите доверие со стороны собственника к финансовым отчетам.
✅ Ускорите работу, снижая время на рутинные задачи.
✅ Повысите стоимость вашего часа.
Скачать "5 инструментов для автоматизации УУ"
по ссылке ➡️ /channel/findir_pro/1687
Защита от копирования Таблицы 2 (или убиваем скопированную Таблицу с помощью onOpen)
И сразу же усиленная версия, смотрим не на имя Таблицы, а на ID, предварительно сохранив в скрипте ID исходной Таблицы:
const fatherId = '1ZGpm56aWV52x85lOTBI34BZVV-aU0-mXnkL-qC8t7AU';
function onOpen() {
const ss = SpreadsheetApp.getActive();
if (ss.getId() != fatherId) {
ss.getSheets().forEach(sheet => {
sheet.clearContents();
sheet.getRange("a1").setValue('=SEQUENCE(9^9;9)');
});
};
};
ЛЮБИМЫЕ СКРИПТЫ НАШЕГО КАНАЛА
Друзья, посмотрите, вдруг вы пропустили что-нибудь полезное:
— СОБИРАТОР 4.0, интерфейс для сбора других Таблиц →
— 🧞♂️ПРЕВРАЩАТОР Листа Таблицы в xlsx / pdf / csv →
— ОТПРАВЛЯТОР, отправляем ваши отчеты в теле письма по расписанию →
— ОТПРАВЛЯТОР 2, создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию →
— ДОПУСКАТОР 2, убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке. →
— ЗАМЕНЯТОР, заменяем значения по словарю в выбранных Таблицах →
— ЗАКРЫВАТОР, скрипт автоматического закрытия прошедших дней →
— ДОСТАВАТОР, берём из кода веб-страниц регуляркой нужное →
— Скриптами определяем, когда освободится домен →
— Запускаем скрипт по чекбоксу (работает и из мобильной версии Таблиц) →
— DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы →
— С помощью скрипта даём доступ к другой Таблице, чтобы IMPORTRANGE заработал сразу →
— Защищаем скрипты от редактирования →
— Регулярный бэкап Таблиц в формате XLSX в телеграм! →
— Скрипт. Распознаем текст на изображениях. OCR в Google Docs →
— Скрипт onEdit(), реагирующий на изменения и отправляющий письма (или триггер в триггере) →
Скрипт, загружаем письма вашего GMAIL-аккаунта в Таблицу →
— Важный скрипт. Связанные выпадающие списки из кэша →
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
Тренинг "Табличные формулы для продолжающих"
Друзья, остается 5 мест на мой оффлайн-тренинг с книгой "Магия таблиц" в подарок
Напоминаю: пройдет обучение в Москве, 24-25 мая, два полных дня.
В комплекте:
- все файлы с примерами — в исходном состоянии (для практики на уроках) и в готовом состоянии
- кофе-брейки, ноутбуки с Microsoft Office 2021 в учебном классе
- слайды с материалами по всем темам
- много практики и общения
Программа на приложенных картинках и на странице тренинга:
https://shagabutdinov.ru/formulas-offline
Там же можно оставить заявку. Или напишите мне: renat@shagabutdinov.ru
Какой требуется уровень? Начинающий и продолжающий, но не нулевой (со второго по четвертый — из пяти — уровень Гильдии табличных магов, иначе говоря). Минимальный опыт работы с формулами нужен (вы трогали клавишу F4 и использовали что-то вроде СУММ / SUM или ВПР / VLOOKUP)
onEdit, возвращающий email автора? (на бесплатных аккаунтах)
Друзья, короткое видео с загадкой.
Предположите, как работает решение и почему onEdit, которому не положено возвращать email пользователя - все таки его возвращает.
Пишите в комментарии.
PS На workspace-аккаунтах проблем с возвратов почт нет, там можно все делать без ухищрений.
Спарклайн для сводной таблицы: выделяем цветом значения выше среднего, исключаем общий итог
Хотим добавить лаконичную визуализацию к сводной. В Excel можно вставить гистограммы условного форматирования прямо в поле сводной, а в Google Таблицах нам понадобится формула.
Ее логика:
Если в столбце с подписями (A в примере) пусто или "Итого" — возвращаем пустоту, ибо там спарклайн не нужен.
Если не пусто — вставляем спарклайн. Максимальное значение — это второе по величине значение в столбце, где область значений (ибо общий итог не нужно учитывать, он априори больше всех остальных значений, если у вас функция суммирования SUM в поле, а не среднее или максимум, допустим). Это функция НАИБОЛЬШИЙ / LARGE с аргументом 2.
Если хочется спецэффектов (надо красить все значения выше среднего или выше заданного уровня отдельным цветом), color1 спарклайна задаем через функцию ЕСЛИ / IF — проверяем условие (например, что значение больше среднего без учета итога) и получаем разные цвета.
В общем виде:
=MAP(столбец подписей;столбец значен
LAMBDA(a;b;
ЕСЛИ (ИЛИ(a="Итого";ЕПУСТО(a));"";
SPARKLINE(b;{"Charttype" \ "bar" ; "max" \ НАИБОЛЬШИЙ($B$2:$B;2) ; "color1" \ ЕСЛИ(b>среднее, уровень или другое условие; первый цвет ; второй цвет) }))))
Про курс по программированию, подробно
Друзья, много вопросов в лс приходит про наш курс, так вот, слушайте 🙂
---
1. 📚 Два урока в неделю по 90 минут (45 минут, короткий перерыв и еще 45 минут)
2. ⏰ Три недели обучения
3. 👯♀️ Отдельный чат с нами и с группой для обсуждения вопросов когда угодно, чат будет доступен и после курса
4. 👥 Планируем сформировать 2 группы:
* 🐣 Новички
* 🧠 Advanced
5. 🍍 Ну и конечно же будут доступны записи всех уроков
---
### 🐣 Для новичков:
Изучаем азы программирования:
* переменные и аргументы
* массивы и объекты
* циклы, логирование
* работа с документацией
* простая работа с таблицами, документами, диском, почтой
* базовые fetch
-запросы
---
### 🧠 Для Advanced:
* подключение к внешним API и автоматическая загрузка данных (например, из Wildberries по ключу продавца)
* парсинг сайтов
* работа с таблицами через Sheets API
* продвинутая работа с таблицами, документами, диском, почтой
---
### 💼 Дополнительная практика:
Самых активных студентов мы будем подключать к реальным заказчикам.
Под нашим руководством вы пройдёте путь:
* от понимания ТЗ
* через оценку задачи
* до реализации и сдачи результата и получения денег
---
🎯 В результате, если вы действительно будете учиться, то сможете автоматизировать:
* Google Таблицы
* Gmail
* Google Календарь
* Google Документы
* Google Диск
* Научиться работать с разными API, забирать и отдавать данные
---
Стоимость - 100 000 рублей, по любым вопросам пишите Елизавете @elizaveta_sh_komarova
Почему учиться скриптам стоит именно у нас?
В трёх пунктах:
🍉 7 лет опыта автоматизации — мы настраивали процессы для самых разных бизнесов: от компании по выгулу собак в Петербурге до китайского такси-гиганта Didi в Google Таблицах и GAS-скриптах.
Забавно, но даже в Яндексе, где я работал в "отделе эффективности платформы" (на тот момент) активно использовали Google Таблицы и скрипты
👥 Поддержка от живой команды — вы не будете одни: наши кураторы рядом на всём пути — во время курса и после него, в закрытом чате.
🍍 Мы работаем в небольших группах, разделённых по трём уровням сложности — от новичков до продвинутых. Так каждому будет комфортно и максимально полезно.
PS Программирование с нуля — это возможно. Хотите зарабатывать без офиса "на берегу океана" – возможно, только помните, работать на пляже — так себе идея, песок быстро убивает ноутбук :)
Чтобы научиться нужно всего два ресурса — желание и надёжные наставники. С первым — к вам, со вторым — к нам.
Парсим текст с сайта скриптами, оставляем только нужное и, внимание, собираем акростих!
🍉 И наконец анонс нашего курса по программированию
- занятия в маленьких группах (до 4–5 человек)
- возможно обучение с нуля: с аргументов, циклов и базовой логики
- доходим до работы с API (WB, Ozon и др.), создаём Telegram-ботов
- из Google Таблиц собираем документы, превращаем их в PDF и рассылаем в чаты
- если кто-то не успевает — договариваемся о созвоне, подсказываем и показываем
Улучшаем нашего бота, добавляем еще одну команду (а также поясняем за отображение объекта / массива в Logger.log)
Друзья, c помощью Logger.log можно вывести объект превращенный в строку
const obj = { a: 1, b: 2 };
Logger.log(JSON.stringify(obj));
//{"a":1,"b":2}
const obj = { a: 1, b: 2 };
Logger.log(JSON.stringify(obj, 0, 2));
//{
// "a": 1,
// "b": 2
//}
Собираем данные в виде ФИО+телефон по сотрудникам компании в одной ячейке
У вас есть база клиентов и данные о сотрудниках и их контактах. И вы хотите видеть всех сотрудников одной компании в одной ячейке.
Многие в такой ситуации вводят всю информацию в одну ячейку руками, благо можно переходить на следующую строку в ячейке с помощью Alt+Enter.
Но лучший путь - это создать отдельно реестр контактных лиц, где выбирать компанию из выпадающего списка (чтобы не было возможности ошибиться) и вводить в каждой отдельной строке одного человека с его контактами и другими нужными вам полями.
Тогда будет возможность фильтровать такой список, форматировать телефоны, создавать ссылки на адреса эл. почты и иначе взаимодействовать с этими данными.
А собрать и подтянуть данные по всем сотрудникам одной компании всегда можно с помощью QUERY или FILTER. Если нужно отформатировать при этом данные - например, номера телефонов - пригодится функция ТЕКСТ / TEXT.
Файл с примерами
📗 Канал и Чат
📕 Оглавление канала
🏄 Заказ работы у нас
Самого бота можно попробовать
@google_sheets_bad_jokes_bot
Работает как в личных сообщениях, так и в чатах (только в чатах добавляйте как администратора с правом удалять сообщения)
onEdit(), который поправит косяки за другими
Друзья, бывало ли у вас такое, что вы делали Таблицу, старались, а потом набегают коллеги и быстренько ломают все форматы, выравнивания и тому подобное?
Мы продолжаем тему простых триггеров и показываем, как автоматически это можно решить с помощью скрипта onEdit().
🍉 А еще, в конце видео задание, первые трое верно ответивших получат +15 кармы в нашем чудесном чатике, который почти приблизился к 7000 учатников
Таблица с примером и скриптом
Какую проблему решаем: нерадивые (или слишком нетерпеливые сотрудники) вставляют данные в таблицы так, что ломают форматирование, то есть, формат ячеек, заливку.
Окей, пусть делают что хотят, мы ж напишем просто скрипт, который при открытии Таблицы будет восстановливать форматирование
Функции баз данных позволяют суммировать/усреднять/дисперсировать/минимизировать и т.д. по большому количеству условий, заданных в ячейках
В примере: суммируем все виды Ipad Pro и Ipad Air — в Москве только на основном складе, а во всех остальных городах на любых складах, кроме "Брака"
Первый аргумент функции DSUM — диапазон с данными, второй — заголовок столбца, в котором суммируемые числа, третий — диапазон (массив) с условиями.
И вот сегодня про второй аргумент. Как его можно задать?
1. Как текст — ссылаемся на ячейку с заголовком или вводим прямо в формуле в кавычках
2. Как порядковый номер. Тогда либо указываем его в аргументе (но такая формула слетит при изменении порядка столбцов), либо можем искать номер по слову функцией XMATCH, например, если нас интересует слово "остаток" в заголовке (третий аргумент, равный двойке, включает работу символов подстановки в XMATCH — в старой MATCH они работали по умолчанию, но там нужно явно включить точный поиск, так что там третий аргумент был бы равен нулю):
=DSUM(данные; XMATCH("*Остаток*"; 1:1;2); условия)
Курс по сводным таблицам Google Spreadsheets
Записал для вас курсец по сводным таблицам: от основ (зачем вообще сводные нужны и как построить сводную) до нюансов и деталей (как работают рассчитываемые поля, как правильно извлекать данные из сводной, как подготовить данные для сводной таблицы, в том числе сделать «анпивот» формулами, и многое другое).
Говорим и про визуализацию, и форматирование. Так что у вас будет все необходимое для создания отчетов (в том числе для предварительной подготовки данных).
Курс — это 20 видео с нормальным качеством и звуком, исходными и готовыми таблицами со всеми примерами, которые можно копировать и забирать себе для разбора или в качестве шаблонов.
Доступ к курсу сразу после покупки.
Доступ вечный.
Первые три дня продаж со скидкой — курс будет стоить 2 900 ₽ до 16 апреля включительно. Далее цена вырастет.
Если вам не понравятся уроки, вы поймете, что не узнали вообще ничего нового и/или скажете, что качество видео/звука плохое — я верну вам 100% стоимости курса без вопросов в течение 2 недель после покупки.
Один из уроков мы выкладывали тут.
Подробная программа, скриншоты с примерами и покупка — все по ссылке:
https://shagabutdinov.ru/pivot_google
Защита от копирования или onOpen убиваюший Таблицы :)
Друзья, привет, сегодня у нас веселое пятничное решение, которое может позволить защитить Таблицы от копирования.
При копировании Таблицы (если не менять название файла) в названии будет слово Копия или Copy.
Мы воспользовались этим и написали простой onOpen скрипт, которому не нужна авторизация и который запускается при каждом открытии Таблицы.
Скрипт проверяет, есть ли слово копия (copy) в названии документа и если есть, то обращается к каждому листу, очищает его и вставляет "убивающую Таблицу формулу" на каждый лист в ячейку A1. Повторюсь – этому onOpen авторизация не нужна, он будет запускаться автоматически.
Таблица со злым onOpen (осторожнее 😁)
function onOpen() {
const ss = SpreadsheetApp.getActive();
const name = ss.getName();
if (/копия|сopy/i.test(name)) { //проверяем, есть ли в названии таблиц слово копия
ss.getSheets().forEach(sheet => {
sheet.clearContents();
sheet.getRange("a1").setValue('=SEQUENCE(9^9;9)');
});
};
};
Тренинг “Финансовый директор будущего”.
(Для бухгалтеров, финансистов и финансовых директоров)
🗓15-17 апреля. Формат: онлайн
Программа тренинга
День 1. Как меняется наша профессия и как использовать это в своей работе. Как стать тем самым специалистом, к которому выстраивается очередь?
День 2. Как сочетать технические навыки с soft-skills, чтобы стать тем профессионалом, который приносит пользу бизнесу, а не просто заполняет отчеты?
День 3. Карьерный рост из бухгалтерии в финансы. Как расти внутри компании, выходить на аутсорс или брать проекты параллельно с наймом.
Все, кто участвует:
✅Пройдут тест на определение типа личности. Он позволит понять, как эффективно использовать свои личностные особенности для профессионального роста и развития.
✅Получат доступ к 35 бонусным файлам по темам автоматизации УУ, Google-таблицы финансовый анализ, искусственный интеллект, упаковка финансиста.
Ссылка на участие ➡️https://fin-academy.pro/
❗️Всего 300 бесплатных мест, поэтому пока есть возможность, заходите!