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

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

Данные -> Настроить проверку данных (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 (по работе с почтой есть интересные и не совсем очевидные лайфхаки).

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

Друзья, с помощью скриптов из одной Таблицы (назовём её Альфа) можно создать в другой Таблице (назовём её Бета) installable триггер (onEdit, onChange, onOpen, onFormSubmit) и при этом в Таблице Бета никто не увидит ни скрипта, ни триггера.

Как это работает – вы редактор Таблицы Бета, но не владелец, вы создаёте (запуская код ниже) из своей Таблицы Альфа триггер в Таблице Бета. Владелец Таблицы Бета не видит ни триггера ни даже скрипта, на который он создан, но скрипт отлично работает в Таблице Бета покуда у вас есть к ней доступ, работает от вашего имени.

function createTrigger() {
ScriptApp
.newTrigger('название скрипта')
.forSpreadsheet('ID Таблицы') //или forForm, forCalendar, forDocument
.onEdit() //или onChange(), onOpen(), onFormSubmit()
.create()
}

function script(e) {
Browser.msgBox(JSON.stringify(e))
}

Как это можно использовать – например, вы можете отдать Таблицу со скриптом своим сотрудникам, не переживая, что они этот скрипт изменят, сломают или унесут себе (они его просто не увидят).

Пишите свои варианты использования в комментарии 🙂

Installable triggers
Class ScriptApp

Скрипт: массово удаляем временные фильтры

Вам знакома ситуация, когда коллеги пользуются Таблицей и чтобы никому не мешать создают временные фильтры и НЕ УДАЛЯЮТ ИХ?

Показываем, как с ними разобраться, очередной прекрасный материал от Михаила Смирнова: https://telegra.ph/Massovo-udalyaem-vremennye-filtry-cherez-Google-Sheets-API-06-06-2

P.S. А здесь мы писали про режимы фильтрации вообще: t.me/google_sheets/341

Еще несколько советов по работе с приложениями Google Диска

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

Короткие встречи в Календаре. В настройках календаря в разделе "Мероприятия" можно задать продолжительность мероприятия по умолчанию. А еще здесь есть волшебный (уж по обещаемому эффекту — точно) флажок "Быстрые встречи". Поможет ли он меньше времени проводить на совещаниях? Не уверен. Но его активация сделает короткие 30-минутные встречи 25-минутными по умолчанию, а часовые — 50-минутными. Увы, опыт показывает, что даже 50-минутные встречи воспринимаются участниками как часовые, так что нужна серьезная дисциплина, чтобы заканчивать время и был реальный перерыв в 10 минут. Но вдруг у вас получится?

Рабочие часы. В настройках календаря есть раздел “Рабочее время” — можно указать свое рабочее расписание (дни и часы). Гарантирует ли это, что вам не поставят встречу на это время? Кхе-кхе. Но во всяком случае коллега получит предупреждение о том, что вы не работаете (хотите не работать) в заданное время.

Увеличить время отмены отправки письма в Gmail. По умолчанию у вас есть всего 5 секунд на отмену отправки письма. Совсем мало! Но это время можно увеличить до 30 секунд. Заходите в настройки — все настройки — общие — отмена отправки и выбирайте один из вариантов (5, 10, 20 или 30 секунд).

Пометить письма, отправленные только вам. Чтобы пометить письма, которые отправлены только вам персонально, а не нескольким адресатам, включите соответствующую опцию в "Общих" настройках Gmail (называется "Значки персональных писем"). У писем, отправленных только вам, будет две стрелки ».

Добавили эти лайфхаки в сборник советов по работе с Google Диском — там все со скриншотами:
https://teletype.in/@renat_shagabutdinov/J0TKzjV7M