Как выделить видимые ячейки в гугл таблицах

Argument ‘Topic id’ is null or empty

Сейчас на форуме

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

ООО «Планета Эксел»
ИНН 7735603520
ОГРН 1147746834949

ИП Павлов Николай Владимирович
ИНН 633015842586
ОГРНИП 310633031600071

Как обработать только видимые на листе строки?

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

var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getActiveSheet(); var selection = sheet.getActiveRange(); var fromRow = selection.getRow(); var toRow = selection.getLastRow(); var fromColumn = 1; var toColumn = 7; var array = sheet.getRange(fromRow, fromColumn, toRow, toColumn).getValues(); var selected_rows = array.length + 1;

Ожидаемый результат: selected_rows = 6.
Полученный результат: selected_rows = 200.

То есть метод getRange получает данные из области выделения, не учитывая результаты фильтрации.

Как обработать только видимые на листе строки?

google-spreadsheet
google-apps-script

Отслеживать
19.1k 6 6 золотых знаков 30 30 серебряных знаков 44 44 бронзовых знака
задан 16 июл 2015 в 11:10
Владислав Погорелов Владислав Погорелов
85 9 9 бронзовых знаков
16 июл 2015 в 11:14

2 ответа 2

Сортировка: Сброс на вариант по умолчанию

/* -------------------------------------------------------------- возвращает массив видимых строк в границах заданной области ----------------------------------------------------------------- */ function getVisibleData (range) < var values = range.getValues(); var selection = range.getSheet().getActiveRange(); var row1 = selection.getRow(); var row2 = selection.getLastRow()-row1+1; var range2 = range.getSheet().getRange(row1, 1, row2); var arrData = [], colors = []; // обрабатываем только видимые строки (работа в условиях включенной фильтрации на листе) // запоминаем текущие цвета var oldColors = range2.getFontColors(); // меняем цвета на новые for (var r = 0; r < oldColors.length; r++) colors[r] = [(oldColors[r] == '#000001') ? '#000002' : '#000001']; range2.setFontColors(colors); // Невидимые строки не окрашиваются. Находим окрашенные и записываем в массив. var newColors = range2.getFontColors(); for (var r = 0; r < colors.length; r++) < if ((oldColors[r][0] !== newColors[r][0])) arrData.push(values[r]); >// возвращаем старые цвета range2.setFontColors(oldColors); // возвращаем массив видимых строк выделенного диапазона. return arrData; >

Отслеживать
ответ дан 20 июл 2015 в 7:37
Владислав Погорелов Владислав Погорелов
85 9 9 бронзовых знаков

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

5 мар 2017 в 7:05

Ну правильно, вы же руками строите range от первого выбранного до последнего выбранного, поэтому и выдает 200. Чтобы получить интересующий range, нужно взять пересечение созданного array с имеющимся selection . В Excel VBA есть метод Intersect(range,range. ), в гугл-таблицах не нашел, но должен быть (нашел вот такой запрос, там упоминается, что есть rangeset, который умеет хотя бы с одним range пересечение делать — его можно попробовать применить). То есть если selection на самом деле является RangeSet, можно вызвать так:

var array = selection.subRangeSet(sheet.getRange(fromRow, fromColumn, toRow, toColumn)).getValues();

Отслеживать
ответ дан 16 июл 2015 в 14:13
1,084 8 8 серебряных знаков 21 21 бронзовый знак
В GAS нет метода RangeSet .
20 июл 2015 в 7:38

google-spreadsheet
google-apps-script

Важное на Мете

Подписаться на ленту

Лента вопроса

Для подписки на ленту скопируйте и вставьте эту ссылку в вашу программу для чтения RSS.

Нажимая «Принять все файлы cookie» вы соглашаетесь, что Stack Exchange может хранить файлы cookie на вашем устройстве и раскрывать информацию в соответствии с нашей Политикой в отношении файлов cookie.

Разделить текст по столбцам в Google Sheets: Формулы vs Встроенное меню!

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

Допустим, в файл записываются данные скриптом (Google Apps Script) в виде данных, размещенных через двойное подчеркивание. И для того, чтобы обработать их, правильно ими воспользоваться, нам нужно их разделить:

Google Docs

Сделать это можно различными способами. Мы покажем Вам 2 способа — формулой и стандартным встроенным меню.

Разделение текста по столбцам в GoogleSheets через встроенное меню

На все про все нам понадобится 5 шагов:

Google Docs

Копируем нужные нам данные из листа Data
Вставляем их в заранее подготовленный лист с заголовками (для удобства)
Нажимаем в меню «Данные» ➤ «Разделить на колонки. «
В появимшемся окне выбираем разделить «Другой» (так как нашего нет в выпадающем списке)
Вводим двойное подчеркивание и вуаля! Данные в нужном нам виде

Разделение текста по столбцам в GoogleSheets с использованием формул

Здесь нам понадобится написать хитроумную формулу, которая и сделает за нас всю работу:

Google Docs

Итак, разберем на части эту формулу:

«Незакрытый» диапазон: Data!A2:A

В отличии от Excel, Google Sheets позволяют делать вот такие ссылки на диапазон. Вот эта запись «:A» означает «до последней строчки в документе». Дело в том, что Google Sheets отличаются от Excel представлением таблицы. В Excel 1 лист имеет 1 048 576 строк и 16 384 столбца. Притом, что если их не использовать — то файл это нагружать не будет. В Google Sheets все видимые ячейки нагружают таблицу. И чем больше их будет — тем тяжелее будет работать с файлом. Поэтому 1 рабочая книга ограничена 2 000 000 ячейками (на дату публикации блога). Однако, если вы в 1 листе сделаете максимальное количество — работать с файлом будет очень тяжело. Поэтому при создании новой книги, размер листа устанавливается 1 000 строк и 26 столбцов (A-Z). При желании можно удалять/добавлять строки/столбцы.

Итак, в нашем случае, у нас стандартные листы 1 000*26. Поэтому диапазон A2:A равен A2:A1000. Прелесть «незакрытого» диапазона в том, что при добавлении новых строчек он автоматически будет увеличиваться.

Часть формулы: FILTER(Data!A2:A;Data!A2:A<>«»)

Такой формулы тоже нет в Excel. Она фильтрует диапазон по заданным условиям. В данном примере она возвратит все ячейки столбца A, которые не пустые. В нашем случае вернется диапазон A2:A5

Часть формулы: Split(FILTER(Data!A2:A;Data!A2:A<>«»);»__»)

Эта формула стандартная. Она делит строчку по заданному разделителю и располагает данные в 1 строке, разбитые по столбцам.

Часть формулы: ARRAYFORMULA(Split(FILTER(Data!A2:A;Data!A2:A<>«»);»__»))

А это — формула массива. Она работает немного по-другому, чем в Excel. Для нее не нужно выделять диапазон, на которые она распостраняется. Достаточно просто ввести ее при вводе основной формулы (или нажать CTRL+SHIFT+ENTER). Она позволяет обычным формулам, которые работают только с 1 значением, работать с диапазонами (SPLIT разбивает 1 значение по разделителю, но не диапазон значений).

Итак формула готова и все прекрасно работает. С одной стороны она показалась намного сложнее в использовании, чем стандартное встроенное меню. Однако представьте, что данные будут постоянно добавляться. Например, добавилась еще 1 запись:

Google Docs

Чтобы превратить эту запись в читаемую с помощью первого способа нам понадобится опять делать 5 шагов. А если данные добавляются постоянно, то это будет забирать нужное нам время на рутинную работу. В случае же с формулой теперь все автоматизировано. Как только данные были добавлены, на нашем листе с формулой сразу же добавился нужный нам результат:

Google Docs

Так что, чем пользоваться решать Вам в зависимости от ситуации.

И помните — в большинстве случаем рутинную работу можно автоматизировать и высвободить время для действительно важных вещей!

Как обычно — ссылка на пример в Google Sheets:

Как суммировать отфильтрованные строки в Google Таблицах (с примерами)

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

SUBTOTAL( 109 , A1:A10 )

Обратите внимание, что значение 109 — это сокращение для получения суммы отфильтрованного диапазона строк.

В следующем примере показано, как использовать эту функцию на практике.

Пример: суммирование отфильтрованных строк в Google Sheets

Предположим, у нас есть следующая электронная таблица, содержащая информацию о различных баскетбольных командах:

Чтобы добавить фильтр к этим данным, мы можем выделить ячейки A1:B10 , затем щелкнуть вкладку « Данные », затем нажать «Создать фильтр »:

Затем мы можем щелкнуть значок «Фильтр» в верхней части столбца «Точки» и снять флажок рядом с первыми тремя значениями 84, 89 и 93:

Как только мы нажмем OK , данные будут отфильтрованы, чтобы удалить эти значения.

Если мы попытаемся использовать функцию SUM() для суммирования столбца точек отфильтрованных строк, она фактически вернет сумму всех исходных значений:

Вместо этого мы можем использовать функцию ПРОМЕЖУТОЧНЫЕ.ИТОГИ() :

Эта функция принимает сумму только видимых строк.

Мы можем вручную проверить это, взяв сумму видимых строк:

Сумма видимых строк: 99 + 94 + 97 + 104 + 109 + 99 = 602 .

Дополнительные ресурсы

В следующих руководствах объясняется, как выполнять другие распространенные операции в Google Таблицах:

Как выделить видимые ячейки в гугл таблицах