Google Sheet IMPORTRANGE Ошибка «Внутренняя ошибка диапазона импорта», когда диапазон представляет собой просто столбец
«Внутренняя ошибка диапазона импорта».
=IMPORTRANGE(«https://docs.google.com/spreadsheets/d/1-bCoiKLjBlM5IGRo9wrdm», «sheet1!B:C») , работает.
Это ошибка? до сих пор это был третий раз, когда мне приходилось менять их много раз? Есть ли какое-нибудь последовательное решение для этого? Я использую это решение временно
5 ответов
Это не могло быть решением проблемы. Я построил целую платформу интеграции данных на листах и сильно полагаюсь на функциональность importrange для защиты доступа к источникам данных от пользователей. Теперь в последнее время #REF начал преследовать мои столы повсюду, и он делает все более или менее непригодным для использования.
Однозначно это ошибка или нехватка ресурсов.
Я думаю, что лучшим решением здесь будет использовать
Я не верю, что уклонение от кеша Google — это исправление или даже обходной путь.
Мы поддерживаем лист с функцией importrange на нескольких вкладках в течение многих лет, и только в течение последней недели возникла проблема.
Мы впервые заметили это в пятницу, а сегодня снова вернулись. В обоих случаях я не думаю, что сделал что-либо, чтобы исправить проблему, особенно сегодня. Я переместил формулу по листу, что привело к обновлению функции importrange, но это все равно привело к «внутренней ошибке диапазона импорта». Функция importrange отключилась на время (я не знаю, сколько сегодня, но я думаю, что это было не менее 15 минут), а затем разрешилась на всех вкладках без изменений.
Я думаю, что это определенно ошибка или Google возится с вещами на сервере. Может, нам нужно найти способ сделать все без использования importrange?
Эти ошибки обычно временные и проходят через несколько часов. Чтобы ускорить это, немного измените формулу импорта, заменив «sheet1!B:B» на «sheet1!B:b» — изменения регистра строчных букв достаточно, чтобы позволить вызову утилизировать кеш Google и получить свежие результаты, что должно позволить вам обойти проблему. .
В дополнение к двойному ответу вы также должны ограничить свой диапазон, чтобы не было большого количества мертвых строк. Так что что-то вроде B:B5000 вместо B:B .
у нас есть несколько листов, которые полагаются на importrange для получения данных из других листов Google, начиная с этой недели у нас возникли проблемы с загрузкой некоторых из них, мы просто получаем внутреннюю ошибку #ref import range.
Я пробовал множество решений, но все они, похоже, работают только временно, после чего при обновлении запроса иногда удается получить данные, размер диапазона не является проблемой, поскольку проблема возникает как при большом импорте, так и при импорте. которые получают только 1 ячейку.
пока лучшее решение, которое у меня есть, это удалить = из формулы, а затем добавить его обратно, чтобы снова загрузить данные, однако это длится всего около 30 минут, прежде чем importrange возвращается к той же ошибке.
в формулах нет ничего необычного
Я пробовал варианты заглавных букв для диапазонов, а также добавлял, если ошибка, чтобы попытаться загрузить вариант формулы
но, похоже, ничего не работает, а когда работает, решение не прилипает.
будем очень признательны за любую помощь или понимание того, что может быть причиной этой проблемы.
Как обойти ошибку IMPORTRANGE: «Результаты слишком велики»?
Я пытаюсь IMPORTRANGE из диапазона, содержащего 240 000 ячеек (40 столбцов и 6000 строк). Функция IMPORTRANGE ошибочна: «Результаты слишком велики». Я не могу найти документацию о ограничениях функции.
Каковы ограничения IMPORTRANGE?
Как мне обойти это, чтобы я мог импортировать эти данные в свой листок?
4 ответа
У меня тоже была аналогичная проблема.
Попробуйте разделить диапазон импорта с помощью формулы массива.
Протестируйте это с помощью разных размеров данных, чтобы получить самую короткую версию, и вы можете делать то, что вам нужно.
Пустые клетки могут иметь значение. Мы наблюдали нарушение импорта в ячейках 23573×11 или 259k, типичный рост составлял около 10 рядов ежедневно, поэтому мы некоторое время находились в ячейках более 250 тысяч. Один столбец в основном пустой, у пары других есть несколько пробелов.
Я не мог заставить ARRAYFORMULA разобрать, как показано выше, или с другими догадками, поэтому я использовал это на своей скрытой вкладке «Ingest».
=importrange(«sheet», «A1:K10000») в ячейке A1
=importrange(«sheet», «A10001:K») в ячейке A10001
В моей рабочей /презентационной вкладке используется
, чтобы обеспечить постоянное форматирование, наши исходные листы перезаписываются ежедневно.
Используя ответ Сэма и документацию для чтения, я нашел способ получить результат BIG DATA без ошибок. Для этого вам нужно сделать шаг за шагом. В одном запросе. Например, если вам нужно экспортировать данные sheet!A3:X100000 .
Попробуйте сделать следующее: сначала сделайте запрос и выберите только
после получения результата просто отредактируйте запрос из
после получения данных снова отредактировать запрос
и продолжайте, пока вы не будете богаты
таким образом я мог бы импортировать около 800 000 ячеек с данными. Для моей задачи этого было достаточно, но я думаю, что если мне нужны более длинные данные результата, я мог бы продолжить, и он будет работать.
Вы также должны помнить, что таблицы Google имеют ограничение на один максимум документа, может содержать только 2 миллиона ячеек.
Из моего опыта использования IMPORTRANGE количество ячеек не было причиной вообще, но в любое время, когда я превысил 36 столбцов, это не получилось. Мои результаты могут составлять 600 строк или 6000 строк, если я не превысил 36 столбцов. По иронии судьбы вы можете обойти это, объединив функции IMPORTRANGE.
Обратите внимание на фигурные скобки , используемые до и после двух функций IMPORTRANGE
Содержание
- Основы
- Создание и открытие файла
- Как сохранять прогресс?
- Совместимость с Excel
- Можно откатиться на более раннюю версию
- Функция «ВПР» (vlookup)
- Как она работает?
- Неточный поиск (наиболее близкое значение)
- Поиск по нескольким условиям (по двум столбцам)
- Функции INDEX и MATCH
- Поиск по нескольким параметрам (ВПР 2D)
- Важно!
- Функция FILTER
- Фильтр по нужным данным
- Фильтр через выпадающий список
- Фильтр с чекбоксами
- Функция «QUERY»
- Подготовка. Парсим данные. Делаем выпадающий список.
- Основная часть. Делаем запросы. Фильтруем данные.
- Функция «Importrange»
- Внутренняя ошибка импорта
- Функция JOIN
- Где взять еще примеры?
Одним из правил хорошего тона в современном мире, помимо умения открывать пиво зажигалкой, считается знание основ Google Sheets. Поэтому давай-ка научимся правильно использовать и читать данные в Гугл таблицах. Неправильно использовать ты и сам сможешь научиться.
Основы
Если ты новичок в этом бойцовском клубе, то держи несколько правил работы, с которых стоит начать, а уже потом научимся подставлять значение из списка и использовать query вместо ВПР. Если ты играешь уже на уровне «Ветеран», то свапай или скроль дальше.
Создание и открытие файла
Создать новую таблицу ты можешь, открыв Google Docs в браузере, либо перейти на https://docs.google.com/spreadsheets/. Ранее созданные таблицы будут отображаться списком ниже
Создавать новые и открывать созданные ранее таблицы можно на главной странице сервиса и через «Google Диск». По умолчанию, созданные через сервис Google Sheets файлы, сохраняются в корне Диска
Как сохранять прогресс?
Вся ваша работа, все изменения будут сохраняться автоматически. Об этом вас уведомит информационное сообщение справа от меню. Для этого, естественно, нужен доступ в интернет.
Помимо того, что ваш файл будет отображен в списке недавно просмотренных/созданных, физическая копия дублируется на Goolge Диск — https://drive.google.com/drive/.
Совместимость с Excel
Если у тебя на работе только допотопный Excel, а на домашнем ноуте прогрессивный Google Sheets, то не переживай — совместимость (в т.ч. обратная) есть. Можно загрузить .xls-файл на Google Диск и просто открыть его в Google таблицах. Для этого в Google Диске нажмите «Создать» и «Загрузить файлы» После загрузки xls-файла откройте его и выберите «Открыть с помощью Google Таблицы»
И наоборот, чтобы Гугл таблицу сохранить так, чтобы потом работать с ней в Excel, в меню «Файл» — «Скачать» выберите файл xlsx или csv
Можно откатиться на более раннюю версию
Если вам нужно вернуть более старую версию таблицы, то это можно сделать — в Гугл таблицах хранится вся история изменений. Чтобы посмотреть эти версии, кликните на «Все изменения сохранены на Goolge Диске» справа от основного меню
Откроется окно, где вы можете не только выбрать версию, но и увидеть её предварительный просмотр.
Функция «ВПР» (vlookup)
Производит поиск по первому столбцу диапазона и возвращает значение из найденной ячейки. VLOOKUP Имеет четыре аргумента:
- Запрос — то, что мы хотим найти в столбце
- Диапазон — ячейки, внутри которых мы будем искать
- Индекс – номер столбца (от начала диапазона), где нужно найти то, что мы вписали в запрос
- Сортировка — логическое значение (истина/ложь). Чтобы вернуть точные совпадения ставьте 0
Как она работает?
В листе «Пример1» мы разберем самую простую реализацию функции VLOOUKUP. У нас есть лист с данными. Нам необходимо, чтобы функция искала нужное значение в листе с «Данными». Для начала создадим выпадающий список в ячейке А2 («Данные» -> «Настроить проверку данных»). В качестве диапазона выберем первый столбец в листе «Данные»:
Далее в ячейку B2 вставляем формулу: =VLOOKUP(A2;’Данные’!A1:C24;3;0). Это позволит нам отображать 3 столбец (т.е. выручку) у выбранной нами пиццерии
Неточный поиск (наиболее близкое значение)
Иногда может потребоваться поиск не точного, а приблизительного значения. За это отвечает четвертый параметр VLOOKUP — сортировка. Для этого нужно поставить значение TRUE, тогда функция вернет значение, ближайшее к запрошенному (меньшее либо равное). См. лист «Пример2».
В зависимости от количества денег, функция подставляет ближайшую ачивку, которую мы можем купить. Единица в конце формулы означает тоже самое, что и FALSE (допускаются оба варианта написания).
Полная формула: =VLOOKUP(D6;A6:B10;2;1)
Поиск по нескольким условиям (по двум столбцам)
ВПР позволяет выполнять поиск по нескольким условиям. Например, нам необходимо показать выручку пиццерий у партнёров. Чтобы её вывести нужно учитывать оба этих параметра. Создадим выпадающие списки с ними. Смотрим лист «Пример3».
Теперь пишем формулу в ячейке G2: =ArrayFormula(VLOOKUP(E2&F2;{A2:A3&B2:B3C2:C3};2;0)). Первый параметр — значения которые мы ищем, там ссылаемся на выпадающие списки E2 и F2. Далее идёт массив (ArrayFormula), который здесь используется как раз для объединения столбцов. Бэкслеш используется для отделения столбцов.
Открыть этот пример VLOOKUP в Google Таблицах
Функции INDEX и MATCH
Проблема функции ВПР (VLOOKUP), как и его аналога ГПР (HLOOKUP) в том, что они ищут данные по одному столбцу или строке. Но что делать, если нам нужен поиск по нескольким параметрам, т.е. и по строке и по столбцу одновременно? Функция, которая умеет выдавать содержимое ячейки из таблицы по номеру строки и столбца — это функция INDEX, а в качестве её параметров часто используется MATCH (русскоязычный аналог — ПОИСКПОЗ).
Поиск по нескольким параметрам (ВПР 2D)
Откроем наш пример:
Для начала создадим выпадающие списки для нашего примера («Данные» -> «Настроить проверку данных»).
Теперь нам нужно, чтобы при выборе параметров нужные данные подставлялись. Как и писали выше, пишем функцию INDEX, внутри которой мы будем используем функцию MATCH.
Полная формула: =INDEX(B4:E12;MATCH(G5;B4:B12;0);MATCH(H5;B4:E4;0))
Теперь все данные подставляются. Как это работает? Первый параметр в функции INDEX — это массив поиска. Мы выбрали всю таблицу (вместе с названием столбцов) — B4:E12. Далее нужно указать еще два аргумента — строку и столбец. В обоих случаях мы используем для этого функцию MATCH
Функция MATCH в свою очередь содержит тоже два аргумента. Первый — это запрос, т.е. что мы будем вообще искать. Мы ссылаемся на выпадающие списки, т.е. что там выбрано, то и ищем (ячейки G5 и H5). Далее идёт диапазон. В первом случае мы выбрали столбец (ФИО Сотрудника — B4:B12). Во втором строку, где указаны наши «столбцы» (B4:E4)
Открыть этот пример INDEX в Google Таблицах
Важно!
С помощью такой формулы вы не сможете получить данные с двух столбцов — только столбец и строка, т.е. их пересечение. Например, в таблице ниже вы не сможете посчитать выручку, «запихнув» в INDEX два условия MATCH по столбцам «Партнёры» и «Пиццерии».
Формула =INDEX(A2:C24;MATCH(F5;A2:A24;);MATCH(G5;B2:B24;)) выдаст ошибку по третьему параметру в формуле. Чтобы использовать оба параметра при поиске см. раздел VLOOKUP чуть выше.
Функция FILTER
Функцией FILTER можно быстро отфильтровать нужные данные с таблицы, чтобы отобразить их в другом листе или таблице. FILTER отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям. Так же можно быстро сделать выпадающий список, который при выборе из него элемента отобразит нужные данные.
Фильтр по нужным данным
Для примера возьмем обычную таблицу, где список из несколько партнеров, у которых имеются несколько пиццерий. Как это дело отфильтровать?
Создаем «Лист2» и делаем скелет в нём. Указываем такие же столбцы, как и в оригинальной таблице. Далее в ячейке под столбцом «Партнеры» вставляем функцию =FILTER(‘Лист1’!A2:C24;‘Лист1’!A2:A24=«Партнер1»).
После этого таблица автоматически заполнится данными «Партнера 1».
Как это работает? В примере функция FILTER содержит два аргумента (может быть несколько условий, у нас оно одно)
- Диапазон — ‘Лист1’!A2:C24; — это тот самый диапазон, который мы будем фильтровать, т.е. все данные будут браться отсюда. В данном случае, это будет вся оригинальная таблица
- Условие — ‘Лист1’!A2:A24=»Партнер1″ — здесь мы указали условие, можно сказать, принцип по которому будет произведена фильтрация. Мы указали условие, что нам нужны данные по таблице там, где будет совпадение «Партнер1» в столбце А.
Фильтр через выпадающий список
Вариант выше плох тем, что «копировать» всю таблицу нужно будет вручную. Т.е. для каждого партнёра нужна будет своя формула, свой фильтр. Примерно будет выглядеть вот так:
Лучшим вариантом здесь будет выпадающий список, такой, чтобы при выборе партнёра, мы получали данные по пиццериям. Делаем это с помощью опять же с помощью FILTER. Встаем на ячейке H3 в «Листе2» и выбираем «Данные» -> «Настроить проверку данных». В качестве диапазона выбираем столбец «Партнеры» из «Листа1«.
У нас получится выпадающий список с партнёрами:
Теперь в ячейке H4 вставляем функцию: =FILTER(D2:F30;D2:D30=H3).
После этого у нас будут фильтроваться данные в зависимости от выбранного элемента в ячейке H3
Фильтр с чекбоксами
Можно сделать отображение информации по флажкам в чекбоксе. Для этого мы создадим небольшой скелет (Лист3) и добавим три чекбокса («Вставка» -> «Флажок»)
Дальше с помощью функции TRANSPOSE (она используется для транспортировки набора данных в другие ячейки) мы спарсим названия столбцов в наш перечень. Полная функция: =TRANSPOSE(‘Лист1’!A1:C1)
Теперь в ячейке H1 пишем основную формулу: =FILTER(‘Лист1’!A1:C;TRANSPOSE(F2:F4)). Она погружает данные с основной таблицы («Лист1»). При этом в условии фильтра указаны наши чекбоксы F2-F4, которые имеют два состояния — FALSE и TRUE. Т.е. данные будут отображаться с учетом состояния чекбоксов
Открыть этот пример FILTER в Google Таблицах
Функция «QUERY»
Функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL-запроса и отсортировать их. Сложность в том, что нужно знать синтаксис SQL, но самую базовую выборку делать очень легко. В сети много примеров бездарного использования функции QUERY, мы же сейчас сделаем крутой выпадающий список с фильтрацией данных
Подготовка. Парсим данные. Делаем выпадающий список.
Итак, у нас есть обычная таблица с несколькими столбцами данных. Лист так и называется — «Данные».
На листе «Проверка» мы с помощью функции UNIQUE отбираем уникальные значения для для столбца А и столбца B
Выбираем нужный диапазон для каждого столбца из листа «Данные»
В итоге, в листе «Проверка» у нас будут два столбца с уникальными значениями. Добавим к ним функцию SORT для, ожидаемо, сортировки. В итоге формулы у нас будут следующие: =SORT(UNIQUE(‘Данные’!A2:A)) и =SORT(UNIQUE(‘Данные’!B2:B))
Переходим на лист «Отчет». Подписываем наши два столбца, как Партнеры и Пиццерии. После чего, заходим в меню «Данные» и выбираем «Настроить проверку данных».
В открывшемся окне «Проверка данных» нужно нажать на иконку таблицы, чтобы выбрать диапазон данных.
Сам диапазон мы берем из листа «Проверка», где у нас собраны и отсортирована выборка из уникальных значений основной таблицы. Соответственно, для партнеров мы выбираем «Столбец А». Аналогичную операцию проделываем с пиццериями («Столбцом B»)
Не забудьте поставить пункт «Запрещать ввод данных» после выбора диапазона и нажимайте «Сохранить»:
После того, как вы это сделали, у нас будет готов шаблон для выпадающих списков в листе «Отчет»:
Основная часть. Делаем запросы. Фильтруем данные.
Теперь мы воспользуемся функцией Query, чтобы выцепить из основной таблицы (лист «Данные») нужные нам элементы. Пусть это будет «Партнёр» и «Город». Пишем такую формулу: =QUERY(‘Данные’!A1:D24;»SELECT * WHERE A = ‘Партнер1’ AND D = ‘Москва’»;1). И получаем отфильтрованные данные
С помощью формулы IF сделаем заготовку для нашего фильтра.
Для партнеров формула будет такой: =IF(A2=»Все партнеры»;»»;» AND LOWER(A) = LOWER(‘»&A2&»‘) «)
А для пиццерий такой: =IF(B2=«Все пиццерии»;«»;» AND LOWER(B) = LOWER(‘»&B2&«‘) «)
В итоге у нас получится выбор данных при изменении нашего выпадающего списка для каждого значения
А сейчас объединим эти две формулы, чтобы был один полноценный запрос:
=IF(A2=«Все партнеры»;«»;» AND LOWER(A) = LOWER(‘»&A2&«‘) «)&IF(B2=«Все пиццерии»;«»;» AND LOWER(B) = LOWER(‘»&B2&«‘) «)
Теперь нужно это всё перенести в функцию query, чтобы выпадающий список фильтровал запросы. Для этого нужно удалить часть запроса в query (на скрине как раз выделена эта часть):
Итоговая формула будет выглядеть вот так:=QUERY(‘Данные’!A1:D24;«SELECT * WHERE 1=1»&IF(A2=«Все партнеры»;«»;» AND LOWER(A) = LOWER(‘»&A2&«‘) «)&IF(B2=«Все пиццерии»;«»;» AND LOWER(B) = LOWER(‘»&B2&«‘) «);1)
Результатом станет то, что при выбора Партнера и пиццерии (можно оставить все), функция query будет показывать нам отфильтрованные данные из основной таблицы:
Открыть этот пример QUERY в Google таблицах
Функция «Importrange»
Главный чит в Google Sheets. Позволяет целиком скопировать данные из одной таблицы в другую. Одной формулой. Форматирование при этом не переносится — только данные. По сути, это обычная ссылка на другую таблицу, а не её «копия». Importrange — отличное решение при импорте данных из одного листа в другой.
Есть условие — для таблицы, на которую вы ссылаетесь, должен быть настроен доступ (разрешение) на извлечение данных. Доступ остается в силе до тех пор, пока пользователь, предоставивший доступ, не будет удален из источника. Imortrange отлично подойдет для того, чтобы работать с данными, к которому у вас есть доступ «Только для просмотра», а также чтобы сделать одну таблицу из множества других (импортировать можно «кусками»)
Откроем наш пример таблицы:
Функция Imortrange имеет всего два аргумента: ссылка на таблицу (ключ) и диапазон, который будем копировать. Ключ — вот эта часть ссылки на таблицу с которой вы хотите скопировать данные. Его можно скопировать из адресной строки:
Это и будет ключом. Дальше идёт диапазон. Тут собственно мы копируем либо всю таблицу, либо её часть. В нашем примере мы копируем данные на соседний лист, но всё это сработает и в случае, если вы будете это делать на совершенно новую/другую таблицу.
Полная формула: =IMPORTRANGE(«1J-Wk9BGHM4tVPkaOGuYT8VS3CUUt-AwoEWgrbsCv1vs»; «Лист1!A1:C9»)
Открыть этот пример Importrange в Google таблицах
Внутренняя ошибка импорта
Если возникает эта ошибка, значит у вас есть некоторая опечатка в формуле, хотя она по всем правилам написана правильно. Как такое получается?
Чтобы исправить эту ошибку необходимо исправить диапазон.
Не правильно: =IMPORTRANGE(«1J-Wk9BGHM4tVPkaOGuYT8VS3CUUt-AwoEWgrbsCv1vs»;‘Лист1’!A1:C9)
Правильно: =IMPORTRANGE(«1J-Wk9BGHM4tVPkaOGuYT8VS3CUUt-AwoEWgrbsCv1vs»; «Лист1!A1:C9»)
Функция JOIN
Объединяет значения в массиве данных, добавляя между ними разделитель. Два обязательных аргумента: разделитель и массив (или одно значение). Самый просто пример использования ниже:
Какой самый простой способ объединения данных различных ячеек в одну? Для этого можно использовать функцию JOIN, которая объединит нужные нам ячейки, а с помощью разделителя придадим удобочитаемый вид.
Полная формула: =JOIN (» «;A5:B5). В нашем случае разделителем является пробел. Растянем формулу на всю вертикаль таблицы и получим результат
С помощью JOIN можно «копировать» информацию из других ячеек в любой последовательности. Так же функцию можно использовать в связке с FILTER для того, чтобы получить все совпавшие элементы из столбца. Для примера возьмем другую таблицу на втором листе (лист «Пример2»)
Нам нужно посчитать квартальную выручку каждой пиццерии. Т.е. с помощью TEXTJOIN мы соединяем два параметра — месяц и выручку. В качестве диапазона данных (третий аргумент в функции TEXTJOIN) мы используем FILTER. Здесь мы использовали функцию TEXTJOIN (вместо «стандартной JOIN), чтобы избавиться от последнего разделителя в перечислении месяцев (столбец «Квартальная выручка»).
Полная формула: =TEXTJOIN(«, «;TRUE;FILTER($C$2:$C$24;$B$2:$B$24=G2))&SPLIT(» : «;» «)&SUM(FILTER($D$2:$D$24;$B$2:$B$24=G2))
Открыть этот пример JOIN в Google таблицах
Где взять еще примеры?
Огромное количество уникальных примеров вы можете найти в канале у Рената Шагабутдинова. Этот человек настоящий сенсей и виртуоз Гугл таблиц, автор трех книг, преподаватель в онлайн-школе Skillbox и человек, который автоматизировал бизнес-процессы в МТС и МИФ. Это действительно уникальная обновляемая (!) коллекция различных скриптов, формул. Обрати внимание, что нужен Телеграм. Ссылки в таблице ведут на канал в телеге, поэтому обзаводись. Открыть примеры в Google Sheets
Плюс к этому, если ты знаешь или хотя бы немного понимаешь английский, советуем тебе очень крутой канал на YouTube, где в отличном качестве можно посмотреть уроки про все основные функции — наслаждайтесь.
Как вам статья?
У нас есть несколько листов, которые полагаются на importrange для получения данных из других листов Google, начиная с этой недели у нас возникли проблемы с загрузкой некоторых из них, мы просто получаем внутреннюю ошибку #ref import range.
Я пробовал множество решений, но все они, похоже, работают только временно, после чего при обновлении запроса иногда удается получить данные, размер диапазона не является проблемой, поскольку проблема возникает как при большом импорте, так и при импорте. которые получают только 1 ячейку.
Пока лучшее решение, которое у меня есть, это удалить = из формулы, а затем добавить его обратно, чтобы снова загрузить данные, однако это длится всего около 30 минут, прежде чем importrange возвращается к той же ошибке.
В формулах нет ничего необычного
=IMPORTRANGE("sheet url","Update_Guide!b2:n2")
Я пробовал варианты заглавных букв для диапазонов, а также добавлял, если ошибка, чтобы попытаться загрузить вариант формулы
=iferror(IMPORTRANGE("sheet url","Update_Guide!b2:n2"),
IMPORTRANGE("sheet url","Update_Guide!B2:N2"))
Но, похоже, ничего не работает, а когда работает, решение не прилипает.
Будем очень признательны за любую помощь или понимание того, что может быть причиной этой проблемы.
В Google Sheet функция IMPORTRANGE для одного столбца в ярости
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1-bCoiKLjBlM5IGRo9wrdm", "sheet1!B:B") я получил
«Внутренняя ошибка диапазона импорта».
Но для
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1-bCoiKLjBlM5IGRo9wrdm", "sheet1!B:C"), работает.
Это ошибка? до сих пор это был третий раз, когда мне пришлось их много раз менять? Есть ли последовательное решение для него? Я использую это решение как временное
=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1-bCoiKLjBlM5IGRo9wrdm", "sheet1!B:C") , "Select Col1")
В заключение:
у меня уже 5 день не вылетает ошибка А по этой ссылке https://issuetracker.google.com/issues/204097721 теперь помечены как исправленные в системе отслеживания проблем.
9 ответов
Лучший ответ
Это не могло быть решением проблемы. Я построил целую платформу интеграции данных на листах и сильно полагаюсь на функциональность importrange для защиты доступа к источникам данных от пользователей. Теперь в последнее время #REF начал преследовать мои столы повсюду, и он делает все более или менее непригодным для использования.
Однозначно это ошибка или нехватка ресурсов.
0
michelek
17 Окт 2021 в 23:51
В дополнение к двойному ответу вы также должны ограничить свой диапазон, чтобы не было большого количества мертвых строк. Так что что-то вроде B:B5000 вместо B:B.
0
Zachary Girson
17 Окт 2021 в 21:23
Я не верю, что уклонение от кеша Google — это исправление или даже обходной путь.
Мы поддерживаем лист с функцией importrange на нескольких вкладках в течение многих лет, и только в течение последней недели возникла проблема.
Мы впервые заметили это в пятницу, а сегодня снова вернулись. В обоих случаях я не думаю, что сделал что-либо, чтобы исправить проблему, особенно сегодня. Я переместил формулу по листу, что привело к обновлению функции importrange, но это все равно привело к «внутренней ошибке диапазона импорта». Функция importrange отключилась на время (я не знаю, сколько сегодня, но я думаю, что это было не менее 15 минут), а затем разрешилась на всех вкладках без изменений.
Я думаю, что это определенно ошибка или Google возится с вещами на сервере. Может, нам нужно найти способ сделать все без использования importrange?
0
Ariel
19 Окт 2021 в 20:58
Есть грязное решение, которое можно использовать временно. Это не защищает вас полностью от этой проблемы, она все равно может возникнуть.
Этот:
IMPORTRANGE("id", "A:A")
Можно заменить на это (обратите внимание, что нижний регистр в том же диапазоне импортируется второй раз):
IFERROR(IMPORTRANGE("id", "A:A"), IMPORTRANGE("id", "A:a"))
Я видел это решение, размещенное здесь Виталием, он получил его от здесь.
1
kishkin
3 Ноя 2021 в 18:26
Я пробовал использовать следующее и решить свою проблему за последние 2 дня …
IFERROR(IMPORTRANGE("id", "A1:B20"),iferror(IMPORTRANGE("id", "A1:b20"),iferror(IMPORTRANGE("id", "a1:B20"),IMPORTRANGE("id", "a1:b20"))))
Значит, вспомните одни и те же функции 4 раза, используя CAPS / Non CAPS в именах диапазонов.
0
Atif Qayyum
3 Ноя 2021 в 18:47
Я пробую это решение, оно работает
До IMPORTRANGE("id", "a:b")
Сейчас IMPORTRANGE("id", "A:b")
1
Kamarul iCetak
31 Окт 2021 в 18:05
Я думаю, что лучшим решением здесь будет использовать
=Query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1-bCoiKLjBlM5IGRo9wrdm", "sheet1!B:C") , "Select Col1")
0
Majid soorani
18 Окт 2021 в 11:33
Я только что столкнулся с такой же ситуацией, но нашел решение, которое сработало для нас.
У нас был 1 лист, который импортировал диапазоны из 2 отдельных листов (вызов A и B).
A импортировал правильно. B показывал внутреннюю ошибку импорта.
Я также заметил, что на листе B мне не удалось просмотреть историю версий. Однако на листе A мне удалось просмотреть историю версий.
— Имеет смысл, что именно здесь может быть проблема, потому что ImportRange извлекается из самой последней сохраненной версии.
ИСПРАВЛЕНИЕ — Я очистил кэш Chrome за последние 7 дней (проблема возникла в течение 3 дней). Затем мне пришлось повторно войти в свою учетную запись, и диапазоны успешно импортировались!
Надеюсь, это поможет кому-то другому.
Примечание. Коллега приехал в Канаду из США 3 дня назад в то же время, когда обнаружилась внутренняя ошибка. Возможно может быть из-за международных ошибок сервера?? Это очень странная теория, но кто знает…
0
Drake
23 Июл 2022 в 00:30
Let’s say you opened your spreadsheet and found out that all your IMPORTRANGE formulas were not working? The previously imported data disappeared and refreshing won’t help get it back. Many Google Sheets users have already suffered from this known IMPORTRANGE drawback. To avoid it, you’d better use an alternative solution, which turns your spreadsheet into a sort of relational database. We’ll show you how to do this a bit later. But for now, let’s troubleshoot your IMPORTRANGE formula and fix the current error.
Common IMPORTRANGE internal errors
The common IMPORTRANGE errors are #ERROR! and #REF!. You can either read how to fix them or watch the video tutorial on the IMPORTRANGE function by Railsware Product Academy or do both. It’s up to you 🙂
#1 IMPORTRANGE #ERROR! – Formula parse error IMPORTRANGE
It’s kid’s stuff! Formula parse error means that you’ve made a mistake in the IMPORTRANGE formula syntax.
How to fix
Verify the formula syntax. Make sure to also validate the ID of the spreadsheet, as well as the sheet name specified in the range. These are the most common reasons for the formula parse error.
#2 IMPORTRANGE #REF! – Permission error or You don’t have permissions to access that sheet
This error states that “You don't have permissions to access that sheet.” In most cases, this means that you’re trying to import data from an unshared Google Sheets doc that is not stored on your Google Drive.
How to fix
Share the source spreadsheet with the owner of the target spreadsheet or make the file shareable with “Anyone with the link.“
#3 IMPORTRANGE #REF! – Allow access or You need to connect these sheets
This is more of a warning than an error. When you import a range from an unshared Google Sheets document stored on your Google Drive, IMPORTRANGE will require you to connect the source and the target sheets.
How to fix
Click the Allow access button to connect the sheets.
#4 IMPORTRANGE #Error! – IMPORTRANGE Result too large
You’ll see this error when you’re importing too many cells. Unfortunately, the exact amount of cells you can import with IMPORTRANGE is undisclosed. In our example, we tried to import 60 columns and 6000 rows (360,000 cells). After we decreased the data range to 4300 rows (258,000 cells), the IMPORTRANGE formula worked.
How to fix
Split the data range into two or more pieces, either vertically (by rows) or horizontally (by columns). Nest IMPORTRANGE formulas for each piece within the ARRAYFORMULA function as follows:
For horizontally split pieces (use commas between IMPORTRANGE formulas):
=ARRAYFORMULA({IMPORTRANGE("sheet-id","data-range-piece#1"),IMPORTRANGE("sheet-id","data-range-piece#2"),...})For vertically split pieces (use semicolons between IMPORTRANGE formulas):
=ARRAYFORMULA({IMPORTRANGE("sheet-id","data-range-piece#1);"IMPORTRANGE("sheet-id","data-range-piece#2");...})For example, here is a failed IMPORTRANGE formula:
=importrange("1bS7FGBbA7nInZJ2VBMaPxqf5B35RXpn-Z3vEcHlTwQo","Data!A:BH")
We split the data range "Data!A:BH" by columns into "Data!A:AM" and "Data!AN:BH" and applied the following formula:
=arrayformula({importrange("1bS7FGBbA7nInZJ2VBMaPxqf5B35RXpn-Z3vEcHlTwQo", "Data!A:AM"),importrange("1bS7FGBbA7nInZJ2VBMaPxqf5B35RXpn-Z3vEcHlTwQo", "Data!AN:BH")})
#5 IMPORTRANGE #REF! cannot find range or sheet for imported range
If you see the #REF! Error with a note “Cannot find range or sheet for imported range”, it’s most likely that either the sheet name is misspelled or you entered a wrong range.
If the formula worked before and then you saw this error, then the sheet was probably renamed or deleted, or the spreadsheet was removed.
How to fix
First of all, double-check the name of the sheet (both in the IMPORTRANGE formula and in your source spreadsheet) and the range you entered. In the vast majority of cases, this is the reason for this internal IMPORTRANGE error.
#6 IMPORTRANGE #REF! – Frozen formulas
This glitch is well known among Google Sheets users. Yesterday, your IMPORTRANGE formulas worked well. Today, they return #REF! and seem to be broken for no reason.
It happens randomly and sometimes fixes itself. For many years, Google has failed to find a stable solution to get rid of this ongoing issue with IMPORTRANGE.
How to fix
There are many approaches to fixing this issue:
- Hard refresh of the sheet and/or browser
- Re-adding the IMPORTRANGE formula to the same cell (use the Google Sheets shortcuts Ctrl+X and then Ctrl+V or clear the cell and use Ctrl+Z to restore it)
- Nest IMPORTRANGE with IFERROR
=IFERROR(IMPORTRANGE("sheet-id","range"))The sheet will reattempt the data import again and again automatically.
- Use the
=now()trick:- Insert a NOW formula (
=now()) in a random cell of the source and target spreadsheets - Insert an IMPORTRANGE formula that references the NOW formula of the other spreadsheet
- Go to File => Spreadsheet settings => Calculation and select Recalculation “On change and every minute“
- Insert a NOW formula (
- Split large chunks of data into pieces using ARRAYFORMULA + IMPORTRANGE, just like with Error! Result too large.
If you know other solutions/approaches to dealing with IMPORTRANGE #REF!, please share them with us to include in the article.
If you need more information about this function, check out our IMPORTRANGE Tutorial.
How to fix all IMPORTRANGE errors at once: A peace of mind solution
The best way to fix IMPORTRANGE fails is to avoid them. Let’s say you have 100 source sheets from which you import data to 30 sheets using IMPORTRANGE formulas. From the target sheets, you import data to another 10 sheets again with IMPORTRANGE. If all these formulas are stuck, you will face issues with troubleshooting and fixing them!
IMPORTRANGE is a function, and it takes some time to process calculations, which slows down the general performance of a spreadsheet. Instead, you can use the IMPORTRANGE alternative – Google Sheets integration. It is free of the mentioned IMPORTRANGE performance issues since no calculations are performed in the spreadsheet. It pulls the static data and saves it in your spreadsheet, just in case anything goes wrong.
To set up the Google Sheets integration, you need Coupler.io, a solution to import data from third-party data sources such as spreadsheets, CSV files, and numerous apps. It is available as a web app and a Google Sheets add-on. For the latter, you’ll need to install Coupler.io the add-on from the Google Workspace Marketplace.
Import data between Google Sheets with Google Sheets integration
Sign up to Coupler.io, click Add new importer, then select Google Sheets as both source and destination apps.
Complete the setup as follows:
Source
- Connect to your Google account and select a file on your Google Drive and a sheet to transfer data from. You can select multiple sheets that will be merged into one master view.
Optionally, you can choose a range in the spreadsheet you want to export data from, i.e. A1:Z9.
Destination
- Connect to your Google account and select a file on your Google Drive and a sheet to transfer data to. You can select an existing sheet, or enter a name to create a new one.
Optionally, you can choose the import mode for your data: you can replace your previous information or append new rows under the last imported entries. Read more about optional destination setup features.
You can run the import right away if you click Save and Run. If you want to automate data import on a schedule, toggle on the Automatic data refresh and customize the schedule.
Schedule
- Select Interval (from 15 minutes to every month)
- Select Days of the week
- Select Time preferences
- Schedule Time zone
In the end, click Save & Run and welcome your data to the spreadsheet.
IMPORTRANGE or Coupler.io: Which is better?
You’d better say NO to IMPORTRANGE and YES to Coupler.io if:
- You import data recurrently
- You import huge amounts of data
- You import data from other sources like CSV, Pipedrive, etc.
- It’s crucial for you to have access to data no matter what
- You’re already tired of IMPORTRANGE issues
In all other cases, you can go with IMPORTRANGE easily because it’s a good function, though not reliable at all. Make the right choice and good luck with your data!
-
Content Manager at Coupler.io
Back to Blog
Focus on your business
goals while we take care of your data!
Try Coupler.io