How to Copy Data from One Google Sheet to Another

Moving data from one Google Sheet to another seems like it should be simple, but the best method depends entirely on what you want to accomplish. Manually copying and pasting works for a one-off task, but it quickly becomes a repetitive nightmare for regular reports. This guide will walk you through a few different ways to get your data where it needs to go, from the basics to the powerhouse function that will change how you work with your spreadsheets.

Method 1: The Simple Copy & Paste (And Its Hidden Dangers)

This is the method everyone knows, but it's important to understand why it’s often the wrong choice for recurring tasks. It's the digital equivalent of handwriting notes from one book to another - effective, but slow and prone to error.

How to Do It

The classic copy-paste is straightforward:

• Open your source Google Sheet and highlight the data you want to move.
• Press Ctrl+C (Windows) or Cmd+C (Mac) to copy it.
• Open your destination Google Sheet, click the cell where you want the data to start, and press Ctrl+V (Windows) or Cmd+V (Mac).

Using Paste Special

Sometimes you don't want to copy everything - just the numbers, or just the formatting. This is where "Paste Special" comes in handy. After copying your data, right-click on the destination cell and navigate to Paste special. You will see several options:

• Values only: Pastes the raw text or numbers without any fonts, colors, or formulas. This is great for moving raw data without messing up your destination sheet's design.
• Format only: Copies only the formatting - like background colors, font styles, and number formatting - and applies it to existing data.
• Formula only: Pastes the formulas but not the results. The formulas will then recalculate based on their new location.
• Column widths only: Adjusts the column widths to match the source sheet, saving you a lot of manual resizing.

The Problem with Manual Copying

While quick for a single task, this method has serious drawbacks for any kind of ongoing reporting or analysis:

1. It's not dynamic. If the data in your original sheet changes, your destination sheet remains outdated. You have to repeat the entire process every single time you need an update.
2. It's prone to human error. Did you copy the right range? Did you forget a row? Small mistakes can lead to big problems in your reports.
3. It's time-consuming. Manually exporting and copying data for weekly or monthly reports is a huge time-waster. That time on Monday morning could be spent analyzing insights, not wrestling with spreadsheets.

For one-time data moves, copy-paste is fine. For anything else, you need a dynamic solution.

Method 2: Referencing Another Tab in the Same Sheet

Before we jump to connecting totally separate files, it’s useful to know how to pull data from a different tab (or "sheet") within the same workbook. This is perfect for creating a summary or dashboard tab that aggregates data from other tabs in your file.

The syntax is simple: just use the sheet name, followed by an exclamation mark, and then the cell or range.

=SheetName!A1:B10

For example, if you have a tab named "Sales Data" and want to display the value from cell F2 (e.g., total revenue) on your "Dashboard" tab, you would go to your Dashboard tab and type this formula into a cell:

='Sales Data'!F2

This connection is live. If the value in cell F2 on the "Sales Data" tab changes, it will instantly update on your "Dashboard" tab. This is a fantastic way to build master dashboards without messy copying, but it only works for tabs inside the same Google Sheet file.

Method 3: The IMPORTRANGE Function (The Best Way to Connect Separate Sheets)

This is the function you’re looking for. IMPORTRANGE allows you to pull a range of data from any Google Sheet into another, and - most importantly - it keeps a live connection between them. When the source sheet updates, the destination sheet updates automatically.

This is the standard for creating master dashboards, combining reports from different team members, or just organizing your data into separate, manageable files.

The IMPORTRANGE Syntax

The function requires two arguments:

=IMPORTRANGE("spreadsheet_url", "range_string")

• spreadsheet_url: The full URL of the source Google Sheet you want to pull data from. You copy this directly from your browser's address bar.
• range_string: A string that defines the tab and cell range to import, like 'Sheet1'!A1:F500. Remember to put the sheet name in single quotes if it contains spaces (e.g., 'Sales Team'!A:G).

Step-by-Step Instructions

Let's walk through connecting two separate spreadsheets.

1. Get the Source URL: Open the Google Sheet you want to pull data from. Copy its full URL from the address bar. For example: https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ_1234abcd5678/edit
2. Define Your Range: In that same source sheet, identify the tab and range of data you want. For example, let's say your data is on a tab called "Q4 Sales" in columns A through E. Your range string would be 'Q4 Sales'!A:E.
3. Write the Formula: Now, go to the Google Sheet where you want the data to appear (the destination sheet). Click on a cell and type the function, combining the URL and range string.

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1aBcDeFgHiJkLmNoPqRsTuVwXyZ_1234abcd5678/edit", "'Q4 Sales'!A:E")

1. Allow Access: The first time you do this, you will see a small #REF! error with a message that says, "You need to connect these sheets." Simply hover over the cell and click the blue "Allow access" button. This is a one-time permission grant for that specific connection. Once you click it, your data will instantly appear.

That's it! Your destination sheet will now automatically mirror the data from the source sheet.

Advanced Tip: Combine IMPORTRANGE With QUERY

Often, you don't need all the data from the source sheet. You might only want rows that match certain criteria. By wrapping your IMPORTRANGE function inside a QUERY function, you can import and filter data at the same time.

For instance, let’s say you only want to import sales data from your source sheet where the status in Column C is "Won".

=QUERY(IMPORTRANGE("URL_HERE", "Sheet1!A:E"), "SELECT * WHERE Col3 = 'Won'")

In this formula:

• IMPORTRANGE(...) pulls the entire dataset (columns A through E).
• QUERY(...) then acts on that imported data.
• "SELECT * WHERE Col3 = 'Won'" is the SQL-like command that tells QUERY to only show rows where the third column (Col3) contains the text "Won".

This turns your Google Sheet from a simple data container into a basic, but powerful, reporting tool.

Method 4: Using Google Apps Script for Full Automation

If you need more control than IMPORTRANGE offers - like running a data transfer on a specific schedule or copying just the values instead of creating a live link - Google Apps Script is your tool.

This requires a little bit of code, but don't worry. You can often copy and paste a template and just change a few details. This is the "power user" method for automating repetitive data workflows.

Simple Script to Copy Data

Here's a basic script that copies a specific range from a source sheet to a target sheet.

1. Open the Script Editor: In your destination Google Sheet, go to Extensions > Apps Script.
2. Paste the Code: A new code editor window will open. Delete any existing code and paste this in:

function copyData() {
  // Replace with the ID of your SOURCE spreadsheet. Find it in the URL.
  const sourceSheetId = '1aBcDeFgHiJkLmNoPqRsTuVwXyZ_1234abcd5678',

  // Replace with the name of the tab in your SOURCE spreadsheet.
  const sourceTabName = 'Q4 Sales',

  // Replace with the name of the tab in THIS spreadsheet where you want the data to go.
  const targetTabName = 'Imported Data',

  const sourceSpreadsheet = SpreadsheetApp.openById(sourceSheetId),
  const sourceTab = sourceSpreadsheet.getSheetByName(sourceTabName),
  const targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet(),
  const targetTab = targetSpreadsheet.getSheetByName(targetTabName),

  // Clear any existing data in the target tab.
  targetTab.clear(),

  // Define the range you want to copy from the source.
  const sourceRange = sourceTab.getRange('A1:E500').getValues(),

  // Paste the data into the target tab.
  targetTab.getRange(1, 1, sourceRange.length, sourceRange[0].length).setValues(sourceRange),
}

1. Customize the Variables: You only need to change the values for sourceSheetId, sourceTabName, and targetTabName at the top of the script. The "ID" is the long string of random characters in the middle of your sheet's URL.
2. Run the Script: Click the floppy disk icon to save, then click "Run". You will need to authorize the script the first time, giving it permission to access your Google Sheets.

To fully automate this, you can set a "trigger" to run this script automatically. Click the clock icon on the left (Triggers), click "Add Trigger," and configure it to run your copyData function every day, week, or even every hour.

Which Method Should You Use?

• For a one-time data transfer: Use classic Copy and Paste. It's fast and easy.
• To create a summary tab in the same file: Use direct Sheet References (e.g., ='Sales'!A1). It’s instantly dynamic and simple.
• For a live link between two different files: Use IMPORTRANGE. This should be your go-to for most cross-sheet reporting needs.
• For scheduled, automated data transfers: Use Google Apps Script. It's the most powerful option for complex workflows.

Final Thoughts

Connecting data in Google Sheets is a fundamental skill that scales with your needs. You can start with simple references and progress to using the dynamic IMPORTRANGE function to build interconnected reports. Mastering this helps you maintain a single source of truth and saves countless hours on manual report building.

While IMPORTRANGE is a great tool, connecting several sources and building a truly comprehensive view of your business performance can still involve managing many different spreadsheets. That's one of the problems we built Graphed to solve. You can connect Google Sheets alongside your other data sources - like Google Analytics, Shopify, or your CRM - and your data is unified in one place. Best of all, you can build real-time dashboards just by describing what you want to see in plain English, skipping the formulas and spreadsheet wrangling entirely.