Graphed LogoGraphed
Login

How to Add Data from One Google Sheet to Another

Cody Schneider•

Tired of manually copying and pasting information between your Google Sheets? There's a much better way to manage, sync, and analyze your data. This tutorial will walk you through the best methods for automatically adding data from one Google Sheet to another, from the essential IMPORTRANGE formula to more advanced scripting solutions.

First, Why Link Google Sheets?

Connecting spreadsheets is a fundamental skill for anyone who manages data. Instead of constantly exporting CSVs or copy-pasting new rows, linking sheets creates a single source of truth. Your main spreadsheet (the destination sheet) automatically updates whenever the data in your source sheet changes.

Common scenarios for linking sheets include:

  • Creating a Master Dashboard: Combine sales data from one sheet, marketing metrics from another, and operational KPIs from a third into a single overview dashboard.

  • Team Collaboration: Let different team members or departments manage their own sheets (e.g., Sales, Marketing, HR) while you pull key information into a centralized report.

  • Protecting Original Data: Share a filtered view of your data in a new sheet without giving collaborators access to your raw, original dataset.

  • Streamlining Workflows: Automatically feed client responses from a Google Form submission sheet into a project management tracker in another sheet.

Now, let's get into the step-by-step methods to make this happen.

Method 1: The Basic Copy and Paste (And Its Limitations)

Before we dive into automated formulas, it's worth covering the simplest method: good old copy and paste. For a one-time data transfer, this is the quickest way to get the job done.

You can use standard keyboard shortcuts:

  • Select the data you want to move.

  • Press Ctrl+C (Windows) or Cmd+C (Mac) to copy it.

  • Open your destination sheet, click on the cell where you want the data to start, and press Ctrl+V (Windows) or Cmd+V (Mac) to paste.

Using "Paste Special" for More Control

Sometimes, a standard paste brings over unwanted formatting or formulas that then break in the new sheet. This is where Paste special comes in handy. After copying your data, right-click the destination cell and navigate to Paste special. You’ll see several options:

  • Values only: This is the most common and useful one. It pastes the raw results of your data without any of the original cell formatting (like bold text, colors) or formulas.

  • Format only: This copies the visual style (colors, bolding, font size) but not the actual data.

  • Column widths only: Conveniently applies the column widths from your source to your destination.

  • All except borders: Pastes everything (values, formatting, formulas) but leaves out any cell borders.

The major downside: Copying and pasting is a completely manual process. The data is static and creates a snapshot in time. If the source sheet gets updated, you must manually recopy and paste the information to keep your destination sheet current. This is inefficient, tedious, and opens you up to human error. For live data syncing, you need a formula.

Method 2: Using the IMPORTRANGE Function for Live Syncing

The IMPORTRANGE function is the go-to solution for creating a live link between two different Google Sheets. It imports a range of cells from a specified spreadsheet, and the connection stays active — any changes in the source sheet will automatically appear in your destination sheet moments later.

Understanding the IMPORTRANGE Syntax

The formula is surprisingly simple and has two parts:

  • spreadsheet_url: This is the full URL of the source Google Sheet you want to pull data from. It should be enclosed in double quotes.

  • range_string: This tells Google Sheets which tab and cells to import. It needs to be in a specific format: "SheetName!CellRange", also enclosed in double quotes (e.g., "Sales Data!A1:F50").

Step-by-Step Guide to Using IMPORTRANGE

Let's walk through an example. Imagine we have a sheet with Q1 sales data and want to pull it into a new "Annual Sales Report" sheet.

1. Get the URL of the Source Sheet

Open the spreadsheet you want to import data from. Copy the entire URL from your browser's address bar. Let's assume the URL is:

https://docs.google.com/spreadsheets/d/1aBcDeFg_2hIjKlMnOpQrStUvWxYz_pAssWord/edit

2. Define Your Data Range

In that same source sheet, identify the specific tab and cell range you need. For our example, the data is on a tab named "Q1 Sales" and occupies cells A1 through E100.

So, our range_string would be: "Q1 Sales!A1:E100"

Tip: If your tab name has spaces (like "Q1 Sales"), it's best to wrap the name in single quotes, though IMPORTRANGE is usually flexible on this: "'Q1 Sales'!A1:E100".

3. Write the Formula in Your Destination Sheet

Open the spreadsheet where you want the data to appear. Click on the cell where you want your imported data to start (e.g., A1) and type the complete formula:

4. Allow Access Between the Sheets

The first time you use IMPORTRANGE to connect two sheets, Google will ask for permission. After you hit Enter, the cell will likely show a #REF! error with a message that says, "You need to connect these sheets."

Hover over the cell, and a blue button that says Allow access will appear. Click it. This is a one-time action, once you've granted permission, the two sheets will be permanently linked and the data will populate instantly.

Advanced Tips for IMPORTRANGE

While IMPORTRANGE is great on its own, its real power is unlocked when you combine it with other functions.

Combine with QUERY for a Dynamic Datalink

Often, you don't want to pull all the data, just specific rows or columns. By nesting IMPORTRANGE inside a QUERY function, you can essentially run a SQL-like query on your imported data. It allows you to select specific columns, filter rows based on conditions, and sort the results.

For example, let's say we only want to import sales data from our source sheet where the 'Status' in column D is "Completed".

Breaking it down:

  • IMPORTRANGE(...) acts as the dataset for our query.

  • "SELECT * WHERE Col4 = 'Completed'" is the query itself. SELECT * means "select all columns," and WHERE Col4 = 'Completed' tells it to only return rows where the fourth column (Column D) equals "Completed".

Combine with FILTER for a Simpler Alternative

The FILTER function is a simplified and often more intuitive alternative than QUERY for isolating rows based on specific criteria.

Common IMPORTRANGE Errors and How to Solve Them

  • Not using quotes where required: Ensure there are quotes around both the spreadsheet URL and the "Sheet Name!A1:Z5" string.

  • "You need to connect these sheets" error: You forgot to Allow access.

Method 3: Using Google Apps Script for Custom Automation

If you need more control than formulas can offer, Google Apps Script is the next step up. Apps Script is a cloud-based JavaScript platform that lets you create custom functions and automate workflows across Google products, including Sheets.

When to Use Apps Script Over IMPORTRANGE

While IMPORTRANGE imports data dynamically, Apps Script provides more power and customization. Consider using it if you need to:

  • Copy over formatting: IMPORTRANGE only pulls cell values. Apps Script can copy values, colors, font styles, and cell formats.

  • Trigger data transfers on a schedule: Run a daily script at 8 AM to copy yesterday's data instead of maintaining a live, constant connection.

  • Perform complex logic: Transform the data as it's copied - for example, looping through rows, changing date formats, and reorganizing columns.

  • Append data: Instead of overriding the destination range like IMPORTRANGE, a script can find the last empty row and append new data below it, preserving existing records.

Example Script: Copying Data from One Sheet to Another

Here’s a basic script that copies a specified range from a source sheet and pastes only the values into a destination sheet.

1. Open the Script Editor

In your destination Google Sheet, go to Extensions > Apps Script. This will open a new tab with the script editor.

2. Paste the Code

Delete any placeholder code and paste the following script. Be sure to replace the placeholder variables with your actual Sheet IDs and tab/range names.

3. Configure and Run the Script

  • Find your Source Sheet ID. This is the long string of characters in the middle of your sheet’s URL (right after /d/ and before /edit).

  • Update the variables at the top of the script with your IDs, tab names, and range.

  • Click the Save project (floppy disk) icon.

  • To run it, click the Run button. The first time, it will ask for authorization to access your spreadsheets. Follow the prompts to approve it.

This script copies over just the raw data values, similar to Paste Special > Values only, without creating any live link. You can customize it heavily - for example, modifying how and when it gets pasted.

Final Thoughts

Pulling data from one Google Sheet into another can streamline any workflow, turning static reports into automated, dynamic dashboards. For most, the IMPORTRANGE formula offers the perfect balance of power and simplicity. And if you need full control over your data flows, custom logic that only Google Apps Script can provide you with.

Managing data like this is a big step up from manual CSV exports, but the complexity grows as you add more sources like Google Analytics, Shopify, Facebook Ads, or your CRM. At some point, managing everything in Sheets becomes a full-time job on its own. That’s where we built Graphed to come in. Graphed connects to all of your sales and marketing platforms (including your Google Sheets) and allows you to build real-time dashboards and reports in seconds using plain English, not formulas. We automate the entire process, giving you back time to act on insights rather than wrangling data.

With Graphed target="_blank" rel="noopener"), you simply describe the report you need, and the platform builds it automatically, turning hours of spreadsheet work into a 30-second task.