Google Таблицы

API OZON ИЗ GOOGLE ТАБЛИЦЫ

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

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

Читайте про API и приходите в наш чат обсуждать 🤓

Статья в MEDUIM

Находим и выводим

Друзья, сегодня мы расскажем, как вывести блок с определенным словом с помощью формулы.

Используем QUERY
В запросе QUERY нельзя просто взять и отфильтровать по яблокам в первом столбце – в этом случае выведется только одна строка. Но, в QUERY есть магические кляузы OFFSET и LIMIT, они определяют, сколько строк отступить сверху при выводе массива и сколько строк вывести всего.

Поэтому:
1. с помощью MATCH / ПОИСКПОЗ находим позицию Яблок в столбце A
=MATCH("яблоки" ; A:A ; 0) //5

2. создаём текстовую строку запроса с результатом этой функции
="limit 3 offset " & MATCH("яблоки" ; A:A ; 0) //limit 3 offset 5

3. итоговая формула, объединяем запрос и QUERY
=QUERY(A1:C11; "limit 3 offset " & MATCH("яблоки" ; A:A ; 0) - 1 ; 0) // -1 чтобы строка с яблоками также попала в выборку

4. заголовков в наших данных нет, поэтому последний аргумент = 0

P.S. Если вам нужны не все столбцы, а только некоторые — вместо * укажите их номера. Например, SELECT A, C для вывода первого и третьего столбцов.

ДОПУСКАТОР 2

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

Недавно к нам пришёл Андрей и объяснил, что ни с кем не хочет делиться своими файлами и попросил добавить в допускатор то, что в первом предложении.

Мы добавили, а теперь делимся с вами. Таблица со скриптом.

Как всегда всё просто:
1. делаете копию Таблицы
2. переходите на лист "убираем всех"
3. вставляете ссылки на файлы в A:A
4. выключаете чекбоксы в B:B
5. выбираете, что нужно сделать по каждой ссылке: убрать пользователей кроме себя И / ИЛИ закрыть доступ по ссылке
6. запускаете скрипт и происходит магия

Про код – мы получаем все ячейки с данными рабочего листа, дальше проходим по каждой строке с ссылкой и выключенным чекбоксом. Если выбрано "удалять всех" – получаем массив всех редакторов / читателей файла и каждого удаляем. Если выбрано "закрыть доступ" – меняем форму доступа на DriveApp.Access.PRIVATE, DriveApp.Permission.NONE и файл становится недоступен по ссылке для всех.

Код отдельно: https://pastebin.com/JzardKXe

Документация:
– про работу с листом
– про работу с файлами
– про доступ по ссылке
– про цикл forEach (но конечно можно использовать любой доступный вам)

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

Массивный ВПР

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

Задача: одной формулой получить стоимость всего проекта.

Решение: будем перемножать весь диапазон с количеством часов специалистов на функцию ВПР, которая вернет массив с их ставками:

=СУММПРОИЗВ(B2:F2;ВПР($B$1:$F$1;'Специалисты и ставки'!$A:$B;2;0))

СУММПРОИЗВ / SUMPRODUCT перемножает элементы массивов и возвращает сумму этих произведений.
Первый аргумент - это часы специалистов, а второй - функция ВПР, которая по заголовкам столбцов подтянет ставки из другого листа.

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

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

===
Еще наше про ВПР:
– ​​ЛЕВЫЙ ВПР (когда искомое значение не в первом столбце)
– ВПР (VLOOKUP) по нескольким условиям
– ВПР в массиве вместо тысячи CУММЕСЛИМН. Статья в Medium.
– ВПР по нескольким диапазонам
– ВПР с интервальным просмотром = 1
– Перекрестный ВПР (ищем по строке и заголовку)
– Видео про функцию ВПР в Google Таблицах

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

КАК МЫ ПРОВЕЛИ ЛЕТО

Друзья, ниже - подборка наших летних скриптов. Полезные, бесплатные и с говорящими названиями.

Налетайте, не благодарите и делитесь с друзьями:

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах t.me/google_sheets/563

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней t.me/google_sheets/564

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске t.me/google_sheets/565

ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное t.me/google_sheets/567

TELEGRAM BOT (+ полная инструкция): t.me/google_sheets/556

СОБИРАТОР (копируем и вставляем много данных через sheets api) t.me/google_sheets/536

===

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

Пять мини-лайфхаков в Google Таблицах

Если вам нужно вернуть только первое значение из массива, возвращаемого FILTER, поставьте перед функцией +
=FILTER(A:C; C:C < 2000) — все значения меньше 2000
=+FILTER(A:C; C:C < 2000) — первое значение меньше 2000

Клавиша F4 — ей мы бы дали звание самой недооцененной. Это повтор последнего действия. Работает не для всего, но вставка строк/столбцов, форматирование и многое другое — очень ускоряет работу. Кстати, работает и в Excel. На Mac: ⌘ + Y или ⌘ + Shift + Z или Fn + F4.

Ctrl+Shift+V — вставка только значений. Еще один наш фаворит среди горячих клавиш. Как же это ускоряет замену формул на значения. Вот бы такое в Excel (да, окно "Специальная вставка" там можно вызвать Ctrl+Alt+V, но потом нужно еще выбрать "Значения" в нем). Кстати, сочетание работает во всех приложениях Google Диска — Презентациях, Документах. На Mac: ⌘ + Shift + V.

/copy — вставьте слово "copy" после ссылки на Таблицу (или другой док Google Диска) и отправляйте ссылку тем, кому нужно создавать копию, а не пользоваться исходной таблицей — сразу будет открываться страница с кнопкой "Создать копию".

Хотите наглядно оформить дэшборд с отклонениями "план-факт" — используйте диаграмму "Сводка" (хотя это не совсем диаграмма) — в качестве диапазона данных используйте два значения, факт и целевое/прошлый период/другой базис для сравнения.
Можно настроить отклонения в % или абсолюте (см скриншот).

===
📕📗📘 Оглавление канала

УДАЛЯТОР / ДОБАВЛЯТОР
Следим за количеством пустых строк в выбранных Таблицах

Тормозит Таблица, а вы боитесь лезть в формулы и что-то оптимизировать?

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

Мы придумали для вас решение (таблица со скриптом), с помощью него вы сможете поддерживать нужное количество строк и столбцов:

Работает так:
1. заносите ссылки на Таблицы
2. по каждой Таблице указываете, сколько пустых строк / столбцов должно остаться
3. запускаете скрипт, скрипт открывает каждый лист каждой таблицы
4. удаляет столбцы и строки, учитывая запас, который вы задали
5. если потребуется – то добавляет строки / столбцы до запаса

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

===
📕📗📘 Оглавление канала

ПОЛЬЗОВАТЕЛЬСКИЕ ФУНКЦИИ
Выводим все листы Таблицы и диапазоны с данными

/**
* Выводим листы и диапазоны
* @customfunction
*/
function листы_диапазоны() {

return
[['Лист', 'Диапазон с данными', 'Границы листа']].concat(SpreadsheetApp.getActive().getSheets()
.map(g => [g.getName(), g.getDataRange().getA1Notation(), g.getRange(1, 1, g.getMaxRows(), g.getMaxColumns()).getA1Notation()]))
}

Как работает? Положите код функции в редактор скриптов Таблицы, после введите в ячейку название функции листы_диапазоны.

Для принудительного обновления – оберните название функции в ЕСЛИ (IF) и добавьте ссылку на чекбокс. Активируете чекбокс – функция пересчитается.
=IF(A1; листы_диапазоны())

🔥Другие функции из гифки:
– выводим имя книги
– текущий лист и ссылка на него
– все листы книги и ссылки на них
https://pastebin.com/8Rr3fgZc

===
📕📗📘 Оглавление канала

ДОСТАВАТОР: берём из кода веб-страниц регуляркой нужное

Представьте – вам нужно зайти на 200 однотипных веб-страниц и достать из них адрес объекта или как в нашем примере – ссылку на изображение.

Так себе перспектива, да?

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

Как всё работает:
0) копируйте Таблицу
1) заполните столбец A: ссылки, которые надо обработать
2) заполните столбец B: регулярное выражение, которое извлечет из кода веб-страницы нужное
3) запускайте!

Скрипт проходит строки по одной, вставляя данные и включая чекбокс, обрабатываются только строки с выключенным чекбоксом.

Про регулярку:
Ссылка, которую достаём, в коде страницы выглядит так:
data-large="/upload/d5/b4/19/37/d.jpeg" class="my-foto">

Нам нужно всё, что после data-large=" и до "

Регулярка будет такой:
data-large="(.+?)"


regex101.com/ – проверить выражение
rexegg.com/regex-tools.html – научиться

💊💡(скрипты, боты и отчёты на заказ)

QUERY по дате, представленной в виде текста

Допустим, у вас есть столбец с текстовыми строками, содержащими даты (в виде 05.06.2020, далее до 15.06.2025 или чего-то подобного)

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

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

ТЕКСТ(МЕСЯЦ(A1);"00" )&"." &ГОД(A1)

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

=QUERY(диапазон_с_данными;"select * WHERE G contains '"&ТЕКСТ(МЕСЯЦ(A1);"00")&"."&ГОД(A1)&"'";1)

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

ДОПУСКАТОР: изменяем права доступа к выбранным файлам и папкам на Google Диске

Хотите добавить к пятидесяти рабочим файлам нового сотрудника как редактора или оперативно убрать сотрудника, который не прошёл испытательный срок и вообще не подходит по духу и грозится все удалить?

Сегодняшнее решение (тут) поможет вам с этим разобраться.

Советы и правила:
– Скрипт работает и с файлами и с папками
– Можно указывать как URL, так и ID файла или папки
– Если даёте доступ к папке, то он распространится на вложенные в папку папки и файлы
– После обработки всей строки скрипт добавит дату и время в столбец E, при следующем запуске скрипт начнёт с первой строки БЕЗ даты и времени (это сделано для того, чтобы обрабатывать много файлов)

Поэтому, если нужно обработать строку – очистите
столбец E
– Чтобы добавлять / удалять сотрудников вы должны быть по крайней мере редактором

===
а еще уменьшаем время, которое вы тратите на рутину (скрипты, боты и отчёты на заказ)

📕📗📘 Оглавление канала

ЗАКРЫВАТОР: скрипт автоматического закрытия прошедших дней

Нерадивые сотрудники заходят в старые сделки и меняют контрагента или сумму случайно? Или специально, пытаясь запутать вас и выгадать что-то для себя? 🐽

Мы создали скрипт, который поможет в таких случаях.

Работает он так – с помощью функции findRow находим номер последней строки со вчерашней датой, далее с помощью delProtection удаляем защищенный диапазон, далее с помощью createProtection создаём диапазон начиная со второй строки листа и по последнюю строку со вчерашней датой.

Таблица со скриптом здесь, код отдельно здесь, чтобы все работало у вас – уносите код к себе в Таблицу и заполните 4, 5, 6, 11 строку в редакторе скриптов: это название рабочего листа, емейлы, которые добавляем в закрытый диапазон, название защищенного диапазона и номер колонки с датой на вашем листе.

==
Разработка Таблиц, скриптов и ботов (от 10к рублей)

📕📗📘 Оглавление канала

ЗАМЕНЯТОР: замена значений из словаря на другие в выбранных Таблицах

Друзья, выкладываем наше готовое решение для замены одних значений на другие в выбранных Таблицах.

Краткая инструкция:
1) делайте копию Таблицы со скриптом, чтобы унести её к себе на диск
2) заполняйте словарь (значения, которые мы ищем и значения, на которые мы будет их заменять) и вносите ссылку на Таблицу со словарём и название листа в ячейки a3, b3
3) в диапазоне a5:c укажите таблицы, листы и диапазоны, в которых требуется произвести замену
4) в диапазоне d5:d отметьте чекбоксами те строки, которые требуется обработать сейчас
5) и запустите скрипт нажатием на кнопку

==
Скрипт создан при поддержке lesollp.ru, спасибо им,

а еще мы пишем любые скрипты, создаём телеграм ботов на заказ (тыц)

📕📗📘 Оглавление канала

Помогаем маме: Google Таблица для отправки посылок Amazon и Ebay

Друзья, мама нашего подписчика Антона активно торгует на Amazon и Ebay. Ежедневно ей приходится отправлять много посылок.

Антон создал решение для отправки и по нашей просьбе рассказал о нём в статье на медиум

Зачем Таблицы в школе?

Людмила работает школьным психологом в украинской школе. Мы попросили ее рассказать, зачем ей в школе Google Таблицы: https://telegra.ph/Prichem-tut-tablicy-Google-07-20

В Excel 365 появилась чудесная функция ПРОСМОТРX / XLOOKUP — хороший апгрейд стандартного ВПР, не страдающий зависимостью от расположения столбцов в исходном диапазоне данных. И хоть мы не про Excel, но уверены, что вам будет полезно про нее знать.

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

В посте смотрим на ПРОСМОТРX, ее синтаксис и возможности, а также вспоминаем, как заменить ее в Google Таблицах с помощью пересборки исходной таблицы прямо внутри функции.

---
📕📗📘 Оглавление канала (в Google Таблице)