Google Таблицы

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Группировка и условия по времени в 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.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Еще несколько советов по работе с приложениями Google Диска

Нарисовать специальный символ в Документах. Для вставки символов нужно зайти в меню "Вставка" — "Специальные символы". А в появившемся диалоговом окне можно не только вводить ключевые слова или искать по категориям (эмодзи, письменность, иероглифы и так далее), но и просто нарисовать тот символ, который вы ищете.

Короткие встречи в Календаре. В настройках календаря в разделе "Мероприятия" можно задать продолжительность мероприятия по умолчанию. А еще здесь есть волшебный (уж по обещаемому эффекту — точно) флажок "Быстрые встречи". Поможет ли он меньше времени проводить на совещаниях? Не уверен. Но его активация сделает короткие 30-минутные встречи 25-минутными по умолчанию, а часовые — 50-минутными. Увы, опыт показывает, что даже 50-минутные встречи воспринимаются участниками как часовые, так что нужна серьезная дисциплина, чтобы заканчивать время и был реальный перерыв в 10 минут. Но вдруг у вас получится?

Рабочие часы. В настройках календаря есть раздел “Рабочее время” — можно указать свое рабочее расписание (дни и часы). Гарантирует ли это, что вам не поставят встречу на это время? Кхе-кхе. Но во всяком случае коллега получит предупреждение о том, что вы не работаете (хотите не работать) в заданное время.

Увеличить время отмены отправки письма в Gmail. По умолчанию у вас есть всего 5 секунд на отмену отправки письма. Совсем мало! Но это время можно увеличить до 30 секунд. Заходите в настройки — все настройки — общие — отмена отправки и выбирайте один из вариантов (5, 10, 20 или 30 секунд).

Пометить письма, отправленные только вам. Чтобы пометить письма, которые отправлены только вам персонально, а не нескольким адресатам, включите соответствующую опцию в "Общих" настройках Gmail (называется "Значки персональных писем"). У писем, отправленных только вам, будет две стрелки ».

Добавили эти лайфхаки в сборник советов по работе с Google Диском — там все со скриншотами:
https://teletype.in/@renat_shagabutdinov/J0TKzjV7M

Скрипт: массово удаляем временные фильтры

Вам знакома ситуация, когда коллеги пользуются Таблицей и чтобы никому не мешать создают временные фильтры и НЕ УДАЛЯЮТ ИХ?

Показываем, как с ними разобраться, очередной прекрасный материал от Михаила Смирнова: https://telegra.ph/Massovo-udalyaem-vremennye-filtry-cherez-Google-Sheets-API-06-06-2

P.S. А здесь мы писали про режимы фильтрации вообще: t.me/google_sheets/341

Скрипт: создаём в Таблице триггер и скрипт, который не увидят редакторы и владелец

Друзья, с помощью скриптов из одной Таблицы (назовём её Альфа) можно создать в другой Таблице (назовём её Бета) installable триггер (onEdit, onChange, onOpen, onFormSubmit) и при этом в Таблице Бета никто не увидит ни скрипта, ни триггера.

Как это работает – вы редактор Таблицы Бета, но не владелец, вы создаёте (запуская код ниже) из своей Таблицы Альфа триггер в Таблице Бета. Владелец Таблицы Бета не видит ни триггера ни даже скрипта, на который он создан, но скрипт отлично работает в Таблице Бета покуда у вас есть к ней доступ, работает от вашего имени.

function createTrigger() {
ScriptApp
.newTrigger('название скрипта')
.forSpreadsheet('ID Таблицы') //или forForm, forCalendar, forDocument
.onEdit() //или onChange(), onOpen(), onFormSubmit()
.create()
}

function script(e) {
Browser.msgBox(JSON.stringify(e))
}

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

Пишите свои варианты использования в комментарии 🙂

Installable triggers
Class ScriptApp

​​Еще три книги про Excel и G Suite

Пополняем наш обзор околотабличных книг! Они уже добавлены в общий обзор, который вы можете найти по ссылке. Там подробнее и с ссылками на издательства/магазины.

Эффективная работа в Microsoft Excel | Алан Мюррей

Книга небольшая, но это прямо-таки концентрат пользы, но не для новичков. Если вы уверенно чувствуете себя в базовых темах Excel — функциях, сводных, форматировании и диаграммах — и хотите научиться продвинутым приемам работы, нетривиальным трюкам с диаграммами, новым функциям (которые появились пока только в Office 365 — ПРОСМОТРX, УНИК, СОРТ, СОРТПО, ФИЛЬТР), вам сюда.

Изучаем Power Query. Наглядный подход к подключению и преобразованию данных из множества источников | Линда Фоукс, Уоррен Спарроу

Книга, которая подойдет и начинающим, и продолжающим: обзор надстроек Power Pivot и Power Query, основы, интерфейс, подключение к разным источникам данных, преобразование данных, язык M (использующийся в Power Query) — раскрыты все ключевые темы. И введение в язык DAX (язык функций в Power Pivot).

Много дополнительных материалов: PDF с цветными изображениями из книги (хотя в самой бумажной книге они тоже цветные, но не всегда в высоком качестве), видео, файлы с примерами.

G Suite For Dummies | Paul McFedries

Книга про все составляющие G Suite для новичков. Максимально подробно и по шагам, как полагается в серии "для чайников". Если говорить про Таблицы, то тут совсем базовая информация: создание и форматирование файлов (без тонкостей вроде пользовательских форматов), основы работы с формулами. Но зато по документам и презентациям вполне себе полноценное руководство, там как раз закрыта большая часть тем. А также по календарю, формам, Meet, настройкам доступов в целом, Gmail (по работе с почтой есть интересные и не совсем очевидные лайфхаки).

Программно даём доступ для IMPORTRANGE к другой таблице

Новая статья от Михаила Смирнова, которому мы (верим, что и вы тоже) отправляем много-много спасиб в карму!

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

Чтобы использовать IMPORTRANGE() необходимо предоставить доступ к таблице, откуда будет осуществлён импорт.

Варианта три:
- дать доступ на чтение (как минимум) для всех (Anyone) по ссылке
- вручную нажать синюю кнопку предоставления доступа
- дёрнуть специальную ссылку скриптом

Третью - незадокументированную - возможность и будем сегодня рассматривать.

Что для нас это значит практически?

Внутри IF мы можем проверять, что редактируется сейчас и, например, если редактируется первый столбец, Лист100, и если значение вставлено и существует (а не удалено) – вставлять в эту строку дату, время и какой-то текст:

function onEdit(e) {
const sh = e.source.getActiveSheet();
const range = e.range;

if (sh.getSheetName() == 'Лист100'
&& range.getColumn() == 1
&& e.value) {

sh.getRange(range.getRow(), 2, 1, 2)
.setValues([[new Date(), 'https://t.me/google_sheets']])
}
}

==
Другие примеры скриптов onEdit(), благо теперь вам понятнее, как эти скрипты работают:

три примера: t.me/google_sheets/432
перенос строки: t.me/google_sheets/533
простой скрипт вставки даты: t.me/google_sheets/171

Самый простой образовательный onEdit() скрипт, который можно написать

onEdit() – "простой триггер", скрипт, названный так будет запускаться при редактировании любой ячейки Таблицы, в которой он сохранён.

Чтобы разобраться в механике напишем скрипт:

function onEdit(e) {
Browser.msgBox(JSON.stringify(e));
}

На ГИФ видно, что происходит – при редактировании любой ячейки Таблицы скрипт (без запуска) выводит окно Browser.msgBox(...) c объектом редактирования, который превращен в строку JSON.stringify(e).

Через объект (событие) редактирования вы можете обратиться к объектам
range https://developers.google.com/apps-script/reference/spreadsheet/range,
spreadsheet https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet,
user https://developers.google.com/apps-script/reference/base/user,

а также сразу из объекта можно получить координаты диапазона редактирования
e.range.columnEnd (columnStart, rowEnd, rowStart)

или введённое в ячейку значение e.value или предыдущее значение e.oldValue

Импорт данных из Google Таблицы в Excel с обновлением через Power Query

Дано: хотим, чтобы данные с листа Google Таблицы попадали в Excel почти что "в режиме реального времени" — чтобы была возможность обновлять данные в Excel, ничего не скачивая и не вставляя руками.

У Таблицы должен быть открыт доступ по ссылке. К этой самой ссылке добавляем справа /export и идем в Excel в Power Query.
Эта надстройка может быть установлена бесплатно в Excel 2010-2013 (нужно скачивать с сайта Микрософт) и является частью Excel 2016 и 2019. В последних версиях ее можно найти на ленте инструментов (Данные → Получить и преобразовать данные / Скачать & преобразовать).

меняем код в опубликованном приложении без публикации

Привет, друзья!

Представьте – у вас есть телеграм бот написанный на gas и каждое изменение кода вам приходится публиковать под новой версией, знакомая ситуация?

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

1) Создаём отдельный (standalone) скрипт script.google.com, внутри будут функции для нашего бота. Например, main(e), функция будет обрабатывать полученное на адрес веб-приложения сообщение из телеграма.

2) Публикуем скрипт как библиотеку (начать развертывание – новое развертывание – библиотека)

3) Идём в Таблицу нашего бота, подключаем библиотеку в режиме разработчика, далее пишем функцию:

function doPost(e){
lib.main(e)
}

4) Стандартно публикуем как веб-приложение (для бота) и на полученный адрес создаём вебхук с токеном нашего бота.

5) В итоге: телеграм отправляет сообщения на адрес развернутого приложения, приложение переадресовывает их в функцию main(e), весь код main(e) - в библиотеке, так как библиотека подключена в режиме разработчика, то используется последняя актуальная версия.

=
Спасибо Льву из нашего чата про Таблицы за отличную идею, кстати, мы сделали в чате капчу - зайдите и посмотрите на нёе.

P.S. 🔥🤓 наши боты с инструкциями и гифками по установке:
– получаем / отправляем сообщения из таблицы: t.me/google_sheets/556
– забираем данные из таблицы по запросу ботом: t.me/google_sheets/727