5

IMPORTRANGE is not updating on one of my sheets. I've been using it on hundreds of other sheets with no update issues but it is no longer functioning for me.

  • The sheet is set to update every minute
  • I have tried the now() trick to get it to update on a recalculation

The formula has to be removed then pasted back in for it to update. If I do an IMPORTRANGE from the sheet to itself, it will update as changes are made.

Any idea what is causing this, or how to fix it?

6

This was an issue with the sharing settings on the sheets.

It looks like a sheet that is set to Specific People in the sharing settings cannot be automatically imported into a second sheet. If you have access to the first sheet, you can set up an IMPORTRANGE, but it will not update.

The sharing permissions on the first sheet need to be set in such a way as it is always accessible by the 2nd sheet without violating any sharing permissions.

In my case, I set it to Anyone in my organization with a link.

  • I have spreadsheets where importrange is working between sheets that are both set to "specific people" access. Furthermore, due to the fact that you mention an option "anyone in my organization" makes it sound like you have approached this from the context of Google Apps for business, and not the "plain" google Docs, which might be a factor as well. We are currently wondering if it makes a difference between document owner or someone else, adding the import range function. – Bram Luyten Jan 14 at 12:13
4

I just ran into this same issue...we've never had an issue with importrange working across MANY sheets which are selectively shared, but today it randomly stopped working. I ran upon this article in my research.

Upon further investigation, I found that only one spreadsheet that was failing to export--the importrange functions on that sheet were still working just fine to pull data in from other places.

Just before it failed, I'd placed edit restrictions on a few columns to keep other users from deleting critical formulas. I went back and removed those restrictions, and the importrange worked just fine again.

So in a long, roundabout story...it appears that sharing with specific users doesn't create the issue, it's restricting users from part of the sheet. It could be just restricting the sheet in question, or it could include the whole workbook. I fixed my issue and haven't dug into it further.

3

It's not about the way you shared; it's about the locale that you use for each spreadsheet.

Go to File -> Spreadsheet Setting -> and make sure you have same Locale and Time zone for both Google sheets. The one you put your data and calculations, and the one you shared with the others, and you used importrange() on it.

  • I had similar problem. I changed the spreadsheets to whoever has a link can view. That didn't seem to help, and all the spreadsheets were set to the same time locale. So, I changed them all to a different time locale and it all worked. – Grubbmeister May 1 at 7:42
1

I had the same problem and removed the sheet protection and it started updating.

  • @MARKMYANSWER To me, this is an answer because it offers a solution ("removed the sheet protection"). It's definitely not a new question. – jonsca Jan 8 at 23:28
-1

The answer is quite simple. You need to use the URL link generated in the Sharing settings when creating your importrange() formula. You have to use the option "Anyone in my organization with a link". If you use the URL shown in your browser, the system may not see the importrange function call as having the correct permissions.

  • Actually IMPORTRANGE could use the spreadsheet ID. – Rubén Dec 3 '18 at 3:00

protected by Community Jul 12 at 20:37

Thank you for your interest in this question. Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).

Would you like to answer one of these unanswered questions instead?

Not the answer you're looking for? Browse other questions tagged or ask your own question.