Why do two users sometimes see different values from importrange?

0
votes

I have a very strange problem, the description can be a bit chaotic.

I have two spreadsheets A and B. One sheet inside A is importing data with importrange from the B. The problem is that sometimes two user see different values in that sheet.

Details:
I have two scripts inside A:

  • one that copies the values from the imported range to another sheet
  • one that calls that WebApp that changes data in B.

When two users have the A open and one of them makes changes to the B via script and then uses the other script which copies the values, everything is fine. But if one user closes the A, the other makes changes in the B with the script and when the first user reopens the spreadsheet, he will see new values, but if he uses the values copying script, the range containing the values from importrange will show old values. Oddly enough, the script that copies values from that range will copy the new correct values even if we don't see them.

The problem goes away when I manually make some changes to spreadsheet B. I don't know, it's like spreadsheet B doesn't refresh when I make changes via script or something

Do you have any ideas what is the reason for this and maybe how to solve it. I think that I can get round this problem but I would not.

1
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaimportrange
Try adding SpreadsheetApp.flush(). May or may not work. – TheMaster
I've tried it before. Now I've added this line a dozen times in multiple places just to be sure, but still work the same – Maciek
When a sheet is open importrange recalculates every 30 minutes. This changes if the sheet is loaded though, and will recalculate on open. This can result in two different users seeing different data. – Rafa Guillermo
It's interesting. When I make changes to my spreadsheet B with a script while A is open all the time, I can see the changes in the imported range after a few seconds. – Maciek

1 Answers

0
votes

Two Google Sheets users might see diffent values on the same spreasheet because formula calculation (including those that make API calls like IMPORTRANGE) are triggered by user actions like opening or edited the spreadsheet.

Some function also might recalculated by changes made by scripts so it's actually possible that an script could "see" different values than those that are seeing by the users.

If you need that the users see the same values, should consider to periodically "freeze" them i.e. make a copy of the spreadsheet then replace the formulas by their results.

Another option is to share the screen rather than relaing on the "real time" collaboration feature of Google Sheets.