I've been having some trouble with a google spreadsheet that relies heavily on queries. The main query I use in this spreadsheet returns about 27 thousand rows and sometimes the results are incomplete. I wonder if there is a way to set a trigger to refresh that query based on a specific cell being filled or not, as it takes so long to refresh it manually during work hours it's impractical.
Thanks in advance
Currently Google Sheets is the one running scheduled refreshes on sheets and it sets limits on how long these processes can run per hour. Usually this time is around 6 minutes or so. As this runtime is shared between all your sheets that use triggers, with many files and queries refreshing at the same time, they are not always be completed in time and they are instead pushed to the next hour. This is the most common reason for delays with triggers.
Normally Google provides more time to users with G Suite business account. If you are not using G Suite, upgrading to that can help.
You should check if you have any files that use triggers which you do not need anymore. Triggers in those files might be delaying your scheduled refreshes. Also, please check if there are any such files in you recycle bin. Remove them permanently as triggers on those remain usually active regardless of their location.
If these measures do not help, please contact us via private ticket.