How to Link Data Between Google Sheets

Linking data between Google Sheets is a powerful way to consolidate information, create dashboards, or simply reference data from one file in another. The primary method for doing this is using the built-in IMPORTRANGE function.

What is IMPORTRANGE?

The IMPORTRANGE function allows you to import a range of cells from a specified spreadsheet. It's the key tool for creating dynamic links where data automatically updates when changes occur in the source sheet.

The basic syntax is:
=IMPORTRANGE("spreadsheet_url", "range_string")

• spreadsheet_url: This is the URL of the Google Sheet you want to pull data from (the source sheet). You can use the full URL or just the spreadsheet ID.
• range_string: This is a string representing the range you want to import, in the format "SheetName!A1:B10". You can specify a single cell, a range, or even multiple ranges (though this gets more complex).

Steps to Link Google Sheets Using IMPORTRANGE

Linking sheets using IMPORTRANGE is straightforward once you understand the syntax and permissions required.

1. Open Your Destination Sheet: This is the sheet where you want the linked data to appear.

2. Identify the Source Sheet: Find the Google Sheet you want to pull data from. Copy its full URL from your browser's address bar.

3. Determine the Range: Decide exactly which cells (or range of cells) you need from the source sheet. Note the sheet name and the cell range (e.g., Sheet1!A1:C50).

4. Enter the Formula: In a cell in your destination sheet (where you want the data to start), enter the IMPORTRANGE formula.

   • Replace "spreadsheet_url" with the URL you copied.
   • Replace "range_string" with the range you identified.

   Example:
   If your source sheet URL is https://docs.google.com/spreadsheets/d/1abc123def456/edit#gid=0 and you want to import Sheet1!A1:C10, your formula would be:
   =IMPORTRANGE("https://docs.google.com/spreadsheets/d/1abc123def456/edit#gid=0", "Sheet1!A1:C10")

5. Handle Permissions: If it's the first time that you are linking this Google sheet to another sheet, you'll see the #REF! warning. This is because the destination sheet needs your permission to access data from the source sheet.

Addressing the #REF! Warning

When you first enter the IMPORTRANGE formula linking two sheets that haven't been linked before, the cell will likely show #REF! with an error message like "You need to connect these sheets."

• Grant Access: Hover over or click on the #REF! error. A prompt or button should appear allowing you to "Allow access" or "Connect sheets." Click this button.
• Once you grant permission, Google Sheets will fetch the data from the specified range in the source sheet, and the #REF! error will be replaced by the imported data. This permission is usually granted per source sheet URL for a given destination sheet.

Practical Tips

• Use Spreadsheet ID: Instead of the full URL, you can often use just the spreadsheet ID (the long string of letters and numbers between /d/ and /edit in the URL) in the IMPORTRANGE function for a slightly shorter formula.
• Separate Arguments: Put the spreadsheet URL and range string in separate cells and reference those cells in your IMPORTRANGE formula. This makes it easier to update the source sheet or range later.
• Named Ranges: Use Named Ranges in your source sheet. This makes your IMPORTRANGE formula more readable (e.g., =IMPORTRANGE("url", "MyDataRange")) and automatically adjusts the range if you add/remove rows/columns within the named range in the source sheet.
• Combining with Other Functions: IMPORTRANGE is often used in conjunction with other functions like QUERY, FILTER, VLOOKUP, etc., to manipulate or search the imported data.

By using the IMPORTRANGE function and handling the initial permission request, you can effectively link data between your Google Sheets.