2

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.

Improve this question

5 Answers 5

Reset to default
3

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/

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Initially importrange seems like a great formula, but to be honest, I hesitate to use it these days because of how unreliable it can be. In my experience importrange just sometimes stops working for seemingly no reason, which often ends up breaking downstream formulas/dashboards/visualizations/etc. Often times, I've found just clearing the importrange and manually entering it again fixes the issue. Sometimes it fixes itself. There may be legitimate causes like you pointed out (or like if the source data is too large for importrange), but it's difficult to debug in my opinion. If you're only struggling with importing data from one sheet to another within one spreadsheet, I would recommend using something like filter instead which works pretty reliably (you can just filter out blank cells to get all the data). If you end up running into issues with importrange across different spreadsheets, I would recommend looking into a Google Apps Script solution (although I understand that requires more technical knowledge).

Improve this answer

1 Comment

Thank you for sharing your experience. Mine is basically the same so I can't even add anything. I believe there would be much less incoherence with importrange if Google provided a way to calculate their limits at a certain moment in time. However, as it is seemingly not possible, I will be leaning into rebuilding the architecture of my sheets or Apps Script indeed if no other information would be found.
2

I also had a Google Sheet that was working with IMPORTRANGE() that was working until July 19th.
I was able to make an exact copy of the workbook to resolve the error for investigation purposes.

The initial workbook still will not load data despite several attempts and theories so we are moving existing processes to point to the new workbook. Just a comment to flag that this issue began on the same day as at least @Bryan B and the OP.

Improve this answer

Comments

2

In my case it was some sort of cache issue. After the target spreadsheet was modified, the data was imported.

Improve this answer

Comments

1

I have had the same issue since around the 19th. Multiple sheets have failed with the same issue "Error, Loading data...". I have had a ticket open with a Google Workspace group for the past 3 days and they have not been able to provide any answers. It's crippling to our operations. Google sheets is unreliable. Time to move to a more reliable patform.

Improve this answer

1 Comment

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.