Combine Multiple Sheets with IMPORTRANGE in Google Sheets

Published on

To combine data from multiple sheets using IMPORTRANGE, you’ll need to specify the URLs of the sheets in one column and the ranges to import in another column.

We will use the REDUCE LAMBDA function to execute multiple IMPORTRANGE operations at once. This method is user-friendly, and I’ll include a detailed formula explanation.

Features of the formula:

  1. You can merge multiple Google Sheets files (workbooks) in one go.
  2. The range dimensions don’t need to be equal in size or identical.
  3. If a workbook contains multiple sheets, you can import and merge data from selected sheets.

As a side note, we will also use functions like CHOOSECOLS, ARRAYFORMULA, and QUERY. However, the core functions are REDUCE and IMPORTRANGE.

Example: Combining Three Sheets with IMPORTRANGE in a Fourth File

Assume you have three Google Sheets files (workbooks) named sales 1, sales 2, and sales 3, each containing a sheet. The sheet names in each file are “Zone 1,” “Zone 2,” and “Zone 3.”

Sample Data from Three Sheets for Import

How can you combine these three sheets using the IMPORTRANGE function in a fourth file without writing three separate formulas for each sheet?

Here are the step-by-step instructions. Keep in mind that we are working on the fourth file.

Step 1: Spreadsheet URLs

Open sales 1 and copy the URL from the address bar. Navigate to the fourth file and paste the URL into cell A1. Similarly, copy-paste the URLs from the sales 2 and sales 3 files into A2 and A3, respectively.

URLs and Range Strings for Data Import

Step 2: Range Strings

In B1, enter the range to import from the sales 1 sheet. The tab name is “Zone 1,” so if you want to import the range A1:C, enter Zone 1!A1:C in B1.

In B2, enter the range to import from the sales 2 sheet. I am entering Zone 2!A2:C.

The last sheet is sales 3. Enter the range to import from this sheet in B3. I am entering Zone 3!A2:C.

When entering the first sheet range, I included the header row and omitted it in the second and third sheets.

In the next step, we will merge data from these sheets using the entered ranges.

Step 3: Combining Multiple Sheets with IMPORTRANGE

In cell C1, enter the following formula.

=ArrayFormula(
   QUERY(
      REDUCE(
         TOCOL(,1), A1:A3&"|"&B1:B3, 
         LAMBDA(accumulator, url, VSTACK(accumulator, 
            IMPORTRANGE(
               CHOOSECOLS(SPLIT(url, "|"), 1), 
               CHOOSECOLS(SPLIT(url, "|"), 2)
            )
         ))
      ), "SELECT * WHERE Col1 IS NOT NULL"
   )
)

The formula may initially return #REF! errors. Hover your mouse over the cell and click “Allow Access.”

Example of Combining Data from Multiple Sheets Using IMPORTRANGE

This way, you can combine multiple sheets using IMPORTRANGE in Google Sheets.

Note: If a workbook contains more than one sheet and you want to merge all of them, you need to specify each sheet individually. Copy each URL from the workbook, enter them in column A, and specify the ranges in column B as described above. This is because each sheet in a workbook has its own URL and tab name.

Formula Breakdown

The formula involves several steps. Let me break it down and explain each step. This breakdown is optional, and you can skip it if you prefer.

Before proceeding, replace the open ranges in B1:B3 with closed ranges, such as Zone 1!A1:C5, Zone 2!A2:C5, and Zone 3!A2:C5. Otherwise, you may encounter issues during the process.

1. Combining Spreadsheet URL and Range String

The IMPORTRANGE function takes two arguments: a spreadsheet URL and the range to import as a string.

Syntax:

IMPORTRANGE(spreadsheet_url, range_string)

To merge data from multiple sheets using IMPORTRANGE, we will use the REDUCE function. REDUCE operates on an array, so we need to combine the URLs and range strings into a single array. First, combine the spreadsheet URL in cell A1 with the corresponding range string in cell B1, and then split them for use in IMPORTRANGE.

Formula:

=A1 & "|" & B1

This combines the URL in cell A1 with the range string in cell B1, using “|” as the separator.

2. Using the IMPORTRANGE Formula

We will split the combined URL and range into two parts using the SPLIT function, and use CHOOSECOLS to extract the first and second parts in IMPORTRANGE as follows:

Formula:

=IMPORTRANGE(CHOOSECOLS(SPLIT(A1 & "|" & B1, "|"), 1), CHOOSECOLS(SPLIT(A1 & "|" & B1, "|"), 2))

This imports the data for the URL specified in cell A1 and the range specified in cell B1.

3. Creating an Unnamed Lambda Function Using IMPORTRANGE

Convert the above formula to a lambda function for use with REDUCE to merge multiple workbooks.

Lambda Function:

LAMBDA(accumulator, url, VSTACK(accumulator, IMPORTRANGE(CHOOSECOLS(SPLIT(url, "|"), 1), CHOOSECOLS(SPLIT(url, "|"), 2))))

This function has two parameters: accumulator and url. It vertically stacks the accumulator values with the result of IMPORTRANGE.

The url represents the combined URL and range string, and the accumulator value is initially null. Use this function with REDUCE.

4. The Role of REDUCE in Combining Multiple Sheets with IMPORTRANGE

Syntax:

REDUCE(initial_value, array_or_range, lambda)
  • TOCOL(,1) is the initial_value in the accumulator, which represents null.
  • A1:A3 & "|" & B1:B3 is the array_or_range argument.
  • The lambda function is as defined above.

When using REDUCE, enter it as an array formula to combine URLs and ranges.

Formula:

=ArrayFormula(REDUCE(TOCOL(,1), A1:A3 & "|" & B1:B3, LAMBDA(accumulator, url, VSTACK(accumulator, IMPORTRANGE(CHOOSECOLS(SPLIT(url, "|"), 1), CHOOSECOLS(SPLIT(url, "|"), 2))))))

This formula combines multiple sheets using IMPORTRANGE. Additionally, QUERY can be used to remove empty rows from the result due to open ranges in column B.

Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

4 COMMENTS

  1. Thanks!

    I’m trying to create an interactive dashboard at work and using your tutorial as a reference since it’s probably the best one out there.

    I’ve got a question about the arrayFormula.

    The argument(s) within the function seems to reference the menu items in column A and the table data in each respective Sheet.

    How does the array formula know to “look at” the URLs?

    • Hi, Ron W.,

      I have used VLOOKUPs to return the URL and cell range to import based on the drop-down selection.

      You can find the relevant info under the subtitle “Link Drop-Down with Spreadsheet_URL and Range_String Using Vlookup.”

LEAVE A REPLY

Please enter your comment!
Please enter your name here

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