Импорт данных из всех Google Таблиц в списке с помощью формул
Дано: есть набор однотипных таблиц. Нужно загружать данные из всех таблиц в списке, при этом список может меняться – могут добавиться новые, могут уйти старые.
Решение с функциями REDUCE и VSTACK
Будем пробегаться по списку таблиц, проверять, не пустая ли ячейка, и если не пустая, то объединять то, что уже набрали к этому моменту, с импортом из очередной таблицы. В общем виде примерно так (функция REDUCE позволяет накапливать результат на каждом шаге):
=REDUCE(первое значение; массив с ссылками на таблицы; LAMBDA (накопленный массив; ссылка на очередную таблицу; формула для объединения накопленного массива и IMPORTRANGE, загружающей данные из очередной таблицы))
В качестве первого значения можно взять заголовки из первой таблицы, а далее брать уже данные без заголовков (начиная со второй таблицы), чтобы они – заголовки – не дублировались.
=REDUCE(IMPORTRANGE(первый файл; строка заголовков); массив с ссылками на таблицы; LAMBDA (накопленный массив; ссылка на очередную таблицу; формула для объединения накопленного массива и IMPORTRANGE, загружающей данные из очередной таблицы))
То есть сначала у нас будет массив из заголовков, а далее на каждом шаге мы будем прибавлять к нему внизу (с помощью функции VSTACK, объединяющей массивы вертикально) очередные данные. А если ячейка в списке таблиц пустая (ЕПУСТО / ISBLANK), то на этом шаге ничего добавлять не будем, оставим накопленный массив как есть.
Со ссылками на ячейки будет так:
=REDUCE(IMPORTRANGE(K2;"A1:G");K3:K; LAMBDA(массив;ссылка;ЕСЛИ(ЕПУСТО(ссылка);массив; VSTACK(массив; IMPORTRANGE(ссылка;"A2:G"))))
Можно добавить сверху функцию QUERY с простым запросом, который будет удалять пустые строки отовсюду (ведь мы импортируем открытые диапазоны вида A2:G, так что получаем все строки, в том числе и пустые).
=QUERY(наша формула; "Where Col1 is not null")
Импорт со списком таблиц и листов на них
Что если листы у нас называются везде одинаково и мы хотим брать данные с разных листов? Можно перечислить названия листов в соседнем столбце:
И тогда в формуле просто добавится объединение названия листа с восклицательным знаком и диапазоном:
IMPORTRANGE(K2;L2&"!A1:G")
Но это для первого IMPORTRANGE. А уже внутри LAMBDA мы ведь не можем ссылаться на отдельные ячейки. И массива с листами у нас там нет — у нас там список таблиц. Решение — использовать СМЕЩ / OFFSET, чтобы ссылаться на столбец справа (на 1 правее) от значения в обрабатываемом массиве:
IMPORTRANGE(ссылка;СМЕЩ(ссылка;0;1;1;1)&"!A2:G")
Также напомню, что если в IMPORTRANGE лист не указывать вообще, то это будет импорт с первого листа по порядку в данный момент.
А еще в IMPORTRANGE можно ссылаться на именованные диапазоны.
Решение с функциями INDEX / ИНДЕКС и FILTER
Альтернативное решение — формула от Михаила Смирнова.
Напомним, функция LET просто позволяет присвоить какому-то выражению имя и далее ссылаться в сложной формуле на него по имени. Это и ускоряет работу, и сокращает формулу. В данном случае мы присваиваем имя links выражению FILTER(K2:K; K2:K <> "") — это просто список ссылок на таблицы с удалением (фильтрацией) пустых значений. INDEX(links; 1) — первая ссылка. Оттуда мы берем заголовки. Далее прибавляем данные без заголовков (объединяем в массиве фигурными скобками с тем, что уже собрали):
=LET(links; FILTER(K2:K; K2:K <> "");
REDUCE(IMPORTRANGE(INDEX(links; 1); "A1:G1"); links;
LAMBDA(acc; cur; {acc; IMPORTRANGE(cur; "A2:G)})))=LET(links; FILTER(диапазон с ссылками; диапазон с ссылками <> "");
REDUCE(IMPORTRANGE(INDEX(links; 1); "строка заголовков"); links;
LAMBDA(acc; cur;
{acc; IMPORTRANGE(cur; импортируемый диапазон со 2 строки)})))Проверка ссылок и открытие доступа к файлам
При любом варианте решения к каждому файлу нужно открывать доступ по отдельности — только один раз. Сразу из формулы, где они обрабатываются пачкой, сделать это не получится. Решением может быть формула в соседнем со списком ссылок столбце, где мы просто будем импортировать одну ячейку A1 с соответствующей таблицы — если доступ не открыт, будет видна ошибка (иначе просто будет импортироваться значение из A1):
Можно более изящно, одной формулой для всего списка через LAMBDA и MAP. Вот пример от Михаила Смирнова (спасибо ему за примеры своих формул!):
={
"Import Access";
MAP(C2:C; LAMBDA(l; IF(l = "";; LEFT("✅" & IMPORTRANGE(l; "A1"); 1))))
}Она пытается импортировать A1, таким образом проверяя, дан ли доступ, если всё ок — то возвращает галочку, а если не ок, то будет ошибка, на которую надо навести мышку и дать доступ. Только делается все это в массиве (сразу обрабатываем весь список ссылок в диапазоне С2:C, кроме пустых ячеек). В LAMBDA функция LEFT / ЛЕВСИМВ вытаскивает первый символ из текстовой строки, состоящей из галочки и импорта (нам ведь надо где-то этот самый импорт осуществить). Если импорт выдаст ошибку, будет ошибка, а если все ОК, от текстовой строки LEFT оставим галочку.
