Чатно-канальный бот v. 3

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

В видео - как это работает.

Как установить бота себе: t.me/google_sheets/1567

🍏Хотите уметь делать таких ботов самостоятельно? Скоро мы запускаем курс, группа будет небольшой и после каждый сможет соединять телеграм, Google Таблицы и делать свой бизнес интереснее и проще

Читать полностью…

Показать защищенные диапазоны

У вас доступ на редактирование, но часть диапазонов защищена. В случае с защитой всего листа у его ярлыка появляется иконка, но не в ситуации, когда защищены отдельные диапазоны на листе. И вы хотите сразу видеть, что нельзя редактировать.

Вид — Показать — Защищенные диапазоны
View — Show — Protected ranges

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Чатно-канальный бот v. 2

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

В видео показал, как всё теперь работает.

Как установить бота себе: t.me/google_sheets/1567

Читать полностью…

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

Пожалуйста, поделитесь:
/channel/google_sheets?boost

Как соберем бусты, то выложим вторую часть про бота выше - он будет работать как на каналах, так и в чатах.

Читать полностью…

⚡ Прокачайте навыки работы в Excel ⚡

Высшая школа Главбух открыла бесплатный доступ к части курсов топовой программы «Excel для бухгалтера. Уровень PRO». На 7 дней вам будут открыты для изучения:
— Инструменты Excel (основные функции, ВПР и многое другое);
— POWER QUERY для быстрого обновления отчетов;
— Профессиональные приемы по работе с Excel от наших экспертов.

Также вам будет доступен тренажер для контроля своих знаний.

Оставляйте заявку и персональный менеджер отправит вам курсы по самым востребованным функциям Excel.

Оставить заявку на доступ к программе >>

Реклама. НОЧУ ОДПО "АКТИОН". ИНН 7706218347.

Читать полностью…

👉 Сегодня всем подписчикам канала мы открыли бесплатный доступ к курсу "Инструменты Excel для финансиста" в Высшей школе финансового директора.

Изучив курс, вы сможете:  
🔹Создавать в Excel динамические графики и гистограммы
🔹Настроить дашборд с метриками под нужды вашей компании
🔹Создать панель оперативного мониторинга и отслеживать показатели 🔹Построить интерактивный график для анализа затрат в Excel

+ В подарок книга по работе в Excel, которая прокачает ваши навыки

Скачивайте дашборды, инструкции, матрицы ключевых метрик, шаблоны с диаграммами и применяйте их в своей работе!

Заказать бесплатный доступ к курсу на 3 дня>>>

#реклама
О рекламодателе

Читать полностью…

Друзья, с началом нового рабочего года вас!

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

Чтобы получать число прописью, обычно используют пользовательские функции. Но если вам нужно без "рублей" и "копеек", можно воспользоваться функцией BAHTTEXT — она превращает число в текст на тайском с добавлением слова "бат", названия валюты (название тоже на тайском, что логично).

А мы это дело переведем на русский или другой нужный язык с помощью GOOGLETRANSLATE.

А потом останется убрать слово "бат" (бата, батов — смотря какое число будет). С помощью REGEXREPLACE.

Повторимся, для нормальной реализации (с рублями-копейками, для договоров) нужна пользовательская функция или надстройка с ней — про это мы тоже напишем

=REGEXREPLACE(GOOGLETRANSLATE(BAHTTEXT(A1);"th";"ru");"бата?\.|батов \.";"")

Читать полностью…

Друзья, поздравляем с наступившими праздниками! Повторяем пост с нашим полезным скриптом.

Редактируем Google Форму прямо из Таблицы!

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

Как всё работает - на гифке, гифка сегодня со звуком.

Что происходит после запуска скрипта в Таблице:
1) в четвертой строке листа "🔥" Таблицы - вопросы, в пятой строке - тип каждого вопроса, под каждым вопросом и типом - варианты ответа, в ячейке "🔥!B2" - форма, которую будем редактировать
2) запускаем скрипт кликом на кнопку и код ищет каждый вопрос по названию в Google Форме
3) если вопрос найден - то скрипт обновляет варианты ответа
4) если вопрос не найден - скрипт добавляет этот вопрос в Google Форму с заданным типом и вариантами ответа

Типы вопросов, с которыми умеет работать скрипт:
CHECKBOX – выбираем ответ на вопрос чекбоксами, можно выбрать несколько вариантов
LIST – выбираем ответ из выпадающего списка
MULTIPLE_CHOICE – выбираем ответ в переключателе

Чтобы воспользоваться Таблицей - копируйте себе

---
⭐️ Оглавление канала: ты-дыц
⭐️ Самый табличный чат на свете: бадабум

Читать полностью…

Самое часто встречающееся текстовое значение

Самое частое число — это мода. Функция МОДА / MODE.

А как найти самое часто встречающееся текстовое значение?
Бен Коллинс в своей книге предлагает решение через MATCH (можно и XMATCH, которая просто по умолчанию ведет точный поиск без обязательного третьего аргумента = 0)

Ищем каждое значение в списке и получаем массив из чисел с порядковым номером каждого элемента (номером той строки, в которой впервые встречается значение). Если диапазон включает пустые ячейки, добавьте IFNA, чтобы заменить ошибки.

IFNA(XMATCH(B2:B;B2:B);)

Потом ищем самое частое число в этом диапазоне:

МОДА(IFNA(XMATCH(B2:B;B2:B);))

И извлекаем по этому порядковому номеру текст ИНДЕКСом / INDEX:

=ArrayFormula(ИНДЕКС(B2:B; МОДА(IFNA(XMATCH(B2:B;B2:B)))))

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Клавиши для выделения столбца/строки

Ctrl + пробел — выделение всего столбца.
Первое нажатие: столбец в рамках диапазона (если активна ячейка какого-то диапазона/таблицы);
Второе: весь столбец листа.

Shift + пробел — выделение всей строки (в случае с таблицей сначала ее строки, затем строки всего листа)

Как запомнить? Клавиша Shift обычно длиннее. Как строка 🙂

❤️ Канал @google_sheets
📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Наш чат, в котором мы уже 6 лет отвечаем на ваши вопросы: @google_spreadsheets_chat

ПС Кто-то напишет в личку и предложит помочь - не отправляйте деньги, вас обманут

Оглавление нашего канала, где перечислены все наши посты: Оглавление канала

Читать полностью…

📊 Освойте Excel с нуля — работайте быстрее и эффективнее

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

Если вы хотите научиться работать с таблицами, экономить время и оптимизировать рабочие задачи, приходите на бесплатный курс Нетологии: «Excel: простые шаги для оптимизации работы с данными».

💡 За 3 занятия вы:
— Освоите ключевые функции, диаграммы и формулы Excel.
— Поработаете со сводными таблицами и составите отчёт.
— Пройдёте весь путь анализа — от подготовки данных до представления результатов.

🎁 Регистрация уже открыта — стартуйте бесплатно и улучшите свои карьерные перспективы

Реклама. ООО "Нетология". ИНН 7726464125 Erid 2VSb5yA2fz8

Читать полностью…

Один инструмент хорошо, а много — лучше!

Во времена «специалистов на все руки» одним инструментом не обойтись. Достаточно взглянуть на список навыков у вакансий с зарплатой от 80 000 рублей, причем вне зависимости от сферы. Чтобы двигаться вверх по карьерной лестнице, осваивайте новые навыки в комплексе.

Становитесь гуру Excel? Подключайте SQL и Python, а заодно прокачивайте и другие востребованные на рынке труда навыки — и soft skills, без которых в эпоху ИИ вообще никак.

Прокачивать желательно на практике, чтобы в резюме сразу капал опыт работы. Например, на курсе «Аналитик PRO» от Changellenge >> Education.

Что вас ждет:
✔️ обучение по принципу «от теории к практике», причем практика составляет 75% курса;
✔️ 11 ключевых аналитических навыков от Python до BI-систем и прокачка soft skills на командных проектах;
✔️ официальный диплом и упрощенный отбор в компании-партнеры: Kept, ДРТ, Arthur Consulting, SBS Consulting и Ozon.
✔️ индивидуальный план развития от карьерного консультанта в подарок

Успейте на предзапись на курс “Аналитик PRO” до 16 декабря. Запланируйте свое обучение на 2025 год со скидкой 40 000 рублей по промокоду TOOL40.

Забронировать со скидкой: https://u.to/KEQPIQ

Читать полностью…

Для подписчиков канала сегодня открыт бесплатный доступ к онлайн-курсу «Power BI для финансиста: от нуля до PRO» в Высшей школе финансового директора.

Cкачайте готовые модели и файлы и повторяйте процесс за автором. По окончании курса - официальное удостоверение о повышении квалификации.  

В программе:  
🔹Расчеты, вычисления, формулы 
🔹Наглядная сквозная аналитика и анализ финансовых показателей
🔹Как превратить 12 отчетов в один
🔹Как быстро рассчитать продажи, прибыль и финансовый результат
🔹Визуализация и разработка дашбордов  

Оставьте заявку на бесплатный доступ на 3 дня по ссылке>>> 

#реклама
О рекламодателе

Читать полностью…

А вы тоже все еще выбираете направление для своей карьеры в 2025 году?

🙄 Маркетинг или программирование?
🙄 Логистика или финансы?
🙄 … или аналитика?

2024 год показал: аналитика — профессия будущего. Принимать решения на коленке больше никто не хочет, даже в условиях турбулентности. Темп роста рынка — более 30% в год! Джуны со знанием SQL, Python и BI-систем получают от 100 000 рублей, а уверенные мидлы — более 250 000. Кстати, аналитика – тоже IT-профессия.

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

🔮 Предлагаем вам пройти бесплатный тест на профориентацию, он займет всего 3 минуты и сразу выдаст результаты. Представляйте себя на месте любимых киногероев, отвечайте на вопросы и узнайте, какой вы аналитик! 🧚‍♀️

Кстати, в конце теста вас ждет промокод на скидку до 30 000 рублей на обучение аналитике в школе Changellenge >> Education, чтобы начать погружаться в любимое направление было еще приятнее!

Пройдите бесплатный тест до 15 декабря и узнайте, какой вы аналитик 🪄

Пройти тест »

Реклама. ООО «Высшая школа аналитики и стратегии». ИНН 7716917009. erid: 2VtzqwJLzXj

Читать полностью…

С помощью Excel можно прогнозировать продажи на Wildberries, делать визуализацию и работать с Big Data. Все меньше компаний готовы принимать решения вслепую и упускать выгоду. Поэтому растет спрос на аналитиков.

Прокачивайте скилы на релевантных бизнес-задачах на программе «Аналитик PRO» онлайн-школы Changellenge >> Education.
Наставники из Яндекса, VK, Сбера и Росатома и других крупных компаний помогут со сложными темами и дадут обратную связь.

Как всё устроено:

1️⃣ Конкретные навыки
Вы получите 11 ключевых аналитических навыков от Python до BI-систем и прокачка soft skills на командных проектах

2️⃣ Практика с первого дня
Вас ждет обучение по принципу «от теории к практике», причем практика составляет 75% курса.

3️⃣ Гарантированное трудоустройство
По итогу вы получите официальный диплом и упрощенный отбор в компании-партнеры: Kept, ДРТ, Arthur Consulting, SBS Consulting и Ozon.

Сейчас в Changellenge >> Education стартовала Кибернеделя аналитики! Успейте зарегистрироваться на обучение со скидкой 20 000 руб. по промокоду CYBERWEEK до 31 января и станьте востребованным аналитиком уже в ближайшем будущем!

Оставьте заявку на сайте: https://u.to/1dxoIQ

Реклама. ООО "Высшая Школа Аналитики и Стратегии" ИНН: 7716917009, erid: 2VtzqvsMyxG

Читать полностью…

🚨 Как снизить риски при работе с внештатниками и автоматизировать бизнес? 

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

На бесплатном вебинаре “Как снизить риски и автоматизировать работу с внештатниками” мы:

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

📅 Когда: 28 января, 11:00 (МСК)
🎙 Спикер: Александр Поштак, юрист с многолетним опытом сопровождения бизнеса.

Зарегистрируйтесь, чтобы защитить свой бизнес и освободить время для роста:👉 https://konsol.pro/webinars/kak-snizit-riski?utm_source=tgposev28&utm_medium=google 

erid: 2W5zFJEsmaQ

#реклама

Читать полностью…

Совсем немножко осталось, добьем, господа?

/channel/google_sheets/1569

Читать полностью…

Бот, который сохраняет сообщения из вашего канала в Таблицу
(🚨c видео-инструкцией по установке!)

Друзья, привет! Мы с 2017 года ведём свой канал про Google Таблицы и скрипты.

Выкладываем посты и иногда их удаляем. Как найти пост, который мы удалили? Смотреть в истории изменений - история есть, но она небольшая, слишком далеко её не открутишь.

Поэтому мы сделали телеграм бота для себя и делимся им с вами.

Бот простой - мы его добавляем на канал как администратора, с минимальными правами и каждый созданный на канале пост (и бусты и изменения прав) бот отправляет в Google Таблицу как строку.

Чтобы развернуть бота у себя - смотрите видео в посте либо прочитайте текст ниже:

1) копируйте Таблицу к себе
2) создайте своего бота в @botfather
3) скопируйте токен, который вы увидели и вставьте в редактор скриптов Таблицы
4) разверните скрипт как приложение
Открываем редактор скриптов,
- начать развертывание
- новое развертывание
- от моего имени (ваш емейл)
- доступ для всех

Нажимаем на кнопку "начать развертывание", далее видим ссылку на веб-приложение, копируем и также вставляйте в код
5) запускаем скрипт "setWebHook"
6) всё! 

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

Таблица с кодом

🧩 Мы делаем ботов посложнее на заказ

⭐️ Через месяц мы запустим курс "как сделать бота для своего бизнеса" :) Следите за анонсами.

Читать полностью…

Как выделить / найти все формулы на листе?

Можно просто нажать Ctrl + `
Или в меню: Вид - Показать - Формулы (View - Show - Formulae), или Alt+V + S + A.

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

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


📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Диаграмма с выбором рядов (флажками)

Добавляем флажки для каждого ряда данных.

Дублируем данные для диаграммы, но формулой, которая будет возвращать значение из исходного диапазона только в том случае, если в соответствующем столбце флажок включен, то есть там значение TRUE / ИСТИНА. А если флажок выключен, то будет возвращать значение N/A с помощью одноименной функции. А такие значения на диаграммах не отображаются. Что нам и надо!

=IF(флажок;ячейка с данными;NA())

Строки с формулами можно скрыть, чтобы не любоваться всеми этими НД-шками, а только любоваться данными да диаграммой волшебной.

P.S. Так как периодически мы видим такое в чатах и таблицах, напомним: если вы проверяете статус флажка, не нужно отдельно прописывать равенство ячейки с флажком значению TRUE.
То есть формулы (где B$1 — ячейка с флажком)

=IF(B$1;...

и

=IF(B$1=TRUE;...

будут работать одинаково. Потому что включенный флажок — это TRUE. Так что в первом варианте будет TRUE. И во втором TRUE = TRUE вернет TRUE. Так что ничего страшного в избыточности нет, но и не нужно :)

P.P.S. В Excel все будет работать аналогично. Правда вот флажки в ячейках есть лишь в 365, а во всех старых версиях можно вставить флажки — элементы управления с вкладки ленты "Разработчик". Подробнее — в статье.

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

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Как начать работать с SQL?

Пройдите бесплатный мини-курс “SQL для новичков” от Changellenge >> Education и освойте SQL всего за 5 часов и получите практические знания, необходимые для работы с базами данных.

🔥 Преимущества курса:
- Выделите всего 5 часов на обучение,
- Изучите ключевые функции и операторы SQL,
- Получите видео-разборы от эксперта,
- Выполните более 30 практических заданий.

🎁 За прохождение мини-курса вы получите скидку 10 000 рублей на обучение на программе "Аналитик данных" от Changellenge >> Education.

📅 Скорее регистрируйтесь, осталось всего 15 свободных мест: https://u.to/JQVKIQ

Реклама. ООО «Высшая школа аналитики и стратегии». ИНН 7716917009. erid:2Vtzqvtebq5

Читать полностью…

НОВОГОДНЯЯ АКЦИЯ!

Наша команда, в преддверии Нового года, решила порадовать наших подписчиков и сделать новогодный подарок!

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

Bot: @vas_mnogo_a_ya_bot
Обязательно в сообщении указать:
1) тег #хочуподарок
2) подробное описание задачи
3) ссылку на Таблицу

Ждём ваши вопросы.
Кому-то точно повезёт!

С наступающим Новым годом! 🎄

Читать полностью…

📊 Excel раньше — какие-то таблички для бухгалтеров.
🚀 Excel сейчас — личный аналитик и оптимизатор.

Превращайте скучные цифры в полезные отчёты, умные таблицы и дашборды без всякой магии на курсе «Excel: от основ до анализа данных».

Вас ждут:
- Продвинутые формулы.
- Визуализация данных.
- Power Query и Power Pivot.

Всё это под руководством Павла Козлова, гуру Excel с 15-летним стажем. Он научил более 5000 человек укрощать эту программу. Теперь ваша очередь.

Неважно, новичок вы или опытный пользователь, курс поможет вам стать тем самым, кто делает красоту в Excel. Присоединяйтесь к курсу и открывайте новые возможности для своей карьеры. - https://netolo.gy/dLPl

Реклама. ООО "Нетология". ИНН 7726464125. Erid: 2VSb5ww3gYG

Читать полностью…

Полезные чаты по теме

Помимо нашего чата советуем чат наших друзей по скриптам: @googleappsscriptrc

И по Таблицам: @google_sheets_pro

ПС Делитесь в комментариях чатами по таблицам, excel, программированию, и всему, что близко

Читать полностью…

Проектная диаграмма с выделением выходных отдельным цветом

(или не выходных, а определенных дней недели — тут вы по аналогии справитесь😉)

Создаем два правила условного форматирования с формулами и выбираем разные цвета.

Для рабочих:

=И(первая дата с закреплением строки>=начало первого этапа с закреплением столбца; первая дата<=конец первого этапа;WEEKDAY(дата;2)>5)

Для выходных (уже не в общем виде, а с ячейками):

=И(D$1>=$B2;D$1<=$C2;ДЕНЬНЕД(D$1;2)<6)

Таблица

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Парсим данные из веб-страницы в Таблицу скриптами

Добрый День господа!

Я - Василий!

〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️
Сегодня небольшой, но полезный скрипт, о том, как спарсить данные с сайта на примере сайта о проведении Бильярдных турниров https://fairplay.host/.
〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️
Цель:

Собрать список участников указанных турниров и посчитать сколько раз каждый из участников участвовал в этих турнирах, для дальнейшего награждения лучших.
〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️〰️
Решение:

1. Забираем с первого листа таблицы список ссылок на турниры:

const ss = SpreadsheetApp.getActive();
  const spisokTurnirov = ss
    .getSheetByName('Список турниров')
    .getDataRange()
    .getValues()
    .slice(1);

2. В коде страницы находим ссылку на API и проходим по всем ссылкам в цикле, формируя список участников с указание их города:

  var arr = [];
  spisokTurnirov.forEach(x => {
    x = x[0].match(/\d{4,}/);
    const url = 'https://fairplay.host/api/tournament/details?id=' + x[0];
    arr = [...arr, ...pars(url)]
  })

3. Создаём объект и считаем количество участником и далее вставляем их на другой лист:

  let spr = {}; //создаём пустой объект

  arr.forEach(z => {

    let iii = spr[z[0]];
    !iii ? spr[z[0]] = 1 : spr[z[0]] = iii + 1; // тернарный оператор
  });

Делайте копию таблицы, пробуйте, смотрите код, пишите вопросы, с радостью на них ответим!

Полный код в таблице с примером.
✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️
Таблица с примером
✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️✔️

Читать полностью…

Запускаем скрипт в заданное время каждый час

Друзья, мне написал Владислав - у него есть скрипт и он хочет его запускать каждый час в конкретной время (например, в 55 минут каждого часа)

Мы решили помочь и написали скрипт, который

1) запускает нужный нам скрипт;
2) далее проверяет, есть ли скрипт в триггерах проекта, если есть - удаляет этот триггер
3) далее создаёт новый триггер, на следующий час, чтобы тот запустился в введенные в коде минуты (аргумент mins)

function googleSheets() {
  setDate(); // запускаем нашу функцию

  let fName = arguments.callee.name; // получаем имя текущей функции
  let triggers = ScriptApp.getProjectTriggers(); // удаляем триггер с текущей функцией, если он есть
  for (let i = 0; i < triggers.length; i++) {
    let trigger = triggers[i];
    if (trigger.getHandlerFunction() == fName) {
      ScriptApp.deleteTrigger(triggers[i]);
    };
  };

  let dt = new Date(); //рассчитываем время для запуска функции в следующий раз
  let h = dt.getHours();
  let mins = 44; //устанавливаем триггер на следующий час на это время
  let newDt = new Date(dt.setHours(h + 1, mins, 0));

  ScriptApp.newTrigger(fName) // ставим функцию на триггер
    .timeBased()
    .at(newDt)
    .create();
};

function setDate() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  sh.getRange((sh.getLastRow() || 0) + 1, 1)
    .setValue(new Date());
};

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

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…

Примеры применения ссылок на таблицы в формулах
начало про ссылки в "умных" таблицах тут

Пара примеров того, как можно использовать ссылки на таблицы на практике.

Допустим, у вас несколько таблиц с одинаковыми заголовками. Чтобы собирать их на одном листе с обновлением, используем функцию VSTACK. Первую берем с заголовками, а в остальных случаях только тело таблицы, чтобы заголовки не дублировались:

=VSTACK(ПерваяТаблица[#ALL]; [Вторая]; ... [ЕщеТаблица])

Если нам надо загрузить умную таблицу из другого файла — засунем ее во второй аргумент IMPORTRANGE. Если нужно с заголовками — опять-таки, добавляем #ALL:

=IMPORTRANGE(ID исходной таблицы; "УмнаяТаблица[#ALL]")

Только в случае с IMPORTRANGE мы берем название таблицы в кавычки, т.к. это не активная ссылка, а текстовый аргумент этой функции.

И вот в обоих случаях нам не надо беспокоиться о том, сколько будет строк в таблицах-источниках. Все новые строки соберутся в одну пачку функцией VSTACK или будут импортированы IMPORTRANGE.

Читать полностью…

Сопоставляем список фамилий в Таблице и список файлов в папке, скрипт

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

У нас есть список документов в папке google диска, пусть это военные билеты или паспорта. 

Каждый документ лежит в папке диска и имеет название в формате `Фамилия Имя Отчество`.

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

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

Код отдельно:

function ссылки_ссылки() {
  const idFolder = idFromUrl('https://drive.google.com/drive/u/0/folders/17uqPgg09Y3EaJJnkbutmzQfOZqIEMi0b'); // ID папки с файлами
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheets()[0];
  const range = sh.getRange(1, 1, sh.getLastRow(), 1);
  let values = range.getValues();
  const files = DriveApp.getFolderById(idFolder).getFiles(); // забираем все файлы из папки

  var filesM = [];

  while (files.hasNext()) { // создаём массив [Имя, Url] каждого файла
    var file = files.next();
    filesM = [...filesM, [file.getName(), file.getUrl()]];
  };

  values = values.map((x, i) => {
    const fileUrl = i == 0 || !x[0] ? '' : filesM.filter(z => new RegExp(x[0]).test(z[0]))[0]?.[1] || 'не найдено';
    return [x[0], fileUrl];
  });

  range.offset(0, 0, values.length, values[0].length).setValues(values);
};

function idFromUrl(url) { // функция для извлечения ID из URL
  return url.match(/[-\w]{25,}/)[0];
};

📗 Чат @google_spreadsheets_chat
📕 Оглавление канала
🏄 Заказ работы у нас

Читать полностью…