Google Sheets — Немного про IMPORTRANGE()

Google Sheets — Немного про IMPORTRANGE()

Michael Smirnov

Правильный канал о Таблицах (Google Sheets): @google_sheets

Чат канала: @google_spreadsheets_chat

Здравствуйте, товарищи!


По случаю радостного повода — гугловцы героически починили IMPORTRANGE(), пишем небольшую статью по теме этой функции.


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


Говорят, пофиксили, но радуемся осторожно (ниже люди продолжали немного ругаться, но вроде перестали)


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


У меня в таблице три листа: import, Финансовый Отдел, Инженерный Отдел. На 2-м и 3-м листах данные, на первый буду импортировать.


В реальной ситуации данные обычно импортируются из разных таблиц, Id'шники в функции импорта разные. Тут искусственный пример, всё происходит в рамках одной таблицы, Id'шники будут везде одинаковые − пусть это никого не смущает.


Данные

Случайным образом нагенерил данных два столбца по 10 строк: даты и суммы.

=ARRAYFORMULA(INT(RANDARRAY(10; 2) * {20\ 1000} + {TODAY()\ 500}))
На листе Инженерный Отдел аналогичные данные


Импортируем

Пробуем импортировать:

=IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "финансовый отдел!A:B")


Теперь объединим данные от двух импортов:

={
  IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A:B");
  IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A:B")
}


Поправим пару мелких неудобств:

  • Заголовки повторяются
  • Импортировались и пустые строки тоже, данные со второго импорта идут только после них


Заголовки можно было бы оставить в одном из импортов, но мы вынесем в отдельную строку, чтобы импорты были однотипные. Добавим фильтрацию с помощью QUERY() – в первом столбце должны быть значения (у вас могут быть другие критерии):

=QUERY(
  {
    "Дата"\ "Сумма";
    IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:B");
    IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:B")
  };
  "WHERE Col1 IS NOT NULL";
  1
)

Теперь всё хорошо, можно работать.


Подписываем данные − откуда взялись

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


Способ 1 − через QUERY

Добавляем заголовок "Источник", оборачиваем каждый импорт в QUERY(), где добавляем столбец с повторяющимся текстом:

=QUERY(
  {
    "Дата"\ "Сумма"\ "Источник";
    QUERY(
      IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:B");
      "SELECT Col1, Col2, 'Финансовый Отдел' LABEL 'Финансовый Отдел' ''";
    );
    QUERY(
      IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:B");
      "SELECT Col1, Col2, 'Инженерный Отдел' LABEL 'Инженерный Отдел' ''";
    )
  };
  "WHERE Col1 IS NOT NULL";
  1
)

Плюсы:

  • ✅ Относительно компактно
  • ✅ Относительно просто для понимания

Минусы:

  • ❌ Надо явно перечислять все столбцы в SELECT
  • ❌ Подписи (название источника) в разных местах, хоть и рядом с каждым импортом. Если забыть её поменять при копипасте, всё будет работать, но можно и не понять, почему данные не импортируются (а они импортируются, у них просто подпись одинаковая).


Способ 2 − через схлопывание и 2D виртуальный диапазон

☠ Сразу скажу, способ не очень хороший, так как требует, чтобы в источниках было одинаковое количество строк. Обычно это не так.


Что за схлопывания (TRANSPOSE(QUERY(TRANSPOSE(...)))), можно изучить здесь. 2D виртуальный диапазон − это столбцы с импортированными данными {...\ ...} (внимание на \) и строка с подписями {"Финансовый Отдел"\ "Инженерный Отдел"}:

=QUERY(
 {
  "Дата"\ "Сумма"\ "Источник";
  ARRAYFORMULA(
   SPLIT(
    FLATTEN(TRANSPOSE(
     {
      TRANSPOSE(QUERY(TRANSPOSE(
       IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:B") & "♥️"
      );; 10^7))\
      TRANSPOSE(QUERY(TRANSPOSE(
       IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:B") & "♥️"
      );; 10^7))
     } & " " & {"Финансовый Отдел"\ "Инженерный Отдел"}
    ));
    "♥️ ";;
   )
  )
 };
 "WHERE Col1 IS NOT NULL";
 1
)


Результат тот же, картинки не будет.


Плюсы:

  • ✅  Более универсальная конструкция. Нет никаких зависимостей от количества столбцов и строк. (На самом деле есть: в заголовках, но это один раз перечислить).
  • ✅ Все подписи в одном месте. Если забыть добавить подпись для нового импорта сразу будет ошибка.

Минусы:

  • ❌ Источники должны содержать одинаковое количество строк. Эту проблему можно обойти через добавление столбца с источником через QUERY() (как в Способе 1), но второй плюс этого способа потеряется.
  • ❌ Сложная для понимания конструкция
  • ❌ Много преобразований строк, что требует ресурсов


Короче, способ не шибко хороший. Для повышения общего уровня культуры. Схлопывания и ARRAYFORMULA() на 2D диапазонах − крайне полезные вещи, часто пригождаются.


Способ 3 – через дополнительные импорты

Можно добавить столбцы с названием источника через повторение значения с помощью IF(SEQUENCE(); ...). Но для этого потребуются дополнительные импорты, с помощью которых мы узнаем количество строк в каждой таблице:

=QUERY(
 {
  "Дата"\ "Сумма"\ "Источник";
  {
   {
    IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:B");
    IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:B")
   }\
   ARRAYFORMULA(
    {
     IF(SEQUENCE(ROWS(IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:A"))); "Финансовый Отдел");
     IF(SEQUENCE(ROWS(IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:A"))); "Инженерный Отдел")
    }
   )
  }
 };
 "WHERE Col1 IS NOT NULL";
 1
)


Результат тот же.


Плюсы:

  • ✅ Можно навставлять ссылок на конкретные строки
  • ✅ Не нужно перечислять колонки

Минусы:

  • ❌ Импортов в два раза больше (хоть мы и не всё повторно импортируем, а только первую колонку) из-за того, что надо знать количество импортированных строк.


Добавляем ссылки на источник

Способ 3 хорош для добавления ссылок на источник.


У нас есть ссылка на лист (вы её видите в браузере):

https://docs.google.com/spreadsheets/d/{SpreadsheetId}/edit#gid={SheetId}
  • {SpreadsheetId} − Id таблицы
  • {SheetId} − Id листа (да, надо пойти и посмотреть)


Если к ней добавить &range={range}, где {range} – это диапазон на листе, получится ссылка на конкретный диапазон (он будет выбран при открытии таблицы):

https://docs.google.com/spreadsheets/d/{SpreadsheetId}/edit#gid={SheetId}&range={range}


Мы для простоты будем добавлять ссылки на колонку A:A соответствующей строки:

=QUERY(
 {
  "Дата"\ "Сумма"\ "Источник";
  {
   {
    IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:B");
    IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:B")
   }\
   ARRAYFORMULA(
     "https://docs.google.com/spreadsheets/d/18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys/edit#"
    & {
      "gid=1424146655&range=A" & SEQUENCE(ROWS(IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Финансовый Отдел!A2:A")); 1; 2);
      "gid=1978378908&range=A" & SEQUENCE(ROWS(IMPORTRANGE("18bnna1mIbuzZtwOzI4GLoXnlZTjScQ9I4lma-bHX1Ys"; "Инженерный Отдел!A2:A")); 1; 2)
     }
   )
  }
 };
 "WHERE Col1 IS NOT NULL";
 1
)

С данными что-то не так? Нажимаем на ссылку и смотрим в источнике, может, правим.

Заключение

Выбирайте способ, какой больше нравится. Или придумывайте свои, и делитесь с товарищами в чате, будем рады.


Если готовить ссылки и подписи в источнике, жить будет гораздо проще: достаточно будет импортировать и отфильтровывать пустые (то, что мы в самом начале делали).


И не забывайте, что существует Собиратор (сейчас версия 4.0, а когда будут более новые − ищите их в канале), который быстро всё статично импортирует (IMPORTRANGE() не сломается и не будет тупить, потому что не будет использоваться) через Sheet API. Там есть и добавление метки.

На этом всё. Спасибо за внимание.

Ссылки

Google Sheets Functions

  • IMPORTRANGE() — документация на функцию
  • QUERY() — документация на функцию
  • TRANSPOSE() — документация на функцию
  • SEQUENCE() — документация на функцию

Хитрые техники

Правильный канал о Таблицах (Google Sheets): @google_sheets

Чат канала: @google_spreadsheets_chat

Report content on this page

Report Page

Please submit your DMCA takedown request to dmca@telegram.org