Google Spreadsheet Import Range #REF! Error (randomly)

Question

This problem was not real for a while, it just happens so often at random moments, nothing changes. I also do not know how to reproduce the problem, but I will give a detailed explanation of what is happening.

Normally, the importRange function works just fine and looks something like this:

Correctly printed table text. I used this strategy on Google Spreadsheets for a while.

Each time, as a rule, Google Spreadsheets seems to break, or at least the importRange function does ... and produces something like the following:

Google seemed to freeze from time to time, and this is happening. I tried setting importRange parameters to! X1: X ,! X2: X5 by changing the title of the imported sheet, etc.

In the past, the transition from X: X to! X1: X fixed some columns, but not always all of them. The only reliable solution to fix this that I found is to recreate both tables. Since the source code (the one that is imported) ultimately becomes a report, I would like to save my boss from the extra effort associated with several spreadsheets, and most likely fix it and do it.

Does anyone know the potential cause of this disruption? Sometimes he corrects himself, sometimes it is not. This happens randomly, and only for some spreadsheets (I have a set of 8 or so that everyone uses importRange , and at the same time no more than 2). Therefore, I honestly do not know where this comes from.

Side note . Google as a whole today does not respond a little to the requests of all its services - my mail service in App Engine for Google Apps has decreased by about half an hour. Can a resource / network attacker restriction cause this behavior in tables?

Refresh . I tried to import Tables from the same table into the same data (i.e. I gave the importRange function a different spreadsheet). Although the key and range are valid if imported into another table, in my original (one full #REF!) This range is imported incorrectly. This makes me think that this failure can only apply to one Table, where any attempt to importRange from any other source does not work .

Answer 1

It's a bit late, but I found it in a search so that it could help someone else - try something like this:

 =IF(ISERROR(ImportRange(SpreadSheet_GUID,"Bookings!p:P")),IF(ISERROR(ImportRange(SpreadSheet_GUID,"Bookings!P:p")),ImportRange(SpreadSheet_GUID,"Bookings!P:P"),ImportRange(SpreadSheet_GUID,"Bookings!P:p")),ImportRange(SpreadSheet_GUID,"Bookings!p:P")) 

Basically, the idea is to get Google to reuse the source address options (upper / lower case letters).

Answer 2

Problems with the IMPORTRANGE () function were in some ways a problem. Over the years, several errors have appeared, and Google decided to find a stable solution, so it works the way it was developed.

Until then, this can help you:

Instead of editing a cell, creating a new spreadsheet or doing something else to reload the page (almost like playing the lottery), you can force Google sheets to automatically reload the function automatically when it does not load:

Wrap the IMPORTRANGE () function, which causes problems with the IFERROR () function. Thus, the sheet tries to import, and only if it fails does it try again. The function can be inserted into several attempts in a row.

You can add named ranges to the source data (right-click-source> define-named-range) for ease. For example, you can create 3 different named ranges for the range "J: J". Name them "J", "Ja" and "Jay", then you call your IFERROR () function on the sheet you want to import:

 =IFERROR( IMPORTRANGE( "SheetID","J" ), IFERROR( IMPORTRANGE( "SheetID","Ja" ), IFERROR( IMPORTRANGE( "SheetID","Jay" ), IFERROR( IMPORTRANGE( "SheetID", "'TabName'!J:J" ), IFERROR( IMPORTRANGE( "SheetID", "'TabName'!j:j" ))))) 

This formula will try to import and deliver it if it succeeds, and if it does not automatically retry 5 times in a row, which does not guarantee that you will succeed, but if you have a 50% chance of success, your import is nested 5 in a row should give you a 96% chance, which is much better. My personal experience was that it did not work, since I made the IFERROR () socket.

Hope this helps.

Answer 3

I had similar problems and they were resolved NOT using public links to spreadsheets. I noticed that the links are open for sharing and the links from the address bar when opening the si sheet.

I also noticed some difficulties when opening shared tables from my other Google accounts. I had to re-enable access to some tables that I imported.

Sorry for my English.

Answer 4

This is true, in my case just Ctrl + X, waiting for a second, and then pasting the formula back in, the Sheets mechanism re-imports and then successfully imports. If you cannot perform this procedure manually, you must use the Tim solution.

Answer 5

I had the same problem and found a solution!

• Reduce the size of the source file

• To reduce, split it and reference the import range again

Now you can see the imported range!

If you think this is not the case, chances may be

• Your cell is busy, so remove all formatting and clear all cells before importing the range.

• Still not working? May God help you!

Answer 6

I tried to add:

 if(ISERROR(importrange... 

and it works.

This does not mean that the error disappears, but when this happens, you just need to reload the sheet and wait a while, and let it update itself.

This is much more practical than manually replacing a capital letter with a small one.

Answer 7

Here is a simple workaround I found for this error:

I do a Find-and-Replace ("search by formulas") for "=" wherever there is an error importrange (= REF!) (Or just for the whole sheet), and replace everything with "#" in order to make it all text, not formula. Then I do another search and replace in the same area and again replace "#" with "=". This successively solves the problem.

Answer 8

I found this solution works fine for me:

In both spreadsheets, paste the equation = now () into a random cell, say Z1. In both spreadsheets, insert the = importrange () function, which refers to the now function of another spreadsheet. Go to the settings of your table and select the recount for every minute. I tried many other suggestions, including using the = now () function, the now URL trick in this topic or Apps Script to insert arbitrary text at a given interval, but nothing would force importrange to update, except manually editing the source code. a sheet.

https://webapps.stackexchange.com/questions/60324/how-can-i-get-google-sheets-to-auto-update-a-reference-to-another-sheet

Answer 9

I was able to fix = REF! error when using "Edit"> "Find and replace"> "Find" = "Replace with" # "> Search in the selected range> Also search in formulas> Find> Finish. The error was fixed without actually launching the replacement command.

Answer 10

Thanks for all your advice. Indeed, replacing letters in a formula seems to get around this problem.

This is a pretty old problem that still persists ... Why isn’t Google working on it?

Sincerely.

Answer 11

It seems that when I take out the apostrophe from the header of the source sheet, the problem is resolved. Perhaps deleting any characters except numbers and letters is also a problem for your problem.