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

Представьте – вам нужно зайти на 200 однотипных веб-страниц и достать из них адрес объекта или как в нашем примере – ссылку на изображение.

Так себе перспектива, да?

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

Как всё работает:
0) копируйте Таблицу
1) заполните столбец A: ссылки, которые надо обработать
2) заполните столбец B: регулярное выражение, которое извлечет из кода веб-страницы нужное
3) запускайте!

Скрипт проходит строки по одной, вставляя данные и включая чекбокс, обрабатываются только строки с выключенным чекбоксом.

Про регулярку:
Ссылка, которую достаём, в коде страницы выглядит так:
data-large="/upload/d5/b4/19/37/d.jpeg" class="my-foto">

Нам нужно всё, что после data-large=" и до "

Регулярка будет такой:
data-large="(.+?)"


regex101.com/ – проверить выражение
rexegg.com/regex-tools.html – научиться

💊💡(скрипты, боты и отчёты на заказ)

ПОЛЬЗОВАТЕЛЬСКИЕ ФУНКЦИИ
Выводим все листы Таблицы и диапазоны с данными

/**
* Выводим листы и диапазоны
* @customfunction
*/
function листы_диапазоны() {

return
[['Лист', 'Диапазон с данными', 'Границы листа']].concat(SpreadsheetApp.getActive().getSheets()
.map(g => [g.getName(), g.getDataRange().getA1Notation(), g.getRange(1, 1, g.getMaxRows(), g.getMaxColumns()).getA1Notation()]))
}

Как работает? Положите код функции в редактор скриптов Таблицы, после введите в ячейку название функции листы_диапазоны.

Для принудительного обновления – оберните название функции в ЕСЛИ (IF) и добавьте ссылку на чекбокс. Активируете чекбокс – функция пересчитается.
=IF(A1; листы_диапазоны())

🔥Другие функции из гифки:
– выводим имя книги
– текущий лист и ссылка на него
– все листы книги и ссылки на них
https://pastebin.com/8Rr3fgZc

===
📕📗📘 Оглавление канала

УДАЛЯТОР / ДОБАВЛЯТОР
Следим за количеством пустых строк в выбранных Таблицах

Тормозит Таблица, а вы боитесь лезть в формулы и что-то оптимизировать?

Есть способ проще - физически удаляем пустые ячейки. Это поможет, ведь даже пустые ячейки имеют вес и при пересчёте формул на них требуется время.

Мы придумали для вас решение (таблица со скриптом), с помощью него вы сможете поддерживать нужное количество строк и столбцов:

Работает так:
1. заносите ссылки на Таблицы
2. по каждой Таблице указываете, сколько пустых строк / столбцов должно остаться
3. запускаете скрипт, скрипт открывает каждый лист каждой таблицы
4. удаляет столбцы и строки, учитывая запас, который вы задали
5. если потребуется – то добавляет строки / столбцы до запаса

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

===
📕📗📘 Оглавление канала

Пять мини-лайфхаков в Google Таблицах

Если вам нужно вернуть только первое значение из массива, возвращаемого FILTER, поставьте перед функцией +
=FILTER(A:C; C:C < 2000) — все значения меньше 2000
=+FILTER(A:C; C:C < 2000) — первое значение меньше 2000

Клавиша F4 — ей мы бы дали звание самой недооцененной. Это повтор последнего действия. Работает не для всего, но вставка строк/столбцов, форматирование и многое другое — очень ускоряет работу. Кстати, работает и в Excel. На Mac: ⌘ + Y или ⌘ + Shift + Z или Fn + F4.

Ctrl+Shift+V — вставка только значений. Еще один наш фаворит среди горячих клавиш. Как же это ускоряет замену формул на значения. Вот бы такое в Excel (да, окно "Специальная вставка" там можно вызвать Ctrl+Alt+V, но потом нужно еще выбрать "Значения" в нем). Кстати, сочетание работает во всех приложениях Google Диска — Презентациях, Документах. На Mac: ⌘ + Shift + V.

/copy — вставьте слово "copy" после ссылки на Таблицу (или другой док Google Диска) и отправляйте ссылку тем, кому нужно создавать копию, а не пользоваться исходной таблицей — сразу будет открываться страница с кнопкой "Создать копию".

Хотите наглядно оформить дэшборд с отклонениями "план-факт" — используйте диаграмму "Сводка" (хотя это не совсем диаграмма) — в качестве диапазона данных используйте два значения, факт и целевое/прошлый период/другой базис для сравнения.
Можно настроить отклонения в % или абсолюте (см скриншот).

===
📕📗📘 Оглавление канала

КАК МЫ ПРОВЕЛИ ЛЕТО

Друзья, ниже - подборка наших летних скриптов. Полезные, бесплатные и с говорящими названиями.

Налетайте, не благодарите и делитесь с друзьями:

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах t.me/google_sheets/563

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней t.me/google_sheets/564

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске t.me/google_sheets/565

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

TELEGRAM BOT (+ полная инструкция): t.me/google_sheets/556

СОБИРАТОР (копируем и вставляем много данных через sheets api) t.me/google_sheets/536

===

📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Массивный ВПР

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

Задача: одной формулой получить стоимость всего проекта.

Решение: будем перемножать весь диапазон с количеством часов специалистов на функцию ВПР, которая вернет массив с их ставками:

=СУММПРОИЗВ(B2:F2;ВПР($B$1:$F$1;'Специалисты и ставки'!$A:$B;2;0))

СУММПРОИЗВ / SUMPRODUCT перемножает элементы массивов и возвращает сумму этих произведений.
Первый аргумент - это часы специалистов, а второй - функция ВПР, которая по заголовкам столбцов подтянет ставки из другого листа.

Обратите внимание, что порядок специалистов на листах отличается. С функцией ВПР меньше вероятность ошибки, чем если бы мы просто перемножали два массива (такая схема возможна только если порядок и количество специалистов на 2 листах строго совпадают).

В случае с ВПР на листе со ставками может быть любое количество специалистов в любом порядке, а подтягивать их можно на любое количество листов с работами, где будет только часть специалистов — опять же, в любой последовательности.
Таблица с примером

===
Еще наше про ВПР:
– ​​ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
– ВПР (VLOOKUP) по нескольким условиям
– ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
– ВПР по нескольким диапазонам
– ВПР с интервальным просмотром = 1
– Перекрестный ВПР (ищем по строке и заголовку)
– Видео про функцию ВПР в Google Таблицах

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

ДОПУСКАТОР 2

Убираем из файлов всех пользователей кроме себя и закрываем доступ по ссылке.

Недавно к нам пришёл Андрей и объяснил, что ни с кем не хочет делиться своими файлами и попросил добавить в допускатор то, что в первом предложении.

Мы добавили, а теперь делимся с вами. Таблица со скриптом.

Как всегда всё просто:
1. делаете копию Таблицы
2. переходите на лист "убираем всех"
3. вставляете ссылки на файлы в A:A
4. выключаете чекбоксы в B:B
5. выбираете, что нужно сделать по каждой ссылке: убрать пользователей кроме себя И / ИЛИ закрыть доступ по ссылке
6. запускаете скрипт и происходит магия

Про код – мы получаем все ячейки с данными рабочего листа, дальше проходим по каждой строке с ссылкой и выключенным чекбоксом. Если выбрано "удалять всех" – получаем массив всех редакторов / читателей файла и каждого удаляем. Если выбрано "закрыть доступ" – меняем форму доступа на DriveApp.Access.PRIVATE, DriveApp.Permission.NONE и файл становится недоступен по ссылке для всех.

Код отдельно: https://pastebin.com/JzardKXe

Документация:
– про работу с листом
– про работу с файлами
– про доступ по ссылке
– про цикл forEach (но конечно можно использовать любой доступный вам)

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Находим и выводим

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

Используем QUERY
В запросе QUERY нельзя просто взять и отфильтровать по яблокам в первом столбце – в этом случае выведется только одна строка. Но, в QUERY есть магические кляузы OFFSET и LIMIT, они определяют, сколько строк отступить сверху при выводе массива и сколько строк вывести всего.

Поэтому:
1. с помощью MATCH / ПОИСКПОЗ находим позицию Яблок в столбце A
=MATCH("яблоки" ; A:A ; 0) //5

2. создаём текстовую строку запроса с результатом этой функции
="limit 3 offset " & MATCH("яблоки" ; A:A ; 0) //limit 3 offset 5

3. итоговая формула, объединяем запрос и QUERY
=QUERY(A1:C11; "limit 3 offset " & MATCH("яблоки" ; A:A ; 0) - 1 ; 0) // -1 чтобы строка с яблоками также попала в выборку

4. заголовков в наших данных нет, поэтому последний аргумент = 0

P.S. Если вам нужны не все столбцы, а только некоторые — вместо * укажите их номера. Например, SELECT A, C для вывода первого и третьего столбцов.

API OZON ИЗ GOOGLE ТАБЛИЦЫ

Друзья, недавно у нашего читателя Дмитрия была задача научиться менять цены на свои товары используя API OZON для продавцов, обращаясь к нему из Google Таблицы.

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

Читайте про API и приходите в наш чат обсуждать 🤓

Статья в MEDUIM

Полезные горячие клавиши в Таблицах

Вооружайтесь мизинцем (ибо все начинается с Ctrl) и берите в работу несколько полезных сочетаний горячих клавиш в Таблицах. А чтобы посмотреть их все, нажимайте Ctrl + /.

Ctrl + A — выделение всей таблицы, к которой относится активная ячейка

Ctrl + ` — отображение формул в ячейках

Ctrl + \ — очистка форматирования (только стилевого, не числового) выделенных ячеек

Ctrl + Backspace — возврат к активной ячейке (если вы пролистали документ и сейчас активная ячейка не на экране)

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

И тройка приемов:
Необязательно использовать функции И / AND для логических операций. Можно заменять ее на умножение. Например, такая формула выдаст единицу, если выполняются оба условия — в ячейке A1 находится текст "Москва", а в A2 число меньше 100. Иначе формула будет возвращать ноль.
=(A1="Москва") * (A2<100)
(про И / ИЛИ в формулах массива писали еще здесь)

В функции ВПР / VLOOKUP можно использовать * для замены любого количества символов.
=ВПР("Москва*";диапазон с данными;2;0) — и мы ищем первую ячейку, которая начинается на слово Москва, забирая данные из второго столбца диапазона с данными.

Если вы хотите считать среднее / сумму за любой период, указанный в ячейке, введите начало диапазона как фиксированную ячейку, а конец диапазона задайте функцией ИНДЕКС со вторым параметром равным длине периода:
=СРЗНАЧ(начало диапазона с данными:ИНДЕКС(диапазон с данными;кол-во периодов))
=СРЗНАЧ(B2:ИНДЕКС(B2:B;$E$1))

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Повторяем диапазон N раз

Друзья, сегодня отличная формула из нашего чата от Михаила Смирнова: повторяем диапазон столько раз, сколько нужно.

Формула такая:
=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1; C1); A2:A5)))

Как она работает:
1) формируем строку, где количество элементов – количество будущих повторов нашего диапазона. Например, { 1 \ 2 \ 3 }, формируется формулой:
=SEQUENCE(1; 3)

2) собираем ЕСЛИ в массиве, в условии – сформированная строка, в TRUE (если условия выполняются) - исходный диапазон:
=ARRAYFORMULA(IF(SEQUENCE(1; 3); A2:A5))

3) Что получилось? ЕСЛИ создает столько столбцов с диапазоном, сколько символов в строке, которую мы задали в первом шаге (смотрите правую часть гифки).

Чтобы объединить все столбцы - добавляем FLATTEN (результат в середине гифки).

P. S. А еще можно добавить FILTER, чтобы отфильтровать от пустых ячеек:
=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1; C1); FILTER(A2:A;A2:A<>""))))

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

3D сумма, но в Таблицах

Друзья, в MS Excel есть функция 3D сумма, она позволяет суммировать ячейку от одного листа до другого, не перечисляя листы, которые будут между. Например, смотрите на гифку - от листа "янв" до "апрель".

В Таблицах такой встроенной функции нет, но мы для вас её повторили с помощью пользовательской функции (скрипт, который вы сможете запускать из листа), код ниже:

function SumSum(shFrom, shTo, cell){
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
let sum = 0;
let x = ss.getSheetByName(shFrom).getIndex();
let y = ss.getSheetByName(shTo).getIndex();

for(x; x <= y; x++){
const sheet = sheets[x-1];
sheet ? sum += sheet.getRange(cell).getValue() : '';
}
return sum;
}

Давайте попробуем разобрать все по строкам:
название функции и аргументы, которые будем в неё передавать из листа (лист начала, листа конца, ячейка, все должно быть в кавычках)
function SumSum(shFrom, shTo, cell){

определяем переменные ss, sheets, x, y и кладём в них текущую активную Таблицу, массив листов, индекс первого листа, индекс последнего листа:
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
let x = ss.getSheetByName(shFrom).getIndex();
let y = ss.getSheetByName(shTo).getIndex();

определяем sum – тут будет собираться наша сумма
let sum = 0;

начало нашего цикла, начинаем от индекса первого листа и продолжаем до индекса последнего листа включительно
for(x; x <= y; x++){

каждый шаг цикла
открываем Лист
const sheet = sheets[x-1];

проверяем, существует ли лист, если существует - достаём из него нужную нам ячейку и добавляем к sum
sheet ? sum += sheet.getRange(cell).getValue() : '';

завершаем цикл
}

возвращаем sum (в ячейку) и завершаем функцию
return sum;
}

==
Формула для запуска из листа должна выглядеть как-то так:
=SumSum("янв"; "апрель";"a1")

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

ОТПРАВЛЯТОР: отправляем ваши отчеты в теле письма по расписанию

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

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

Таблица со скриптом

Как всё работает:
Заполняете лист "настройки", одна строка в нём - одно правило отправки диапазона. Вам нужно ввести таблицу, откуда берём данные, лист и диапазон, кому отправляем письмо, как часто его отправляем и тему письма

Дальше вы можете либо руками запустить скрипт (из "/ меню скриптов /"), либо поставить скрипт test на ежедневное выполнение, скажем, от 12 до 13.

Как заставить скрипт запускаться каждый день:
инструменты > редактор скриптов > кликаем на часы > "добавление триггера" > выбираем функцию test > триггер по времени > по дням > выбираем нужный час

Что происходит после запуска скрипта?
Скрипт запустился и определяет, если ли что-то, что нужно отправить сегодня и не было ли сегодня это уже отправлено (проверяются столбцы "дни недели отправки" и "дата и время последней отправки").

Если такие строки есть, то скрипт их обрабатывает: забирает из указанных диапазонов значения и с помощью простого скрипта превращает их в html-таблицу, далее формируется письмо и улетает адресатам.

Если всё получилось - в столбце "дата и время последней отправки" появляется дата и время когда письмо было отправлено.

==
Идейный вдохновитель и спонсор этого решения - Сергей М из нашего чата, спасибо ему 😎👏