Google Таблицы

Перевод строки: как разделить по нему текст или очистить текст от него в Google Таблицах и Excel

Сегодня говорим про СИМВОЛ(10), он же Alt+Enter, он же перевод строки:
- Как разбить текст из одной ячейки, в которой много строк, на отдельные столбцы
- Как удалить переходы на новую строку из всех ячеек диапазона

https://teletype.in/@renat_shagabutdinov/arny9pYwYld

Мы сделали пользовательскую функцию, в функцию задаём ячейку с данными и строку с заголовками

Функция парсит объекты и заполняет ячейки, если ключ / заголовок не найден (выделенные столбцы) – возвращает прочёрк.

Итак, как же работает это чудо –

1) мы не можем сразу превратить в объект то, что в строке у Владимира, чтобы структура {}, {}, ... стала валидной – добавляем внешний массив, то есть добавим[ и ] к текстовой строке;
`[${r}]`

2) структура ОК, но это по прежнему простая текстовая строка – сделаем из неё объект;
JSON.parse(`[${r}]`)

4) дальше map – цикл по вложенным объектам и внутри каждого объекта – еще один map – по ячейкам заголовков, мы проверяем, есть ли в текущем объекте текущий ключ, если есть – достаём значение, если нет – возвращаем прочерк;

map всегда возвращает массив первоначальной структуры, в нашем случае это отлично – мы создаём нужный нам массив просто подменяя структуру массивов, которые обходим;

Функция целиком:
function JsonToTable(r, head) {
return JSON.parse(`[${r}]`)
.map(object => {
return head[0].map(h => object[h] ?? '-' )})
}

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

ПАРСИМ ОБЪЕКТ ИЗ ЯЧЕЙКИ ТАБЛИЦЫ

Коллеги, свежее из чата: в чат пришёл Владимир, у Владимира в ячейке Таблицы было такое (скриншот, верхняя часть) – ряд объектов с парами ключ / значение, объекты были разделены запятыми.

Задача Владимира – распарсить эту строку и превратить её в плоскую Таблицу (скриншот нижняя часть)

Исторические данные индекса Московской Биржи (IMOEX) в Google Sheets

Михаил Смирнов рассказывает, как собрать ссылку и вооружиться функцией IMPORTXML (или IMPORTHTML), чтобы получить данные индекса в Таблицу.

Достаём индексы, статья в telegraph

P. S. Не можем не напомнить про другой полезный материал от этого же автора: получаем курсы валют в Google Таблицу

ПОКАЗЫВАЙ-КА ФАЙЛЫ:
СКРИПТ-ИЗВЛЕКАТОР ИЗ ПАПОК GOOGLE ДИСКА

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

> извлекаем файлы из папки / папок, создаём реестр в Таблице

> попадается изображение – добавляем функцию =IMAGE, которая покажет картинку в Таблице

> задавать можно как одну папку, так и несколько

Чтобы воспользоваться – копируйте Таблицу себе и задайте в ячейке B1 ссылку на одну или несколько папок (если папок несколько – разделите их через запятую).

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

Код с комментариями в pastebin

==
Ребята, прямо сейчас ищем двадцатую тысячу подписчиков – подписывайтесь на канал @google_sheets, чат @google_spreadsheets_chat, рассказывайте про нас коллегам и друзьям :)

​​Задача - меняем формулы на значения в выбранном диапазоне
/ отображаем статус в toast

Друзья, простой пост со скриптом, вдохновлённый вопросом в нашем чате.
Разбираем скрипт по блокам:

function setValues() {

//определили активный диапазон и активную рабочую книгу
const [ss, active_range] =
[SpreadsheetApp.getActive(), SpreadsheetApp.getActiveRange()];

//проверяем – есть ли в активном диапазоне формулы (объединяем диапазон до строки и ищем "=") если формул нет - выводим в toast ошибку и завершаем скрипт
if (!/=/.test(active_range.getFormulas().flat().join(''))){
ss.toast(' формулы в активном диапазоне не обнаружены - ничего не вставляем!');
return;
}

//если формулы есть – берём диапазон как значения и вставляем на прежнее место выводим сообщение в toast
const values = active_range.getValues();
ss.toast('вставляем в активный диапазон значения!')
active_range.setValues(values);
}

//ну и функция onOpen – формирует меню в Таблице, из которого вы сможете вызвать наш скрипт
function onOpen(e) {
SpreadsheetApp.getUi()
.createMenu("- > в этом меню у нас скрипты")
.addItem("меняем формулы на значения!", "setValues")
.addToUi();
}

Просто копируйте код выше (комментарии можно не удалять).

P.S. На всякий случай – Таблица с примером

Напишем-ка бота!

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

teletype.in/@google_sheets/androids

p.s. любое изменение кода надо публиковать, редактирую публикацию (нажимая на карандаш и выбирая "новую версию"), так у вас обновится код и не поменяется ссылка на приложение

А еще с помощью флажков можно вводить (переключать) в ячейках не только логические значения ИСТИНА и ЛОЖЬ.

Если у вас есть в таблице поле, в котором может быть только два варианта (но это не ИСТИНА и ЛОЖЬ, а, допустим, "штатный" и "внештатный" в списке сотрудников), воспользуйтесь проверкой данных.

Данные -> Настроить проверку данных (Data -> Data validation) или клавиши Atl+D --> V
Выберите в качестве правила "Флажок" и отметьте галочку "Использовать персонализированные значения" (Tick Box, Use custom cell values)

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

Флажки в ячейках с формулами

Когда мы вставляем флажки в пустые ячейки (Вставка-Флажок / Insert-Tick Box или клавиши Alt+I --> X) -- мы можем переключать их "руками", меняя значение с ИСТИНА / TRUE на ЛОЖЬ / FALSE в ячейке с флажком.

Но есть и другой вариант применения -- если вы вставляете флажок в ячейку с логическим выражением (возвращающим ИСТИНА или ЛОЖЬ) -- его нельзя будет переключить, но зато значение, которое возвращает формула, будет отображаться у вас в виде флажка.

Книги про таблицы, визуализацию данных, Excel и Power Pivot

Итак, вы любите почитать на досуге и... любите Таблицы, Excel, диаграммы (или еще что-нибудь эдакое). Какой книгой порадовать себя? Давайте посмотрим. Список неполный и ни к чему не обязывающий. Делитесь любимыми книгами по теме и вокруг нее — почитаем и с радостью добавим в обзор!

ЗАПУСКАТОР – реагируем на изменения формул / Таблицы и запускаем скрипты

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

Стандартные триггеры на изменение / редактирование тут не помогут, они реагируют если изменение сделал человек (а еще onChange может сработать при обновлении функции IMPORTRANGE).

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

Поехали делать
1. Пишем в редакторе скриптов целевой Таблицы функцию doGet(), она будет отправлять письмо;

function doGet(e) {
MailApp.sendEmail('mail.com', 'тема: привет!', e.parameter.row)
}

Разворачиваем скрипт как веб-приложение
(запуск от имени – от моего имени;
доступно – всем)

2. Пишем пользовательскую функцию, она будет
– считать количество строк на листе заказы;
– сверять с количеством, которое сохранено в скрипт проперти;
– если в проперти цифра меньше – функция перезапишет проперти и отправит на адрес развернутого приложения строку с данными (в параметре row);
– в функцию вставьте ссылку на ваше развернутое приложение;

function zapuskator(array) {
array = array.filter(t => t.some(g => g));
const length = array.map(t => t[0]).length;
const property = PropertiesService.getScriptProperties();
const rows_property = property.getProperty('rows');

if (rows_property < length) {
property.setProperty('rows', length);
const url = 'https://script.google' + '?row=' + array[length - 1].join();
return [
[new Date(), url, UrlFetchApp.fetch(url).getResponseCode()]
]
}
return length
}

3. вставляйте пользовательскую функцию в Таблицу, её аргумент – диапазон из листа заказы;
4. данные меняются - функция пересчитывается;
5. !!!!!!!!

С помощью этого способа вы можете разобраться не только с Glide, а еще и в принципе следить за формулами / данными, запуская скрипты из веб-приложения тогда, когда это вам будет нужно.

За идею сегодняшнего пост спасибо Волонду и Tanaike https://tanaikech.github.io/

Код в пастебин

Скрипты для новичков.

Часть 2.
– обращаемся к массиву (а еще к выбранной строке, к столбцу, к последней строке)
– считаем количество строк в массиве
– фильтруем массив от пустых строк
– находим последнюю строку с данными в выбранном столбце

teletype.in/@google_sheets/start_gas2

УНИКИ стали еще умнее: выводим только уникальные столбцы с помощью UNIQUE

Друзья. а вы заметили, что у функции UNIQUE теперь стало 3 аргумента, а не один?
Теперь можно не только выводить уникальные строки, но и столбцы тоже. А еще дубликаты можно удалять совсем. Давайте по порядку. Синтаксис функции:

=UNIQUE(range; [by_column]; [exactly_once])

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

Но если второй аргумент by_column вы укажете как TRUE (по умолчанию FALSE = удаление дубликатов строк), то UNIQUE выведет только уникальные столбцы, а не строки.

А третий аргумент, равный TRUE, позволяет вообще убрать дублирующиеся строки или столбцы (смотря что во втором) . Иначе говоря: Если в ваших данных были строки, повторяющиеся хотя бы 2 раза, функция UNIQUE с такой настройкой вообще их не выведет в итоге, даже по 1 разу!

Файл с примерами (Создать копию) -- там продемонстрированы разные варианты.

Получаем курсы валют в Google Таблицу

Друзья, сегодня у нас мощнейшая статья от Михаила Смирнова.

Внутри статьи:
1) как получить курсы с помощью GOOGLEFINANCE;
2) как получить курсы Центрального банка РФ, cbr.ru;
3) из НБУ, bank.gov.ua;
4) из НБ РБ, nbrb.by;
5) из XE.com;

Для загрузки используем функции IMPORTXML и IMPORTHTML.

Статья про курсы

⚙️
Наш чат: @google_spreadsheets_chat

Оглавление канала: goo.gl/HdS2qn

UPDATE На текущий момент данные ЦБ РФ и Национального банка Республики Беларусь описанным ниже методом получить не удаётся. Предположительно, они закрылись от гугловских IP (с которых идут запросы, когда вы пишите формулы импорта), а, может, и не только от гугловских, но от всего Запада (из России всё доступно).

Скрипты для новичков.
Часть 1.

Привет! Открываем серию, в которой будем вам показывать простые приёмы работы с Таблицей скриптами, также будем немного касаться JS (языка, на основе которого написан Google Apps Script, язык, на котором мы пишем скрипты в Google Docs)

Часть 1:
– обращаемся к текущей Таблице
– к выбранному листу
– забираем диапазон заполненных ячеек
– разбираемся, что нам возвращается

teletype.in/@google_sheets/start_gas1

Несколько функций по одному полю в сводной таблице + перенос значений в строки

Друзья, сегодня хотим напомнить/рассказать про некоторые аспекты в сводных таблицах:
- К одному столбцу можно применять несколько агрегирующих функций;
- Поля в сводной можно переименовать, чтобы безобразие вида "SUM из Остаток, шт." превращать в "Сумма остатков" или что-то еще на человеческом языке;
- Значения (агрегирующие функции) можно переносить в строки.

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

===
Канал про Таблицы: @google_sheets
Чат: @google_spreadsheets_chat
Оглавление канала: goo.gl/HdS2qn

Что чаще вспоминают в чате, рейтинг за 2021 год

Друзья, у нас есть чат (в котором практически 3000 участников), недавно у нас появился рейтинг постов, которые вспоминают в чате чаще всего.

Рейтинг, по убыванию:
1. Прекрасная Таблица с подсказками про QUERY от Павла Мрыкина
t.me/google_sheets/616

2. ВПР, достаём последнее значение по ключу
t.me/google_sheets/558

А здесь – t.me/google_sheets/640 всё, что мы писали про ВПР (в подвале поста).

3. Условное форматирование, выделяем дубликаты и не только
t.me/google_sheets/296

4. СОБИРАТОР 4.0 – скрипт для сбора других Таблиц с большим количеством настроек
t.me/google_sheets/661

5. Простой телеграм бот (получает сообщения в Таблицу, сохраняет файлы на Google Диск и отправляет сообщения из Таблицы)
t.me/google_sheets/556

6. ДВССЫЛ (INDIRECT) в массиве – пользовательская функция
t.me/google_sheets/460

7. Памятка по работе с условиями в формулах FILTER, QUERY, SUMIF
t.me/google_sheets/283

8. Памятка с советами, позволяющими ускорить работу документа.
t.me/google_sheets/4

9. Памятка про ? и *
t.me/google_sheets/254

P. S. За помощь в подготовке поста спасибо Виталию П.