Graphed LogoGraphed
Login

How to Refresh Data in Google Sheets

Cody Schneider•

Constantly hitting the refresh button on your browser just to see if your Google Sheet has updated is a familiar, and frustrating, feeling. To make sound decisions, you need access to the most current data, but manually updating your sheets is a tedious chore. This guide will walk you through foundational methods to automatically and manually refresh your data in Google Sheets, moving you from repetitive tasks to reliable, up-to-date reports.

Why Stale Data is Your Worst Enemy

Before diving into the "how," it's important to understand the "why." Working with outdated information isn't just a minor inconvenience, it has real-world consequences for your business. Stale data can lead to:

  • Poor Decision-Making: Imagine adjusting your marketing budget based on last week's performance data. You might pour money into a campaign that has already started to underperform or cut spending on one that's just starting to gain traction.

  • Inaccurate Reporting: When you present a report to your team or stakeholders, they expect the numbers to reflect the current reality. Presenting old data erodes trust and makes your analysis less valuable.

  • Wasted Time and Effort: If your team is acting on old information, they're likely focusing their efforts in the wrong places. This inefficiency can stall progress and lead to missed opportunities.

Ensuring your data is fresh isn't just a technical task - it's a fundamental part of running a data-informed business. Now, let's explore how to make that happen.

Method 1: The Simple Manual Recalculation

Sometimes, the "refresh" you need is simply forcing Google Sheets to recalculate its formulas. Functions like NOW(), TODAY(), or RAND() are "volatile" and are supposed to update periodically or when a change is made to the sheet. However, they can sometimes get stuck.

Additionally, if you have complex formulas that depend on other cells, you might want to trigger a full recalculation manually. The easiest way to do this isn't in a menu - it's to make a simple change to your sheet and then undo it.

How to Manually Recalculate:

  1. Navigate to any empty cell in your sheet.

  2. Type any character (e.g., "a") and press Enter.

  3. Immediately press Ctrl + Z (or Cmd + Z on a Mac) to undo the change.

This tiny action forces Google Sheets to re-evaluate the formulas across your entire workbook, which often solves issues with functions that aren't displaying the latest calculations. This does not, however, refresh data pulled from external sources using a function like IMPORTRANGE.

Method 2: Handling Caching with Import Formulas

One of the most common reasons data becomes stale in Google Sheets is due to functions that pull data from external websites or other spreadsheets, such as IMPORTHTML, IMPORTXML, IMPORTDATA, or IMPORTRANGE.

To preserve performance, Google Sheets "caches" the results from these functions, storing a copy of the data and only fetching new data periodically. According to Google's documentation, these functions can take up to an hour to refresh automatically. When you need real-time information, an hour is far too long to wait.

The "Dummy" Parameter Trick

A clever way to force these functions to pull fresh data is by making a tiny, meaningless change to the formula itself. The most common method involves adding a "dummy" or junk parameter to the source URL that changes automatically.

Let's say you're using IMPORTHTML to pull a table from a Wikipedia page:

To force this to refresh, you can append a query parameter to the URL that references a cell that continuously changes. The easiest way to do this is with the NOW() function.

Step-by-Step Instructions:

  1. Change your Sheet’s Recalculation Settings: Navigate to File > Settings > Calculation. In the "Recalculation" dropdown, change the setting from "On change" to "On change and every minute." This ensures the NOW() function updates automatically.

  2. Designate a "Refresh" Cell: Pick an unused cell, say Z1, where you can generate a continuously changing value. In cell Z1, enter this formula:

This formula takes the current date and time and converts it into a text string that looks something like "#2023-10-27-14-30-01". Every minute, this value will update automatically.

  1. Modify Your Import Formula: Now, go back to your original IMPORTHTML formula. You'll need to join the URL with your refresh cell (Z1) using an ampersand (&). Since the URL is a text string, you'll need to concatenate them:

Because the value in Z1 changes every minute, the URL inside your IMPORTHTML formula changes too (e.g., ending with #2023-10-27-14-30-01, then #2023-10-27-14-31-01, etc.). Google Sheets sees this "new" URL and is forced to fetch the data from scratch, bypassing the cache and giving you a freshly updated view.

Method 3: Refreshing Pivot Tables and Connected Sheets

Pivot Tables

Pivot Tables in Google Sheets summarize data from another range within your spreadsheet. Generally, when you update the source data range, the pivot table reflects those changes automatically. The more common issue is when you add new rows of data that fall outside the pivot table's original source range.

For example, if your pivot table's source is Sheet1!A1:D100 and you add new data in row 101, the pivot table won't see it.

To fix this:

  1. Click on any cell within your pivot table.

  2. The Pivot table editor will appear on the right side of your screen.

  3. At the top of the editor, you'll see the data source range (e.g., 'Sheet1'!A1:D100). Click this range.

  4. Change the range to include all your data. A good practice is to make the range boundless by removing the number from the second half, like so: 'Sheet1'!A1:D. This tells the pivot table to include all data from columns A to D, regardless of how many rows are added.

Your pivot table will now automatically update as new rows are added to your source sheet.

Connected Sheets for BigQuery

If you work with massive datasets stored in Google BigQuery, you can use the "Connected Sheets" feature. This powerful connector allows you to analyze billions of rows of data directly within Google Sheets without crashing your browser.

Connected Sheets also come with built-in refresh scheduling, removing the need for workarounds.

How to Schedule a Refresh for Connected Sheets:

  1. With your Connected Sheet open, find the data panel at the bottom of the tab (often labeled as an extract or with the BigQuery icon).

  2. Click on the Refresh Options button. It often looks like a small menu next to the "Refresh" button.

  3. Select Schedule refresh.

  4. A dialog box will appear, allowing you to set up a repeat schedule (e.g., every day, every week, or every month) at a specific time.

  5. Click Save.

Your data from BigQuery will now automatically refresh on the schedule you've set, ensuring your high-level reports are always based on the latest underlying data.

Method 4: Full Automation with Google Apps Script

For the ultimate control over your data refreshing, Google Apps Script is the perfect tool. Apps Script is a JavaScript-based platform that lets you create custom functions and automate workflows within the Google Workspace ecosystem. Don't worry if you're not a coder, you can often use simple, pre-written snippets to achieve your goals.

You can use Apps Script to create a function that forces a refresh and then program that function to run automatically on a timer (e.g., every 15 minutes, every hour).

Step 1: Write a Simple Refresh Script

This script uses the same principle as our manual trick - it modifies a cell to force imported data formulas to recalculate.

  1. Open your Google Sheet.

  2. Click on Extensions > Apps Script. A new tab will open with the script editor.

  3. Delete any boilerplate code in the Code.gs file and paste the following snippet:

  1. Don't forget to change "Dashboard" and "A1" to match the sheet name and refresh cell you're actually using.

  2. Click the Save project icon (it looks like a floppy disk).

Step 2: Set Up a Time-Driven Trigger

Writing the function is only half the battle. Now you need to tell Google to run it automatically.

  1. In the Apps Script editor, click on the Triggers icon on the left menu (it looks like a clock).

  2. Click the blue + Add Trigger button in the bottom right.

  3. A configuration window will appear. Set it up as follows:

    • Choose which function to run: Select forceRefresh.

    • Choose which deployment should run: Leave as Head.

    • Select event source: Change to Time-driven.

    • Select type of time-based trigger: Choose options like Minutes timer, Hour timer, or Day timer. For most frequent refresh, pick Hour timer and Every hour.

  4. Click Save.

  5. Google will ask you to authorize the script. Review permissions and click Allow.

That's it! Your forceRefresh script will now run automatically at the interval you chose, keeping your data current without you lifting a finger.

Final Thoughts

Moving from manual updates to reliable, automated workflows is the key to creating trustworthy reports in Google Sheets. Whether you use a simple formula trick, set up Connected Sheets, or write a custom Apps Script, the goal is always the same: getting current, accurate data so you can make smarter decisions faster.

This constant need to connect, refresh, and maintain data pipelines — especially from sources scattered across different marketing and sales platforms — is exactly why we built Graphed. We automate this entire process by connecting directly to your tools like Google Analytics, Shopify, Facebook Ads, and Salesforce. Instead of funneling data into spreadsheets and building refresh workarounds, Graphed gives you a live look with dashboards that update in real-time. Just ask a question in plain English, and Graphed builds the report for you — no formulas, scripts, or CSV downloads required.