Learn the limits and best practices for scheduling report refreshes in Dataslayer for Google Sheets. Avoid common issues and optimize your schedules for efficiency.
Scheduling automated refreshes in Dataslayer is an essential way to keep your reports up to date without manual intervention. However, improper scheduling can lead to performance issues due to Google’s execution time limits. Follow these best practices to avoid issues and optimize execution times.
1. Stay Within Dataslayer’s Scheduling Limits
To ensure smooth performance and avoid overloading Google’s execution limits, Dataslayer enforces a maximum of 7 schedules per document and per user in Google Sheets, with an additional limit of up to 3 weekly schedules.
If you try to create more than 7 schedules in a single spreadsheet, you will receive an error message preventing additional schedules. This limit cannot be exceeded.
How to Prevent Issues
- Regularly review schedules: Remove unnecessary or outdated schedules to stay within the limit.
You can review and delete your schedules directly from the My Schedules section on the Dataslayer web interface or from the Dataslayer extension in the spreadsheet where you set up the schedule.
2. Avoid Scheduling Too Many Reports at the Same Time
Google imposes a strict limit on the execution time of Apps Script, which is 6 minutes per hour per Google account. This means that all scheduled refreshes triggered under the same account within the same hour must collectively finish within 6 minutes.
If multiple schedules run simultaneously and take too long, some will fail because they exceed this time limit.
Important Note on Scheduling Execution Time
When setting a schedule at a specific time (e.g., 08:00), Google may execute it at any point between that minute and the next hour (e.g., between 08:00 and 09:00). The exact execution time for the first day is determined by Google. From then on, the schedule will run at that same time every day. For example, if the first execution happens at 08:37, all future runs will also occur at 08:37.
You can check Google Apps Script Quotas to learn more about Google's account limit.
How to Prevent Issues
- Stagger your schedules: Instead of scheduling multiple refreshes at the same time, distribute them across different hours (e.g., 08:00, 10:00).
- Avoid overlapping schedules: If you have multiple spreadsheets under the same Google account, ensure they do not all update at the same time.
- Optimize query execution time: Request only the necessary data (see point 3).
3. Optimize Query Performance to Reduce Execution Time
Long-running queries increase the risk of hitting Google’s 6-minute execution limit, especially when multiple large queries are scheduled close together. Large datasets take longer to process, increasing the likelihood of failures.
How to Prevent Issues
- Use filters: Limit the amount of data retrieved by setting date ranges or applying filters to fetch only the necessary data.
- Reduce columns: Avoid retrieving excessive columns—only select the data fields you truly need.
- Limit data range: If you don’t need historical data, request only recent records instead of an entire dataset. You can also use the "Combine new with old results" option to make your query more efficient.
- Test query runtime: Run your queries manually to check how long they take and optimize them if needed.
4. Avoid Scheduling During Peak Hours
Peak times, such as 06:00 am and 08:00 am, often experience higher demand on Google’s servers, leading to slower execution and higher chances of failed updates.
During CET hours the peak hours are usually from 6 to 10 am, Mondays are usually the busiest day, especially the first Monday of each month.
How to Prevent Issues
- Choose off-peak times: Instead of scheduling at 06:00 am, opt for 05:00 or 11:00 to avoid heavy congestion.
- Spread out refresh times: If you have multiple scheduled reports, distribute them across different times instead of clustering them.
5. Monitor and Adjust Your Schedules Regularly
Even well-planned schedules can sometimes fail due to API changes, server congestion, or quota limitations. Keeping an eye on your schedules ensures they continue running smoothly.
How to Prevent Issues
- Manually test schedules: Run a manual refresh at the scheduled time to verify it executes correctly.
- Adjust timing as needed: If failures persist, reschedule to a different time or optimize your queries.
Need Help?
If you encounter scheduling issues or need further assistance, contact us via live chat. You can find it in the bottom right corner of the screen. Our support team is happy to help!