Пользовательские форматы чисел

Друзья, привет! С наступившим Новым годом!

Возвращаемся в рабочий режим. И оформляем наши таблицы наглядно! Для этого мы собрали в одну статью всю информацию про пользовательские форматы чисел:
— где они настраиваются
— какие символы используются в пользовательских числовых форматах
— как настроить отдельные форматы для положительных и отрицательных чисел, нуля и текста
— как добавить цвет в форматы
— как добавить условия в пользовательских форматах - и какие там есть ограничения
— пользовательские форматы для номеров телефонов
— округление до тысяч и миллионов с помощью формата
— выравнивание по десятичной запятой
— заполнение ячейки определенным символом
— функция ТЕКСТ
— пользовательские форматы даты и времени

https://shagabutdinov.ru/custom_format/

Таблица с примерами к статье с пользовательскими форматами чисел ⬆️

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

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

В видео учимся строить спарклайны, задавать их параметры в ячейках рабочего листа и в массиве внутри функции SPARKLINE, рассматриваем несколько примеров:
- Динамика выручки
- Анализ структуры продаж
- Позиционное сравнение нескольких товаров
- Визуализация выполнения плана: базовый уровень и превышение плана
- Строим график динамики котировок акций одной формулой

https://www.youtube.com/watch?v=ijkGAXCWaKU

Объединяем любое количество диапазонов Таблицы в одной функции.

Друзья, ДВССЫЛ / INDIRECT в массиве не работает, но очень часто вам нужно сослаться на ряд ячеек с диапазонами и собрать их в один массив. Например, у вас в Таблице двенадцать листов с расходами за разные месяца и вы хотите собрать все строки этих листов с помощью одной формулы, просто выделив диапазоны мышкой.

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

Чтобы функция работала в вашей Таблице - скопируйте код и вставьте его в редактор скриптов этой Таблицы.

Функция отдельно:
function indirects(ranges){
var ss = SpreadsheetApp
.getActiveSpreadsheet();

if(ranges.map){
return [].concat.apply([], ranges.map(function(i){return ss.getRange(i).getValues()}));}
else{return ss.getRange(ranges).getValues();}
}

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

Пока что наш лучший скрипт для сбора. С интерфейсом для ввода.

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

Просто скопируйте Таблицу себе и укажите в диапазоне «A2:C7» ссылки на Таблицы, листы и номера столбцов, которые нужно скопировать.

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

Самое главное — вы можете поставить скрипт на регулярное выполнение и собирать данные каждую ночь, раз в два часа, etc. Для этого создайте триггер: нужно открыть редактор скриптов, меню изменить > триггеры текущего проекта, добавить триггер и поставить функцию get_google_sheets на нужную периодичность выполнения.

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

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

Будут вопросы, пишите в наш чат: @google_spreadsheets_chat

Сапер и морской бой в Таблицах
Уже пошли игры

Друзья, вокруг нашего канала собирается сообщество людей, которые по-настоящему увлечены Таблицами и всем, что в них можно сделать. Поэтому сегодня мы выкладываем целых две игры — Сапёр и Морской Бой, их написал наш подписчик Лео. Он прошёл путь от «ночью заснуть не мог, думал-думал, но не придумал» до «ловите парни, я сделал».

Сапёр
Морской бой

Достаём просмотры и лайки статей прямо из веб-страниц. Волшебство =IMPORTXML.

Друзья, сегодня публикуем статью нашего подписчика Михаила. В своём материале на habr он рассказывает о том, как собрал все свои публикации в одной Google Таблицe и с помощью формул подтягивает к каждой публикации количество просмотров, лайков и другую информацию.

В функции =IMPORTXML два аргумента: адрес веб-страницы и запрос Xpath для поиска данных. Чтобы понять, что писать в Xpath, чтобы он вернул то, что вам нужно — можете воспользоваться бесплатным дополнением для браузера ChroPath.

А на гифке — пример, подтягиваем просмотры нашей статьи про ключевые отличия Ms Excel от Google Таблиц.

Статья Михаила на habr
Таблица LynxReport: учёт публикаций

Друзья, вашему вниманию - еще один видеоурок для начинающих!

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

https://www.youtube.com/watch?v=sqBhegW-JYU

Отображаем функцией =IMAGE() картинки из Google Диска в ячейке

Друзья, привет! Поменялась ссылка, которую нужно добавлять к ID файла на Google Диске, чтобы вывести изображение в ячейку. Старый вариант не работает, публикуем работающий.

Ссылка теперь такая: "https://docs.google.com/uc?export=download&id="

Целиком формула для ячейки A1, с регулярным выражением, достающим из ссылки ID файла будет выглядеть так:
=IMAGE("https://docs.google.com/uc?export=download&id="& REGEXEXTRACT(A1;"[-\w]{25,}"))

Чтобы все работало и изображение должно быть расшарено для всех по ссылке.

Таблица, в которой все работает

P.S. Регулярное выражение [-\w]{25,} матчит ровно 25 символов, каждым из которых может быть либо буква-цифра-подчёрк (\w, word character), либо дефис.

Дата и время в Google Таблицах

Сегодня хороший день - 43 873.
Во всяком случае, так говорят Таблицы. Мы, люди, называем это 12 февраля 2020 года.

Да, при работе с датами и временем в Google Таблицах бывает путаница.
Надеемся, наш видеоурок вам поможет.

В нем:
- Обсуждаем, как отображаются дата и время в Google Таблицах
- Знакомимся с функциями для отображения текущих даты и времени:
Для расчета разницы между двумя датами в разных единицах измерения, включая рабочие дни
Для определения номера года, месяца, дня и недели по ячейке с датой
Для расчета того, какой день наступит по истечению X рабочих дней

https://www.youtube.com/watch?v=Lbj5kh0q5KQ

Скрипт для работы с Google Контактами
Сохраняем контакты из Таблицы

Друзья, привет! Сегодня — гостевой скрипт от нашего подписчика Дмитрия @Mityayka1. Скрипт синхронизирует Таблицу и Контакты, добавляя в Контакты только те контакты, которых там не было, проверяя по номеру телефона. Принцип работы скрипта - на гифке.

Дима снабдил скрипт очень подробными комментариями, поэтому, если вы хотите понять логику работы с классом Google Контактов, а также посмотреть на то, как в скрипте обходятся с IF / циклами / массивами - открывайте редактор скриптов и читайте.

Дима — спасибо за полезный материал 👍👌🔥

>> Таблица со скриптом по ссылке

Приходите в наш чат: https://tttttt.me/google_spreadsheets_chat

Как работать с Таблицами из R

Друзья, привет. Алексей Селезнев записал небольшой урок о том, как работать с API Google Таблиц с помощью языка программирования R.

Из урока можно научиться:
1. Проходить авторизацию для работы с API.
2. Считывать данные с листов Google Таблиц.
3. Создавать Google Таблицы.
4. Создавать новые листы в Google Таблицах.
5. Записывать данные на листы Google Таблиц.
6. Дописывать данные в созданные ранее листы Google Таблиц.

https://youtu.be/no75MyT87l0

Канал Алексея про R, его библиотеки и маркетинг: @R4marketing

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

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

>> @google_spreadsheets_chat

Выбираем чекбокс — получаем код ответа сервера по URL.

(или о том, как запустить любой скрипт изменяя ячейки на листе)

Привет, друзья. Сегодня у нас скрипт, который вытягивает в Таблицу код ответа сервера (смотрите ГИФ).

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

Скрипт «дождётся» того, что описано в условии оператора IF (буквально — редактирования первого столбца листа «чек») и запустит то, что внутри оператора — передаст в функцию URL страницы и вставит результат в соседнюю ячейку.

С помощью этой конструкции вы сможете запускать любой нужный вам скрипт изменением выбранных ячеек на листе, в том числе с приложения Таблиц на телефоне.‼️

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

Чтобы Таблица у вас работала — откройте редактор скриптов, триггеры и поставьте функцию go на триггер редактирования Таблицы.

Собираем данные в виде ФИО+телефон по сотрудникам компании в одной ячейке

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

Многие в такой ситуации вводят всю информацию в одну ячейку руками, благо можно переходить на следующую строку в ячейке с помощью Alt+Enter.

Но лучший путь - это создать отдельно реестр контактных лиц, где выбирать компанию из выпадающего списка (чтобы не было возможности ошибиться) и вводить в каждой отдельной строке одного человека с его контактами и другими нужными вам полями.
Тогда будет возможность фильтровать такой список, форматировать телефоны, создавать ссылки на адреса эл. почты и иначе взаимодействовать с этими данными.
А собрать и подтянуть данные по всем сотрудникам одной компании всегда можно с помощью QUERY или FILTER. Если нужно отформатировать при этом данные - например, номера телефонов - пригодится функция ТЕКСТ / TEXT.

Файл с примерами

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

По шагам разбираем формулу, которая:
- ищет название товара в поисковике по картинкам
- берет первое изображение
- вставляет его в ячейку Google Таблиц.

https://www.youtube.com/watch?v=9NmZS8PfJuQ

А текстом с формулами мы писали про это здесь.

Диаграмма Ганта с помощью одной формулы

Друзья, посмотрите на диапазон A1:D12 на скриншоте. Из этих данных можно построить диаграмму Ганта, написав формулу в одной ячейке, сейчас расскажем как.

ПОЕХАЛИ СТРОИТЬ:
1) Мы написали для вас пользовательскую функцию RASPARCE — она превратит диапазон в плоскую таблицу, в которой одна строка будет одним днём отсутствия (смотрите пункт 1. на скриншоте).

2) Дальше используем полученный массив данных в QUERY. В функции с помощью WHERE отбираем тип отсутствия, выбранный в выпадающем списке, группируем по сотрудникам и создаём сводную таблицу кляузой PIVOT, результат — диаграмма Ганта (1. / 2. на скриншоте)

Итоговая формула, которая строит Ганта по дням:
=QUERY(rasparce($A$1:$D$12);"select Col1, count(Col2) where Col2='"&G4&"' group by Col1 pivot Col3")

Или так, чтобы построить по месяцам:
=QUERY( rasparce($A$2:$D$13);"select Col1, count(Col2) where Col2 = '"&G21&"' group by Col1 pivot month(Col3)+1, year(Col3)")

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

Храним данные в Properties и обращаемся к ним
Аналог функции ВПР

Друзья, Properties Service — это такие специальные отсеки для хранения данных. Вы можете положить туда данные, вы можете к ним обращаться и вы можете их удалить. Есть Properties пользователя, есть скрипта и есть Таблицы. Данные хранятся в парах ключ — значение. Подробнее про службу можно прочитать вот здесь: https://developers.google.com/apps-script/guides/properties?hl=ru.

Сегодня у нас отличный пример того, как можно работать с этой службой — гостевой скрипт от нашего читателя @vitalich. В первой функции Виталич берёт лист Таблицы, создает из него словарь и кладёт в Properties.

Потом, при выборе значения в выпадающем списке активируется вторая функция — триггер onEdit(), который обращается к этому словарю, ищет в нём выбранный ключ и выводит два соответствующих ключу значения в соседние ячейки редактируемой строки.

Сказать спасибо @vitalich-у или задать ему вопрос можно в нашем чате.

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

Чёрная магия IMPORTXML
Скрипт: вставляем формулу, заменяем ее на значения

Друзья, IMPORTXML — это мощнейшая функция Таблиц, она позволяет обращаться к выбранной веб-странице и доставать из неё то, что соответствует запросу Xpath, который вы ввели. Подробнее мы рассказывали здесь.

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

Мы подготовили для вас Таблицу, которая решает эту задачу.
Работает так:
1. Вводите в диапазон «A3:C» ссылки на страницы, Xpath, отмечаете чекбокс и нажатием на 🚀 запускаете скрипт
2. Скрипт вставит функции с этими аргументами друг под другом и сразу же заменит их результат на значения. К каждой строке добавит дату и время.

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

Таблица IMPORTXML

Сортировка и фильтрация по цвету в Google Таблицах

Ура! Заработала еще одна опция, которой не хватало в Таблицах. Еще одна вещь, которую теперь можно делать без установки дополнений.

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

1. Ставим фильтр любого типа ("Фильтр", т.е. обычный фильтр для всех пользователей или "Фильтры", т.е. filter views для индивидуального пользования)

2. Щелкаем на кнопку фильтрации на нужном столбце -> Сортировать по цвету / Фильтровать по цвету

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

В срезах пока осталась только фильтрация по условию.

Опция будет доступна и для пользователей GSuite, и для пользователей с обычными бесплатными аккаунтами Google Диска.

Появилась пока не у всех - в некоторых личных аккаунтах опции пока нет.