Волшебство "найти и заменить"
Приводим mm-dd к dd-mm не формулой.

Друзья, в инструменте "найти и заменить" можно использовать регулярные выражения. Разбираем несколько примеров, которые вам точно могут пригодиться.

— Вместо пустых ячеек вставляем наш текст
Найти: ^$
Заменить: Наш текст
К ячейкам должен быть применён текстовый формат
данных. Пустая или пробельная ячейка: ^\s*$

— Заменяем перенос строки на наш текст
Найти: \n (или \r, \r\n)
Заменить: Наш текст

Встречались с тем, что в диапазоне американский формат дат, а его нужно быстро привести к российскому?

Группы захвата:
— Сделаем из mm-dd-yyyy на dd-mm-yyyy
Найти: (\d{1,2})-(\d{1,2})-(\d{4}|\d{2})
Заменить: $2-$1-$3

— Сделаем из dd-mm-yyyy на День: dd, месяц: mm
Найти: (\d{1,2})-(\d{1,2})-(\d{4}|\d{2})
Заменить: День: $1, месяц: $2

Спасибо нашу чату, @vitalich и @mildly_disastrous за идею поста.

📣 Наш чат | Оглавление нашего канала

Несколько ссылок в одной ячейке

Небольшая новость: теперь добавлять гиперссылки можно к отдельным фрагментам текста, а не ко всей ячейке целиком.
Если раньше при создании гиперссылки (быстро сделать это можно с помощью Ctrl/Cmd + K) в ячейке автоматически формировалась функция ГИПЕРССЫЛКА / HYPERLINK, то теперь ссылки работают без функции и их может быть несколько в одной ячейке.

При наведении на ячейку с несколькими ссылками будет появляться всплывающий список всех имеющихся в ней ссылок - как на скриншоте.

При этом объединить несколько функций ГИПЕРССЫЛКА (например, если формируете ссылку формулой, как в нашем примере с ссылкой на отправку письма контрагенту из таблицы) в одной ячейке не получится. Только вставленные руками ссылки.

📲 наш чат

Telegram бот, который записывает всё, что видит в Google Таблицу

Вам потребуется 15 минут времени, чтобы создать такого бота самостоятельно.

В статье на медиуме – полная инструкция, справятся даже новички.

📣 Наш чат: @google_spreadsheets_chat

UPDATE к Telegram боту

— теперь бот умеет сохранять файлы, которые отправлены в чат на ваш Google Диск (quick view - тоже)
— ссылка на сохраненный файл попадёт в Таблицу
— подпись к файлам сохранится тоже

Статья (разворачиваем бота с 0 за 15 минут)
Таблица с кодом бота (кто развернул предыдущую версию – просто обновите страницы скрипта doPost и download и опубликуйте приложение под новой версией, регистрировать вебхук заново не нужно)

---
📕📗📘 Оглавление канала (в Google Таблице)

Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE

Итак, вы хотите создать простой дэшборд, в котором будете агрегировать данные по неделям или дням.

И при этом хотите легко переключать режим “недели / дни" (или изменение любого другого параметра), не залезая в формулы.

В статье разбираем, как построить такой отчёт

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

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

Очень простой летний скрипт

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

Работает он так:
1. заполняете лист "настройки": вносите название листа шаблона, а также названия листов, которые нужно создать
2. запускаете скрипт :)

Код скрипта:
function myFunction() {
const ss = SpreadsheetApp.getActive()
sheet1 = ss.getSheetByName('настройки'),
data = sheet1.getDataRange().getValues(),
sheet0 = ss.getSheetByName(data[0][0]);

for(let x = 1; x < data.length; x++){
const sheet_name = data[x][0];

if(!ss.getSheetByName(data[sheet_name])){
sheet0.copyTo(ss).setName(sheet_name);

}
}
}

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

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

Табличные диалоговые окна. Подсказки ввода dadata.ru

Друзья, Роман @romanigro (он же строгий модератор нашего чата) в своей статье рассказывает, как создать диалоговое окно для удобного ввода и подключить к нему подсказки.

Статья с примерами кода

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

Превратим 1 час, 2 мин, 22 сек в 1:02:22

Друзья, знаем, что вам нравятся такие формулы.

На входе у нас есть текстовые строки:

2 мин, 54 сек
11 мин, 37 сек
4 мин, 18 сек

Превратим эти строки в правильное время Google Таблиц:

1) С помощью трёх REGEXEXTRACT извлекаем из строк часы, минуты и секунды отдельно;
2) Добавляем IFNA, функция вернет 0, если какого-то показателя не будет;
3) Добавляем то, что получилось в функцию TIME(часы; минуты; секунды);

Формула целиком:
=TIME(
IFNA(REGEXEXTRACT(B4;"(\d+) ч.*");0);
IFNA(REGEXEXTRACT(B4;"(\d+) мин");0);
IFNA(REGEXEXTRACT(B4;"(\d+) сек");0))

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

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

Превратим 10.62 руб. в правильное число

У нас есть текстовые строки:

10.62 руб.
8.85 руб.
0руб.

Превратим их в числа:

1) с помощью SUBSTITUTE(B3 ; "руб." ; "") заменяем все "руб" на ничего
2) с помощью SUBSTITUTE(... ; "." ; ",") заменяем точку на запятую
3) используя TRIM убираем пробелы, если они есть
4) и с помощью VALUE превращаем то, что получилось в число

Формула целиком:
=VALUE( TRIM( SUBSTITUTE( SUBSTITUTE(B3 ; "руб." ; "") ; "." ; ",")))

Другой вариант (регулярка):
=VALUE(
REGEXREPLACE(C3;"(\d+)\.?(\d+)?.*";"$1,$2"))

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

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

Тормозит таблица?

Узнаём, какие в Таблице формулы и на каких они листах.

Простой скрипт для вас:
1) вводите в ячейку B2 ссылку на Таблицу,
2) нажимаете на выходца из убежища
=) скрипт соберет все формулы на всех листах и вставит их на лист

Так вы сможете увидеть лишние и тяжелые формулы и их оптимизировать. Что такое тяжелые формулы? Например, не советуем использовать QUERY вместо SUMIFS для каждой ячейки. Не стоит делать запасы из десятков тысяч протянутых впрок формул.

Таблица со скриптом (делайте копию, чтобы сохранить её себе и запускать скрипты)

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

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

Функция CHAR (СИМВОЛ) и генератор случайных кодов

Друзья, в статье рассказываем про функцию CHAR, выводим 20 000 разных символов с помощью SEQUENCE и создаем генератор случайных кодов.

Статья про генератор
(с Таблицей с формулами и скриптом)

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

Народный Telegram бот
❗️ Отправляем сообщения прямо из Таблицы

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

Теперь бот не только логирует все сообщения, а еще и умеет их отправлять в выбранные чаты или пользователям.

Итого, сейчас бот умеет:
– сохранять в Таблицу все сообщения, которые пишут ему и которые пишут в чаты, в которых он есть
– сохранять все файлы / фото на Google Диск
– отправлять сообщения из Таблицы

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

Статья с инструкцией по установке

Таблица с ботом

Видео с полной установкой

---
📕📗📘 Оглавление канала (в Google Таблице)

ВПР, достаём последнее значение по ключу

Функция =VLOOKUP("Вася";$A$3:$B$7;2;0) вернёт 100 – первое найденное значение из диапазона поиска по ключу "Вася".

Что делать, если мы хотим последнее значение по нашему ключу – 300?

1) Отсортируйте таблицу поиска по строкам по убыванию
2) Вставьте результат в диапазон ВПР

После этого последние строки станут первыми и функция начнет возвращать их.

Магия в том, что всё можно сделать внутри одной формулы:
=VLOOKUP("Вася"; sort(A3:B7; row(B3:B7); false) ;2;0)

P. S. а тут про левосторонний ВПР, с помощью него можно вернуть столбцы, которые расположены ДО ключа

В Excel 365 появилась чудесная функция ПРОСМОТРX / XLOOKUP — хороший апгрейд стандартного ВПР, не страдающий зависимостью от расположения столбцов в исходном диапазоне данных. И хоть мы не про Excel, но уверены, что вам будет полезно про нее знать.

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

В посте смотрим на ПРОСМОТРX, ее синтаксис и возможности, а также вспоминаем, как заменить ее в Google Таблицах с помощью пересборки исходной таблицы прямо внутри функции.

---
📕📗📘 Оглавление канала (в Google Таблице)

Зачем Таблицы в школе?

Людмила работает школьным психологом в украинской школе. Мы попросили ее рассказать, зачем ей в школе Google Таблицы: https://telegra.ph/Prichem-tut-tablicy-Google-07-20

Помогаем маме: Google Таблица для отправки посылок Amazon и Ebay

Друзья, мама нашего подписчика Антона активно торгует на Amazon и Ebay. Ежедневно ей приходится отправлять много посылок.

Антон создал решение для отправки и по нашей просьбе рассказал о нём в статье на медиум

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

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

Краткая инструкция:
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)

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