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

И отдельное спасибо тем, кто присылал нам свои решения, мы начислили вам +10 кармы в чате за каждый опубликованный пост:

@mike59 (+30 кармы), целых три поста:
— Изменяем наш Google Calendar с помощью скриптов
— Скрипт для работы с Google Календарем. Напоминалка про дни рождения
— Таблица для учета своих активов

@mildly_disastrous (+20 кармы):
— Прочитаем, что написано на стикерах. OCR в Google Docs
— Важный скрипт. Связанные выпадающие списки из кэша
— Перемещение мебели

@IT_sAdmin (+10 кармы):
— Вывод названия месяца из даты в именительном падеже

@IT_sAdmin, @mrykin, @avazbek88, Макс Махров (всем по +10)
— База знаний регулярных выражений для REGEXEXTRACT

@akunilovskiy (+10)
— Планировщик для занятий в тренажерном зале

Друзья, будут новые идеи — пишите @namokonov, @renat_shagabutdinov

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

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

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

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