Цвета в пользовательских форматах: табличка-памятка

Напомним, что в пользовательских форматах можно не только задавать несколько стандартных цветов словами:
[Red] [Green] [Blue] [Yellow] [Magenta] [Green] [Yellow] [Cyan]

Но и по номерам:
[Color1] [Color2] ... [Color55]

Выкладываем для вас табличку с кодами всех цветов!
(синяя заливка использовалась только чтобы было видно некоторые "бледные" цвета)

Обновляем Таблицу по ключу скриптом onEdit()

Привет, друзья! Сегодня рубрика – простой скрипт с комментариями по каждой строке кода.

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

Вот здесь (скрипт и комментарии), показываем, как задача решается с помощью триггера onEdit(). Как вы помните – onEdit() не надо запускать, он реагирует на все изменения Таблицы.

Таблица с решением (скопировать >)

=
Для заминки: в ячейке "H2" на листе "ЛистЛист" наш чекбокс, отследим с помощью onEdit(), что ячейка изменилась (чекбокс нажали или отжали) и выведем окно со словом "привет":

function onEdit(e) {
//определяем текущий лист, который редактируется и диапазон
let sheet = e.source.getActiveSheet();
let range = e.range;

//проверяем, редактируется ли ячейка "H2" на "ЛистЛист"
if (range.getA1Notation() == 'H2' && sheet.getName() == 'ЛистЛист!') {
//если да — что-то запускаем
Browser.msgBox('привет!')
}
}

Надо бы диапазон проредить.

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

Дальше вместо ... вставляем диапазон (из таблицы или результат каких-то формул).

Как убрать N первых строк диапазона?

=QUERY(...; "OFFSET N";)

Как убрать N первых столбцов диапазона?

=TRANSPOSE(QUERY(TRANSPOSE(...); "OFFSET N";))

Как убрать чётные строки диапазона?

=QUERY(...; "SKIPPING 2";)

Как убрать нечётные строки диапазона?

=QUERY(QUERY(...; "OFFSET 1";); "SKIPPING 2";)

Как убрать чётные столбцы диапазона?

=TRANSPOSE(QUERY(TRANSPOSE(...); "SKIPPING 2";))

Как убрать нечётные столбцы диапазона?

=TRANSPOSE(QUERY(QUERY(TRANSPOSE(...); "OFFSET 1";); "SKIPPING 2";))

Ну, вы поняли: для столбцов всё то же, что и для строк, только в начале и в конце делаем TRANSPOSE.

Ещё всё это можно совмещать.

Убираем чётные столбцы и строки:

=TRANSPOSE(QUERY(TRANSPOSE(QUERY(...; "SKIPPING 2";)); "SKIPPING 2";))

Оставляем каждую N-ю строку, начиная с первой:

=QUERY(...; "SKIPPING N";)

Оставляем каждую N-ю строку, начиная с M-ой (вместо M-1 надо вставить конкретное число):

=QUERY(QUERY(...; "OFFSET M-1";); "SKIPPING N";)

Полная версия статьи с примерами: telegra.ph/Kak-proredit-kolonki-i-stolbcy-diapazonov-v-Google-Sheets-10-20

Удаляем строки в определенном порядке в Excel

Так, а что делать в Excel, где хоть и появились уже и ФИЛЬТР/FILTER с УНИК/UNIQUE (только в Office 365), но великая QUERY все еще отсутствует?
Вот лайфхак от автора книги "Эффективная работа в Microsoft Excel" Алана Мюррея.

1 В соседнем с данными пустом столбце вводим какой-нибудь символ (например, смайлик) для тех строк, которые нужно оставить, и другой - для тех, что нужно удалить (например, @).

2 Протягиваем это дело с помощью маркера заполнения (в правом нижнем углу ячейки) на весь столбец.

3 Идем в "Найти и заменить" (FInd and Replace) - Ctrl + F или Ctrl + H - в поле "Найти" (Find what) вставляем символ из удаляемых строк и нажимаем "Найти все" (Find All).

4 Нажимаем Ctrl + A, чтобы выделить все найденные ячейки, закрываем окно, нажимаем Ctrl + - (вызов диалогового окна "Удалить") и выбираем "строку" (Entire Row).

Проблемы с IMPORTRANGE()

Уже более 10 дней люди массово жалуются на ошибку "Import Range internal error" при использовании IMPORTRANGE() там, где она раньше работала.

Это проблема на стороне Google.

Известны два костыля для временного использования.

1-й костыль:

Необходимо поменять регистр одной из букв (сделать из прописной строчную, или наоборот) во втором параметре IMPORTRANGE(). Второй параметр - это импортируемый диапазон.

Было так:

IMPORTRANGE("id"; "A:A")

Меняем одну букву A на a:

IMPORTRANGE("id"; "A:a")

Должно отпустить. Но потом проблема может вернуться. На этот случай есть 2-й костыль.

2-й костыль:

Дублируем IMPORTRANGE() внутри IFERROR(). Первый - оригинальный, второй - с изменённым регистром одной буквы. Например, так:

IFERROR(IMPORTRANGE("id"; "A:A"); IMPORTRANGE("id"; "A:a"))

Сколько у вас в формуле IMPORTRANGE() - все надо так оформить.

---

Лучей добра @Vitalich за 2-й костыль.

---

‼️Чтобы проблема была быстрее решена, не поленитесь зайти в созданный для Гугловских разработчиков баг и поставить ему звёздочку. ⭐️ Баг здесь.

‼️Так же проголосуйте за вопрос на Stackoverflow, гугловцы к ним присматриваются. 🔼 Вопрос на SO здесь.

🎓А ранее писали про вариант с флажком, щелчок на который будет инициировать обновление: t.me/google_sheets/362

‼️Чтобы проблема была быстрее решена, не поленитесь зайти в созданный для Гугловских разработчиков баг и поставить ему звёздочку. ⭐️ Баг здесь.

⬆️ Проблемы с IMPORTRANGE()

‼️Так же проголосуйте за вопрос на Stackoverflow, гугловцы к ним присматриваются. 🔼 Вопрос на SO здесь.

Новая функция LAMBDA в Excel: пользовательские функции без макросов

Нет в Excel QUERY и, например, REGEXEXTRACT, но зато появилась функция для создания пользовательских функций — LAMBDA. Не можем не поделиться этой интересной новостью.

Если раньше в Excel нужно было создавать пользовательские функции с помощью макросов (VBA), то теперь можно функцией.
Синтаксис у нее такой:

=LAMBDA([переменная]; … ; [переменная]; формула)

Переменных может и не быть (хотя тогда LAMBDA не имеет особого смысла, можно просто присвоить имени Excel формулу без аргументов и вызывать ее по этому имени), может быть одна или несколько. В конце последним аргументом всегда будет формула с этими переменными.

Рассмотрим на простом примере с отклонением факт/план. Допустим, план в столбце B, факт в столбце C. Обычная формула будет выглядеть так:

=C2 / B2 - 1

А в случае с лямбдой мы указываем переменные и формулу в общем случае:

=LAMBDA(план ; факт ; факт / план - 1)

После чего сохранить ее в диспетчере имен (Ctrl+F3) под любым именем, какое вы хотите присвоить этой функции — например, “Прирост”.

И далее вызывать свою функцию, вводя только аргументы (они будут отображаться в подсказке — см скриншот.

Пока функция доступна в Office 365 участникам программы Office Insider.

Ссылки:
Announcing LAMBDA: Turn Excel formulas into custom functions
Office Insider

Библиотеки в Google Apps Script
– что это такое
– создаём и публикуем свою
– импортируем в таблицу
– используем
– и много полезных ссылок

Очередной прекрасный материал от Михаила Смирнова: telegra.ph/Google-Apps-Script-Library--Biblioteki-v-Gugl-Skriptah-11-02

Библиотеки в Google Apps Script II — создаём триггер для библиотечной функции

И продолжение про библиотеки от Михаила Смирнова:
1. узнаём скриптами имя библиотеки
2. создаём скриптами триггер с функцией из библиотеки
3. делаем этот триггер самоудаляющимся

Выжимка: telegra.ph/Google-Apps-Script-Library--Sam-sebe-trigger-v-biblioteke-korotkaya-versiya-11-04

Статья целиком: telegra.ph/Google-Apps-Script--library-getResource-libSymbol-trigger-11-02

Поиск с учетом регистра

"Обычная" функция ВПР / VLOOKUP ищет значение без учета регистра. "ipad" и "IPAD" и "iPad" - ей все равно.
Как быть, если нужно искать значение с учетом регистра?

Функция СОВПАД / EXACT проверяет точное совпадение значений - своих аргументов - и возвращает ИСТИНА / TRUE, если они равны (то есть совпадают все символы и регистры всех символов).

Если ввести ее в массиве, то получим столбец со значениями ИСТИНА и ЛОЖЬ, и ИСТИНА будет в строке с нужным нам значением:
=ARRAYFORMULA(EXACT(искомое значение; просматриваемый столбец))

Ну а дальше дело техники: находим строку с ИСТИНОЙ через ПОИСКПОЗ / MATCH
=MATCH(TRUE; EXACT(искомое значение;просматриваемый столбец);0)

И ИНДЕКСом / INDEX вытаскиваем из столбца с нужными данными значение из найденной строки:
=INDEX(возвращаемый столбец; MATCH(TRUE; EXACT(искомое значение;просматриваемый столбец);0))

Можно воспользоваться и функцией FILTER - она будет возвращать несколько значений, если их будет больше одного, а не только первое:
=FILTER(возвращаемый столбец;EXACT(искомое значение;просматриваемый столбец))

Таблица с примером (Сделать копию)

P.S. Заодно мини-памятка. Где регистр учитывается, где - нет.
Учитывается:
- в регулярных выражениях
- в текстовых условиях кляузы WHERE функции QUERY
- в функции НАЙТИ / FIND
- в функции ПОДСТАВИТЬ / SUBSTITUTE

Не учитывается:
- в обычных логических выражениях, например =A1=B1
- в СУММЕСЛИМН / SUMIFS и других подобных функциях
- в функциях ВПР / VLOOKUP и ПОИСКПОЗ / MATCH
- в функции ПОИСК / SEARCH
- в текстовых условиях в функции FILTER

задачник в Таблице с оповещениями в Telegram чаты и каналы

Подготовили для вас маленькое решение, а вот умеет оно немало: с помощью скрипта, который есть внутри при активации флажков в столбцах "начало", "конец" задачник фиксирует время начала и окончания задачи. Помимо этого задачник отправляет в Telegram чаты / каналы сообщения о том, что задача начата (назначена) и закончена.

Чтобы все заработало у вас, нужно:
1. скопировать Таблицу с кодом к себе;
2. создать своего Telegram бота через @botfather, скопировать его токен и вставить в ячейку I1;
3. добавить созданного бота в чаты / каналы, куда вы будете отправлять оповещения;
4. также добавить в эти чаты @myidbot, командой /getgroupid узнать chat_id чатов и внести в ячейку I2 (один или несколько через запятую), потом бота можно удалить;
5. создать триггер: открыть редактор скриптов → триггеры → добавление триггера → функция: onSender → тип события: при редактирование → создать;

★ Код отдельно: pastebin.com/Ew9Uj75F
★ Оглавление канала: goo.gl/HdS2qn

Специальная вставка: перенос только ширин столбцов

Допустим, нам нужно сделать в одной таблице ширину каждого столбца точно такой же, как в другой.
К счастью, такая опция в специальной вставке есть. Копируем ячейки в первой таблице (Ctrl+C), правой кнопкой щелкаем на первую ячейку во второй таблице, и в контекстном меню выбираем "Специальная вставка" (Paste special) --> "Только ширина столбца" (Column width only).

И в Excel тоже работает! Ctrl+Alt+V + ш(w)
Или правая кнопка мыши --> Специальная вставка (Paste Special) --> ширины столбцов (Column widths).

Топ-20 упоминаний постов нашего канала за полгода (1.05.21 - 31.10.21)

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

1. крутейшая таблица с подсказками про query (19)
t.me/google_sheets/616

2. Народный Telegram бот❗️Отправляем сообщения прямо из Таблицы (13)
t.me/google_sheets/556

3. СОБИРАТОР 4.0 (9)
t.me/google_sheets/661

4. ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты (9)
t.me/google_sheets/719

5. Считаем сумму по каждой строке / столбцу в формуле массиве (8)
t.me/google_sheets/502

6. Избранные посты нашего канала (7)
t.me/google_sheets/418

7. ускоряем работу Таблицы (7)
t.me/google_sheets/143

8. ​​Условное форматирование. Выделяем дубликаты и не только (7)
t.me/google_sheets/296

9. Как сделать, чтобы при выставлении определенного статуса в соседней колонке Таблицы автоматически появлялась дата и время? (7)
t.me/google_sheets/171

10. ВЫГРУЖАТОР: СОЗДАЁМ РЕЕСТР ФАЙЛОВ ИЗ ПАПКИ, ВЛОЖЕННОСТЬ – УЧИТЫВАЕТСЯ (7)
t.me/google_sheets/635

11. ДВИГАЕМ ВРЕМЯ ФОРМУЛАМИ (7)
t.me/google_sheets/593

12. Важный скрипт. Связанные выпадающие списки из кэша (7)
t.me/google_sheets/408

13. Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF(и аналогах) (6)
https://tttttt.me/google_sheets/283

14. ​​Как посчитать:
- Количество ячеек, начинающихся не на букву "Т"?
- Сумму всех товарных позиций из 7 и более символов?
- Количество записей не из 4 символов? (6)
t.me/google_sheets/254

15. Повторяем диапазон N раз (6)
t.me/google_sheets/587

16. Библиотека небольших скриптов onEdit (6)
t.me/google_sheets/432

17. памятка по условиям, с помощью которых можно отобрать (для функции SUMIFs/СУММЕСЛИМН, например) пустые и непустые ячейки (5)
t.me/google_sheets/77

18. Работаем со вшитыми (богатыми 😊) в ячейки ссылками (4)
t.me/google_sheets/668

19. ОТПРАВЛЯТОР 2.0. Создаём из Таблиц PDF и JPG и рассылаем в Телеграм по заданному расписанию
t.me/google_sheets/643

20. отправлятор таблиц в телеграм чаты
бесплатно и по расписанию (4)
t.me/google_sheets/604

★ Чат: @google_spreadsheets_chat
★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn

P.S. @vitalich, спасибо за анализ чата и составление подборки 😎

IMPORTRANGE: от простого к сложному

Друзья! Большая статья от Михаила Смирнова:
— что такое IMPORTRANGE?;
— простой случай применения;
— добавляем к каждой строке информацию: из какой таблицы загружены данные (три способа);
— добавляем к каждой строке ссылку на строку в источнике;

telegra.ph/Google-Sheets--Nemnogo-pro-IMPORTRANGE-11-12

Выгружатор постов / просмотров / картинок из каналов Telegram

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

Как воспользоваться:
0. Посмотрите гифку :)
1. Копируете Таблицу с кодом к себе;
2. Заполняете лист настройки – вам нужно ввести ссылку на стартовый пост и количество постов от стартового, которое скрипт попробует спарсить;
3. Запускаете скрипт из меню с 🐧 (наверное, это скворец);
4. Скрипт вставит результат на лист "логи" и обновит дату / время на листе "настройки";

Как скрипт в принципе работает:
1. Берёт стартовый пост (например, t.me/google_sheets/739), берёт количество постов, которое мы хотим выгрузить (например, 3);
2. Создаёт массив ссылок добавляя к стартовому посту 1,
[t.me/google_sheets/739,
t.me/google_sheets/740,
t.me/google_sheets/741]

Поэтому, если на канале был пост, потом его удалили и эта ссылка попала в массив - по ссылке не вернется ничего❗️;

3. Далее весь массив ссылок запрашивается с помощью fetchAll();

Общая квота запросов на google-аккаунт – 20 000 запросов на бесплатном и 100 000 на google workspace, но если вы запросите сразу 1000 постов – то по части запросов сервер Telegram может не вернуть ничего, даже если посты были, подумав про вас и про ваш IP-адрес нехорошее, поэтому – запрашивайте умеренно ❗️;

4. Из результата по каждой ссылке с помощью регулярных выражений скрипт достаёт текст поста / просмотры / картинку;

Только код: pastebin.com/RbGjK7ju
Таблица с кодом: Таблица

Документация: fetchAll, квота запросов в день

PS Идеи что добавить к функционалу – напишите в комментариях.

Удалили нужный файл на Google Drivе?
Рассказываем, что делать.

Друзья, Александр Иванов написал про то, что делать, если вы случайно удалили нужный файл – категорически рекомендуем: gdriveru.blogspot.com/2021/11/restorefiles2.html

★ Чат: @google_spreadsheets_chat
★ Больше постов в оглавлении нашего канала: goo.gl/HdS2qn