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

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

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

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

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

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

​​Еще три книги про 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 (по работе с почтой есть интересные и не совсем очевидные лайфхаки).

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

Друзья, с помощью скриптов из одной Таблицы (назовём её Альфа) можно создать в другой Таблице (назовём её Бета) 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

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

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

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

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

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

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

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

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

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

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

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

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