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

Допустим, нам нужно сделать в одной таблице ширину каждого столбца точно такой же, как в другой.
К счастью, такая опция в специальной вставке есть. Копируем ячейки в первой таблице (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

DRIVE COLUMBUS, смотрим на свой Google Диск и управляем им прямо из Таблицы | @google_sheets

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

— Хотите из Таблицы контролировать свои рабочие файлы и папки и следить за тем, кому они расшарены?

— Пришел новый сотрудник? Вы сможете добавить его к нужным файлам за несколько минут.

— Старый сотрудник перестал оправдывать доверие и вы решили с ним попрощаться? Удалим его из файлов, пока он ничего не удалил сам :)

🔥 Таблица со скриптом (делайте копию)
🔥 Инструкция

P.S. А без помощи Игоря из Японии это решение бы не состоялось, спасибо ему! 🎎

❗️❗️ Важный апдейт

Custom Functions: пользовательские функции в Google Apps Script

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

Пару замечаний по пользовательским функциям написал Михаил Смирнов в новой статье. Благодарим Михаила за полезный материал!

https://telegra.ph/Google-Apps-Script--Custom-Functions--Poleznye-Melochi-11-04

Функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ / SUBTOTAL: подсчет только отфильтрованных строк

Эта функция позволяет производить вычисление только с видимыми строками.
У нее такой синтаксис:
=SUBTOTAL(номер функции ; диапазон ; [еще диапазон]; ...)

Номер функции определяет, какая операция будет производиться. Функций всего 11 - стандартный набор, который, например, есть и в вычислениях сводных таблиц Excel (в Google к нему в сводных еще добавляется подсчет уникальных значений).

Вот базовые функции (кроме них есть еще стандартное отклонение и дисперсия):
1 и 101 - среднее
2 и 102 - количество чисел
3 и 103 - количество значений
4 и 104 - максимум
5 и 105 - минимум
6 и 106 - произведение
9 и 109 - сумма

Каждая функция бывает в двух вариантах - коротком (9 или 11, например) и длинном из трех цифр (109 или 111).

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

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

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

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

P.S. В Excel функция работает аналогично. Кроме того, если вы создаете "Таблицу" (Ctrl + T) и затем строку итогов (Total Row) в ней, то в строке итогов функция ПРОМЕЖУТОЧНЫЕ.ИТОГИ будет формироваться автоматически, вам достаточно выбрать тип вычисления, например, "среднее".

Астрологи объявили неделю видео: делимся с вами топ-10 уроков от дружественного канала STM Solution, и несколькими нашими видео, которые вы могли пропустить.

Топ-10 STM Solution:
1️⃣ Как создавать и делать базовое форматирование

2️⃣ Как делать автозаполнение данных, закрепление строк, добавление листов

3️⃣ Как создавать макросы и делать кнопки

4️⃣ Формулы, Ссылки, Выпадающие списки и Фильтрация

5️⃣ Что такое функция IF (Если)

6️⃣ Как делать Импорт/Экспорт Excel и защиту данных

7️⃣ Как собрать данные из множества листов Google таблиц в один не используя скрипты

8️⃣ Первое знакомство и создание своей функции

9️⃣ Как создавать,искать шаблоны и форматировать текст

1️⃣0️⃣ Продвинутое условное форматирование

Наши уроки:
Дата и время в Google Таблицах. Функции СЕГОДНЯ, ТДАТА, РАЗНДАТ, РАБДЕНЬ, ЧИСТРАБДНИ, НОМНЕДЕЛИ

Функции подсчета и суммирования в Google Таблицах (СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ)

Добавляем изображение товара в ячейку Google Таблиц по его названию

Спарклайны в Google Таблицах. Синтаксис и примеры

Фильтры и фильтрация в Google Таблицах. Совместная работа

Функция ВПР(VLOOKUP) в Google Таблицах + ВПР с поиском по заголовкам

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

От ностальгирующих пользователей 😏

t.me/google_sheets/851

Выгружаем все файлы / папки, обращаемся к GSUITE-дискам

Апдейт к нашему посту, разбираем комментарии.

1) Как выгрузить все файлы и папки?
Просто вводите вместо ссылки на папку:
https://drive.google.com/drive/u/0/folders/root и небольшие диски (скажем, до 10 000 файлов) успеют выгрузиться за 6 минут, на бОльшие – времени не хватит и скрипт закончится по таймауту.

2) Как выгружать корпоративные диски?
Добавьте в код в редакторе скриптов, в функцию "search":

optionalArgs.corpora = "allDrives"; //возможные варианты: user|drive|domain|allDrives
optionalArgs.includeItemsFromAllDrives = true;
optionalArgs.supportsAllDrives = true;

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

DRIVE COLUMBUS
ДОКУМЕНТАЦИЯ DRIVE API

Памятки 2.0

Друзья, привет! Игорь @Drozdov_Igor_Advisd составил Таблицу с ответами на многие типовые вопросы начинающих и продолжающих - с ссылками на посты в чате/канале и статьи:
https://docs.google.com/spreadsheets/d/1CKlGTouezCy8Y0G2I50HLrSCeaOVKQsbKzo_6bBTw0w/edit#gid=0

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

ВЫГРУЖАТОР: достаём скриптом количество подписчиков чатов и каналов в Телеграм

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

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

Теперь вы сможете отследить, насколько эффективную рекламную кампанию вы провели, как растут ваши конкуренты и другие каналы :)

Как это работает:
1) Делаем копию Таблицы;
2) Заполняем на листе "настройки" какие каналы и чаты парсим;
3) Запускаем скрипт из меню со "❄️", скрипт добавит строки с данными на лист "результат", каждый новый запуск будет добавлять новые строки;
4) Запускать каждый раз вручную необязательно – скрипт можно поставить в расписание, создав триггер для функции getChannelsDetails (кто не знает как – смотрите ГИФку).

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

​​Пополняем обзор околотабличных книг еще тремя изданиями!
Книга из серии "для чайников" про Excel 2019, "Бизнес-анализ в Excel" и визуальное руководство по Google Workspace.
Эти и все остальные книги с обложками, подробными описаниями и ссылками - в обзоре:
https://teletype.in/@renat_shagabutdinov/excellent_books

Teach Yourself VISUALLY Google Workspace
Хорошее руководство по Google Workspace (бывший G Suite) для новичков и продолжающих.

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

Excel 2019 для чайников
Как всегда в этой серии — подробно, по шагам, много таблиц, дополнительных разъяснений. Но выбор тем удивляет при этом. Очень мало про функции и формулы, практически ничего. Но есть небольшая вводная глава про макросы, например.

Бизнес-анализ в Excеl: Финансы, продажи, логистика
Все же скорее это не бизнес-анализ, а справочник по Excel. Вот, например, глава про текстовые функции — там нет бизнеса, только перечисление функций и описание их синтаксиса. Собственно бизнес-анализ — последние главы, они очень короткие и простые (теория очередей, складская логистика, инвестиционный портфель, точка безубыточности) — кажется, что эти темы нужно отдельно изучать, а Excel отдельно.

И такой скрипт у нас есть:

🧞‍♂️Превращатор Листа Таблицы в xlsx / pdf / csv
— файл мгновенно скачивается
— либо сохраняется на Google Диск
t.me/google_sheets/652

Другие вопросы можно задать в нашем чате: @google_spreadsheets_chat

И просто полистать оглавление нашего канала: goo.gl/HdS2qn

Привет, завтра выложим скрипт для автоматического превращения изображений в текст :)

Распознаем текст изображений / PDF скриптом

Друзья, в Google Docs есть технология OCR (оптическое распознавание текста).

Технология доступна из скриптов, задаете файл, далее скрипт используя Drive API создаёт новый файл на Google Диске, заменив все изображения заданного файла на распознанный текст.

Это сработает с файлами PDF, изображениями и Google Документами со вложенными изображениями.

Создали для вас Таблицу со всем нужным кодом. Как применить.
1) Делаем копию Таблицы со скриптом;

2) В ячейку листа "config" вставляем URL файла (пойдет PDF, картинка, Google Документ с картинками);

3) Запускаем скрипт кнопкой "GO!", скрипт создаст из заданного файла новый Google Документ с текстом и вставит весь текст из него на лист "OCR";

4) Хотите сразу удалять временный файл – уберите комментарий (//) с 29 строки кода;

Fin

⭐️ Наш чат: t.me/google_spreadsheets_chat

⭐️ Оглавление нашего канала, в котором множество других скриптов: goo.gl/HdS2qn

⭐️ Про OCR:
habr.com/ru/post/114670/
habr.com/ru/post/97173/

❗️ P.S. К сожалению, технология только развивается и некоторые скриншоты распознаются весьма примерно. Чтобы получить хороший результат – исходное изображение должно быть в высоком качестве.

Расскажите в комментариях о том, что получилось у вас

Схватка двух ёкодзун. Сравнение Google Таблиц и Excel

Друзья, подготовили для вас Google Таблицу (естественно), в которой сравнили Excel и Таблицы. У них очень много общего, и это многое сохраняется при переносе файлов из одного редактора в другой; но много и мелких различий, нюансов. Надеемся, таблица поможет тем, кто переходит с одного редактора на другой или работает в обоих и хочет лучше ориентироваться. По некоторым темам добавили ссылки на наши посты.

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

И с наступающим Новым годом! Увидимся после праздников 🥂