In Google Sheets, pause formula calculation until Apps Script has completed

Question

I have a situation in which I have two separate Spreadsheets (let's call them 1 and 2). In 1, I import a specific sheet from 2 using a script that copies the sheet from 2 and pastes an exact duplicate in 1. I used to use IMPORTRANGE, but the formula itself was unreliable (the sheet from 2 is quite large).

My problem is that in 1, there is another sheet that references the imported sheet from 2 using several complex arrayformulas. When I run the script to import the data from 2, it takes a very long time because the arrayformulas continue calculating while the data is importing from 2. Then once the data is imported, those arrayformulas recalculate again.

Is there any way to run the entire script and prevent Sheets from calculating the arrayformulas until the script completes? Open to other ideas as well.

Thank you!

Answer 1

Here's one approach that might work.

For your formulas, wrap the IMPORTRANGE with IFERROR and then have a trigger like IF(ISBLANK(...) for your other formulas. Reference a cell further down in your dataset (e.g. A200) that will be nonblank if your initial IMPORTRANGE completed properly.

Let me know if that makes sense.