Гугл таблицы собрать данные с разных листов

Обновлено: 05.10.2024

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

И где он?
Сделайте пример что есть , что надо получить. Автор - doober
Дата добавления - 04.04.2020 в 14:02

А почему формулы так категорически не подойдут? Если у Вас все листы - и первичные, и сводный - в одном файле, то речь тогда не идёт об иногда нерасторопных IMPORTRANGE. Для связи внутри одного файла есть масса других функций.

Поддерживаю Сергея в плане файла примера (ссылки на расшареную Гугл-таблицу). Причем, постарайтесь сделать такой примерчик, чтобы он в дальнейшем остался в открытом доступе - для грядущих поколений. А то в этом разделе много тем, в которых авторы после получения решения закрывали свои файлы от посторонних глаз, тем самым обесценивая топики, особенно если решения были приведены внутри этих файлов - т.е. после обсуждения не оставалось ничего полезного Спасибо за понимание!

Извините, что сразу не приложила пример. Тогда немного конкретизирую задачу: есть Таблица 1, содержащая несколько листов (в файле примера три листа), а также Таблица 2, в которую нужно свести все данные из листов первого файла.
Не рассматриваю для решения формулы, т.к. это постоянная подгрузка, а хотелось бы иметь возможность работать с фалом Таблица 2 без дополнительных "тормозов".

И где требуемый результат и расшареная таблица с редактированием по ссылке?
Скрипты где писать? doober

И где требуемый результат и расшареная таблица с редактированием по ссылке?
Скрипты где писать? Автор - doober
Дата добавления - 04.04.2020 в 22:42

Да что же сегодня творится доступ исправлен, надеюсь, теперь работает.

function onOpen() <
var ui = SpreadsheetApp.getUi();
ui.createMenu('Моё меню')
.addItem('Объединить данные из листов таблицы 1', 'getUnionSheetsDataFromAnotherFile')
.addToUi();
>
function getUnionSheetsDataFromAnotherFile() <
var ssSource = SpreadsheetApp.openById('1vRD9CvmhgI3D8C36w6lzEYNwPN2kPeb7zCJdbIuUV-g');
var strAddr = 'A2:C';
var dataAll = [];
['Лист1','Лист2','Лист3'].forEach(function(sh) <
var range = ssSource.getSheetByName(sh).getRange(strAddr);
var dataOne = range.getValues().filter(function(row) < return row[0] >);
dataAll = dataAll.concat(dataOne);
>);
if (!dataAll.length) return; // если массив пустой, то досрочно завершаем процесс
var shSvod = SpreadsheetApp.getActive().getSheetByName('Свод 2'); // устанавливаем лист вставки массива данных, сформированного выше
if (shSvod.getMaxRows() > 10) shSvod.deleteRows(11, shSvod.getMaxRows()-10); // вначале урезаем лист вставки до некоторого минимального кол-ва строк
shSvod.getRange(strAddr) // устанавливается диапазон A2:C (после возможного выше удаления строк)
.clearContent() // очистка содержимого установленного диапазоне A2:C
.offset(0, 0, dataAll.length) // изменение размера диапазона - под диапазон вставляемого массива (при нехватке строк - они автоматически добавляются)
.setValues(dataAll) // вставка значений из массива
.offset(0, 0, 1) // устанавливается диапазон A2:C2 (одна строка) - для последующего копирования формата
.copyTo(shSvod.getRange(strAddr), SpreadsheetApp.CopyPasteType.PASTE_FORMAT, false); // копирование только формата из диапазона A2:C2 в переопределенный диапазон A2:C
>

Ого, даже целых два решения. Безгранично благодарна.

Да, я не очень подробно описала, на этом небольшом примере работает замечательно!

По поводу сбора данных по условию не написала изначально, поэтому просить его было бы наверное уже наглостью.

Очень помогли обоими решениями, побегу применять.
Большое спасибо!

Ого, даже целых два решения. Безгранично благодарна.

Да, я не очень подробно описала, на этом небольшом примере работает замечательно!

По поводу сбора данных по условию не написала изначально, поэтому просить его было бы наверное уже наглостью.

Очень помогли обоими решениями, побегу применять.
Большое спасибо! d-141

Да, я не очень подробно описала, на этом небольшом примере работает замечательно!

По поводу сбора данных по условию не написала изначально, поэтому просить его было бы наверное уже наглостью.

Очень помогли обоими решениями, побегу применять.
Большое спасибо! Автор - d-141
Дата добавления - 05.04.2020 в 20:12

Как объединить или объединить несколько листов в один лист в Google Sheet? Здесь я расскажу о простой формуле решения этой задачи.

Объединить / объединить несколько листов в один лист в листе Google

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

1. Введите эту формулу:

= в ячейку нового листа, где вы хотите объединить данные из других листов. Смотрите скриншот:

Ноты:

1. В приведенной выше формуле Qua1, Qua2, Qua3и Qua4 названия листов, которые вы хотите объединить; A2: C используется для получения всех ячеек листов - Qua1, Qua2, Qua3 и Qua4 из второй строки в последнюю.

2. В приведенной выше формуле len ('Qua1'! A2: A), len ('Qua2'! A2: A), len ('Qua3'! A2: A), len ('Qua4'! A2: A) указывает, что запись в столбце A непуста, если есть пустые ячейки, строка будет исключена из объединенного листа.

2. Затем нажмите Перейти key, все строки из определенных листов были объединены в один лист, как показано на следующем снимке экрана:

Есть 5-6 страниц. Количество страницы будет добавляться, их название неизвестное (могут быть разные).
Нужно вытаскивать часть данных с этих страниц (размеры таблицы может отличаться) в одну общую (типа базы данных)

Причем если данные в таблице изменяться, то должно поменяться и в этой сводной табл.

Я пробивал через "именованные диапазоны"
=
где
Н1 Диапазон1
Н2 Диапазон2
Н3 Диапазон3
Но тогда нужно вручную добавлять диапазоны (что в целом не проблема) и прописывать страницу (что уже сложнее).

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

Извините немного пропал, но продвинулся в своем проекте).

По страницам:
Агромол(станок 221) - это страница с данными (Диапазон данных 'Агромол(станок 221)'!B16:H42)
Лисопилка(пила 215) - это страница с данными (Диапазон данных 'Лисопилка(пила 215)'!B16:H34)
С этих листов будут копироваться данные. Диапазоны разные. Т.е. ширина одинаковая, разные кол-во строк

БД - это сборка данных с листов (промежуточный, технический лист)
Все данные - тут уже обрабатываться и выводиться необходимые данные.

Как я все реализовал:
- руками присваиваем имя необходимому диапазону (написал пару формул, чтобы имя сформировалось и было легко найти)
- на листе БД прописываю диапазоны (оранжевым)
- и дописываю в формулу ссылки на диапазоны - красным
=

Все работает, но был совет пересобрать формулу, как это сделать? Или может еще есть вариант попроще?
т.е. что бы имя диапазона попадало в список "Диапазоны для БД" и потом дописывались в формулу

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

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

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

Прикрепляю небольшой видеообзор итогового решения, что бы было понятней о чем речь)

Вот ссылка на папку с таблицами, которые будет описаны далее.

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

И есть таблица руководителя, содержащая форму отчета.

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

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

Для этого создаем в таблице руководителя ровно столько листов, сколько таблиц сотрудников. Данные будем подтягивать формулой IMPORTRANGE()

Посмотрите короткий обзор применения формулы в данной ситуации.

Подробнее о формуле:

Обязательно заключайте аргументы в кавычки!

Пример функции:

Для самопроверки воспользуйтесь готовой формулой

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

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

Для этого хорошо подходить формула СУММЕСЛИМН()

Подробнее о формуле:

=СУММЕСЛИМН(диапазон суммирования;диапазон критерия 1; критерий 1; диапазон критерия 2; критерий 2;…)

И эту формулу нужно приписать относительно каждой таблицы.

Вид формулы в ячейке отчета:

Про формулу СУММЕСЛИМН и про то как ее использовать для разных вычислений напишу статью немного позже.

А пока прикрепляю запись вебинара, где мы очень подробно разбираем IMPORTRANGE, СУММЕСЛИМН, ЕСЛИ, FILTER для создания модели отчета.

Если хотите получить записи курса по Гугл таблицам, то можно посмотреть информацию на этой страничке.

Какой-то велосипед из костылей и желудей. Вы про СУБД слышали, не?

Конечно слышал) мы этим и занимаемся!
Суть статьи в том, что бы каждый предприниматель смог простым способом настроить себе отчетность. Практика показывает, что большинство пользователей не знают элементарных вещей.

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

Не все могут и хотят делать что то сложное. Да возможно это нецелесообразно. Есть простые и общедоступные инструменты.

Решать такого уровня задачи можно в любом гуе и тоже без программирования. Только там функций больше и импорт\экспорт в любой любимый табличный редактор наладить не проблема.

Например? Возможно читателям статьи будет интересно.

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

Тут очевидно, что без разработчиков не обойтись! Я писал выше о цели статьи.

query builder
база данных
Специалист/10
Для того, что бы получить что то нужна функциональность, которая будет отправлять запросы в базу.
Люди это интерфейсом называют.
Тут очевидно, что без разработчиков не обойтись! Я писал выше о цели статьи.
Кому очевидно? Специалистам хелпэксел.про? Ну такое. Мне вот для нажатия далее-согласен-далее-далее-окей разработчик не нужен, и это всё ещё проще костылей в табличном редакторе с очень условной реляционностью.
Да в целом вы можете к нам на сайт перейти и будет понятно вообще чем
мы занимаемся
Пытаетесь продавать таблицы в экселе и на 40% не работающих чат-ботов.
и почему имеем в этом экспертность
Скорее экспертность вас имеет.

Это рассуждения человека далекого от реальности) спасибо за то, что уделили столько времени! Любая критика очень даже приемлема.

Практического применения, тем более массового, инструментов, которые вы привели нет!

Читайте также: