Google Таблицы

превращатор листа Таблицы в xlsx

привет! наш сегодняшний скрипт превращает активный лист таблицы в xlsx файл

что можно поменять в коде:
1 строка – в какую папку сохранять файл (или в корень google диска, если аргумент не заполнен)
10 строка – как созданный файл будет называться
14 строка – всплывающее окно с ссылкой на созданный файл, если надоест - можете закомментировать

самая главная часть скрипта – функция getBlob, если приглядитесь к ней, то сможете понять, что поменять, чтобы сохранять не только в XLSX, а еще в других форматах (примеры на gist)

весь код отдельно в pastebin
таблица со скриптом

===
📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

GOOGLE DATA STUDIO, пример отчёта и рассказ о его создании

Михаил Смирнов, большой фанат матричных формул, а по совместительству – отец ребёнка, который участвует в математическом кружке в городе Н. рассказал о своём опыте создания дашборда в GDS для этого кружка.

Статья, два примера дашборда (для десктопа и для телефонов/планшетов), а еще ссылки на полезные материалы: https://telegra.ph/Google-Data-Studio-na-primere-nebolshoj-zadachi-06-06

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Спарклайн с расчетом данных прямо в формуле

Дано: данные по продажам товарных позиций по месяцам. Много строк с товарами и столбцы-месяцы. Итоговых сумм у нас нет. Но при этом мы хотим посмотреть динамику визуально - по всем товарам по месяцам.

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

Сумма по каждому столбцу (месяцу) - СУММ(B2:B), СУММ(C2:C) и так далее.
После объединяем их в один массив:
{СУММ(B2:B) \ СУММ(C2:C) \ СУММ(D2:D) \ СУММ(E2:E) \ СУММ(F2:F) \ СУММ(G2:G)}

И остается этот виртуальный диапазон данных (массив) использовать как аргумент в функции, формирующей спарклайн. Вторым аргументом будет тип спарклайна (charttype) - столбчатый (column). Тип спарклайна тоже задаем в массиве, экономим место на рабочем листе 😉

=SPARKLINE({СУММ(B2:B) \ СУММ(C2:C) \ СУММ(D2:D) \ СУММ(E2:E) \ СУММ(F2:F) \ СУММ(G2:G)} ; {"charttype" \ "column"})

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

Решить задачу можно разными способами, например, так:
- с помощью СЧЁТЗ определить, сколько у нас заполнено столбцов
- с помощью SEQUENCE затем сформировать номера этих столбцов, от первого столбца с данными (в примере это второй столбец на листе)
- подставить это все в АДРЕС, чтобы получить адреса ячеек вида B1, C1 и т.д.
- из АДРЕСа достать номера заголовков с помощью регулярного выражения (достаем только латинские прописные буквы).
- все это собрать в запрос для функции QUERY вида sum(B), sum(C), sum(D) и т.д.
- с помощью ИНДЕКСа взять только вторую строку из выдачи QUERY (так как спарклайн умеет отображать только числа, то заголовки из выдачи QUERY будут ему мешать).
- все это засунуть в функцию SPARKLINE.

Ух! Если у вас будут идеи альтернативных решений этой задачки - пишите, мы с радостью ими поделимся 😉 В файле с примером есть пошаговый разбор.

===

📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📘 КАНАЛ: @google_sheets / оглавление
📗 ЧАТ: @google_spreadsheets_chat

Инструмент "Анализ данных" в Google Таблицах

Если "Статистика по столбцам", о которой мы писали недавно, только появилась в Таблицах, то "Анализ данных" существует давно. Это тоже инструмент для быстрого анализа данных с несколькими опциями:
- добавить визуальное чередование строк (оно также доступно в меню "Формат" - "Чередование строк")
- расчет суммы всех значений, среднего, минимума и максимума и количества значений в диапазоне.
- несколько графиков (обычный линейный график, гистограмма) с динамикой (распределение строить не умеет, в отличие от статистики по столбцам). Если в данных несколько столбцов, построить и комбинированную диаграмму (гистограмма по одному показателю, график по другому)

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

Графики также можно перетащить мышкой на рабочий лист, если один из них вы хотите сохранить на будущее в вашей таблице.

Инструмент живет по адресу:
- нижний правый угол, иконка с плюсом (при наведении курсора появляется надпись "Анализ данных"
- Alt + Shift + X

===

📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📘 КАНАЛ: @google_sheets / оглавление
📗 ЧАТ: @google_spreadsheets_chat

отправлятор таблиц в телеграм чаты
бесплатно и по расписанию

друзья, мы создали решение для вас, оно:

берёт диапазон Таблицы →
превращает в картинку →
отправляет в чаты (еще и по расписанию)

вам больше не надо платить за zapier, всё бесплатно и крутится на мощностях вашего аккаунта

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

как перенести и настроить?
1) копируем таблицу
2) заполняем лист "//": откуда берём диапазон, на какие chat_id отправляем, в какие дни недели и по каким часам
3) в редакторе скриптов вставляем токен своего бота
4) ставим скрипт на запуск каждые полчаса – пусть запускается и проверяет, есть ли строки, которые нужно обработать

как зарегистрировать бота в Telegram и узнать chat_id для отправки?
– бот регистрируется через пользователя @botfather, он же вам отправит токен, чтобы вставить его в скрипт
– chat_id группы / пользователя можно узнать с помощью @myidbot

таблица с решением: здесь
инструкция побольше (как создать триггер и бота, узнать chat_id): вот тут

p. s. а прекрасный сниппет конвертации в PNG мы взяли отсюда

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

===

📕 НАШ КУРС НА SKILLBOX (Таблицы и скрипты)
📘 КАНАЛ: @google_sheets / оглавление
📗 ЧАТ: @google_spreadsheets_chat

Статистика по столбцам - еще один новый инструмент Google Таблиц

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

Инструмент живет по адресу: Данные -> Статистика по столбцам
Горячие клавиши для инструмента: (Alt+D) + L

Справа откроется боковая панель с данными по выбранному столбцу. Чтобы посмотреть данные по любому другому столбцу, просто щелкайте на любую ячейку нужного столбца (либо нажимайте на стрелки на самой панели, чтобы двигаться влево-вправо по столбцам). Активный столбец при включенном инструменте выделяется пунктирной границей и светло-зеленой заливкой.

В панели 3 блока:
- График. Можно выбрать количество (будет гистограмма с количеством значений) или распределение (гистограмма с автоматически выбранными интервалами и количеством значений, входящих в эти интервалы)
- Периодичность - как часто в столбце встречаются те или иные значения. При наведении курсора на значение все ячейки с ним будут выделяться более ярким оттенком.
- Описание. Это статистика. Для текстовых столбцов будет только кол-во строк, уникальных значений и пустых ячеек. Для столбцов с числовыми данными - еще и сумма, среднее, медиана, минимум и максимум. Напомним, что сумму, среднее, мин и макс также можно увидеть внизу справа на строке состояния (но не по столбцу целиком, а по выделенному в данный момент диапазону).

Это не единственный инструмент для быстрого анализа. Еще есть панель анализа данных, которая в Таблицах живет давно. Она позволяет быстро вставлять функции, сводные и графики. Мы расскажем про нее отдельно.

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Парсим YOUTUBE скриптами

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

Внутри две темы:
– пишем скрипт, который парсит страницу ролика на ютубе и достаёт просмотры / лайки / дизлайки
– кладём скрипт в библиотеку (и как её вообще создать)

Будут вопросы – приходите в наш чат: @google_spreadsheets_chat и задавайте, там есть и Роман :)

Статья
Документация, про библиотеки

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

SmartFill в Google Таблицах - мгновенное заполнение данных с помощью автоматически формируемой формулы.

В Таблицах появилось несколько интересных вещей. Расскажем о каждой, сегодня начнем с аналога "Мгновенного заполнения" (Flash Fill) Excel (вызывается автоматически либо по сочетанию Ctrl+E либо из ленты Главная - Заполнить - Мгновенное заполнение).

Эта опция считывает паттерн заполнения данных и предлагает автоматически заполнить столбец до конца, исходя из этого паттерна. Например, если вы два раза ввели второе слово из соседнего столбца, то для всех остальных строк Excel предложит сразу вывести вторые слова из этого столбца.

Теперь в Google Таблицах есть похожая функция. Называется она SmartFill. Разница только в том, что она не заполняет ячейки значениями, а предлагает формулу, которая будет тянуть данные по вашему шаблону.
Для ее активации не нужно что-то нажимать. Вводите в пустом столбце данные из соседнего столбца в том виде, в котором вам нужно их извлечь (например, в столбце A у вас названия товаров, а в столбце B вы начинаете вводить только первое слово - название бренда). И Таблицы сами через несколько введенных значений распознают паттерн и предложат заполнить весь столбец формулой, которая будет по этому паттерну данные извлекать.

В примере с ФИО подсказка появилась в процессе ввода четвертого имени из столбца с ФИО (в Excel обычно со 2-го значения)
Далее можно либо сразу согласиться и заполнить ячейки (галочка), либо посмотреть формулу (там будут функции на английском), либо отказаться и продолжить заполнять вручную (крестик).

Формула может быть довольно монструозная. Но тем не менее будет решать задачу во многих случаях. Вот такой конструкцией предлагается доставать имена (второе слово из ячейки):
=ПСТР(A2;НАЙТИ(" ";A2) + 1;НАЙТИ(СИМВОЛ(160);ПОДСТАВИТЬ(A2;" ";СИМВОЛ(160);2)) - 1 - (НАЙТИ(" ";A2)))

Думаем, многие наши читатели знают, как сделать проще 🙂 Кстати, можно отправить отзыв (обратную связь) на формулу. Мы это проделали с данной формулой 🙂 Посмотрим, будут ли изменения.

С задачей по извлечению имени и фамилии (без отчества) опция тоже справилась (и формула даже короче - смотрите файл-пример).

Так или иначе, всегда можно быстро сохранить формулы как значения (выделяем диапазон -> Ctrl+C -> Ctrl+Shift+V).

Мы также попробовали опцию на других данных — индексах, числах и т.д. Пока кажется, что она не столь сообразительна, как собрат из Excel, и не хочет работать на малом количестве данных, и находит паттерны тоже только на 3-5 раз. Но будем надеяться, что она будет улучшаться со временем. Как Таблицы в целом 🙂

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

ДВИГАЕМ ВРЕМЯ ФОРМУЛАМИ

– как получить дату следующего понедельника
– как перейти на два месяца назад
– как прибавить три часа
– как отбросить время и оставить только дату
– как округлить время до часа

И подобные кейсы в прекрасном справочнике от участника нашего чата Михаила Смирнова, спасибо ему 👏.

На скриншоте – не всё, смотрите Таблицу.

==
Еще полезное про даты:
> Выводим ряд чисел или дат. Одной формулой.
> Сегодня хороший день - 43 873.

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

ОТПРАВЛЯТОР: отправляем ваши отчеты в теле письма по расписанию

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

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

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

Как всё работает:
Заполняете лист "настройки", одна строка в нём - одно правило отправки диапазона. Вам нужно ввести таблицу, откуда берём данные, лист и диапазон, кому отправляем письмо, как часто его отправляем и тему письма

Дальше вы можете либо руками запустить скрипт (из "/ меню скриптов /"), либо поставить скрипт test на ежедневное выполнение, скажем, от 12 до 13.

Как заставить скрипт запускаться каждый день:
инструменты > редактор скриптов > кликаем на часы > "добавление триггера" > выбираем функцию test > триггер по времени > по дням > выбираем нужный час

Что происходит после запуска скрипта?
Скрипт запустился и определяет, если ли что-то, что нужно отправить сегодня и не было ли сегодня это уже отправлено (проверяются столбцы "дни недели отправки" и "дата и время последней отправки").

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

Если всё получилось - в столбце "дата и время последней отправки" появляется дата и время когда письмо было отправлено.

==
Идейный вдохновитель и спонсор этого решения - Сергей М из нашего чата, спасибо ему 😎👏

3D сумма, но в Таблицах

Друзья, в MS Excel есть функция 3D сумма, она позволяет суммировать ячейку от одного листа до другого, не перечисляя листы, которые будут между. Например, смотрите на гифку - от листа "янв" до "апрель".

В Таблицах такой встроенной функции нет, но мы для вас её повторили с помощью пользовательской функции (скрипт, который вы сможете запускать из листа), код ниже:

function SumSum(shFrom, shTo, cell){
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
let sum = 0;
let x = ss.getSheetByName(shFrom).getIndex();
let y = ss.getSheetByName(shTo).getIndex();

for(x; x <= y; x++){
const sheet = sheets[x-1];
sheet ? sum += sheet.getRange(cell).getValue() : '';
}
return sum;
}

Давайте попробуем разобрать все по строкам:
название функции и аргументы, которые будем в неё передавать из листа (лист начала, листа конца, ячейка, все должно быть в кавычках)
function SumSum(shFrom, shTo, cell){

определяем переменные ss, sheets, x, y и кладём в них текущую активную Таблицу, массив листов, индекс первого листа, индекс последнего листа:
const ss = SpreadsheetApp.getActive();
const sheets = ss.getSheets();
let x = ss.getSheetByName(shFrom).getIndex();
let y = ss.getSheetByName(shTo).getIndex();

определяем sum – тут будет собираться наша сумма
let sum = 0;

начало нашего цикла, начинаем от индекса первого листа и продолжаем до индекса последнего листа включительно
for(x; x <= y; x++){

каждый шаг цикла
открываем Лист
const sheet = sheets[x-1];

проверяем, существует ли лист, если существует - достаём из него нужную нам ячейку и добавляем к sum
sheet ? sum += sheet.getRange(cell).getValue() : '';

завершаем цикл
}

возвращаем sum (в ячейку) и завершаем функцию
return sum;
}

==
Формула для запуска из листа должна выглядеть как-то так:
=SumSum("янв"; "апрель";"a1")

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Повторяем диапазон N раз

Друзья, сегодня отличная формула из нашего чата от Михаила Смирнова: повторяем диапазон столько раз, сколько нужно.

Формула такая:
=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1; C1); A2:A5)))

Как она работает:
1) формируем строку, где количество элементов – количество будущих повторов нашего диапазона. Например, { 1 \ 2 \ 3 }, формируется формулой:
=SEQUENCE(1; 3)

2) собираем ЕСЛИ в массиве, в условии – сформированная строка, в TRUE (если условия выполняются) - исходный диапазон:
=ARRAYFORMULA(IF(SEQUENCE(1; 3); A2:A5))

3) Что получилось? ЕСЛИ создает столько столбцов с диапазоном, сколько символов в строке, которую мы задали в первом шаге (смотрите правую часть гифки).

Чтобы объединить все столбцы - добавляем FLATTEN (результат в середине гифки).

P. S. А еще можно добавить FILTER, чтобы отфильтровать от пустых ячеек:
=ARRAYFORMULA(FLATTEN(IF(SEQUENCE(1; C1); FILTER(A2:A;A2:A<>""))))

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat

Полезные горячие клавиши в Таблицах

Вооружайтесь мизинцем (ибо все начинается с Ctrl) и берите в работу несколько полезных сочетаний горячих клавиш в Таблицах. А чтобы посмотреть их все, нажимайте Ctrl + /.

Ctrl + A — выделение всей таблицы, к которой относится активная ячейка

Ctrl + ` — отображение формул в ячейках

Ctrl + \ — очистка форматирования (только стилевого, не числового) выделенных ячеек

Ctrl + Backspace — возврат к активной ячейке (если вы пролистали документ и сейчас активная ячейка не на экране)

Ctrl + D — заполнение вниз. Допустим, у вас есть столбец с данными, вы ввели одну формулу рядом с ним и хотите ее "протянуть". Выделяйте диапазон и используйте эти горячие клавиши (другой вариант — двойной щелчок по маркеру в правом нижнем углу ячейки, но хорошо, когда есть альтернатива на клавиатуре)

И тройка приемов:
Необязательно использовать функции И / AND для логических операций. Можно заменять ее на умножение. Например, такая формула выдаст единицу, если выполняются оба условия — в ячейке A1 находится текст "Москва", а в A2 число меньше 100. Иначе формула будет возвращать ноль.
=(A1="Москва") * (A2<100)
(про И / ИЛИ в формулах массива писали еще здесь)

В функции ВПР / VLOOKUP можно использовать * для замены любого количества символов.
=ВПР("Москва*";диапазон с данными;2;0) — и мы ищем первую ячейку, которая начинается на слово Москва, забирая данные из второго столбца диапазона с данными.

Если вы хотите считать среднее / сумму за любой период, указанный в ячейке, введите начало диапазона как фиксированную ячейку, а конец диапазона задайте функцией ИНДЕКС со вторым параметром равным длине периода:
=СРЗНАЧ(начало диапазона с данными:ИНДЕКС(диапазон с данными;кол-во периодов))
=СРЗНАЧ(B2:ИНДЕКС(B2:B;$E$1))

===
📕 Оглавление канала
📘 Канал: @google_sheets
📗 Чат: @google_spreadsheets_chat