Auto Refresh Google Sheets: How to Set It Up
Auto-refreshing a Google Sheet means making it pull in new data or recalculate formulas on a schedule without you clicking anything. You can do this with a simple time-driven trigger in Google Apps Script, which can update the sheet every minute, hour, or day. Below, I’ll explain the exact steps, the limitations, and the common pitfalls so you can set it up correctly the first time.
Why You Might Need Auto-Refresh
Google Sheets normally recalculates when you edit a cell, or when a formula like NOW() or RAND() is set to update on change or every minute (you can choose that in File > Settings > Calculation). But that only works while the sheet is open in a browser tab. If you’re importing live data from another URL using IMPORTXML, IMPORTDATA, or IMPORTHTML, those functions refresh only when the sheet is opened or when you manually trigger them. If you’re building a dashboard that should show fresh stock prices, weather data, or a live feed, you need a way to force a refresh on a schedule—that’s where Apps Script comes in.
How to Auto-Refresh Using Google Apps Script
Google Apps Script is a JavaScript-based scripting platform that can interact with your Sheets. You can create a time-driven trigger that runs a small function every minute, hour, or day. Here’s the step-by-step:
- Open your Google Sheet.
- Click Extensions > Apps Script.
- In the script editor, delete any existing code and paste this simple function:
function refreshSheet() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
// Force a recalculation by re-entering the same value in a dummy cell
var range = sheet.getRange('A1');
range.setValue(range.getValue());
}This function reads the value in cell A1 and writes it back, which triggers a recalculation of the whole sheet. If your sheet doesn’t have anything in A1, it will just set it to empty—that’s fine. You can also use sheet.getActiveSheet().getRange('A1').setValue(Math.random()) to force a change, but that will put a random number in A1.
- Click the clock icon (Triggers) in the left sidebar.
- Click Add Trigger.
- Set the function to
refreshSheet, the event source to Time-driven, and choose the interval you want (every minute, every hour, etc.). - Click Save and authorize the script when prompted.
That’s it. The script will now run on schedule and force your sheet to recalculate, which also refreshes any IMPORT functions.
What the Trigger Actually Refreshes
When the script runs, it doesn’t necessarily reload the data from the external source. It just forces Google Sheets to re-evaluate formulas. For IMPORTXML, IMPORTDATA, and IMPORTHTML, a recalculation usually triggers a fresh fetch from the URL. However, if the external site is slow or blocks requests, the import may fail or return stale data. For most use cases, the trigger works fine.
One important detail: the trigger only runs when the script is associated with the sheet. If you close the browser tab, the script still runs on Google’s servers—that’s the beauty of Apps Script. It doesn’t need your computer to be on.
How to Choose the Right Refresh Interval
Google Apps Script has quotas for how often a trigger can run. For a free Google account, time-driven triggers can fire at most once per minute. That’s the fastest you can set it. If you need more frequent updates (like every 10 seconds), you’d have to use a different method, such as a custom script that runs while the sheet is open (using Utilities.sleep() in a loop), but that’s resource-intensive and not recommended for large sheets.
For a typical dashboard, every 5 or 15 minutes is usually enough. If you’re tracking stock prices, you might want every minute. But keep in mind that every trigger run consumes a small amount of your Apps Script quota. The free tier allows about 20,000 triggers per day, so even a per-minute trigger (1,440 runs/day) is well within limits.
Common Problems and How to Avoid Them
Here are a few issues you might run into:
- IMPORT functions return #N/A or #REF! This often happens because the external site is blocking Google’s servers, or the URL is malformed. Try opening the URL in a browser to make sure it’s accessible.
- The trigger runs but the sheet doesn’t update. This can happen if the formula is not volatile. Some functions, like
IMPORTXML, are recalculated when the script sets a value, but if you’re using a custom function that caches results, it might not refresh. In that case, useSpreadsheetApp.flush()in the script to force the update. - Authorization errors. When you set up the trigger, you must authorize the script. If you get an error, go to the Apps Script editor, click the clock icon, and re-save the trigger. You may need to re-authorize.
- Quota exceeded. If you run the trigger too often, you might hit the daily trigger quota. Check your quota at
https://script.google.com/homeunder “My quotas�.
Alternatives: Does Google Sheets Have a Built-in Auto-Refresh?
Yes, but limited. In File > Settings > Calculation, you can set the recalculation to “On change and every minute� or “On change and every hour.� This works for volatile functions like NOW(), TODAY(), and RAND(), but it does not refresh IMPORT functions. Those only update when the sheet is opened or when a change is made. So the Apps Script trigger is the only reliable way to auto-refresh imported data on a schedule.
Another alternative is to use a third-party tool like Zapier or IFTTT to trigger a refresh, but those are more complex and may require a paid plan. The Apps Script method is free and built-in.
Costs and Limitations
Using Apps Script triggers is free for personal Google accounts, but there are quotas. For a free account, you get 20,000 triggers per day and 90 minutes of execution time per day. If you have a Google Workspace account, the quotas are higher (e.g., 100,000 triggers per day). If you exceed the quota, the trigger won’t run until the next day, so you might miss updates. For most personal dashboards, this won’t be an issue.
Also, note that the script runs on Google’s servers, so it doesn’t use your computer’s resources. However, if you have many sheets with triggers, the execution time can add up. Keep your refresh function simple—just set a dummy cell—and it will run in under a second.
Frequently Asked Questions
Does auto-refresh work if the sheet is closed?
Yes. The time-driven trigger runs on Google’s servers, so it doesn’t matter if you have the sheet open or closed. The sheet will recalculate in the background, and when you next open it, you’ll see the updated data.
Can I auto-refresh every second?
No, the minimum interval for a time-driven trigger is one minute. For more frequent updates, you’d need to use a custom script that runs continuously while the sheet is open, but that’s not recommended because it can slow down the sheet and may hit execution limits.
Why is my IMPORTXML not refreshing even with the trigger?
The trigger forces a recalculation, but if the external site blocks Google’s servers or the URL returns an error, the import might fail. Try opening the URL in a browser to see if it’s accessible. Also, check that you’re using the correct XPath or query.
Will auto-refresh affect my other formulas?
Yes, it will recalculate all formulas in the sheet, which is usually what you want. However, if you have a very large sheet with many volatile formulas, the recalculation might take a few seconds. That’s fine, but it could slow down your workflow if you’re editing at the same time.
Can I set different refresh intervals for different sheets?
Yes, you can create separate triggers for each sheet. Just open the Apps Script project for each sheet and set up a trigger with the desired interval. Each sheet has its own script.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.