Страница телеграм канала Google Таблицы

google_sheets logo

Google Таблицы

4530 подписчиков

Работа с данными в Google Таблицах. Кейсы, решения и угар. Объясняем на гифках. наша книжка: https://goo.gl/rDHu2N оглавление канала: https://goo.gl/HdS2qn Написать нам: @namokonov, @renat_shagabutdinov


Входит в категории: Технологии
Google Таблицы
08.12.2018 20:12
Про нашего бота: @google_spreadsheets_chat_bot Привет, друзья! Недавно мы решили попрактиковаться в интеграции Google Таблиц и Telegram и создали своего телеграмм бота. Бота зовут Щитс. Код написан на Google Apps Script и работает на веб-хуках, скрипт развернут как веб-приложение на Google Drive (это значит - бесплатно, за хостинг ничего платить не нужно). Что умеет (команды бота): /спасибо @durov - команда для чата, можно тегнуть участника и сказать спасибо, "спасибо" копятся /идея напишите про ёлки и мандарины - ваша идея сохранится в Таблице с идеями /поиск query - бот вернет из оглавления нашего канала все посты со словом запроса /вопрос можно ли удалить Таблицы навсегда? - ваш вопрос сохранится в Таблице с вопросами /донат - ссылка на наши Яндекс.Деньги и список последних поддержавших канал Друзья, пользуйтесь! Будем рады обратной связи и предложениям по развитию бота, можете писать их в личные сообщения, либо сохранять в Таблице через команду /идея.
Читать

Google Таблицы
06.12.2018 10:12
function fillActiveSheet(){ var sheet = SpreadsheetApp.getActiveSheet(); var dataRange = sheet.getDataRange(); var data = dataRange.getValues(); var headers = data.shift(); var newData = fillData_(data); newData.unshift(headers); dataRange.setValues(newData); } function fillFullActiveDataRange(){ var range = SpreadsheetApp.getActiveRange(); range.setValues(fillData_(range.getValues())); } function fillData_(data){ return data.map(function(row){ return row.map(function(item, i){ if(item !== this[i] && item !== ) this[i] = item; if(this[i] === undefined) this[i] = ; return this[i]; }, this); }, data.slice(0, 1)); }
Читать

Google Таблицы
06.12.2018 10:12
Заполняем пустые ячейки в "рваном" диапазоне скриптом. Привет, друзья! В Excel есть функция, которой мы частенько пользовались - возможность выбрать только пустые ячейки в диапазоне и заполнить эти ячейки содержимым из крайней непустой ячейки каждого диапазона. Мы перенесли эту функцию в Google Таблицы, написав два скрипта: - первый заполняет выбранный диапазон - второй заполняет лист целиком Гифка: http://recordit.co/TVg2CUyxpF.gif Таблица со скриптом здесь. Файл > создать копию и можно будет открыть инструменты > редактор скриптов и посмотреть код с комментариями. Лайфхак по использованию: каждый раз вставлять скрипт в нужную Таблицу - долго. Поэтому можете создать для себя специальную Таблицу с этим скриптом (и другими полезными скриптами для обработки данных). Дальше просто вставляете в эту мастер-таблицу массив, обрабатываете и используете результат уже в нужной вам Таблице. P.S. Спасибо @oshliaer за помощь.
Читать

Google Таблицы
02.12.2018 16:12
Привет, друзья! Сегодня о том, как с помощью функций достать адрес ячейки, букву(ы) столцба и номер строки. Три простые формулы: 1) Номер строки: =ROW( ) Если ничего не вводить в круглых скобках, то вы получите номер строки, в которую введена формула 2) Адрес ячейки: =CELL("address";A3) 3) Буква столбца из адреса ячейки: =REGEXEXTRACT(CELL("address";A6);"[A-Z]+")
Читать

Google Таблицы
26.11.2018 13:11
Задача из практики нашего клиента. Дано: выгрузка из 1С, в которой есть вес и цена товара в каждом заказе. Нужно считать стоимость по товарным категориям без промежуточных расчетов. Поле "вес" в некоторых строках пропущено - пустые ячейки. В таких случаях нужно принимать вес за единицу (иначе в этих строках стоимость товара будет равна нулю, т.к. пустая ячейка - вес - будет приниматься за ноль). Просто перемножение двух столбцов с условием на товарную категорию решается легко - функцией FILTER. =СУММ(FILTER($B$2:$B*$C$2:$C;$A$2:$A=E3)) А чтобы автоматически приравнивать пустые ячейки в одном из столбцов к единице, добавим функцию ЕСЛИ/IF: =СУММ(FILTER(ЕСЛИ($B$2:$B="";1;$B$2:$B)*$C$2:$C;$A$2:$A=E3)) (вид выгрузки на скриншоте упрощен для примера, но сути это не меняет).
Читать

Google Таблицы
20.11.2018 14:11
Друзья, отличная новость! Теперь в одной Таблице может быть до 5 миллионов ячеек на всех листах. Напоминаем про наши чаты, там можно задавать вопросы: @google_spreadsheets_chat - наш чат про Google Таблицы @googleappsscriptrc - чат про Google Apps Script, его ведут наши друзья.
Читать

Google Таблицы
17.11.2018 15:11
Наш читатель Коля из Тернополя спрашивает: "Ребят, есть список картинок, с названиями 1.jpg, 2.jpg и т.д. (шаг - единица). Как мне узнать, картинок с какими номерами нет, если известно, что всего их, например, 10?" Коля, есть способ: 1) Формируем список от 1 до 10 с помощью функции ROW() / СТРОКА() 2) Добавляем к каждому элементу списка - ".JPG" 3) Помещаем полученный список из всех возможных картинок в функцию FILTER в диапазон вывода и в диапазон условия, критерием условия станет список картинок, которые у нас есть. Формула выведет только то, что есть в первом списке и отсутствует во втором. Итоговая формула: =FILTER(ROW(A1:A10)&".jpg";ISNA(MATCH(ROW(A1:A10)&".jpg";A3:A6;0))) Если писать функции на русском языке: =FILTER(СТРОКА(A1:A10)&".jpg";ЕНД(ПОИСКПОЗ(СТРОКА(A1:A10)&".jpg";A3:A6;0))) Таблица с примером
Читать

Google Таблицы
14.11.2018 12:11
Друзья, недавно мы писали про то, какие бывают фильтры и как с ними работать пользователям с разными видами доступа. А сегодня давайте разберем, какие виды доступа к документам вообще есть в Google Таблицах. При создании нового документа вы являетесь владельцем и больше никто файл не сможет просматривать или редактировать, даже если получит ссылку. Владелец у файла всегда один, он может файл удалить, редактировать, добавлять новых редакторов, передать право владения. Доступы бывают: - на просмотр без права копирования и скачивания - человек с таким доступом может только открыть файл и просмотреть его, но скопировать данные, распечатать файл или скачать его в любом формате он не сможет. - на просмотр с правом копирования и скачивания - нельзя менять файл, но можно создать копию или скопировать данные. При создании копии владельцем станет тот, кто ее создал - и больше ни у кого доступа в новый файл не будет. Новый файл будет находиться на Google Диске того пользователя, который создал копию. С любым из двух доступов на просмотр можно создавать временные фильтры - они не видны другим и удаляются после выхода из файла. Можно просматривать чужие фильтры (filter views), но нельзя их редактировать. - на комментирование. Тоже с правом копирования и без него. Отличаются от просмотра возможностью оставлять комментарии (не примечания - их видят, создают и редактируют только пользователи с правом на редактирование). - на редактирование - можно изменять ячейки, добавлять и удалять листы, копировать документ, ставить обычный фильтр прямо на рабочем листе, создавать и удалять фильтры (filter views). Временные фильтры, созданные пользователями с правом на просмотр или редактирование в рамках сеанса не видны редакторам. Доступ тоже бывает двух видов - с правом добавления новых редакторов или без него. В первом случае каждый из редакторов сможет добавить новых, которые в свою очередь смогут править документ. Во втором случае у пользователей будет только возможность редактировать документ, новых редакторов сможет добавлять только владелец. Все эти доступы можно давать конкретным пользователям или по ссылке. Доступ по ссылке означает, что любой человек, имеющий эту ссылку, сможет документ просматривать/комментировать/редактировать. Если у вас G Suite (корпоративный аккаунт), вы сможете открывать доступ по ссылке только пользователям из вашего домена (вашим коллегам, имеющим корпоративную почту Gmail). Есть также доступ по ссылке для всех в интернете. Это означает, что документ сможет найти любой пользователь сети через поисковик. (однажды я искал какую-то статистику и случайно попал через поисковую систему на чью-то таблицу, у которой был такой доступ). О том, как менять и как передавать доступ со скриншотами.
Читать

Google Таблицы
10.11.2018 11:11
Отправляем письма скриптом. Опять. Друзья, недавно в наш чат пришла Варя и попросила помочь с регулярной отправкой почты из Google Таблицы скриптом. Задача звучала так—есть постоянно обновляемый (например, формулой QUERY) список адресов электронной почты. Нам нужно по расписанию вызывать скрипт и отправлять письма только на те адреса, на которые мы их еще не отправляли. Рассказываем про решение
Читать

Google Таблицы
03.11.2018 15:11
В правиле условного форматирования можно использовать данные другого листа. Друзья, привет! Речь о правиле условного форматирование формулой. Чтобы сослаться на другой лист - просто напишите диапазон и имя листа в кавычках и оберните в функцию INDIRECT (ДВССЫЛ). Такое правило будет работать. Например: выделим города-миллионники. Гифка: http://recordit.co/LBQufQSzIP Формула из примера: =COUNTIF(INDIRECT("Города-миллионики!A:A");A2) =СЧЁТЕСЛИ(ДВССЫЛ("Города-миллионики!A:A");A2) Таблица
Читать

Google Таблицы
30.10.2018 19:10
Друзья, давайте вспомним про правила совместной работы с фильтрами. Они особенно актуальны, когда пользователей в документе много. Обычный фильтр, кнопка "Фильтр" на панели = фильтрация, которая отображается у всех пользователей Filter views (фильтры), кнопка "Фильтры" на панели = фильтрация, которая отображается только у того, кто создал фильтр или вошел в него (по ссылке или через меню "Фильтры") Временные фильтры, кнопка "Фильтры" на панели = фильтрация, которая отображается только у того, кто ее создал. Такие фильтры удаляются при закрытии вкладки и не видны другим пользователям. Временные фильтры доступны пользователям с доступом на комментирование/просмотр. — помните, что обычный фильтр и результаты фильтрации видны абсолютно всем пользователям с любым доступом. Если вы отфильтровали данные с помощью него, другие люди не увидят скрытые строки, пока не снимут фильтр (снять его они смогут, только имея доступ на редактирование); — есть фильтры (filter views) которые видны только тому пользователю, который в этот фильтр зашел. Их можно сохранять под названиями, ссылки на них можно передавать другим (это очень удобно, когда нужно вместе посмотреть на какой-то срез данных) - просто копируйте ссылку с окончанием вида fvid из адресной строки. — доступ ко всем созданным в файле filter views есть у любого пользователя, даже с доступом на просмотр (но менять и удалять их он не сможет) — пользователь с доступом на просмотр/комментирование может создавать только временные фильтры или просматривать чужие фильтры. Временные фильтры не сохраняются и не видны другим пользователям. — именно filter views - оптимальное решение для совместной работы. Обычный фильтр же лучше вообще не использовать, если пользователей больше одного. При этом не забывайте, что фильтры (filter views) сохраняются, по умолчанию - под названиями вида ФильтрN, где N - порядковый номер. Хорошее правило - удалять все фильтры без названий периодически, чтобы не засорять файл. Если у фильтра нет названия, значит, его создали "на один раз". Правильная же ситуация - когда у каждого отдела/сотрудника/ситуации есть свой фильтр, например "Иванов", "Маркетинг" или "Проекты в работе с приоритетом 2". — filter views и временные фильтры можно использовать в той ситуации, когда кто-то из ваших коллег все-таки воспользовался обычной фильтрацией на листе, а вам нужно видеть всю таблицу вместе со скрытыми строками - и вы не хотите его фильтр сбивать. Создавайте себе свой фильтр (filter view) и смотрите в нем на всю таблицу целиком без фильтрации. Ссылка на видеоурок про фильтры разных типов:

Читать

Google Таблицы
26.10.2018 19:10
QUERY в массиве. Найдем максимальное значение по каждой строке. Друзья, привет! Сегодня будем расширять границы вашего понимания функции QUERY. Задача звучит так - есть ряд строк с разным количеством значений в каждой. Нужно - найти максимум по каждой строке (или минимум или среднее или сумму) и сделать это с помощью одной формулы. Концептуально решение такое - мы разворачиваем диапазон (TRANSPOSE) и с помощью формулы создаем текст запроса для QUERY, он обращается к каждому столбцу диапазона, получает максимум. Дальше переворачиваем наш массив обратно, попутно убирая заголовки. Подробно и по шагам решение на скриншоте Таблица с примером Итоговая формула: =QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:C5);""& ARRAYFORMULA(JOIN(",";"max(Col"&row(A2:A5)-1&")"))));"Col2") P.S. Неоценимую помощь в поиске решения оказал @Volond (он же @SigmaSpola). Спасибо ему
Читать

Google Таблицы
23.10.2018 13:10
Парсим погодное API в Таблице. Пользовательская функция. Друзья, привет! Недавно мы решили научиться брать данные из API, предоставляющего прогноз погоды: https://openweathermap.org/api. Мы написали пользовательскую функцию на Google Apps Script, она парсит JSON (вот такой), достает из него нужные нам поля и выводит результат на лист Таблицы. Пользовательская функция с описанием - в посте.
Читать

Google Таблицы
17.10.2018 15:10
"Найти и заменить" в Таблицах: удаляем ненужные данные, заменяем слова и символы в ячейках и формулах. Не только же про QUERY внутри QUERY нам писать - сегодня предлагаем небольшую инструкцию по простому инструменту "Найти и заменить". Он полезен не только для поиска каких-то текстовых значений на листе. С помощью него можно удалить или заменить определенные символы или слова (хотя бы пробел, которым заменяется разделитель разрядов при выгрузке данных из других систем; или же ссылку на файл-источник во всех ваших функциях IMPORTRANGE).
Читать

Google Таблицы
12.10.2018 13:10
QUERY c QUERY в QUERY Привет, друзья. Сегодня - лайфхак с функцией QUERY. У нас есть таблица из шести столбцов (на скриншоте диапазон A1:F7). А нужно нам, допустим, объединить столбцы 1-2-3 и 1-5-6 в один массив, а потом отсортировать результат по столбцу 1. Поехали: 1) Отбираем нужные столбцы в двух отдельных QUERY: =QUERY(A2:F7;"A, B, C") и =QUERY(A2:F7;"A, E, F") 2) Объединяем результат двух функций в массив с помощью { и }: ={QUERY(A2:F7;"A, B, C");QUERY(A2:F7;"A, E, F")} 3) Полученный массив помещаем в еще одну функцию QUERY как диапазон данных и сортируем по первому столбцу: =QUERY({QUERY(A2:F7;"A, B, C");QUERY(A2:F7;"A, E, F")};"* order by Col1")
Читать