QUERY+IMPORTRANGE function sometimes works and sometime doesn't

Question

I have 3 spreadsheets.

• First spreadsheet is used for input values.
• Second spreadsheet is used for calculate and query data.
• Third spreadsheet is used for showing summary.

In the second spreadsheet, I created a sheet and put a function like this in a cell

=QUERY(IMPORTRANGE("sheetID","sheet1!A:A"),"select Col1,count(Col1) where Col1 matches '"&join("|",QUERY({SKU!A:A;SKU!B:B},"select * where Col1<>''"))&"' GROUP BY Col1",)

Then, in the third spreadsheet, I use importRange function to get the data from the second spreadsheet.

The problem is sometimes QUERY+IMPORTRANGE function in the second spreadsheet works well but sometimes it doesn't. If it doesn't, the data in the third spreadsheet will not show anything as well.

I fixed this problem by having to open the second spreadsheet and third spreadsheet together at the same time and re-input function in the second spreadsheet by copy and paste function. Then the problem will be fixed.

I would like to know what causes this problem and how to fix it in the right way.

Answer 1

the easiest fix would be if you import all importranges in some sheet of your spreadsheet and then refer to it by standard means. this method will increase load speed as well as fix all load time dependencies which occur atm in your current setup