We are going to share some tips with you regarding how you can refresh your Google spreadsheet data in a more effective way. This topic is closely related to the G-Accon auto-refresh processes.
Let's assume that you would like to pull the Invoice data for 2019 and 2020 for one of your QuickBooks account and generate a report that automatically refreshes.
First, you would need to estimate how often you need to refresh all of your data. Most likely your 24-month-old data won’t need to be refreshed every day, so you can set your first template to be refreshed, for instance, weekly.
In this example, we will create a query from January 1, 2019 to December 31, 2019 and set up the automatic weekly update.
You can also use Create Workflow option to set up the automated schedule for all your templates.
We start out table with A1 cell by creating a new sheet. We want to include report title, headers, and date range.
You also have the ability to name your template in Template name field, Invoice 2019 in our case.
Then we set up the auto refresh schedule. We select weekly refresh frequency that will be executed with Wednesday around 1am.
Finally, we click on the "Update And Execute" button to save the template and execute the first query. This is the result of the first query.
To execute the second query we need to select the Invoice table again or load the template from the Load Saved Template menu option if the template has been saved in cloud before.
The next step is to provide new Date Range to the second template - from January 1, 2020 till July 31, 2020. See the picture below. The new data set will be displayed starting at A16 cell. We want to exclude the report headers, date range and uncheck "Create a new sheet" option as we want to consolidate all data within one single Google sheet. Our second query will be executed twice a week on Mondays and Fridays at around 1am.
This is the result from the second query's execution that we display on the same google sheet.
And finally, we will create the 3rd query that will pull the latest data for the Invoice table. We need to update data every 3 hour and let's say, we can set up the dynamic range. This is how it can be done:
Another tip is to use Google spreadsheet =today() function to dynamically refresh the last query:
This is the result from the third query's execution that we display on the same google sheet.
If you select Update/Modify/Delete Templates for Current Sheet from the main menu option, you see three templates that have different names, date ranges and update schedules. The name of the templates can be updated.
There are multiple benefits to follow this approach. The first benefit is you avoid reaching Google limitation. Apps Script services impose daily quotas and hard limitations on some features. If you exceed a quota or limitation, your script will throw an exception and terminate execution.
https://developers.google.com/apps-script/guides/services/quotas
The second, your templates will be refreshed faster, in most efficient way, and without any exceptions as your data splits by chunks.
