How to import data from one Google Sheet to another

In the previous tutorial, we discussed how to import data from another Sheet in the same Google Spreadsheet. This tutorial will show you how to import data from one Google Sheet to another. And also, you can use this method to import data from multiple Google Spreadsheets.

For importing data from one Google Sheet to another, we need to have a way to identify each Google Spreadsheet uniquely.

How to uniquely identify each Google Spreadsheet

In Google Sheets, you can uniquely identify the Spreadsheet using the Spreadsheet URL or the Spreadsheet ID. We are going to use these unique identifiers when referring to other Spreadsheets.

The structure of the Spreadsheet URL looks like below. The value between the “d/” and “/edit” is the spreadsheet id. The red color part of the following URL is the Spreadsheet ID.

https://docs.google.com/spreadsheets/d/1NX5KsLpL1jva5DYmbLZC22fP92HzA3BVk53MGaCdpCo/edit#gid=0

Remember the following function to import data from one Google Sheet to another

=IMPORTRANGE(spreadsheet_url, range_string)

The IMPORTRANGE function lets you import data from other Google spreadsheets.

The first parameter of the function is the URL of the spreadsheet which contains your data to be imported.

The second parameter is the range of the cell that contains your data to be imported.

How to use the IMPORTRANGE function to import data from other Spreadsheets

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dUJrExGGARRR2fApNUe-L0DoGFe7YwW3XSgrGImifYk/edit#gid=0","Sheet1!A1:Y2824")

The above video explains how to use the IMPORTRANGE function to import data from other Google Spreadsheets.

You can set the IMPORTRANGE function to dynamically incorporate every new row added to the data source. To do that, you need to change the data range parameter as showed below.

Sheet1!A1:Y2824 into Sheet1!A1:Y (Remove row number)

You can replace the URL with the Spreadsheet ID explained above to clean the function a little.

For example, the following also should yield the same result above.

=IMPORTRANGE("1dUJrExGGARRR2fApNUe-L0DoGFe7YwW3XSgrGImifYk","Sheet1!A1:Y2824")

Include the IMPORTRANGE function inside other functions

The IMPORTRANGE function yields a range of cells. Therefore, you can include inside other Google Sheets function that requires a range of cells as a parameter. That way you can do more with the IMPORTRANGE function than importing data into Google Sheets directly.

Import data in Google Sheets and QUERY on them

You can provide this IMPORTRANGE function as the first parameter of the QUERY function. Then you can write your queries for that range.

Since this is a generated range, you should use column numbers as Col1, Col2, Col3, etc... to refer to the columns.

Example

In the above data set, if you want to import only the sales record where the quantity ordered is greater than 60. You can use IMPORTRANGE and QUERY function together as explained below.

=query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dUJrExGGARRR2fApNUe-L0DoGFe7YwW3XSgrGImifYk/edit#gid=0","Sheet1!A1:Y2824"),"select * Where Col2>60")

In the above example, the first parameter of the QUERY function is the Entire data range you imported from the other Google Spreadsheet using the IMPORTRANGE function.

The second parameter,

"select * Where Col2>60"

of the QUERY function is the SQL statement. In this query, we request to return all rows where values in column 2 are greater than 60.

Sort data in the IMPORTRANGE function

You can easily sort your imported data using the SORT function. The sort function Sorts the rows of an array or range by the values in one or more columns.

To sort the data, provide the IMPORTRANGE function as the first parameter (range – the range you are going to sort). Then provide the index of the column containing the values by which to sort.

For example, the following formula, sort the range based on the values in column 2 in ascending order.

=SORT(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dUJrExGGARRR2fApNUe-L0DoGFe7YwW3XSgrGImifYk/edit#gid=0","Sheet1!A2:Y"),2,true)

Filter data in the IMPORTRANGE function

Using the FILTER function with IMPORTRANGE is a little of trickery. You cannot use the FILTER function directly to filter data in the IMPORTRANGE function.

For the first parameter of the FILTER function, provide the IMPORTRANGE function. Then you need to use the INDEX function to return the required array of cells. As the first parameter of the INDEX function, provide the column which you expect to apply a filter condition using the IMPORTRANGE function.

For example, the following formula returns the rows in which the column ‘B’ values are greater than ’60’.

=FILTER(
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dUJrExGGARRR2fApNUe-L0DoGFe7YwW3XSgrGImifYk/edit#gid=0","Sheet1!A2:Y"),
INDEX(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1dUJrExGGARRR2fApNUe-L0DoGFe7YwW3XSgrGImifYk/edit#gid=0","Sheet1!B2:B")>60)
)

Note: You can easily filter the data in the IMPORTRANGE function using the QUERY function.

Import Data from Multiple Spreadsheets into One Range

You can import data from multiple Spreadsheets to a single range. This is important when you want to perform analysis on them as a whole or when you want to display them on a dashboard like Google Data Studio.

To do this, you need to add semicolon-separated multiple IMPORTRANGE functions for each data source inside curly brackets.

Example

Let’s say you have three separate Google Spreadsheets, that contain 2003, 2004, and 2005 sales data, and datasheets are Y2003, Y2004, and Y2005 respectively. If data is in the A2:Y range in each sheet.

You can import data in all those three Spreadsheets to a single range using the IMPORTRANGE function as explained below.

={
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1wY0Vv8dDAFl1riXkKm5iEDZPpwHPorH6mIRCJgPlve4/edit#gid=0","Y2003!A2:Y");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1yjJ6RPfStAYh604g09hQgI0D1U4GLADbtvpcJcFKpJ8/edit#gid=0","Y2004!A2:Y");
IMPORTRANGE("https://docs.google.com/spreadsheets/d/1DMUbKWy0LYDDUiPe5PQWCpIFFy86u5WnF7sFnq_fZBk/edit#gid=0","Y2005!A2:Y")
}

Note:

As of the time this post is written, you need to test each IMPORTRANGE function separately for each sheet and allow access. Otherwise, if you put all the IMPORTRANGE functions together and try to grant access, it will show you an error before you allow access for all the sheets.

The following video explains how to import data from multiple Google Sheets to a single range using the IMPORTRANGE function.

Using this method, you can dynamically import data added to your data sources (spreadsheets) to this range.

Wrapping Up

When you are working with spreadsheets, you occasionally need to link multiple spreadsheets together to do more complex analysis or display them in a dashboard.

It is easier to import data from one google sheet to another than in other types of spreadsheets. The only thing you need to Import data from other Google Spreadsheets is the IMPORTRANGE function. We can use this function with many other functions to import only the data you want. And also, unlike in other spreadsheets applications, you will not break the links even if you change the file location.

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.