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

Друзья, выкладываем наше готовое решение для замены одних значений на другие в выбранных Таблицах.

Краткая инструкция:
1) делайте копию Таблицы со скриптом, чтобы унести её к себе на диск
2) заполняйте словарь (значения, которые мы ищем и значения, на которые мы будет их заменять) и вносите ссылку на Таблицу со словарём и название листа в ячейки a3, b3
3) в диапазоне a5:c укажите таблицы, листы и диапазоны, в которых требуется произвести замену
4) в диапазоне d5:d отметьте чекбоксами те строки, которые требуется обработать сейчас
5) и запустите скрипт нажатием на кнопку

==
Скрипт создан при поддержке lesollp.ru, спасибо им,

а еще мы пишем любые скрипты, создаём телеграм ботов на заказ (тыц)

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

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

Нерадивые сотрудники заходят в старые сделки и меняют контрагента или сумму случайно? Или специально, пытаясь запутать вас и выгадать что-то для себя? 🐽

Мы создали скрипт, который поможет в таких случаях.

Работает он так – с помощью функции findRow находим номер последней строки со вчерашней датой, далее с помощью delProtection удаляем защищенный диапазон, далее с помощью createProtection создаём диапазон начиная со второй строки листа и по последнюю строку со вчерашней датой.

Таблица со скриптом здесь, код отдельно здесь, чтобы все работало у вас – уносите код к себе в Таблицу и заполните 4, 5, 6, 11 строку в редакторе скриптов: это название рабочего листа, емейлы, которые добавляем в закрытый диапазон, название защищенного диапазона и номер колонки с датой на вашем листе.

==
Разработка Таблиц, скриптов и ботов (от 10к рублей)

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

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

Хотите добавить к пятидесяти рабочим файлам нового сотрудника как редактора или оперативно убрать сотрудника, который не прошёл испытательный срок и вообще не подходит по духу и грозится все удалить?

Сегодняшнее решение (тут) поможет вам с этим разобраться.

Советы и правила:
– Скрипт работает и с файлами и с папками
– Можно указывать как URL, так и ID файла или папки
– Если даёте доступ к папке, то он распространится на вложенные в папку папки и файлы
– После обработки всей строки скрипт добавит дату и время в столбец E, при следующем запуске скрипт начнёт с первой строки БЕЗ даты и времени (это сделано для того, чтобы обрабатывать много файлов)

Поэтому, если нужно обработать строку – очистите
столбец E
– Чтобы добавлять / удалять сотрудников вы должны быть по крайней мере редактором

===
а еще уменьшаем время, которое вы тратите на рутину (скрипты, боты и отчёты на заказ)

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

QUERY по дате, представленной в виде текста

Допустим, у вас есть столбец с текстовыми строками, содержащими даты (в виде 05.06.2020, далее до 15.06.2025 или чего-то подобного)

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

Итак, вам нужно доставать все строки, в которых встречается дата за определенный месяц и год (числа любые).
Будем вводить в ячейке A1 дату, а извлекать из нее текстовую строку вида "MM.ГГГГ" будем следующей конструкцией:

ТЕКСТ(МЕСЯЦ(A1);"00" )&"." &ГОД(A1)

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

=QUERY(диапазон_с_данными;"select * WHERE G contains '"&ТЕКСТ(МЕСЯЦ(A1);"00")&"."&ГОД(A1)&"'";1)

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

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

Представьте – вам нужно зайти на 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