​​Выделение нескольких листов в 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.

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

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

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

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

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

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

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

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

скрипт, который логирует все изменения в Таблице, сохраняя 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 этого запроса

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

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

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

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

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

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

На скриншоте — производственный календарь за ноябрь, в месяце 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

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

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

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

Таблица

Ищем слово из списка в ячейке

Если нужно проверить, встречается ли в ячейке одно из нескольких слов (например, один из четырех брендов), можно поступить так:
1 Cлепить из этих слов одну текстовую строку с регулярным выражением, объединив их через вертикальную черту.
Сделать это можно с помощью функции JOIN
=JOIN("|" ; список слов для поиска)

На выходе получится (с названиями брендов из нашего примера) : Acer|Lenovo|Dell|HP (это простое регулярное выражение - одно из перечисленных значений)

2 И этот результат можно использовать в функции REGEXMATCH, чтобы проверить, встречается ли хотя бы одно из слов в ячейке:
=REGEXMATCH(ячейка ; JOIN("|" ; список слов для поиска) )

А если нужно вывести список , а не проверять каждую строку - эту формулу нужно будет использовать внутри FILTER:
=FILTER(диапазон ячеек ; REGEXMATCH(ячейка ; JOIN("|" ; список слов для поиска) ) )

P.S. Чтобы сделать условие нечувствительным к регистру, добавьте в начале выражения (?i):
"(?i)" & JOIN("|" ; список слов для поиска)

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

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

Регулярные выражения в диалоговом окне "Найти и заменить"

Таблица с базой примеров по регулярным выражениям

Простой пример использования fetchall

Коллеги, fetch в скриптах Таблиц - один запрос к веб-странице, fetchall - сразу пачка.

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

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

Пара нюансов ниже
1) когда формируем массив запросов – обязательно добавляем к каждому muteHttpExceptions: true, чтобы из-за недоступности одного у нас не упала пачка запросов целиком
function create_rqsts(urls) {
return urls.map(row => isURL(row[0]) ? { url: row[0], muteHttpExceptions: true } : '')
.filter(t => t)
}

2) собственно запрашиваем всё
function fetch_all_all_all(req) {
return response = UrlFetchApp.fetchAll(req);
}

3) каждый ответ обрабатываем регуляркой, достаём нужное
function match_all_all_all_all(array) {
return array.map(page => {
let m = page.toString().matchAll(d.regExp);
return 'https://adv-map.com' + [...m]?.[1]?.[1] || '-'
})
}

4) пересобираем исходный массив ссылок, на место валидных ссылок вставляем полученный из пункта 3 результат
function redesign(urls, data) {
return urls.map((h, i) => [isURL(h[0]) ? data[urls.slice(0, i + 1).filter(f => f[0]).length - 1] : '-']);
}

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

Документация fetch, fetchall

​​Проверка данных (Data validation) с формулами

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

Принцип здесь такой же, как и с использованием формул в условном форматировании: вы пишете формулу для первой ячейки диапазона, к которому применяете правила, и используете абсолютные и относительные ссылки ($). Представляйте, что вы вводите формулу прямо в первую ячейку диапазона проверки и потом будете ее "протягивать". Проверка данных будет запрещать ввод везде, где формула будет возвращать ЛОЖЬ / FALSE.

Примеры правил
Разрешить вводить значение только один раз:
=СЧЁТЕСЛИ($A$2:$A;A3)<=1
Проверка будет ругаться, если введенное значение уже встречалось в столбце A.

Разрешить вводить только рабочие дни:
=ДЕНЬНЕД(A2;2)<6
Проверка будет ругаться, если номер дня недели у введенной даты - 6 или 7, то есть это суббота или воскресенье. Напомним, что у ДЕНЬНЕД / WEEKDAY нужно указать второй аргумент = 2, именно такой тип нумерации соответствует привычному для нас (где понедельник = 1).

Разрешить вводить текст не длиннее 8 символов:
=ДЛСТР(A2)<=8
Здесь проверка будет ругаться, если вы ввели более 8 любых символов.

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

Уникализатор / пользовательская функция от нашего канала

Привет! Работает так: передаёте в функцию диапазон и указываете номер столбца с ключом, а функция:

1) возврат вам только по одной строке с каждым ключом;
2) эти строки будут "максимально обогащенными" - функция оставит в каждой ячейке строки последнее непустое значение по ключу строки (смотрите что получилось на скриншоте, ключи - Пушкин и Гумилёв);
3) на входе отфильтровываются все #N/A и #REF;

Функция текстом:
function unique_googlesheets(arr, key) {
key = key - 1;
let o = {};

arr.map(row => row.map(cell => ['#REF!', '#N/A'].includes(cell) ? '' : cell))
.forEach(y => {
if (!(y[key] in o)) {
o[y[key]] = y;
} else {
o[y[key]] = o[y[key]].map((h, i) => y[i] || h)
}
})
return Object.values(o);
}

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

★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat
★ Избранные посты нашего канала: t.me/google_sheets/418