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

Выдёргиваем аргументы из функций QUERY, IMPORTRANGE, HYPERLINK

Функция FORMULATEXT - извлекаем текстовую строку с формулой

Эта функция выдает вам формулу из ячейки в виде текста. Единственный аргумент – ссылка на ячейку с формулой.

Может пригодиться, чтобы вытащить что-нибудь из формулы. Когда у нас есть текст и нам нужно получить из него фрагмент (например, ссылку из функции ГИПЕРССЫЛКА / HYPERLINK или запрос из QUERY), можно дальше использовать и обычные текстовые функции типа ПСТР / MID, ПОИСК / SEARCH и ДЛСТР / LEN, чтобы с теми или иными костылями извлечь нужное, и регулярные выражения в функции REGEXEXTRACT.

Например, если нам нужно вытащить ссылку из ГИПЕРССЫЛКИ:
=REGEXEXTRACT (FORMULATEXT(ссылка на формулу) ; """(.+?)""" )

FORMULATEXT выдаст текст с формулой из ячейки, а REGEXEXTRACT - извлечет текст из первых кавычек.

А если нужно вытащить из IMPORTRANGE ссылку на источник и превратить ее в кликабельную ссылку в отдельной ячейке:
=HYPERLINK(REGEXEXTRACT(FORMULATEXT(ссылка на формулу) ; """(.+?)""") ; "Ссылка")

Здесь достаем текст формулы из ячейки с IMPORTRANGE (это делает FORMULATEXT), затем из этой формулы достаем ссылку на исходную таблицу (это делает REGEXEXTRACT) и затем превращаем ее в кликабельную (HYPERLINK) и не показываем в ячейке саму ссылку, а только слово "Ссылка" (последний аргумент HYPERLINK).

Какие у вас идеи / примеры применения FORMULATEXT? Что бывает необходимо доставать из формул вам?

Ссылка на примеры
Сделать копию

P.S. В Excel эта функция называется Ф.ТЕКСТ на русском и FORMULATEXT на английском

Пользовательские списки в Excel

Сегодня — небольшой совет для пользователей Excel.

Скорее всего, вы знаете, что можно ввести, например, “Январь” в Excel, потянуть мышкой за маркер в правом нижнем углу ячейки — и список месяцев продолжится. Excel знает порядок месяцев на русском только потому, что есть такой встроенный список — но мы можем добавлять и другие, свои списки. Допустим, у вас в компании есть список из городов/филиалов/фамилий — да чего угодно, что нужно вводить списком и в определенном (не обязательно алфавитном) порядке:

Москва
Санкт-Петербург
Казань
Саратов
Екатеринбург

Следуем по адресу:
Файл - Параметры - Дополнительно - Изменить списки
File - Options - Advanced - Edit Custom Lists

Вводим свой список и нажимаем Добавить / Add.
Все: можно ввести в ячейку первое значение списка и потянуть мышкой.

Таких полезных мелочей в Excel очень и очень много. О десятке (а скорее всего и больше) поговорим уже 5 октября во вторник на открытом уроке в МИФе. Но об этом уже в следующем посте :)

Решим формулами задачу нашего подписчика Андрея

Дано:
На склад завезли 1000 шт. велосипедов. Стоимость хранения 1 шт - 3 рубля / день. Каждый день продается по 10 штук товара. Как посчитать итоговую стоимость хранения товара, к моменту, когда он кончится?

Решение 1
Если товар продаётся по 10 штук в день, то мы его продадим за 100 дней (1000 / 10), сформируем уменьшающийся склад (1000, 990, 980) за каждый день с помощью функции SEQUENCE:
SEQUENCE( 100 [количество строк] ; 1 [количество столбцов] ; 1000 [первое значение] ; -10 [шаг] )

Далее суммируем все строки и умножим на стоимость хранения товара — 3 рубля / шт / день. Итоговая формула:
=СУММ( SEQUENCE(1000/10;1;1000;-10) ) * 3

(на скриншоте a6:a и b6)

Решение 2
Вариант от Михаила Смирнова, без создания массива со складом за каждый день. Сумма арифметической прогрессии и остаток:
=3 * ((10 + MOD(1000; 10) + 1000) * INT(1000 / 10) / 2 + MOD(1000; 10))

(на скриншоте d5:d7)

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

Наш чат, не заходите: @google_spreadsheets_chat

Письмошная — выгрузка файлов и писем по фильтрам

Друзья, сегодняшний пост – коллаборация нашего канала и Дамира Х., передаём слово автору:

Иногда есть необходимость сохранить файлы, которые пришли во вложении на вашу почту. Для ускорения процесса, скрипт с интерфейсом в виде таблицы:

На листе настройки – прописываем свои фильтры в зелёных ячейках.

На листе "Отчет по выгрузке" будут сохраняться письма, ссылки на скачанные файлы и на папки к ним.

Можно скачать файлы по фильтрам "тема письма" и "от кого" за сегодня и за указанный в настройках диапазон дат.

При необходимости скрипт getMailsToday() - можно поставить на ежедневный триггер для автоматизации загрузки.

Таблица (делайте копию)

Скрипт отдельно в pastebin

==
★ Канал про Таблицы: @google_sheets
★ Оглавление канала: goo.gl/HdS2qn
★ Чат: @google_spreadsheets_chat

Загружаем из своей почты 20 xlsx-файлов с помощью письмошной →

Итак, пример использования утреннего скрипта: в моей почте 20 писем с темой HP REPORT, в каждом письме – xlsx-файл.

Чтобы выгрузить текст писем в Таблицу, а все файлы на Google Диск —

1) делаем копию нашей Таблицы (если не скопировали еще);
2) заполняем только тему, все остальные фильтры не заполняем;
3) запускаем скрипт!

😮😎

★ Наш чат: @google_spreadsheets_chat