Небольшая статья про 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

ПЕРЕМЕЩАТОР — продолжаем показывать магию с Google Диском

Привет, недавно в наш чатик пришли с вопросом – есть список ссылок на файлы на Google Диске, как каждый из них скопировать в 10 папок?

Наш сегодняшний скрипт отвечает на этот вопрос, а еще он может не только копировать, но и перемещать файлы.

Инструкция, как пользоваться:
1) копируйте Таблицу со скриптом себе;
2) заполняйте URL файла и URL папки для перемещения (столбцы A и B);
3) выбирайте: копируем или перемещаем (столбец C);
4) запускайте скрипт из меню с 🔥 (при первом запуске скрипт попросит авторизацию – это нормально, этим вы разрешаете вашему скопированному скрипту обратиться к вашему диску – разрешаем);

Скрипт запустится и скопирует / переместит файлы исходя из настроек, попутно заполнит информацию в Таблице (название файла, название папки, результат и время), если вы запустите скрипт еще раз – скрипт обработает строки только без результата copy / move done.

Да и чтобы копировать файлы - у вас должен быть доступ и к файлу и к папке назначения 😉

Перемещатор (посмотреть на код в pastebin)

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

Developer MetaData

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

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

Есть отличное решение, про которое почти никто не знает, хоть оно и описано в документации – Developer MetaData.

С помощью DM вы можете соотнести (положить, приклеить) пару ключ и значение на Таблицу, на лист Таблицы, на строку или столбец. Далее вы сможете извлекать по заданному ключу как значение, так и саму "коробку" куда вы положили это значение, обращаться к ней и выводить еë текущие аргументы: номер строки / столбца для строки / столбца, название для листа / таблицы.

Примеры кода:
//создаём DM для листа "Лист с таким названием"
function createDM() {
SpreadsheetApp.getActive().getSheetByName('Лист с таким названием')
.addDeveloperMetadata("ключ для листа", "какое-то значение", SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT)
}

//обращаемся к DM по ключу и возвращаем актуальное название листа:
function getDM() {
Logger.log(SpreadsheetApp.getActive()
.createDeveloperMetadataFinder().withKey('ключ для листа').find()[0].getLocation().getSheet().getName());
}

Код в пастебин (пример с гифки тоже там)

PS Обратите внимание:
— DM не проверяет ключ на уникальность и вы можете создать несколько ключей с одинаковыми названиями (в коде же мы обращаемся к первому);
— Есть лимиты - это 30к символов на файл + 30к на каждый лист. Т.е. файл с 3 листами может суммарно сохранить 120к символов. В квоте считается каждый символ и ключа и значения;

Регулярные выражения — компактная памятка от @vitalich

В Таблицах можно использовать регулярные выражения
1) для проверки строки на соответствие выражению (функция REGEXMATCH)
2) для замены части строки на другой текст (REGEXREPLACE)
3) для извлечения строки, которая соответствует регулярному выражению (REGEXEXTRACT)

Для Таблиц в регулярках достаточно знать (или самые часто используемые части RE2):
Классы символов: . \d \D \s \S \w \W \b \B (для \w нужно понимать, что не во всех средах будут матчится не-латинские буквы)
Пробельные спецсимволы: \n \r \t
Границы ^ $
Выбор или-или |
Выбор из набора [ ] и выбор всего, кроме символов из набора [^ ]
Квантификаторы жадные ( * + {n} {n,m} {n,} ) и как управлять их "жадностью" (*? +? {n,m}? {n,}?)
Извлекаемые группы ( )
Неизвелекаемые группы (?: )

Для отладки табличных выражений на regex101 слева надо включать Golang

Таблица с большим количеством примеров

PS 🙋‍♂️ Делитесь в комментариях тем, что помогает вам.

#regexp