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 Deals 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 headers, date range and deep links to HubSpot accounts.
You also have the ability to name your template in Template name field.
The next step is to set up the auto refresh schedule. We select weekly refresh frequency that will be executed every 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 Deals object 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 Deals. 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 numerous benefits to follow this approach. First of all, 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.
Secondly, your templates will be refreshed faster as you decrease the amount of the returned data. And finally, your queries will be executed in most efficient way without any exceptions and time outs.