​​Выделение нескольких листов в Google Таблицах

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

Выделить можно так (ровно как в Excel):
— С нажатым Ctrl щелкать на отдельные листы;
— Выделить первый лист, зажать Shift и щелкнуть на последний лист в группе.

Ярлыки выделенных листов станут белыми — после этого вызывайте правой кнопкой контекстное меню и действуйте! Можно удалить, скрыть (но эти два пункта, если выделены не все листы таблицы), скопировать, переместить, поменять цвет ярлыка.

Чтобы снять группировку — просто щелкните на любую ячейку или отдельный лист вне группировки.

Увы, вводить данные и форматировать ячейки одновременно на нескольких листах, как в Excel, нельзя (пока?)
Как и ссылаться на группу листов в формулах. В Excel, напомним, это выглядит так — первый и последний лист через двоеточие. Например, если нужно посчитать среднее ячеек C5 на листах от "листа1" до "листа5":
=СРЗНАЧ('лист1:лист5'!C5)
(апострофы не нужны, если названия листов в одно слово)

Ну что, друзья, пора бы нам и о Формах поговорить...
Сегодня делимся полезной статьей от Александра Макеева - про предзаполненную форму.

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

Благодарим Александра за отличный материал!

https://telegra.ph/Google-Form-Predzapolnenie-08-15-2

Апдейт СОБИРАТОРА, собираем только обновлённые Таблицы

Небольшое, но полезное изменение СОБИРАТОРА — мы добавили скрипт, который обходит файлы из списка и в столбец Q вставляет дату последнего изменения файла.

Как использовать — в столбец частота (P) напишите подобную формулу:
=if(Q3-O3>0; 0; 100000000)

В ней сравниваем дату последнего изменения файла и дату последней загрузки файла СОБИРАТОРОМ, если изменение было после – формула выведет 0 в столбец частота и это обновит этот файл при следующем запуске скрипта по триггеру.

Скрипт обновления дат запускается из меню, но также можете положить его на триггер, функция называется getLastUpdateDates.

Код скрипта отдельно: https://pastebin.com/ecBAd8Rv

PS если будете вставлять просто код – не забудьте активировать drive api в скриптах в сервисах

Кубик-рубик в Таблицах

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

Лев, активный участник нашего сообщества создал на GAS-скриптах кубик-рубик, показав все грани отдельно.

Как он сам рассказывает: "ехал в автобусе, подумал, смогу ли я сделать такое, кубика у меня не было, поэтому визуализировал перемещение граней в голове".

Что получилось и как перемещается можно посмотреть здесь: https://docs.google.com/spreadsheets/d/1k5UB5BIdKVDkjyqjUyjwFoDTj2LYsPEquHgg_SvupNs/edit?usp=sharing

PS и еще сапëр и морской бой от того же автора t.me/google_sheets/462

Видели другие игры на Таблицах / GAS, есть идеи, что можно сделать? Пишите в комменты.

Группировка и условия по времени в QUERY

Чтобы сгруппировать данные по часам, минутам или секундам в QUERY, используются функции hour, minute, second — в SELECT и GROUP BY. Все по аналогии с функциями year, month, day, quarter, dayofweek для группировки по дате.

А если нужно условие на столбец со временем, его нужно задавать в следующем формате:
WHERE A > timeofday'HH:MM:SS'
Например, если нужны только строки со временем после 12:00:
WHERE A > timeofday'12:00:00'

Допустим, у нас есть данные по неким заявкам четырех разных типов. В столбце A - время заявки, в столбце B - тип.
Мы хотим получить количество заявок разных типов по часам - в период с 12 до 18.

Чтобы сгруппировать по часам в строках и посчитать количество заявок:
Select hour(A), count(A) group by hour(A)

Чтобы сгруппировать по типам заявок в столбцах, добавим PIVOT:
Select hour(A), count(A) group by hour(A) pivot B

Ну и добавим условие на период времени:

=QUERY(A1:B; "Select hour(A), count(A) where A>timeofday'12:00:00' and A<timeofday'18:00:00' group by hour(A) pivot B" )

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

Итого:
=QUERY(A1:B;
"Select hour(A), count(A)
where A>timeofday'12:00:00' and A<timeofday'18:00:00'
group by hour(A)
pivot B
label hour(A) 'Часы'" )

Именно эту несложную задачу можно решить и с помощью сводной (там еще и итоги можно будет добавить). Сводная в таблице с примером тоже есть. Но не во всех случаях подойдет сводная. Например, кверить можно массив с несколькими внешними таблицами сразу (которые будут загружаться функциями IMPORTRANGE, объединенными в массиве {... ; ... ; ...} ). Или у вас будут сложные фильтры на другие столбцы исходных данных, например, текстовые - и вам понадобится условие, заданное с помощью регулярных выражений. Тогда пригодится условие MATCHES в кляузе WHERE в функции QUERY.

Файл с примером (Создать копию)

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

Ваш персонаж — аналитик в крупной ритейл-компании, а каждое занятие — история с прописанным сюжетом. Вы построите интерактивную карту клиентов в DataLens, соберете отчёт-конструктор расходов в Excel, сравните показатели в Power BI, а в конце наведёте красоту в Tableau. Узнаете много нового и познакомитесь с основными инструментами визуализации данных.

А ещё ваш дашборд может выиграть приз, подробнее → https://netolo.gy/gZW

#реклама

​​Сводная по “бесконечному” количеству строк

Задача: сделать сводную на основе всех строк на листе.
Открытый диапазон не работает: при попытке исправить источник на что-то вроде A2:F он тут же превратится в A2:F1000 (если на листе 1000 строк).

Но если заголовки данных в первой строке, как это нередко бывает — можно в качестве источника указать столбцы целиком: A:F.

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

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

P.S. В Excel для этой задачи можно отформатировать диапазон как “Таблицу” (Ctrl+T или Главная - Форматировать как Таблицу) и построить сводную на основе нее. Таблица расширяется при добавлении в нее новых строк, так что они попадут в сводную. И не придется строить сводную по столбцам (хотя это тоже будет работать, тогда аналогично придется фильтровать пустые значения, как описано выше для Google Таблиц)

P.P.S. Спасибо нашему читателю Сергею за важное уточнение: если в поле, по которому вы фильтруете, могут появиться новые значения, то лучше не убирать пустые в списке значений, а отфильтровать по условию (например, "Содержит данные"). См. картинку и отдельный лист с этим примером

​​Изучите инструменты для принятия решений, научитесь применять их в бизнесе и личной жизни. Сможете увереннее действовать в стрессовых ситуациях. Получите пошаговый алгоритм действий в условиях неопределённости от преподавателя программы MBA «Лидеры изменений».

Регистрируйтесь на онлайн-интенсив: 👉 https://clc.to/u6-mGA

За 3 дня вы научитесь:
✔️ избегать ментальных ловушек;
✔️ мыслить критически;
✔️ находить источники мотивации;
✔️ формировать оптимальный план реализации проектов;
✔️ выстраивать алгоритм принятия решений;
✔️ сохранять спокойствие и работоспособность в условиях неопределённости.

📣 Спикер интенсива — преподаватель Европейской ассоциации ICF, executive-коуч, Agile-коуч, консультант, бизнес-тренер — Марина Михайленко.

🎁 Все участники получат подборку статей о компаниях, которые успешно внедрили цифровые решения в бизнес. А всем, кто дойдёт до конца, мы подарим электронную книгу «Сила эмоционального интеллекта» Адель Линн, которая поможет лучше понимать себя и окружающих.

Ждём вас в прямом эфире 6–8 сентября в 19:00 по московскому времени.

#реклама

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

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

Скрипт при любом пользовательском изменении в таблице фиксирует это изменение, добавляет время, лист, диапазон и email редактора.

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

Тестируйте у себя Таблицах и приходите в комментарии с обратной связью, как скрипт будет работать у вас – нам очень интересно :)

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

Логируем любые изменения в Таблице:

function onEdit(e) {
 if (e.value != e.oldValue) {
   let ss = e.source;
   let sh = ss.getSheetByName('лог') || ss.insertSheet('лог');
   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

И сразу вторая "усиленная" :) версия скрипта – логируем изменения только в листах / диапазонах, которые перечислены в 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.insertSheet('лог');
   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

Небольшая статья про api, cookie, авторизацию на минималках

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

В материале от Романа Игнатова простой пример того, как на сайте без API через GAS-скрипты можно получить cookie и дальше использовать их открывая страницы, которые доступны только после авторизации пользователям.

Кажется, что это первый материал на эту тему на русском 🔥
ignatov-script.blogspot.com/2021/09/api.html

P. S. "Авторизовываться", с помощью логина и пароля в этом примере не требуется, но если для вашего сайта это необходимо – формируйте post-запрос на страницу авторизации, в payload передавайте логин и пароль и забирайте cookie уже из headers этого запроса

Хотите улучшить свое портфолио и получить продвижение по работе всего за 4 месяца? Тогда курс «Excel + Google-таблицы с нуля до PRO» как раз для вас. Спешите занять место со скидкой!
Курс подойдет тем, кому нужно быстро делать расчеты, планировать продажи, разрабатывать маркетинговый план и вычислять стоимость инвестиционных объектов.

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

👉 Подробнее: https://clc.to/7tNcOQ

#реклама

Эмулируем действия браузера: проходим авторизацию скриптами, загружаем отчет в csv, а потом парсим и вставляем в Таблицу

Еще один живой пример  — отправляем на страницу авторизации сайта post-запрос, с username / password и получаем cookies.

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

Код целиком:
- получаем cookie,
- запрашиваем свой отчёт
- парсим CSV
- вставляем в Таблицу
pastebin.com/WL06GdM8

Пример про сайт doggylogs.com, но по аналогии вы можете переписать код под получение данных из нужного вам ресурса. Главный инструмент и помощник вам в этом – консоль в браузере, проходите авторизацию, запрашивайте данные и смотрите, на какие url и в каком формате браузер отправляет данные. Изменяйте код из примера и сможете автоматически загружать данных из сайтов без API. Инджой :)

​​Клиенты уходят, а продажи компании падают?
Классическая ситуация, если неправильно выстроен процесс работы с заказчиком. Пора это исправить!
Битрикс24.CRM — это бесплатный и надежный помощник для отдела продаж, который:
— Поможет упорядочить поток клиентов. Вам больше не придется переходить из аккаунта в аккаунт в соцсетях и собирать заявки вручную. В CRM автоматически падают все заказы из Телеграма, Инстаграма, Вотсапа, Вконтакте, телефона. Это позволит легко общаться с клиентами прямо из CRM.

— В реальном времени показывает, как обстоят дела в продажах: что в работе, на какой стадии клиент, кто им занимается и на что обратить внимание. CRM напоминает, кому написать письмо, выставить счёт, а кому допродать.

— Будет принимать оплату там, где удобно вашим клиентам: прямо из CRM вы сможете сформировать заказ и отправить ссылку на оплату в мессенджеры, соцсети, по SMS и даже в директ инстаграма. Клиент выберет удобный способ, а вся информация по сделке сохранится в CRM.
Переходите по ссылке, и подключайтесь к телеграм-ассистенту от Битрикс24. Следуйте подробным инструкциям по работе в CRM и стройте свой бизнес по-новому!

#реклама

Сколько рабочих дней в ноябре с учётом праздников??
— Достаём производственный календарь формулой!

На скриншоте — производственный календарь за ноябрь, в месяце 30 дней, из них 20 рабочих и 10 выходных.

Достаём дни формулой из Таблицы

Есть ряд ресурсов из которых мы можем выгружать данные по 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

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

Дашборды, сквозная аналитика, формулы DAX и многое другое станет доступно после прохождения практического онлайн-курса по Power BI PRO.
Основатель Quod Solutions Finance Аркадий Ойслендер проведет вас от азов до профессионального владения этим инструментом, поделится опытом и рабочими лайфхаками, а личный наставник поможет отточить знания на практике.

Курс вы будете проходить в своём темпе, дедлайнов и расписания в Skillbox нет. После курса вы подготовите и защитите дипломный проект — дашборд для сквозной аналитики с данными из amoCRM, Google Analytics и Яндекс.Директ.

Сейчас на курс действует скидка. Используйте время с умом! Переходите по ссылке и регистрируйтесь: https://clc.to/VU9_2A

#реклама

Скрипт от Дамира: создаём типовые Google Документы из Таблицы

слово автору:

Была у меня задачка: печатать типовые документы из данных по таблице. Я состряпал скрипт 1й версии, в нём было много минусов (не удобно эксплуатировать при изменении тегов или добавлении новых).
Пофиксил, поменял работу скрипта. Как работает:
Шапка таблицы состоит из тегов заключенных в {} - Эти теги мы проставляем в шаблоне уведомления (который в формате гугл докс, аналог ворда). Скрипт создает копию шаблона по каждой строке и меняет теги на данные из таблицы.
Данные без тегов в шапке не обрабатываются.
Скрипт инициализируется из доп. кнопки на панели инструментов "Пакетная печать"
Теги можно добавлять и менять в самой таблице (не забывая конечно добавить их потом в шаблон).
Печать актов лучше дробить (более 200 строк перевариваются плохо)

Таблица