Как получить первые или последние 3 (или сколько угодно) строк из диапазона с данными?

Выводим 3 первые строки :
=ARRAY_CONSTRAIN(A1:E9;3;COLUMNS(A1:E9))

Выводим 3 последние строки:
=CHOOSEROWS(A1:E9; SEQUENCE(1;3;ROWS(A1:E9);-1))

А создать диапазон диких, но симпатичных привидений нам помогла протянутая формула =REPT("👻";ROW())

Задача из нашего чатика: @google_spreadsheets_chat, скоро 5000 участников, залетайте!

PS Вариантов этих формул может быть множество - напишите в комментариях свои, а мы добавим в Таблицу

Таблица

⭐️ Оглавление канала: ты-дыц

Переносим форматирование с одного листа на множество

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

Что делать? Можно обойти каждый лист и вставить форматирование туда.

А можно всё сделать на одном листе (у нас он называется "шаблон") и воспользоваться нашим скриптом.

Скрипт в комментариях ↓

Всё хорошо, но на всякий случай делимся нашими постами :)

Как сделать бекап Google Диска t.me/google_sheets/883

Регулярный бекап Таблиц в формате XLSX в телеграм t.me/google_sheets/884

Главная статья импорта. Руководство по функции IMPORTRANGE

Друзья, собрали в одном месте для вас максимум информации по функции IMPORTRANGE:
— Cинтаксис функции
— Как может выглядеть ссылка на файл и на диапазон
— Разрешение на доступ
— Загружаем отдельные столбцы из исходника
— Объединяем данные из нескольких источников
— Загружаем данные из списка (который может меняться) таблиц
— Проблемы с загрузкой данных
— Вложенные функции или отдельный лист для импорта
— Как часто обновляются данные
— Как вытащить из IMPORTRANGE ссылку и сделать активной
— Ссылки на другие материалы

https://teletype.in/@renat_shagabutdinov/importrange

Вопрос жизни и смерти

В нашего бота, в котором можно заказать работу пришел вот запрос (скриншот).

Ну что же, давайте поможем, нельзя дать никому умереть.

Переходите по ссылке на наш пост с подборкой маленьких, но очень полезных onEdit скриптов:
t.me/google_sheets/1133

Многие скрипты с комментариями 😉

Нашему чату про Таблицы почти исполнилось 6 лет!

А еще в почти 5000 участников, давайте доберем: @google_spreadsheets_chat

Хотим провести жеребьевку и распределить команды (или что-то еще) по группам или корзинам?

Можно использовать функцию WRAPCOLS. Укажем вторым аргументом, сколько команд должно быть в группе — и готово. Чтобы все было случайно, можно перемещать исходный диапазон (Данные — Перемешать ячейки диапазона, Data — Randomize Range)

=WRAPCOLS(диапазон с командами; число в одной группе)

Но если не хотим перемешивать диапазон вручную, а чтобы формула сразу распределяла команды случайно, то воспользуемся сортировкой (SORT) по случайному диапазону (RANDARRAY).

SORT(диапазон; RANDARRAY(число элементов); 1)

И все вместе:
=WRAPCOLS(SORT(диапазон;RANDARRAY(число элементов в нем);1); число элементов в группе)

Спасибо нашему любимому автору Бену Коллинзу за идею!

Персональная техподдержка компаний от @google_sheets

Друзья, привет! Наша новая услуга – внедрение нашей команды (автор канала Евгений и его спецы) в ваш бизнес для помощи по Таблицам и GAS-скриптам.

💥Как это выглядит

Мы создаём чатик, вы добавляете своих коллег, мы добавляем наших асов.

Вы пишете в чатик вопросы по формулам, Таблицам, просите написать небольшие скрипты, обращения к api, телеграм-ботов, консультируетесь.

Мы со своей стороны быстро и профессионально* вам помогаем.

*наш канал существует с 2017 года, наш чат существует с 2018 года и каких только задач за это время не приходилось решать, а также авторы канала написали книгу и записали курс.

💥 Условия

Сейчас возьмем 2-3 компании, стоимость 225 000 р. за три месяца, далее по 75 000 р. в месяц, если захотите продолжить.

Пишите @namokonov, обсудим.

Что входит в наш пакет поддержки компаний:

1) Разумеется, помощь по формулам, напишем с вами самые сложные и оптимальные, перепишем ваши медленные;

2) Скрипты, пара примеров:

— Сможем связать несколько таблиц в одну, собирать данные и превращать в красивый отчет;

— Сможем работать скриптами с Googlе Диском, пришел работник: автоматически расшарили для него пакет документов, работник оказался слаб духом, не выдержал темпа и уволился, в тот же день уберем эти файлы из его доступа;

— Сможем создавать Google Документы / Word файлы со счетами из данных Таблиц;

3) Интеграции со внешними API, пример: торгуете на озон и ВБ: сможем настроить скрипты так, чтобы данные о ваших остатках, продажах и заказах попадали сразу в Таблицу, также сможете менять цены и описание товаров из Таблицы

4) Телеграм боты, полезные для вашего бизнеса:

— Сотрудник работает на полях? Сделаем бота для удобной отправки данных прямо в Таблицы / базу данных BQ;

— Менеджменту нужно отслеживать KPI в режиме реального времени? Настроим бота, который будет отправлять сообщения в чаты, когда выбранные метрики в Таблицах выйдут за установленные рамки;

— Бот, который мы делали для компании DiDI: пользователь садится в такси, быстро находит через поиск номер а/м и отвечает на ряд вопросов по водителю и по поездке (в моменте ботом пользовались 100 человек), все отправляется и в Таблицу и в BQ;

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

Если остались вопросы по пакету, пишите @namokonov, обсудим.

Сколько все стоит и какие условия

Обращаемся к Телеграм и достаём

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

Всё просто, делайте копию Таблицы, вводите юзернеймы каналов в A2:A и запускайте скрипт из меню.

Это уже третий наш пост про то, что можно достать из Телеграм, два предыдущих:

Достаём скриптом количество подписчиков чатов и каналов в Телеграм

Достаём из телеграм каналов посты и просмотры

А вот еще одна новинка, но несопоставимого масштаба... код Python будет работать прямо в Excel!

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

Работать будет прямо в ячейках — можно будет начинать ввод с =PY в формуле или через ленту инструментов.
А вычисления будут производиться в облаке. То есть будет нужен доступ к сети.

Для ссылок на объекты (ячейки и диапазоны, данные в "умных" таблицах) Excel будет использоваться функция Python xl().

Ссылки:
Официальная новость
Общие сведения о Python в Excel
Начало работы с Python в Excel
Python in Excel (на странице дистрибутива Anaconda)

Выведем, когда и кем менялся конкретный файл на Google Диске сегодня

Друзья, опять мы к вам с подарками и с простым скриптом.

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

Чтобы скрипт заработал не забудьте добавить Drive Api в сервисах в редакторе скриптов:

function main() {
const dt = new Date(new Date().setHours(0,0,0,0));
const url = 'https://docs.google.com/...';
Logger.log(listFileRevisions(url).filter(x => x[1] >= dt));
};

function listFileRevisions(file) {
return Drive.Revisions.list(file.match(/[-\w]{25,}/)[0])
.items.map(revision => {
return [
revision.id,
new Date(revision.modifiedDate),
revision.lastModifyingUserName,
revision.lastModifyingUser.emailAddress,
];
});
};

@zadavai_vopros_bot

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

Да-да, поздравления с 23 февраля и 8 марта для коллег вы тоже сможете писать с помощью ИИ и даже в «стихах» :)

Про бота / Пишем ботом простые скрипты / Отвечаем ботом на отзывы / Работа с текстом

КопиПастер от Александра

Друзья, сегодня Таблица с простым скриптом с сайдбаром от Александра, слово автору:

Написал для работы простую, но удобную программку. Может кому тоже пригодится. Смысл такой: юзер добавляет в колонку дежурные фразы, которые ему приходится часто отправлять по почте, в чатах, в ТМС и т.д. Чтобы каждый раз не писать много текста, можно один раз эти фразы написать в таблице. Они списком попадают в сайдбар, откуда их можно отредактировать, если надо и скопировать в буфер кнопкой. Казалось бы ерунда, но может сэкономить кучу времени в рутинной работе.

PS Ну понятно, что скрипт не наш, мы бы это назвали "вставлятор" 😅

--
продаём разные скрипты для маркетплейсов, к примеру - загружаем статистику по рекламным кампаниям и в озоне и в ВБ скриптами и в Таблицу (пишите @namokonov)

Вычисляемое поле в сводной: умножаем сумму на одно значение

Бывает, что нужно умножить сумму всех значений (например, продаж в штуках или выработки в тоннах) на одно значение (например, цену).

По умолчанию у нас будут суммироваться все значения всех полей.
То есть при такой формуле:

=Продажи*Цена

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

А нам надо:

= Все продажи * одна цена

В таком случае выбираем в редакторе сводной в рассчитываемом поле "Суммировать по:" — "Другое".
И тогда мы получим только первую продажи и только первую цену.
Добавляем к полю с продажами (тому, которое нужно суммировать) функцию SUM / СУММ и будет нам счастье.

=СУММ('Поле, которое суммируем') * 'Поле, которое не суммируем'

Для любителей видео — пара минут со звуком.

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

Друзья, присылайте нам (контакты в профиле) ваши материалы про Google Таблицы, скрипты и интеграции вокруг. То, что будет интересно нашей аудитории - будем выкладывать. А с нас – плюшки :)

А сегодня пост от нашего подписчика @ramathethird, даём ему слово:

-
🤖 Представляем вам готовое решение для возможности развернуть своего Telegram бота с технологией оптического распознавания символов (OCR)!

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

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

📝 Для разворачивания бота необходимо выполнить следующие шаги:

1️⃣ Создайте новый Telegram бот.
2️⃣ Получите api-токен вашего бота.
3️⃣ Скопируйте себе данный standalone script.
4️⃣ Опубликуйте скрипт как веб-приложение (от лица своего аккаунта и для всех, если не понятно — вот видео) и скопируйте URL.
5️⃣ Заполните переменные botToken, webAppUrl, folderId.
6️⃣ В разделе "Сервисы" добавьте "Drive API" (если не добавился при копировании).
7️⃣ "Взведите" бота с помощью функции setWebhook().
8️⃣ Отправляйте боту изображение с текстом для теста.

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

⚠️ Важно отметить, что технология оптического распознавания символов (OCR) не всегда может быть 100% точной. Некоторые факторы, такие как качество изображения, шрифт, язык и другие факторы могут повлиять на точность распознавания текста. В результате могут возникать неточности или ошибки в распознанном тексте.

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

НАШ ЧАТ, про Таблицы и скрипты

Проверка данных (Data validation) с формулами

Если вам не хватает готовых решений в проверке данных (проверка на числа, даты, вхождение символа/слова в текстовую строку, соответствие адресу электропочты) — пользуйтесь вариантом "Ваша формула" и создавайте свои собственные правила проверки.

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

Примеры правил
Только уникальные значения
=СЧЁТЕСЛИ(диапазон;ячейка со значением)<=1
Проверка будет ругаться, если введенное значение уже встречалось в столбце A.

Только рабочие дни:
=ДЕНЬНЕД(первая ячейка;2)<6
Проверка будет ругаться, если номер дня недели у введенной даты - 6 или 7, то есть это суббота или воскресенье. Напомним, что у ДЕНЬНЕД / WEEKDAY нужно указать второй аргумент = 2, именно такой тип нумерации соответствует привычному для нас (где понедельник = 1).

Текст не длиннее 8 символов:
=ДЛСТР(первая ячейка)<=8
Здесь проверка будет ругаться, если вы ввели более 8 любых символов.

Только формулы:
=ISFORMULA(первая ячейка)

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

Наш чат про Таблицы и про скрипты: @google_spreadsheets_chat