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 a whole year’s Purchase Order data and generate a report that automatically refreshes hourly. First, you would need to estimate how often you need to refresh all of your data. Most likely your 12-month-old data won’t need to be refreshed every day, so you can set your first template to be refreshed weekly.
In this example, we will first create a query from January 1, 2019 to August 31, 2019 and set up the automatic weekly update.
We start out table with A1 cell by creating a new sheet. We want to include report title, headers, and deep links to Xero accounts.
You also have the ability to name your template in Template name field.
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 "Execute" button to execute the first query. This is the result of our first query.
To execute the second query we need to select Purchase Order Details table again and provide new Date Range - from September 1, 2019 till December 31, 2019. 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 Purchase Order Details. We need to update data hourly 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 your 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 mani menu option, you see three templates that have different update schedule. The name of the templates can be updated.
There are multiple benefits to follow this approach. First of all you avoid reaching Xero limits which are related to the number of API calls that your application can make against a particular Xero organization.
- Minute Limit: 60 calls in a rolling 60 second window
- Daily Limit: 5000 calls in a rolling 24 hour window
https://developer.xero.com/documentation/auth-and-limits/xero-api-limits
Secondly, to 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
And finally, your templates will be refreshed faster, in most efficient way, and without any exceptions.
