December 7, 2020

How to Import Multiple Sheets Using the IMPORTRANGE Function

Google Sheet spreadsheet
SECTIONS
  1. Importing a data range from a single sheet
  2. A workaround for IMPORTRANGE to import multiple sheets

Importing a data range from a single sheet

The IMPORTRANGE function allows you to import a specific data range from another Google Sheets workbook. It has the following syntax:

=importrange("url_of_workbook","data_range")

For example, we will import the following Pivot Table from a source spreadsheet to a destination spreadsheet:

The range from the source spreadsheet to be imported to the destination sheet.
The range from the source spreadsheet to be imported to the destination sheet.


The URL to the source spreadsheet and the range are needed to import the array using IMPORTRANGE function.

Both the URL and the data range should be enclosed by quotation marks. The workbook must be either a public workbook or one that you have access to. If the workbook you are including is also yours, a prompt will pop up, asking you to connect the sheets. Click Allow access. 


A pop-up prompt by Google Sheets to allow access to another Google Sheets worksheet. Statement: You need to connect these sheets. 


Afterward, the data will be loaded. 

The range from source spreadsheet imported in the destination spreadsheet. Note that the formatting and style from the source spreadsheet was not copied.
The range from source spreadsheet imported in the destination spreadsheet. Note that the formatting and style from the source spreadsheet was not copied.

A workaround to import multiple sheets

The question is, how can we import multiple sheets using the IMPORTRANGE function?

Unfortunately, we cannot specify multiple spreadsheets or multiple sheets via the IMPORTRANGE function. The function works with only one range from a single source specified. 

Nonetheless, there are workarounds. A simple workaround involves using a pair of braces with a set of items separated by a comma or a semicolon. The comma would put the ranges side-by-side:

={importrange(url1,range1),importrange(url2,range2),importrange(url3,range3)}

while the semicolon would put them on a single column:

={importrange(url1,range1);importrange(url2,range2);importrange(url3,range3)}

If we want to import multiple sheets from a single spreadsheet, we add an IMPORTRANGE function for each of the sheets, specifying their respective ranges, while specifying a single URL for all of them:

={importrange(url,range1);importrange(url,range2);importrange(url,range3)}

The result will look like this:

The ranges from multiple sheets combined in the single destination spreadsheet. 
The ranges from multiple sheets combined in the single destination spreadsheet. 


Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.