I got a sheet with a script that will fetch data from another sheet through query formula and importrange. Then at the end of the day, The script will automatically triggered and sent them to Line (If you don't know Line, Just imagine WhatsApp for asian.)
The problem is The result is not what I expected. The message i got from it will be #ERROR! all the time unless I opened the sheet and let Query and Importrange refresh itself first. How do I just let it run without having to open sheet at all?
And yes, I already had trigger on set and on that aspect. It worked fine. I also turned on the Iterative calculation and Recalculation to On Change and Every hour.
Here's dummy sheet link along with script attached. https://docs.google.com/spreadsheets/d/1r5L7KDfOX8T9Gtv2lkWhABWuExwSjcQ1mCZvwCbqpUQ/edit#gid=108539279
Most of the formulas are recalculated "locally" every time a spreadsheet is opened. Unfortunately opening the spreadsheet on the script (by usen open, openById and openByUrl) doesn't "wait" for all the calculations to be finished . Considering this it's better to do all the required calculations using JavaScript rather than Google Sheets formulas.
P.S. The setting "on change and every hour" only affects volatile functions like NOW, TODAY, RAND and RANDBETWEEN