Копируем данные из сотен Таблиц с помощью Advanced Google Services

Максимальное время работы одного GAS скрипта — 6 минут. Это значит, что если вы с помощью Spreadsheet Service пытаетесь обойти сотни Таблиц и скопировать из них тысячи строк — времени может не хватить и ваш скрипт завершится по таймауту.

Есть альтернатива, есть более быстрый способ манипулировать файлами, обращаться к ним, копировать данные, удалять данные и наводить всяческий хаос, как мы любим — это Advanced Script Services. У вас будут те же 6 минут для работы скрипта, но за это время вы сможете успеть больше.

Мы подготовили для вас пример: скрипт 100 раз открывает оглавление нашего канала, объединяет данные в один массив и вставляет результат в Таблицу. Еще Advanced API грузит только заполненные ячейки, чтобы это побороть и не нарушить итоговую схему данных — скрипт дописывает пустые ячейки в те строки массива, где они нужны.

Официальная документация

Перед использованием службу нужно активировать

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

Всё, что касается Google Таблиц можно обсудить в нашем чате: @google_spreadsheets_chat

Присоединяйтесь!

Срезы в Google Таблицах

Итак, друзья, Google Таблицы продолжают обновляться - и некоторое время назад в них появились срезы.

Они похожи на срезы в Excel. Там их можно применять к сводным таблицам и "умным таблицам" (которые вызываются опцией "Форматировать как таблицу").

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

В Google Таблицах Срезы можно применять к обычным диапазонам, сводным таблицам и диаграммам.
Вызываются срезы через меню Данные -> Срезы.

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

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

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

Библиотека небольших скриптов onEdit()

Друзья, привет, в этом проекте мы собираем небольшие скрипты onEdit(), про которые когда-то писали на канале. А ниже — два новых скрипта, спасибо нашему чату за идеи.

1) Изменяем название листа в зависимости от введенного в ячейку A1:
function onEdit(e) {
var ss = e.source;
var sheet = ss.getActiveSheet();
var r = e.range;
var v = e.value;

if(r.getA1Notation() === 'A1' && sheet.getName() !== v){sheet.setName(v)}
}

2) Пользователь написал «лазер» в третьем столбце — отправим эту строку на другой лист
function onEdit(e) {
var ss = e.source;
var sheet1 = ss.getActiveSheet();
var sheet2 = ss.getSheetByName("сюда");
var lr = sheet2.getLastRow() + 1;
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
var value = e.value;

if(column == '3' && value.match(/лазер/gi)){
sheet2.getRange("A" + lr + ":" + "C" + lr).setValues(
sheet1.getRange("A" + row + ":" + "C" + row).getValues()
)}
}

Функция QUERY. Формируем запрос в WHERE ко всем колонкам формулой

Привет! Запрос в QUERY — текстовая строка и поэтому его всегда можно сформировать формулой.

Представьте — в ваших данных сто колонок и вы хотите написать запрос и вывести строки, в которых каждая ячейка будет больше 0.

Можно перечислить все сто колонок руками: WHERE Col1>0 and Col2>0 and ColN>0 and Col100>0, а можно написать формулу, которая создаст эту текстовую строку.

На скриншоте показываем, как это сделать.

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

OR / AND в функции FILTER

Друзья, в FILTER можно комбинировать логические условия OR и AND. Для этого нужно сделать следующее:

1) Взять каждое условие в круглые скобки
2) Соединить условия разделителем:
OR (ИЛИ): +
AND (И): * или ; или , (в зависимости от региональных настроек разделителем может выступать точка с запятой или запятая; звездочка будет работать при любых настройках)

Примеры — на скриншоте
Таблица с примером

Вывод названия месяца из даты в именительном падеже

— МЕСЯЦ/MONTH возвращает номер месяца (от 1 до 12) на который выпадает заданная дата
— ВЫБОР/CHOOSE достает из списка с названиями месяца значение по индексу
— Формула сможет работать в ARRAYFORMULA, сразу для многих ячеек (см. скриншот, там диапазон A1:A10)

Другие способы из нашего чата:
Второй способ, IFS:
https://t.me/google_spreadsheets_chat/62466
Третий способ, ВПР:
https://t.me/google_spreadsheets_chat/62520
Четвертый способ, INDEX, но без ARRAYFORMULA:
https://t.me/google_spreadsheets_chat/62470

Спасибо @oshliaer и @IT_sAdmin за помощь в подготовке поста 🤗

Наш чат: @google_spreadsheets_chat

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

Вводная от меня. Основа его Таблицы — функция IMPORTXML, она позволяет вытаскивать почти любую строку из веб-страницы, вам нужно только научиться писать xpath запрос.

Таким образом вы всегда сможете собирать нужные вам котировки и другую информацию без скриптов / интеграции с АПИ.

Кстати, скриптами это делать даже сложнее — чтобы обычный parsexml из скрипта проглотил сайт, html нужно сначала обработать, экранировать экранируемое, с тегами порядок навести, с кавычками, etc.

Так что разбирайтесь, как писать xpath и пользуйтесь этой мощной функцией.

Статья: https://vc.ru/finance/92990-upravlencheskiy-uchet-lichnyh-aktivov
Таблица с портфелем: https://clck.ru/K9CLT

Налетай, скрипты покупай

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

С помощью скриптов и нашей магии можно сделать что угодно. ВОТ СОВСЕМ.

Например, открываем Excel файл на вашем диске, превращаем в Таблицу, форматируем, конвертируем в текстовый файл, отправляем по почте, сохраняем на Google Диск и делаем post-запрос одновременно. Это реально и мы это делали.

Что еще делали
✔ выгружали письма из gmail ящика и использовали их, чтобы создать в календаре события (Маша З, 30 ноября, делаем ноготочки)
✔ все виды конвертаций файлов на вашем диске
✔ реестр файлов диска и смена прав доступа к файлам через интерфейс Таблицы (уберите сотрудника из всех файлов разом)
✔ интеграции со внешними API, забираем остатки, продажи и вставляем в Таблицу

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

Дорого. Эффективно.

За скриптами писать сюда: @namokonov

Решили в чате отличный кейс: добавляем ячейку с любым текстом после каждой ячейки нашего массива.

В примере (таблица с ним) после каждой ячейки добавляем новую со словом «СТРОКА».

Формула по шагам разобрана на скриншоте.

Итоговая формула:
=TRANSPOSE(SPLIT(JOIN("vasyaСТРОКАvasya";A5:A8);"vasya";0))

Наш стремительный чат: @google_spreadsheets_chat,

Спешите, в нём скоро закончится место, нас там почти 1500 🤗

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

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

Формат (Alt+O) -> Числа (N) -> Другие форматы - > Другие форматы чисел

А дальше можно вводить формат. В самом простом виде просто вводим нули для цифр и знаки, которые хотим отображать:
0(000)000-00-00

Но такой формат не будет работать для номеров вида 9101234567, 1234567.
Только для 79161234567 или 89161234567.

Поэтому можно пойти дальше и вспомнить, что есть возможность задавать условия в пользовательских форматах.
Так, можно предусмотреть отдельный формат для чисел меньше 9999999 - без кода:
[<9999999]000-00-00;0(000)000-00-00

Такой вариант корректно отобразит и номера из 11 цифр, и номера из 7.
Но все еще не справится с номером из 10 цифр - как 9101234567.

Так что можно сделать еще один шаг и прописать два условия: [<9999999]000-00-00; [<10000000000](000)000-00-00; 0(000)000-00-00
Теперь у нас предусмотрены три варианта.

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

Добавляем к QUERY итоги

Функция QUERY не умеет добавлять итоги по строкам / столбцам к данным, которые выводятся.

Зато, вы можете написать несколько QUERY: одну для вывода запроса, другие для вывода итогов и объединить все эти функции в один массив.

Подробно про объединение массивов мы писали здесь.

В результате вы получите одну формулу и эта формула будет выводить всё.

Примеры формул, QUERY с итогами по столбцам:
={ QUERY(B3:D11;"SELECT B, SUM(D) GROUP BY B PIVOT C");
TRANSPOSE( QUERY(B3:D11;"SELECT SUM(D) GROUP BY C")) }

QUERY с итогами по столбцам, строкам и общей суммой:
={
{ QUERY(B3:D11;"SELECT B, SUM(D) GROUP BY B PIVOT C");TRANSPOSE( QUERY(B3:D11;"SELECT SUM(D) GROUP BY C")) }\
{ QUERY(B3:D11;"SELECT SUM(D) GROUP BY B");QUERY(B3:D11;"SELECT SUM(D) label SUM(D) ''") }
}

Подробный разбор - на скриншоте.
Таблица с примером

Сегодня два кейса.

У нас есть ряд чисел от 0 до 1231, как посчитать одной формулой количество чётных элементов?

Разбор — на скриншоте и в Таблице.

Итоговая формула:
=COUNTIF( ARRAYFORMULA( ISEVEN(SEQUENCE(1232;1;0)));true)

Мы выбрали любой месяц, как посчитать в нем количество суббот?

Разбор — на скриншоте и в Таблице.

Итоговая формула:
=COUNTIF( ARRAYFORMULA( WEEKDAY(SEQUENCE(DAY( EOMONTH(I1241;0) );1;I1241);2))

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

Пользовательские форматы: округление до тысяч и миллионов

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

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

Если хочется отображать в тысячах с десятыми/сотыми/прочими долями тысячи, можно добавить знаки после запятой:
0.00,

Сработает округление и для форматов с разделителями разрядов:
#,##0,
(То есть мы будем показывать число в тысячах, но если округленный результат будет иметь больше 10 тыс. - то в нем появятся разделители разрядов)

Можно добавить надпись после числа - чтобы не запутаться и точно понимать, что здесь числа в тысячах:
#,##0, тыс

Помня о том, что мы можем задавать отдельные форматы (через точку с запятой) для положительных и отрицательных чисел, можно задать формат с округлением для положительных чисел, а отрицательные показывать в обычном формате:
#,##0,;#,##0

И, конечно, можно пойти дальше и отображать число в миллионах или миллиардах:
#,##0,, - миллионы с разделителями разрядов
0,,, - миллиарды без разделителей разрядов

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

Скрипт: создаем каталог из файлов выбранной папки в Google Таблице. Google Drive Api.

Как работает:
1) вам нужно сделать копию таблицы
2) ввести в ячейку «B1» ID папки
3) запустить из меню [ с к р и п т ы ] единственный скрипт

Скрипт рекурсивный, он будет искать в папке, во вложенных папках, а также покажет все случаи, когда файл, находящийся в папке одновременно лежит еще где-то, на Google Drive это возможно.