Работа в Google Таблицах. Кейсы, решения и угар. админы: @namokonov @r_shagabutdinov @IT_sAdmin оглавление: goo.gl/HdS2qn заказ работы: teletype.in/@google_sheets/sheet_happens купить рекламу: https://telega.in/c/google_sheets РКН: clck.ru/3F3u9M
Автоматическая группировка для строк — всё по взаимному согласию и по условиям. 😉
У нас есть скрипт, который автоматически группирует строки в Google Таблицах по заданным условиям. Работает стабильно, руками ничего складывать не нужно — всё само.
Делиться или оставить в столе до лучших времён?
Напишите в комментариях, пригодится вам такой помощник — поймём, стоит ли выкладывать. 👇🙂
А теперь, друзья, ответ на вопрос от телезрителя из солнечной Турции!
Не Мехмет интересуется:
«Как узнать, сколько памяти занимают мои проперти в Google Apps Script в проекте? Не пора ли их очищать?
Начнем с того, что общий доступный объем (неудаляемый) - 500 кб
Объем пары (ключ, значение) - 9 кб
https://developers.google.com/apps-script/guides/services/quotas#current_limitations
Скрипт, которым вы можете проверить занятый объем
function propertyUsedSize() {
const properties = PropertiesService.getScriptProperties().getProperties();
let totalBytes = 0;
for (let key in properties) {
totalBytes += getUTF8Length(key);
totalBytes += getUTF8Length(properties[key]);
}
const kilobytes = (totalBytes / 1024).toFixed(2);
Logger.log('~ объём пропертей: ' + kilobytes + ' КБ'); //~ объём пропертей: 17.65 КБ
}
function getUTF8Length(str) {
let utf8length = 0;
for (let i = 0; i < str.length; i++) {
const charCode = str.charCodeAt(i);
if (charCode < 0x80) utf8length += 1;
else if (charCode < 0x800) utf8length += 2;
else if (charCode < 0x10000) utf8length += 3;
else utf8length += 4;
}
return utf8length;
}
Аааа! Наш курс (базовая группа стартует уже завтра)
Поэтому вам вот такой интересный и простой onEdit с хранением в скрипт проперти и со структурой объект всего - объект
function dataToProp() {
const pr = PropertiesService.getScriptProperties();
const ss = SpreadsheetApp.getActive();
const s0 = ss.getSheetByName('Справочник')
.getDataRange()
.getValues()
.map(f => [f[0], f[1]])
const s = s0.slice(1);
let o = {}
l1 = s.length;
l2 = s[0].length;
for (x = 0; x < l2; x++) {
for (y = 0; y < l1; y++) {
o[s0[0][x]] = s.map((f, i) => f[x]).filter(f => f);
};
};
console.log(o);
o = JSON.stringify(o);
pr.setProperty('key', o);
};
function onEdit(e) {
let range = e.range; //ячейка
let sheet = e.source.getActiveSheet(); //лист
if (sheet.getName() == 'Рабочий') {
let row = range.getRow();
let key = e.value; //что ввели в ячейку
if (!key) return;
let column = range.getColumn();
if (row >= 1 && column == 1) {
const pr = PropertiesService.getScriptProperties();
let listdata = pr.getProperty('key')
listdata = JSON.parse(listdata);
let list = listdata[key];
let rule = SpreadsheetApp.newDataValidation().requireValueInList(list).build();
range.offset(0, 1).setDataValidation(rule);
}
}
};
Загружаем товары с Ozon через API
*или то, чем мы с вами и будем весело заниматься на курсе 🙂 *
Сегодня — научимся тянуть список всех товаров нашего купца (или, как говорят на Ozon, селлера) через API прямо в Google Таблицы.
Я специально разобрал код в видео медленно, пошагово и с объяснениями, также сам код прокомментировал внизу
function list() {
const url = 'https://api-seller.ozon.ru/v3/product/list';
let params = {
method: 'post',
muteHttpExceptions: true,
contentType: 'application/json;',
'headers': {
'client-id': Client_Id,
'Api-Key': Api_Key
}
};
var arr = [];
for (var x = 0; ; x++) {
let payload = {
"filter": {
"visibility": "ALL"
},
"last_id": !x ? '' : last_id,
"limit": 1000
};
params['payload'] = JSON.stringify(payload);
console.log('x:', x);
console.log(params);
var r = UrlFetchApp.fetch(url, params); //строка
r = JSON.parse(r); //JSON
var last_id = r.result.last_id
r = r.result.items;
if (!r.length) { break; };
var headers = Object.keys(r[0]);
r = r.map(rr => headers.map(h => h == 'quants' ? rr[h].join(',') : rr[h]));
arr = [...arr, ...r];
};
arr = [headers, ...arr];
Logger.log(arr);
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName('list')
sh.clearContents();
sh.getRange(1, 1, arr.length, arr[0].length)
.setValues(arr);
};
✅ Таблица умножения — решена!
Друзья, огромное спасибо за вашу невероятную активность в комментариях!
Честно, мы не ожидали, что будет настолько много вариантов формул — и таких разных, и таких находчивых.
Каждому, кто попробовал себя в этом задании, мы начислили +5 кармы в нашем чате — заслуженно 💎
Особенно радует, что даже простая задача может вдохновить на десятки оригинальных решений.
Одно из многих элегантных решений:
= ARRAYFORMULA(SEQUENCE(10) * SEQUENCE(1; 10))
function pifagor(n) {
return Array.from({ length: n }, (_, x) => // создаём внешний массив (строки), длины n
Array.from({ length: n }, (_, y) => // для каждой строки создаём массив (столбцы), длины n
(x + 1) * (y + 1) // вычисляем значение ячейки: (номер строки + 1) * (номер столбца + 1)
)
);
}
Хотите сменить профессию? Станьте бизнес-аналитиком за 7 месяцев
🔹 Используйте свою экономическую базу — превратите знания в востребованные навыки
🔹Работайте с данными и стратегиями — помогайте бизнесу принимать выгодные решения
🔹Освойте все необходимые инструменты для старта в профессии — SQL, Tableau, моделирование в нотациях IDEF0, BPMN 2.0, EPC
Уже через полгода вы сможете:
1. Анализировать бизнес-процессы и искать пути оптимизации.
2. Решать реальные кейсы от работодателей и собрать портфолио.
3. Претендовать на позицию младшего бизнес-аналитика и расти в карьере.
Нетология поможет с получением работы. Во время учёбы вы получите опыт работы с заказчиками и разберёте тестовые задания из реальных вакансий с поддержкой экспертов в бизнес-аналитике. Специалисты по трудоустройству помогут найти компании для стажировки, составить резюме, портфолио и подготовиться к собеседованию.
Присоединяйтесь к курсу «Бизнес-аналитик» от Нетологии со скидкой 45% по промокоду ANALYTIC45.
Подробнее о курсе
Реклама. ООО "Нетология". ИНН 7726464125 Erid 2VSb5xNA9u2
Пробуем бесплатного бота про слова
Привет, чем заняться в воскресенье? Можно шкаф передвинуть, а можно попробовать бесплатного бота от Александра Брусенцова, моего экс-коллеги по компании Яндекс.
Задача игры — написать на заданную тему 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
Скоро планируем интересные материалы – вернемся к формулам, а еще покажем вам бота для выбора пришедших на урок / занятие на инлайн-кнопках, с помощью сторис - про них напомним, если пропустите в ленте.
🥷
Друзья, подскажите!
Кто-нибудь записывает вебинары с автоматическими субтитрами и полной расшифровкой речи (желательно с таймкодами)?
Мы перепробовали разные инструменты, но пока не нашли идеальное решение.
Будем очень благодарны за ваши советы и опыт — делитесь в комментариях! 🙏
Друзья, пару мест на курсе осталось и стартуем
На нашем курсе не грузим теорией. Учим тому, что реально используют на работе — и за что платят заказчики:
✅ Автоматизация Google Таблиц, Документов, Диска, Почты и Календаря
✅ Подключение к внешним API и загрузка данных напрямую в Таблицы
✅ Парсинг сайтов
✅ Скрипты, которые создают отчёты на основе формул
Только практические кейсы — никаких «абстрактных задачек».
🚀 Про курс — коротко:
1. 💸 Стоимость — 100 000 ₽
2. 👥 Маленькие группы
3. 🖥 Онлайн-занятия 2 раза в неделю + записи
4. 💬 Чат участников
5. 📆 Длительность — 1,5–2 месяца
6. 🎯 Наша цель — *реально научить*
❓Вопросы и бронь, оплата — @namokonov, @elizaveta_sh_komarova
🌪 Стартуем скоро!
✅ Сегодня всем подписчикам канала мы открыли бесплатный доступ к курсу "Инструменты Excel для финансиста" в Высшей школе финансового директора.
Изучив курс, вы сможете:
🔶Создавать в Excel динамические графики и гистограммы
🔶Настроить дашборд с метриками под нужды вашей компании
🔶Создать панель оперативного мониторинга и отслеживать показатели
🔶Построить интерактивный график для анализа затрат в Excel
+ В подарок книга по работе в Excel, которая прокачает ваши навыки
Скачивайте дашборды, инструкции, матрицы ключевых метрик, шаблоны с диаграммами и применяйте их в своей работе!
Заказать бесплатный доступ к курсу на 3 дня>>>
#реклама
О рекламодателе
Что вы конкретно получите на нашем курсе по разработке на Таблицах, пример
Показываю в видео два примера обращения к API OZON
— получает токен
— получаем по этому токену рекламные кАмпании, берем из них отдельные элементы и вставляем всё в Таблицу
Таблица с кодом
На нашем курсе не грузим теорией. Учим тому, что реально используют на работе — и за что платят заказчики:
✅ Автоматизация Google Таблиц, Документов, Диска, Почты и Календаря
✅ Подключение к внешним API и загрузка данных напрямую в Таблицы
✅ Парсинг сайтов
✅ Скрипты, которые создают отчёты на основе формул
Только практические кейсы — никаких «абстрактных задачек».
🚀 Про курс — коротко:
1. 💸 Стоимость — 100 000 ₽
2. 👥 Маленькие группы
3. 🖥 Онлайн-занятия 2 раза в неделю + записи
4. 💬 Чат участников
5. 📆 Длительность — 1,5–2 месяца
6. 🎯 Наша цель — *реально научить*
❓Вопросы и бронь, оплата — @namokonov, @elizaveta_sh_komarova
🌪 Стартуем скоро!
Задачка для подписчиков
Друзья, привет! Всех с началом новой недели.
У нас для вас есть небольшая задачка - сделайте классическую, таблицу умножения от 1 * 1 до 9 * 9, реализовать можно как формулами, так и скриптами.
То, что получится - пишите в комментарии или в наш чат @google_spreadsheets_chat
🚀 В следующем посте покажу свой вариант с помощью скриптов.
Тренинг "Табличные формулы для продолжающих"
Друзья, остается 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
Работает как в личных сообщениях, так и в чатах (только в чатах добавляйте как администратора с правом удалять сообщения)