13

I would like to aggregate the data of multiple spreadsheets into one spreadsheet.

  • Spreadsheet 1 has a Row of Strings A2:A500
  • Spreadsheet 2 has a Row of Strings A2:A500
  • Spreadsheet 3 is supposed to have a Row of both (Spreadsheet1!A2:A500 AND Spreadsheet2!A2:A500).

Duplicates shall not be handled differently. I would like them to appear as often as they appear in the different sheets.

Is it possible to do this without writing a script or using jQuery, e.g. by using IMPORTRANGE?

What does not work: I have tried using IMPORTRANGE as follows: 

ARRAY{IMPORTRANGE("key-of-spreadsheet1","list!A2:A500"), IMPORTRANGE("key-of-spreadsheet2", "list!A2:A500")}

This causes an error.

Community
  • 1
  • 1
FlorianT.
  • 833
  • 4
  • 14
  • 29
  • Not sure I understand the question. I would basically like to copy the whole sheet! So References from "A2:H1000". – FlorianT. Jul 28 '15 at 07:27

4 Answers4

30

You should be able to use a vertical array in the Spreadsheet 3:

={IMPORTRANGE("Sheet1Key","SheetName!A2:A500");IMPORTRANGE("Sheet2Key","SheetName!A2:A500")}
simhumileco
  • 31,877
  • 16
  • 137
  • 115
Chris Hick
  • 3,004
  • 1
  • 13
  • 15
  • @Chris Hick this was exactly my first guess too. But it does not work that way. Apparently spreadsheet expected a different range... – FlorianT. Jul 28 '15 at 07:30
  • 2
    I have an example sheet that combines 3 importranges into one sheet https://goo.gl/bEZgO9 The formula to create the vertical array is in cell A1, and also includes FILTER formulae to remove the blank rows. Can you adapt this formula to work with your own sheets? – Chris Hick Jul 28 '15 at 10:16
  • This seams to solve my problem! Very complex formula though.... How do do you organize the order in which the rows appear in your mainsheet? – FlorianT. Jul 28 '15 at 11:10
  • 1
    the ; within the { } array marks where the next dataset begins so you can place them in whichever order you prefer. Once the import is working properly though, you can treat the whole { } array as a dataset for other formulae, such as SORT({ },1,1,2,0) or QUERY({ },"order by Col1,Col2 desc") , so you can arrange it more precisely that way – Chris Hick Jul 28 '15 at 16:30
  • 1
    It was the ";" that did the trick. And thanks for the hint to use it as a whole new dataset. This has solved my problem! – FlorianT. Jul 29 '15 at 07:15
  • I was getting the error `In ARRAY_LITERAL, an Array Literal was missing values for one or more rows`. Turns out I needed to run the `IMPORTRANGE` in a separate sheet and allow access/permissions. The error result from the second import was messing up the vertical array. – J.T Apr 29 '19 at 14:57
  • Is it possible to do this using a folder and all sheets within. They all have the data in the first sheet and the same cells – Eoin Jun 28 '19 at 14:27
  • 1
    This does not work for me. It only prints one value – Matej J Aug 04 '20 at 21:18
  • 1
    Does this still work? For me only data from First sheet is displayed – Sniper Jan 04 '21 at 10:01
  • 1
    I suspect you have blank rows at the end of the first sheet. If you scroll further down you should see the other data stacked underneath. You can use a FILTER or QUERY to remove the blanks - see the example sheet linked to my comment above to see this working – Chris Hick Jan 05 '21 at 09:16
23

Of course, it is also possible to combine several IMPORTRANGE() functions with the QUERY() function, which gives us a greater control over the results we import.

For example, we can use such a construction:

=QUERY(
  {
    IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100");
    IMPORTRANGE("key-or-url-of-spreadsheet-2", "'sheet-name-2'!A2:Z100");
    IMPORTRANGE("key-or-url-of-spreadsheet-3", "'sheet-name-3'!A2:Z100");
    IMPORTRANGE("key-or-url-of-spreadsheet-4", "'sheet-name-4'!A2:Z100")
  },
  "SELECT * WHERE Col1 IS NOT NULL ORDER BY Col3 ASC"
)

###Explanation:

The above query removes blank lines from imported ranges:

SELECT * WHERE Col1 IS NOT NULL

and sorts ascending all data collected together in relation to the third column:

ORDER BY Col3 ASC

For descending, just use DESC in place of ASC.

Of course, we can also arrange any other criteria, or omit them displaying everything without modification:

"SELECT * "

###Note:

In order to use the above constructed query, we first need to call a single IMPORTRANGE() method for each of the spreadsheets we want to refer:

=IMPORTRANGE("key-or-url-of-spreadsheet-1", "'sheet-name-1'!A2:Z100")

We have to do this even if we refer to the same spreadsheet in which we write this formula, but for every spreadsheet it is enough to do it once.

This is to be able to connect these sheets and allow access to the sheets (to which we have the access rights anyway):

                                                    enter image description here

After giving permission for all spreadsheets, we can use the above query.

ssn
  • 2,727
  • 6
  • 27
  • 37
simhumileco
  • 31,877
  • 16
  • 137
  • 115
  • 1
    This should be accepted as the right answer. It worked perfectly. – syldman Mar 07 '21 at 20:35
  • 1
    What is the 'key' of the spreadsheet and why do you need it if you're using sheets within the same spreadsheet? – Agent Zebra Jul 08 '21 at 19:54
  • Hi @AgentZebra! We have to use the URL of the spreadsheet or its key because of the `IMPORTRANGE` method specification. You can extract the key from the URL: it's the hash between `/d/` and next `/`. For example, such URL: `https://docs.google.com/spreadsheets/d/11VE20qQ5xnu8uuQGJjCE0cn--_3G3Qd3RkOIIySNTEw/edit#gid=0` has the key like that: `11VE20qQ5xnu8uuQGJjCE0cn--_3G3Qd3RkOIIySNTEw`. – simhumileco Jul 09 '21 at 07:42
  • 1
    It seems that it is also possible to simply use the URL of the spreadsheet as the key which is a lot easier if you are working with many spreadsheets – joidegn Oct 05 '21 at 12:53
1

I am also applying above given formula for getting data from multiple spreadsheet which is getting an error something is like IN ARRAY_LITERAL An array literal was missing values for one or more rows.

jeff porter
  • 6,560
  • 13
  • 65
  • 123
AshwiniM
  • 11
  • 1
  • 3
    If you have a problem don't write an anwers to a question that has been dead for 3 years. Ask a new question, stating your case with specific details and referring to this one. – Markus Deibel Mar 14 '19 at 12:51
  • Although this should be another question, it is a problem you can hit when using the actual answer. And the reason is that the two imported ranges do not have the same number of columns. You might be tempted to import the first range as 1:1 to get the headers. The problem is that if the second range describes A:Y but the sheet has A:Z columns, it will not match the 1:1 import since the 1:1 import will be pulling in A:Z. Follow me? Not sure I do... ;) – RedYeti Apr 02 '19 at 15:33
0

Easy fix: Apply the filter to the entire column / sheet instead of just the current selection. This will automatically update all of the filters to include new additions.

Russ
  • 1