Дополнительная информация о функциях импорта
В Google Таблицах доступны функции импорта, позволяющие оптимизировать работу с таблицами. К ним относятся:
- IMPORTHTML
- IMPORTDATA
- IMPORTFEED
- IMPORTXML
- IMPORTRANGE
Лимиты на использование
Если функции импорта потребляют слишком много трафика, появится следующее сообщение: «Ошибка. Из-за большого количества запросов загрузка данных может занять некоторое время. Советуем сократить число функций IMPORTHTML, IMPORTDATA, IMPORTFEED и IMPORTXML в созданных таблицах».
Для владельцев документов действуют строгие ограничения в отношении работы с таблицами. Именно поэтому таким пользователям необходимо разумно использовать формулы и функции в таблицах. В квоте также может учитываться изменение, внесенное соавтором.
Чтобы устранить сообщения об ошибках, уменьшите количество изменений в исходном диапазоне, из которого импортирована функция. Например, если разрешенное значение для исходного диапазона в функции =IMPORTDATA(исходный диапазон) часто обновляется, вы должны разрешить внешние вызовы. Они, в свою очередь, могут привести к снижению скорости загрузки.
Актуальность данных
Чтобы данные в таблицах поддерживались в актуальном состоянии и это не препятствовало работе с ними, в отношении функций IMPORTDATA, IMPORTHTML и IMPORTXML действуют следующие правила:
- Все три функции автоматически проверяют наличие обновлений каждый час, пока документ открыт, даже если в формулу и лист не вносились изменения.
- Если вы удаляете и повторно добавляете ячейки или перезаписываете их с помощью одной и той же формулы, функции обновляются.
Важно! Если вы откроете и обновите документ, это не приведет к обновлению функций.
Пересчитываемые функции
При использовании функции импорта в ячейке может отобразиться надпись «#ERROR!» с сообщением «Ошибка. В качестве аргумента этой функции нельзя указать ячейку, содержащую строки NOW(), RAND() или RANDBETWEEN()«.
Чтобы избежать чрезмерного потребления трафика, функции импорта не могут напрямую или косвенно ссылаться на пересчитываемую функцию, такую как NOW, RAND или RANDBETWEEN, поскольку она часто обновляются.
Если вы получили указанное выше сообщение об ошибке, но все равно хотите посмотреть результаты пересчитываемой функции, скопируйте их. Для этого нажмите Специальная вставка
Только значения.
Важно! После этого все значения станут статичными. Например, если вы скопируете результат функции NOW и вставите только значения, они не будут обновляться.
Примечание. Единственным исключением является пересчитываемая функция TODAY, которая обновляется один раз в день.
Сообщение об ошибке «Результат слишком большой»
Если вы получили это сообщение при использовании функции IMPORTXML, сократите объем данных, который возвращает запрос XPATH.
Статьи по теме
- IMPORTFEED
- IMPORTXML
- IMPORTHTML
- IMPORTDATA
- IMPORTRANGE
Using the answer of Sam and reading documentation, I found the way how to get result of BIG DATA without error. For that you need to make an export step by step. In one query. For example if you need to export data sheet!A3:X100000.
Try to do the following:
first make a query and select only
=QUERY(importrange("link_sheet", "sheet!A3:X10000"), "select *", 0);
after get result just edit query from
=QUERY(importrange("link_sheet", "sheet!A3:X10000"), "select *", 0);
to
=QUERY(importrange("link_sheet", "sheet!A3:X20000"), "select *", 0);
after getting data edit a query again
=QUERY(importrange("link_sheet", "sheet!A3:X300000"), "select *", 0);
and continue while you won’t rich
=QUERY(importrange("link_sheet", "sheet!A3:X100000"), "select *", 0);
with that way I could to import around 800 000 cells with data. For my task it was enough, but I think If I need longer result data, I could continue and it will works.
You should also remember that Google Spreadsheets have a limit on one document maximum can have only 2 million cells.
If you are importing large volumes of data, you may eventually encounter the IMPORTRANGE “Result too large” error.
We can use the VSTACK or HSTACK functions to work around the “Result too large” error in Google Sheets.
The IMPORTRANGE function returns the #ERROR! value, when the number of cells to import is above its capability.
A quick solution to this problem is to import the data part by part and stack them. You can also use the QUERY function together with each imported data before stacking to enhance the performance.
The number of cells that can be imported using the IMPORTRANGE function is limited. This limit may vary from user to user depending on the types of data in the cells.
Since there is no official documentation on the exact limit, it is difficult to say exactly how many cells can be imported.
I recently got the “Result too large” error when I tried to import 26 columns and 20,000 rows. If I specified 12,606 rows with that many columns, the formula worked flawlessly. However, for my particular data, it could not handle more than that.
I solved this problem in two ways. In the first approach, I imported every 10,000 rows and 26 columns and stacked them vertically.
In the second approach, I imported 20,000 rows and 13 columns each and stacked them horizontally.
Below in the next section, we will see how to use the VSTACK and HSTACK functions with multiple IMPORTRANGE formulas to solve the “Result too large” error.
VSTACK with IMPORTRANGE and the Result Too Large Error
Formula Example:
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",VSTACK(IMPORTRANGE(url,"Sheet2!A1:Z10000"),IMPORTRANGE(url,"Sheet2!A10001:Z20000")))Syntax: IMPORTRANGE(spreadsheet_url, range_string)
The role of LET in this formula is to assign the “spreadsheet_url” to the name url. This allows us to avoid repeating it in IMPORTRANGES.
In short, we have assigned the “spreadsheet_url” to the name url outside the IMPORTRANGE function. Therefore, within the function, we can use the name url instead. This dramatically reduces the length of the formula.
There are two IMPORTRANGE formulas nested within the VSTACK function.
IMPORTRANGE(url,"Sheet2!A1:Z10000")IMPORTRANGE(url,"Sheet2!A10001:Z20000")
You can add more IMPORTRANGE formulas after the second one, separated by a comma.
Let’s now move on to the HSTACK solution to the #ERROR! Result too large errors.
HSTACK with IMPORTRANGE and the Result Too Large Error
Formula Example:
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",HSTACK(IMPORTRANGE(url,"Sheet2!A1:M20000"),IMPORTRANGE(url,"Sheet2!N1:Z20000")))There are two IMPORTRANGE formulas nested within the HSTACK function. The first formula returns the first 13 columns, and the second formula returns the next 13 columns.
IMPORTRANGE(url,"Sheet2!A1:M20000")IMPORTRANGE(url,"Sheet2!N1:Z20000")
Here also, you can add more IMPORTRANGE formulas after the second one, separated by a comma.
How can I improve the performance of importing large volumes of data?
We now have two solutions to solve the IMPORTRANGE Result too large error. These are the VSTACK + IMPORTRANGE and HSTACK + IMPORTRANGE methods.
However, there are certain things that you can consider to improve their performance.
One option is to use the QUERY function with them to filter rows of IMPORTRANGE imported data, thereby cutting short its length.
When you use the QUERY function with VSTACK + IMPORTRANGE, you can use it to filter rows conditionally. This can shrink the data. For example, you can use the following formula to filter out blank rows based on column 5:
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",VSTACK(QUERY(IMPORTRANGE(url,"Sheet2!A1:Z10000"),"Select * where Col5 is not null"),QUERY(IMPORTRANGE(url,"Sheet2!A10001:Z20000"),"Select * where Col5 is not null")))What about using the QUERY function with HSTACK and IMPORTRANGE? You can use this combination to remove columns.
Here is an example formula:
=LET(url,"https://docs.google.com/spreadsheets/d/1OiMbGNDOkzHu5DWaUvFSj_5cdq3rhvqu-ocWeXhbflo/edit#gid=1707494620",HSTACK(QUERY(IMPORTRANGE(url,"Sheet2!A1:M20000"),"Select Col1,Col2,Col3"),QUERY(IMPORTRANGE(url,"Sheet2!N1:Z20000"),"Select Col10,Col11,Col12")))Remember one thing: each formula returns 13 columns. So, in QUERY, you can select columns from 1 to 13 in each formula, not 1-13 in the first formula and 14-26 in the second formula.
If you want to learn more about filtering IMPORTRANGE, please check out my following tutorials.
- IMPORTRANGE Within FILTER Function in Google Sheets.
- How to Use IMPORTRANGE Function with Conditions in Google Sheets.
- How to Use Query with Importrange in Google Sheets.
Conclusion
In short, these are the methods to solve the IMPORTRANGE “Result too large” errors and enhance performance:
- Split the data into smaller chunks and import each chunk. Join the data then using the VSTACK or HSTACK function. This can reduce the amount of data that is imported at once.
- Use QUERY to filter rows conditionally. This can shrink the data.
- Use QUERY to remove columns. This can reduce the number of columns.
Вопрос
Я пытаюсь выполнить IMPORTRANGE из диапазона, содержащего 240 000 ячеек (40 столбцов и 6 000 строк). Функция IMPORTRANGE выдает ошибку «Результаты слишком велики».
Я не могу найти документацию по ограничениям этой функции.
Каковы ограничения функции IMPORTRANGE?
Как я могу обойти это, чтобы я мог импортировать эти данные в свой лист?
Ответ на вопрос
14-го февраля 2017 в 10:01
#37759634
У меня тоже была похожая проблема.
Попробуйте разделить диапазон импорта с помощью формулы массива.
Пример:
становится
Проверьте это с различными размерами данных, чтобы получить самую короткую версию, и это должно сделать то, что вам нужно.
Решение / Ответ
2-го февраля 2017 в 10:23
#37759633
Мои эксперименты показывают, что существует максимальное ограничение на количество элементов, которые вы можете импортировать с помощью функции importrange(), которое не связано с общим количеством ячеек в вашем листе Google.
Эта цифра, похоже, составляет около 175 000 ячеек. Я не уверен, что это официальное максимальное значение, но это самое большее, что я смог импортировать без получения ошибки ‘слишком большой’.
Ответ на вопрос
8-го марта 2017 в 2:43
#37759635
Пустые ячейки могут быть фактором. Мы наблюдали разрушение импортного диапазона при 23573×11 или 259 тыс. ячеек, типичный рост составляет 10 или около того строк ежедневно, так что мы уже давно превысили 250 тыс. ячеек. Один столбец в основном пустой, в паре других есть несколько пустых.
Я не смог заставить ARRAYFORMULA разобрать данные, как показано выше, или с другими предположениями, поэтому я использовал это в скрытой вкладке «Ingest».
=importrange("Лист", "A1:K10000") в ячейке A1
=importrange("Лист", "A10001:K") в ячейке A10001
Моя рабочая вкладка/вкладка презентации использует
чтобы обеспечить постоянное форматирование, наши исходные листы перезаписываются ежедневно.
Ответ на вопрос
27-го апреля 2018 в 11:04
#37759637
Используя ответ Сэма и читая документацию, я нашел способ, как получить результат BIG DATA без ошибок. Для этого нужно сделать экспорт шаг за шагом. В одном запросе. Например, если вам нужно экспортировать данные sheet!A3:X100000.
Попробуйте сделать следующее:
сначала сделайте запрос и выберите только
после получения результата просто отредактируйте запрос с
на
после получения данных отредактируйте запрос еще раз
и продолжать, пока вы не разбогатеете
Таким образом я смог импортировать около 800 000 ячеек с данными. Для моей задачи этого было достаточно, но я думаю, если мне понадобятся более длинные данные, я смогу продолжить и это сработает.
Вы также должны помнить, что у Google Spreadsheets есть ограничение на то, что в одном документе может быть не более 2 миллионов ячеек.
Ответ на вопрос
23-го мая 2017 в 7:24
#37759636
Из моего опыта использования IMPORTRANGE, количество ячеек не было причиной вообще, но каждый раз, когда я превышал 36 столбцов, он терпел неудачу. Мои результаты могли быть 600 строк или 6000 строк до тех пор, пока я не превышал 36 столбцов. Как ни странно, это можно обойти, комбинируя функции IMPORTRANGE.
Пример: =QUERY({IMPORTRANGE("Spreadsheet_Key", "Sheet1!A:AI") , IMPORTRANGE("Spreadsheet_Key", "Sheet1!AJ:AM")}, "WHERE Col38= 'test'").
Обратите внимание на скобки {}, используемые до и после двух функций IMPORTRANGE
#google-sheets #google-sheets-formula
#google-sheets #google-sheets-формула
Вопрос:
Итак, я использую query функцию с importrange и совсем недавно получил сообщение об ошибке «Результат слишком большой». Данные, которые я импортирую из исходного листа, содержат более 10500 строк данных.
=QUERY(IMPORTRANGE("sheet_name", "Social media posts!A:AS"),"SELECT Col1, Col43, Col23, Col16, Col12, Col44, Col45, Col2, Col3 WHERE Col2='instagram'", 1)
Комментарии:
1. попробуйте разделить его на пакеты и использовать
IFвместоQUERYэтого, чтобы это могло сработать
Ответ №1:
Я могу предложить 2 возможных обходных пути.
Разделите importrange на две части:
={QUERY(IMPORTRANGE("url/id", "Social media posts!A:AS5000"),"SELECT Col1, Col43, Col23, Col16, Col12, Col44, Col45, Col2, Col3 WHERE Col2='instagram'", 1);
QUERY(IMPORTRANGE("url/id", "Social media posts!A5001:AS"),"SELECT Col1, Col43, Col23, Col16, Col12, Col44, Col45, Col2, Col3 WHERE Col2='instagram'", 1)}
Разделите запрос на две части:
={QUERY(IMPORTRANGE("url/id", "Social media posts!A:AS"),"SELECT Col1, Col43, Col23, Col16 WHERE Col2='instagram'", 1),
QUERY(IMPORTRANGE("url/id", "Social media posts!A:AS"),"SELECT Col12, Col44, Col45, Col2, Col3 WHERE Col2='instagram'", 1)}