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

Друзья, в этом посте мы разберём как запустить скрипт после того, как 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/

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

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

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

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

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

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

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

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

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

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

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

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

teletype.in/@google_sheets/androids

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

​​Задача - меняем формулы на значения в выбранном диапазоне
/ отображаем статус в 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. На всякий случай – Таблица с примером

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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] ?? '-' )})
}

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

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

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

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

меняем код в опубликованном приложении без публикации

Привет, друзья!

Представьте – у вас есть телеграм бот написанный на gas и каждое изменение кода вам приходится публиковать под новой версией, знакомая ситуация?

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

1) Создаём отдельный (standalone) скрипт script.google.com, внутри будут функции для нашего бота. Например, main(e), функция будет обрабатывать полученное на адрес веб-приложения сообщение из телеграма.

2) Публикуем скрипт как библиотеку (начать развертывание – новое развертывание – библиотека)

3) Идём в Таблицу нашего бота, подключаем библиотеку в режиме разработчика, далее пишем функцию:

function doPost(e){
lib.main(e)
}

4) Стандартно публикуем как веб-приложение (для бота) и на полученный адрес создаём вебхук с токеном нашего бота.

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

=
Спасибо Льву из нашего чата про Таблицы за отличную идею, кстати, мы сделали в чате капчу - зайдите и посмотрите на нёе.

P.S. 🔥🤓 наши боты с инструкциями и гифками по установке:
– получаем / отправляем сообщения из таблицы: t.me/google_sheets/556
– забираем данные из таблицы по запросу ботом: t.me/google_sheets/727

Импорт данных из Google Таблицы в Excel с обновлением через Power Query

Дано: хотим, чтобы данные с листа Google Таблицы попадали в Excel почти что "в режиме реального времени" — чтобы была возможность обновлять данные в Excel, ничего не скачивая и не вставляя руками.

У Таблицы должен быть открыт доступ по ссылке. К этой самой ссылке добавляем справа /export и идем в Excel в Power Query.
Эта надстройка может быть установлена бесплатно в Excel 2010-2013 (нужно скачивать с сайта Микрософт) и является частью Excel 2016 и 2019. В последних версиях ее можно найти на ленте инструментов (Данные → Получить и преобразовать данные / Скачать & преобразовать).

Самый простой образовательный onEdit() скрипт, который можно написать

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

Чтобы разобраться в механике напишем скрипт:

function onEdit(e) {
Browser.msgBox(JSON.stringify(e));
}

На ГИФ видно, что происходит – при редактировании любой ячейки Таблицы скрипт (без запуска) выводит окно Browser.msgBox(...) c объектом редактирования, который превращен в строку JSON.stringify(e).

Через объект (событие) редактирования вы можете обратиться к объектам
range https://developers.google.com/apps-script/reference/spreadsheet/range,
spreadsheet https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet,
user https://developers.google.com/apps-script/reference/base/user,

а также сразу из объекта можно получить координаты диапазона редактирования
e.range.columnEnd (columnStart, rowEnd, rowStart)

или введённое в ячейку значение e.value или предыдущее значение e.oldValue

Что для нас это значит практически?

Внутри IF мы можем проверять, что редактируется сейчас и, например, если редактируется первый столбец, Лист100, и если значение вставлено и существует (а не удалено) – вставлять в эту строку дату, время и какой-то текст:

function onEdit(e) {
const sh = e.source.getActiveSheet();
const range = e.range;

if (sh.getSheetName() == 'Лист100'
&& range.getColumn() == 1
&& e.value) {

sh.getRange(range.getRow(), 2, 1, 2)
.setValues([[new Date(), 'https://tttttt.me/google_sheets']])
}
}

==
Другие примеры скриптов onEdit(), благо теперь вам понятнее, как эти скрипты работают:

три примера: t.me/google_sheets/432
перенос строки: t.me/google_sheets/533
простой скрипт вставки даты: t.me/google_sheets/171

Программно даём доступ для IMPORTRANGE к другой таблице

Новая статья от Михаила Смирнова, которому мы (верим, что и вы тоже) отправляем много-много спасиб в карму!

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

Чтобы использовать IMPORTRANGE() необходимо предоставить доступ к таблице, откуда будет осуществлён импорт.

Варианта три:
- дать доступ на чтение (как минимум) для всех (Anyone) по ссылке
- вручную нажать синюю кнопку предоставления доступа
- дёрнуть специальную ссылку скриптом

Третью - незадокументированную - возможность и будем сегодня рассматривать.

​​Еще три книги про Excel и G Suite

Пополняем наш обзор околотабличных книг! Они уже добавлены в общий обзор, который вы можете найти по ссылке. Там подробнее и с ссылками на издательства/магазины.

Эффективная работа в Microsoft Excel | Алан Мюррей

Книга небольшая, но это прямо-таки концентрат пользы, но не для новичков. Если вы уверенно чувствуете себя в базовых темах Excel — функциях, сводных, форматировании и диаграммах — и хотите научиться продвинутым приемам работы, нетривиальным трюкам с диаграммами, новым функциям (которые появились пока только в Office 365 — ПРОСМОТРX, УНИК, СОРТ, СОРТПО, ФИЛЬТР), вам сюда.

Изучаем Power Query. Наглядный подход к подключению и преобразованию данных из множества источников | Линда Фоукс, Уоррен Спарроу

Книга, которая подойдет и начинающим, и продолжающим: обзор надстроек Power Pivot и Power Query, основы, интерфейс, подключение к разным источникам данных, преобразование данных, язык M (использующийся в Power Query) — раскрыты все ключевые темы. И введение в язык DAX (язык функций в Power Pivot).

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

G Suite For Dummies | Paul McFedries

Книга про все составляющие G Suite для новичков. Максимально подробно и по шагам, как полагается в серии "для чайников". Если говорить про Таблицы, то тут совсем базовая информация: создание и форматирование файлов (без тонкостей вроде пользовательских форматов), основы работы с формулами. Но зато по документам и презентациям вполне себе полноценное руководство, там как раз закрыта большая часть тем. А также по календарю, формам, Meet, настройкам доступов в целом, Gmail (по работе с почтой есть интересные и не совсем очевидные лайфхаки).