Search code examples
google-sheetsgoogle-sheets-formula

ERROR "Loading data" with IMPORTRANGE function in Google Sheets

I have a document (let's call it "original") with IMPORTRANGE function on it. It takes data from one sheet in the original and imports it to another sheet in the original. It worked fine until some moment (there weren't any updates or changes to the document) and then it just stopped loading data with "ERROR Loading data". I checked every possible problem with IMPORTRANGE function and none of them seem to be causing it. Also in other documents, I can use IMPORTRANGE to import data from original but I can not do this in other sheets of this original document. (So I can't import data from sheet 1 to sheet 2 of original, but can import data from sheet 1 to other documents) Also, I can import data from other documents with IMPORTRANGE to this original document just fine.

As far as I understand, this can be caused only by the limitation of unique "shared" entities on a document (https://support.google.com/docs/thread/2207155?msgid=7345711).

My questions are:

  • Am I missing something else that can cause such behavior?
  • If this is indeed access limitations, can it be confirmed somehow? Can you count your number of accesses at the moment?
  • How it can be fixed? (Solutions in the attached discussion are not great, to say the least)

Error image

EDIT 1

Maybe the following information could bring needed insight into my problem. I could not import data with IMPORTRANGE from Sheet 1 to Sheet 2 of the original document.

But I can import data from Sheet 1 of the original document to some other document and then import from that one to Sheet 2.

So basically it does not allow to make direct import, but allows import through other documents somehow.

EDIT 2

I was not able to resolve these problems with IMPORTRANGE, but I found many more suitable solutions to this particular case.

Firstly, as Logans' answer suggests, you could use QUERY to get data from sheets within one spreadsheet.

Secondly, the solution I found is arrays or {}. You can retrieve data from Sheet1 to Sheet2 by simply adding ={'Sheet1'!A:B}.

Hopefully, this will help those, who use IMPORTRANGE in the scope of one spreadsheet.

Solution

  • Looks like the error loading data is an issue with the importrange() function itself which has inconsistent behaviour, since it is built to pull data from other spreadsheets. I suggest just using QUERY() since you are pulling data from the same spreadsheet, there is no need to use importrange.

    More details here: Google Sheets: Use IMPORTRANGE Within Same Spreadsheet

    Otherwise, I'd suggest you raise your concern here:
    https://issuetracker.google.com/