Геотаргетинг, СДЭК, находим объекты на карте в Таблице Google

Сегодня публикуем статью Романа Игнатова, по совместительсту — модератора 🎖 нашего чата.

Рома написал скрипт, по выбранному адресу отображающий в Таблице виджет Яндекс Карт с наложенными точками пунктов выдачи заказов СДЭК. При выборе пункта его название сохраняется в Таблицу.

Этот скрипт — отличный пример работы с внешними API. Еще эта реализация показывает, что в html-окно, которое выводят Таблицы можно дейстительно поместить что угодно. Виджет карт — не предел.

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

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

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

Продолжим рассматривать примеры спарклайнов и сегодня посмотрим на несколько параметров:
— ymin и ymax - позволяют провести виртуальную ось, отсечь часть значений (например, все отрицательные) на спарклайне-графике типа line;
— negcolor позволяет задавать цвет всех отрицательных значений в столбчатом спарклайне типа column
— highcolor и lowcolor - выбрать цвета для точек с макс и мин значениями;
— firstcolor и lastcolor - задать цвет для крайних точек

Напомним, что цвета можно задавать не только с помощью их обозначений на английском (red = красный), но и с помощью hex-кодов, что обеспечивает доступ к большему разнообразию.
Вот вам код для цвета "Королевский пурпурный Крайола": 7851a9

А вообще любой код можно узнать, вбив название цвета в Яндексе.

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

Другие посты про спарклайны:
Спарклайн с условием
Видеоурок: Синтаксис и примеры
Что такое спарклайны?

Тест на знание Google Таблиц

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

Вот тест: t.me/QuizBot?start=Qbs6aoqy

Наш чат про Таблицы и скрипты, если у вас будут вопросы или вы просто захотите поделится результатом 🎰

Динамический спарклайн: выбираем период, точку отсчета и цвет спарклайна и максимальной точки (цвет - на русском языке)

Друзья, привет. Астрологи провозгласили месяц спарклайнов – продолжим развлекаться с маленькими графиками.

Сегодня рассмотрим такой пример: данные по выручке за несколько лет по месяцам.

Создадим формулу, которая будет строить спарклайн по выбранному количеству месяцев и начиная с выбранного месяца.
Для этого нужна будет функция СМЕЩ, формирующая ссылку на динамический диапазон. Зададим точку отсчета - первый месяц с данными; высотой диапазона будет выбранная пользователем в ячейке длительность периода, точкой отсчета (отступом по строкам) – позиция выбранного месяца.

=СМЕЩ(первая ячейка с данными; ПОИСКПОЗ(выбранный в выпадающем списке месяц - точка отсчета; диапазон с месяцами в наших данных;0)-1;0; выбранная длительность периода; 1)

Вычитаем единицу из позиции выбранного месяца, т.к. для января , который первый в диапазоне, нам отступать никуда не нужно.
Последний аргумент- это ширина, она равна единице.
0 в функции СМЕЩ - это отступ по столбцам, он здесь не нужен. Берем данные из одного и того же столбца с показателем.

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

Помня о том, что в случае со спарклайнами нельзя добавить ось и подписи данных, выведем отдельно минимальное и максимальное значение за выбранный период - просто будем использовать СМЕЩ как аргумент функций МИН и МАКС. Так мы будем понимать масштаб, понимать, какому значению соответствует минимальный и максимальный столбцы.

Ну и добавим еще немного интерактивности - сделаем возможность вводить цвет спарклайна в целом и максимальной точки руками и на русском языке.
Для этого будем в аргументе функции SPARKLINE переводить текст с названием цвета на английский с помощью GOOGLETRANSLATE.

Напомним параметры такого спарклайна:
charrtype (тип) – column (столбчатый)
color – цвет спарклайна
highcolor – цвет максимального значения

Ссылка на файл с примером

Другие посты про спарклайны:
– Меняем цвет у отрицательных, максимальных и минимальных, первых и последних значений.
– Спарклайн с условием
– Видеоурок: Синтаксис и примеры
– Что такое спарклайны?

Выведем все пары
Декартово произведение строк

Привет! Декартово произведение – все возможные пары элементов двух массивов. Если всё равно непонятно – просто посмотрите на скриншот )

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

function RENE(range1, range2){
return []
.concat(...range1
.map(y => []
.concat(...[range2
.map(h => [y[0], h[0]]
)]))).filter(t => t[0]);
}

Второй вариант – решение вопроса с помощью стандартных формул Таблиц. Минус в том, что с большим количеством строк такое решение работать не будет из-за ограничения функции JOIN на количество символов.

=ARRAYFORMULA(TRANSPOSE(SPLIT(
{REPT(JOIN("^";A3:A5)&"^";4);REPT( JOIN("^";B3:B6)&"^";3)};
"^")))

Таблица с примером
Наш чат, там препарируем и не такие формулы 🔪

​​Динамическая диаграмма в Google Таблицах

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

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

И будем строить график на основании трех параметров, выбранных пользователем:
– за какой период
– начиная с какого месяца
– по какому показателю

Реализуется все не так элегантно, как в случае со спарклайном.
Для диаграммы данные придется вывести в ячейки рабочего листа. Сделаем это с помощью функции СМЕЩ, как и в предыдущем примере.
(Напомним, в Excel для создания таких диаграмм СМЕЩ можно вводить прямо в функцию РЯД в диаграмме)

На самом деле, мы воспользуемся двумя функциями СМЕЩ, соединенными в массив: одна будет выводить даты из первого столбца по выбранному диапазону, а вторая — данные из второго или третьего столбца в зависимости от выбранного пользователем показателя.
(Будем в зависимости от выбора менять значение смещения по столбцам в функции СМЕЩ)

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

И еще нашей диаграмме не хватает заголовка. Если в Excel прямо из заголовка диаграммы можно было сослаться на ячейку, как в обычной формуле, например =A1 и в A1 ввести формулу, которая будет формировать текст заголовка, то в Таблицах возможен только фиксированный текст. Так что остается сформировать динамический заголовок с помощью формулы в ячейке над диаграммой.

Будем брать название показателя и добавлять к нему фразу "за период с янв 17 по дек 19". Периоды будем оформлять с помощью функции ТЕКСТ / TEXT, которая меняет форматирование числовых данных по заданному шаблону.

Файл с примером: копируйте себе на диск, изучайте, разбирайте формулы.

Простой onEdit() скрипт переноса строки

Недавно в нашем чате спросили – как пользователь Таблицы может переносить строки из одного листа на другой?

Отвечаем. Вот здесь Таблица со скриптом.

Скрипт работает так – активируете на листе "отсюда" флажок в третьем столбце – эта строка удаляется из листа "отсюда" и значения вставляются на лист "сюда".

Код с комментариями:
https://pastebin.com/yhbU0FDf 🤗

Избранные посты нашего канала:
https://tttttt.me/google_sheets/418

Оглавление канала в Google Таблице

СОБИРАТОР 3.0 (копируем и вставляем данные через sheets api)

Как-то к нам пришел Дима @IT_sAdmin и сказал – ребята, помогите! Мне очень нужно регулярно брать данные из больших Таблиц, фильтровать, а потом копировать результат в другие Таблицы, других Таблиц будет много (сотни их). Нужно, чтобы был интерфейс и всем этим волшебством можно было управлять из него.

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

Что добавили нового:

1) Фильтрация данных источника (столбец F листа настройки)
Примеры:
row[0] == 'Москва' || row[0] =='Спб' – Москва или Питер в первом столбце
row[2] > 10 && row[4] != 'Вася' – третий столбец больше 10 и в пятом столбце не имя Вася
row[4] – в пятом столбце есть данные

2) Дополнять / удалять / ❌ (пропускать) – вы выбираете, что делать с листом, на который вставляете данные, вы можете либо дополнять его новыми строками, либо очищать перед вставкой данных. А выберете ❌ – скрипт пропустит эту строку.

3) Маркер загрузки строк (✅ и количество строк) – мы добавили новое поле (столбец K), если загрузка выполнена успешно – появляется ✅ + количество загруженных строк. При повторном запуске скрипт продолжает с первой необработанной строки. Если хотите загрузить все Таблицы – очистите столбец K.

4) Стало быстрее – теперь к Таблицам обращаемся напрямую через SHEETS API, а не через обёртку.

5) Три попытки – теперь если сервер недоступен, то скрипт попытается еще.

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

Будут вопросы по работе скрипта – пишите, поможем
📣💬 @google_spreadsheets_chat

Волшебство "найти и заменить"
Приводим mm-dd к dd-mm не формулой.

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

— Вместо пустых ячеек вставляем наш текст
Найти: ^$
Заменить: Наш текст
К ячейкам должен быть применён текстовый формат
данных. Пустая или пробельная ячейка: ^\s*$

— Заменяем перенос строки на наш текст
Найти: \n (или \r, \r\n)
Заменить: Наш текст

Встречались с тем, что в диапазоне американский формат дат, а его нужно быстро привести к российскому?

Группы захвата:
— Сделаем из mm-dd-yyyy на dd-mm-yyyy
Найти: (\d{1,2})-(\d{1,2})-(\d{4}|\d{2})
Заменить: $2-$1-$3

— Сделаем из dd-mm-yyyy на День: dd, месяц: mm
Найти: (\d{1,2})-(\d{1,2})-(\d{4}|\d{2})
Заменить: День: $1, месяц: $2

Спасибо нашу чату, @vitalich и @mildly_disastrous за идею поста.

📣 Наш чат | Оглавление нашего канала

Несколько ссылок в одной ячейке

Небольшая новость: теперь добавлять гиперссылки можно к отдельным фрагментам текста, а не ко всей ячейке целиком.
Если раньше при создании гиперссылки (быстро сделать это можно с помощью Ctrl/Cmd + K) в ячейке автоматически формировалась функция ГИПЕРССЫЛКА / HYPERLINK, то теперь ссылки работают без функции и их может быть несколько в одной ячейке.

При наведении на ячейку с несколькими ссылками будет появляться всплывающий список всех имеющихся в ней ссылок - как на скриншоте.

При этом объединить несколько функций ГИПЕРССЫЛКА (например, если формируете ссылку формулой, как в нашем примере с ссылкой на отправку письма контрагенту из таблицы) в одной ячейке не получится. Только вставленные руками ссылки.

📲 наш чат

Telegram бот, который записывает всё, что видит в Google Таблицу

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

В статье на медиуме – полная инструкция, справятся даже новички.

📣 Наш чат: @google_spreadsheets_chat

UPDATE к Telegram боту

— теперь бот умеет сохранять файлы, которые отправлены в чат на ваш Google Диск (quick view - тоже)
— ссылка на сохраненный файл попадёт в Таблицу
— подпись к файлам сохранится тоже

Статья (разворачиваем бота с 0 за 15 минут)
Таблица с кодом бота (кто развернул предыдущую версию – просто обновите страницы скрипта doPost и download и опубликуйте приложение под новой версией, регистрировать вебхук заново не нужно)

---
📕📗📘 Оглавление канала (в Google Таблице)

Переключение дэшборда между днями и неделями — с помощью функции SEQUENCE

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

И при этом хотите легко переключать режим “недели / дни" (или изменение любого другого параметра), не залезая в формулы.

В статье разбираем, как построить такой отчёт

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

---
📕📗📘 Оглавление канала

Очень простой летний скрипт

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

Работает он так:
1. заполняете лист "настройки": вносите название листа шаблона, а также названия листов, которые нужно создать
2. запускаете скрипт :)

Код скрипта:
function myFunction() {
const ss = SpreadsheetApp.getActive()
sheet1 = ss.getSheetByName('настройки'),
data = sheet1.getDataRange().getValues(),
sheet0 = ss.getSheetByName(data[0][0]);

for(let x = 1; x < data.length; x++){
const sheet_name = data[x][0];

if(!ss.getSheetByName(data[sheet_name])){
sheet0.copyTo(ss).setName(sheet_name);

}
}
}

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

---
📕📗📘 Оглавление канала

Табличные диалоговые окна. Подсказки ввода dadata.ru

Друзья, Роман @romanigro (он же строгий модератор нашего чата) в своей статье рассказывает, как создать диалоговое окно для удобного ввода и подключить к нему подсказки.

Статья с примерами кода

---
📕📗📘 Оглавление канала

Превратим 1 час, 2 мин, 22 сек в 1:02:22

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

На входе у нас есть текстовые строки:

2 мин, 54 сек
11 мин, 37 сек
4 мин, 18 сек

Превратим эти строки в правильное время Google Таблиц:

1) С помощью трёх REGEXEXTRACT извлекаем из строк часы, минуты и секунды отдельно;
2) Добавляем IFNA, функция вернет 0, если какого-то показателя не будет;
3) Добавляем то, что получилось в функцию TIME(часы; минуты; секунды);

Формула целиком:
=TIME(
IFNA(REGEXEXTRACT(B4;"(\d+) ч.*");0);
IFNA(REGEXEXTRACT(B4;"(\d+) мин");0);
IFNA(REGEXEXTRACT(B4;"(\d+) сек");0))

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

---
📕📗📘 Оглавление канала

Превратим 10.62 руб. в правильное число

У нас есть текстовые строки:

10.62 руб.
8.85 руб.
0руб.

Превратим их в числа:

1) с помощью SUBSTITUTE(B3 ; "руб." ; "") заменяем все "руб" на ничего
2) с помощью SUBSTITUTE(... ; "." ; ",") заменяем точку на запятую
3) используя TRIM убираем пробелы, если они есть
4) и с помощью VALUE превращаем то, что получилось в число

Формула целиком:
=VALUE( TRIM( SUBSTITUTE( SUBSTITUTE(B3 ; "руб." ; "") ; "." ; ",")))

Другой вариант (регулярка):
=VALUE(
REGEXREPLACE(C3;"(\d+)\.?(\d+)?.*";"$1,$2"))

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

---
📕📗📘 Оглавление канала