Страница телеграм канала Google Таблицы
Google Таблицы
14171 подписчиков
Работа с данными в Google Таблицах. Кейсы, решения и угар. Объясняем на гифках. наша книжка: https://goo.gl/rDHu2N оглавление канала: https://goo.gl/HdS2qn Написать нам: @namokonov, @renat_shagabutdinov
Входит в категории: Технологии
14.09.2021 12:09
Достаём дни формулой из Таблицы
Есть ряд ресурсов из которых мы можем выгружать данные по API прямо из Таблиц, формулами или скриптами, например, isdayoff.ru. Рассмотрим, как с ними поработать формулами.
1) Сформируем текстовую строку для обращения к API и подставим её в функцию IMPORTDATA, в ячейках A2 и B2 — год и месяц (2021 и 11)
=IMPORTDATA("http://isdayoff.ru/api/getdata?year="& A2 &"&month="& B2 &"&cc=ru")
2) Получаем строку 000111100000110000011000001100
0 в строке — рабочий день, 1 — выходной. Чтобы получить количество рабочих дней — заменим все 1 на пустоту и посчитаем длину получившейся строки
=LEN(REGEXREPLACE(A5;"1";))
//20
3) Чтобы получить список дата / тип дня (выходной или рабочий):
=index({date(A2;B2; SEQUENCE(len(A5))) mid(A5;SEQUENCE(len(A5));1)})
Таблица с примером
PS Альтернативы из чата: Дамир загружает данные скриптами и вставляет значения, Дмитрий выгружает рабочие часы из xmlcalendar.ru
14.09.2021 12:09
Сколько рабочих дней в ноябре с учётом праздников??
— Достаём производственный календарь формулой!
На скриншоте — производственный календарь за ноябрь, в месяце 30 дней, из них 20 рабочих и 10 выходных.
13.09.2021 12:09
Клиенты уходят, а продажи компании падают?
Классическая ситуация, если неправильно выстроен процесс работы с заказчиком. Пора это исправить!
Битрикс24.CRM — это бесплатный и надежный помощник для отдела продаж, который:
— Поможет упорядочить поток клиентов. Вам больше не придется переходить из аккаунта в аккаунт в соцсетях и собирать заявки вручную. В CRM автоматически падают все заказы из Телеграма, Инстаграма, Вотсапа, Вконтакте, телефона. Это позволит легко общаться с клиентами прямо из CRM.
— В реальном времени показывает, как обстоят дела в продажах: что в работе, на какой стадии клиент, кто им занимается и на что обратить внимание. CRM напоминает, кому написать письмо, выставить счёт, а кому допродать.
— Будет принимать оплату там, где удобно вашим клиентам: прямо из CRM вы сможете сформировать заказ и отправить ссылку на оплату в мессенджеры, соцсети, по SMS и даже в директ инстаграма. Клиент выберет удобный способ, а вся информация по сделке сохранится в CRM.
Переходите по ссылке, и подключайтесь к телеграм-ассистенту от Битрикс24. Следуйте подробным инструкциям по работе в CRM и стройте свой бизнес по-новому!
#реклама
12.09.2021 10:09
Эмулируем действия браузера: проходим авторизацию скриптами, загружаем отчет в csv, а потом парсим и вставляем в Таблицу
Еще один живой пример — отправляем на страницу авторизации сайта post-запрос, с username / password и получаем cookies.
Далее запрашиваем свой отчет в CSV (он доступен только авторизованным пользователя), парсим его и без сохранения на Google Диск сразу вставляем в активную Google Таблицу, всё это — скриптами.
Код целиком:
- получаем cookie,
- запрашиваем свой отчёт
- парсим CSV
- вставляем в Таблицу
pastebin.com/WL06GdM8
Пример про сайт doggylogs.com, но по аналогии вы можете переписать код под получение данных из нужного вам ресурса. Главный инструмент и помощник вам в этом – консоль в браузере, проходите авторизацию, запрашивайте данные и смотрите, на какие url и в каком формате браузер отправляет данные. Изменяйте код из примера и сможете автоматически загружать данных из сайтов без API. Инджой :)
10.09.2021 13:09
Хотите улучшить свое портфолио и получить продвижение по работе всего за 4 месяца? Тогда курс «Excel + Google-таблицы с нуля до PRO» как раз для вас. Спешите занять место со скидкой!
Курс подойдет тем, кому нужно быстро делать расчеты, планировать продажи, разрабатывать маркетинговый план и вычислять стоимость инвестиционных объектов.
После прохождения курса ваше резюме пополнится такими ключевыми навыками, как:
создание сводных диаграмм, спарклайнов;
прогнозирование ситуаций и различных показателей;
работа с диапазонами;
импорт и экспорт данных;
умение фильтровать большие массивы;
создание макросов для VBA;
работа с функциями проверки данных.
Подробнее: https://clc.to/7tNcOQ
#реклама
09.09.2021 10:09
Небольшая статья про api, cookie, авторизацию на минималках
Когда вы вводите свой логин и пароль на сайте через браузер – вы получаете cookie, cookie помогают при перезагрузке страницы или при входе через некоторое время не авторизовываться заново, а использовать уже созданную сессию.
В материале от Романа Игнатова простой пример того, как на сайте без API через GAS-скрипты можно получить cookie и дальше использовать их открывая страницы, которые доступны только после авторизации пользователям.
Кажется, что это первый материал на эту тему на русском
ignatov-script.blogspot.com/2021/09/api.html
P. S. "Авторизовываться", с помощью логина и пароля в этом примере не требуется, но если для вашего сайта это необходимо – формируйте post-запрос на страницу авторизации, в payload передавайте логин и пароль и забирайте cookie уже из headers этого запроса
07.09.2021 13:09
И сразу вторая "усиленная" :) версия скрипта – логируем изменения только в листах / диапазонах, которые перечислены в dict.
let dict =
{
Лист1: { colFrom: 1, colTo: 5, rowFrom: 1, rowTo: 5 },
Лист2: { colFrom: 1, colTo: 5, rowFrom: 1, rowTo: 5 }
};
function onEdit(e) {
let [shName, row, col] = [e.source.getSheetName(), e.range.getRow(), e.range.getColumn()]
if (e.value != e.oldValue
&& shName in dict
&& dict[shName].colFrom <= col && dict[shName].colTo >= col
&& dict[shName].rowFrom <= row && dict[shName].rowTo >= row
) {
let ss = e.source;
let sh = ss.getSheetByName(лог) || ss.Sheet(лог);
let email = Session.getEffectiveUser().getEmail();
let log_data = [shName, e.range.getA1Notation(), e.value, e.oldValue, email, new Date()];
sh.appendRow(log_data);
}
}
Код в pastebin: pastebin.com/pmnSCR7F
07.09.2021 13:09
Логируем любые изменения в Таблице:
function onEdit(e) {
if (e.value != e.oldValue) {
let ss = e.source;
let sh = ss.getSheetByName(лог) || ss.Sheet(лог);
let email = Session.getEffectiveUser().getEmail();
let log_data = [shName, e.range.getA1Notation(), e.value, e.oldValue, email, new Date()];
sh.appendRow(log_data);
}
}
Код в pastebin: pastebin.com/WsBScirz
07.09.2021 13:09
скрипт, который логирует все изменения в Таблице, сохраняя email пользователя
Друзья, привет, по мотивам утреннего обсуждения в чате (смотрите скриншот) мы подготовили простой скрипт.
Скрипт при любом пользовательском изменении в таблице фиксирует это изменение, добавляет время, лист, диапазон и email редактора.
Есть максимум изменений, которые onEdit() может обработать в секунду, если вы за них выйдете (если пользователей, редактирующих Таблицу одновременно будет слишком много) – то некоторые изменения могут не записаться.
Тестируйте у себя Таблицах и приходите в комментарии с обратной связью, как скрипт будет работать у вас – нам очень интересно :)
PS Важное уточнение от нашего читателя Дамира: работает только при изменении одной ячейки, если внести данные протягиванием или удалить несколько ячеек – лог не пишется
05.09.2021 12:09
Изучите инструменты для принятия решений, научитесь применять их в бизнесе и личной жизни. Сможете увереннее действовать в стрессовых ситуациях. Получите пошаговый алгоритм действий в условиях неопределённости от преподавателя программы MBA «Лидеры изменений».
Регистрируйтесь на онлайн-интенсив: https://clc.to/u6-mGA
За 3 дня вы научитесь:
избегать ментальных ловушек;
мыслить критически;
находить источники мотивации;
формировать оптимальный план реализации проектов;
выстраивать алгоритм принятия решений;
сохранять спокойствие и работоспособность в условиях неопределённости.
Спикер интенсива — преподаватель Европейской ассоциации ICF, executive-коуч, Agile-коуч, консультант, бизнес-тренер — Марина Михайленко.
Все участники получат подборку статей о компаниях, которые успешно внедрили цифровые решения в бизнес. А всем, кто дойдёт до конца, мы подарим электронную книгу «Сила эмоционального интеллекта» Адель Линн, которая поможет лучше понимать себя и окружающих.
Ждём вас в прямом эфире 6–8 сентября в 19:00 по московскому времени.
#реклама
04.09.2021 10:09
P.P.S. Спасибо нашему читателю Сергею за важное уточнение: если в поле, по которому вы фильтруете, могут появиться новые значения, то лучше не убирать пустые в списке значений, а отфильтровать по условию (например, "Содержит данные"). См. картинку и отдельный лист с этим примером
04.09.2021 09:09
Сводная по “бесконечному” количеству строк
Задача: сделать сводную на основе всех строк на листе.
Открытый диапазон не работает: при попытке исправить источник на что-то вроде A2:F он тут же превратится в A2:F1000 (если на листе 1000 строк).
Но если заголовки данных в первой строке, как это нередко бывает — можно в качестве источника указать столбцы целиком: A:F.
Теперь все строки с листа будут учитываться в сводной. Но один минус — при наличии в диапазоне пустых строк и в сводной будут появляться пустые значения по тем полям, которые используются в группировке. Исправляется это просто — нужно добавить в фильтр любое поле и исключить в нем пустые значения (это должно быть то поле, которое точно заполняется для непустых строк — то есть отсутствие в нем значений точно гарантирует, что это полностью пустая строка).
Таблица с примером (создать копию)
P.S. В Excel для этой задачи можно отформатировать диапазон как “Таблицу” (Ctrl+T или Главная - Форматировать как Таблицу) и построить сводную на основе нее. Таблица расширяется при добавлении в нее новых строк, так что они попадут в сводную. И не придется строить сводную по столбцам (хотя это тоже будет работать, тогда аналогично придется фильтровать пустые значения, как описано выше для Google Таблиц)
02.09.2021 11:09
Хотите освоить визуализацию данных на реальных задачах — попробуйте бесплатный курс-симулятор от Нетологии и Yandex.Cloud.
Ваш персонаж — аналитик в крупной ритейл-компании, а каждое занятие — история с прописанным сюжетом. Вы построите интерактивную карту клиентов в DataLens, соберете отчёт-конструктор расходов в Excel, сравните показатели в Power BI, а в конце наведёте красоту в Tableau. Узнаете много нового и познакомитесь с основными инструментами визуализации данных.
А ещё ваш дашборд может выиграть приз, подробнее https://netolo.gy/gZW
#реклама
31.08.2021 09:08
Группировка и условия по времени в QUERY
Чтобы сгруппировать данные по часам, минутам или секундам в QUERY, используются функции hour, minute, second - в и GROUP BY. Все по аналогии с функциями year, month, day, quarter, dayofweek для группировки по дате.
А если нужно условие на столбец со временем, его нужно задавать в следующем формате:
WHERE A > timeofdayHH:MM:SS
Например, если нужны только строки со временем после 12:00:
WHERE A > timeofday12:00:00
Допустим, у нас есть данные по неким заявкам четырех разных типов. В столбце A - время заявки, в столбце B - тип.
Мы хотим получить количество заявок разных типов по часам - в период с 12 до 18.
Чтобы сгруппировать по часам в строках и посчитать количество заявок:
hour(A), count(A) group by hour(A)
Чтобы сгруппировать по типам заявок в столбцах, добавим PIVOT:
hour(A), count(A) group by hour(A) pivot B
Ну и добавим условие на период времени:
=QUERY(A1:B; "hour(A), count(A) where A>timeofday12:00:00 and Atimeofday12:00:00 and A
29.08.2021 14:08
Кубик-рубик в Таблицах
Когда меня в следующий раз будут спрашивать, что можно сделать скриптами Таблиц я буду просто отправлять этот пост.
Лев, активный участник нашего сообщества создал на GAS-скриптах кубик-рубик, показав все грани отдельно.
Как он сам рассказывает: "ехал в автобусе, подумал, смогу ли я сделать такое, кубика у меня не было, поэтому визуализировал перемещение граней в голове".
Что получилось и как перемещается можно посмотреть здесь: https://docs.google.com/spreadsheets/d/1k5UB5BIdKVDkjyqjUyjwFoDTj2LYsPEquHgg_SvupNs/edit?usp=sharing
PS и еще сапр и морской бой от того же автора t.me/google_sheets/462
Видели другие игры на Таблицах / GAS, есть идеи, что можно сделать? Пишите в комменты.