Combining multiple Google sheets into single Google spreadsheet

2
votes

I have multiple google sheets (45 of them, all dynamic, its content will keep on changing) and I want to combine data of all of them into a single file. I was hoping to use some importrange or QUERY function so that moment one / any file is updated, the changes reflect automatically on the combined sheet.

Example book 1 :

https://docs.google.com/spreadsheets/d/1sOkV9BT8ptndE0xcN2MUi4tTZ5p-aZxuLY_u29Y6nBI/edit?usp=sharing

Example book 2 : https://docs.google.com/spreadsheets/d/1jZml9oVI2Flma407AB4ndl_ZIsQ_5I8zhkzvbL_EO_g/edit?usp=sharing

Example combined book : https://docs.google.com/spreadsheets/d/1LkiIY1aUmwaouR7f4KhaGs6MV365KY7DYUPMiTzxjvc/edit?usp=sharing

1
google-sheets

1 Answers

1
votes
  • first you need to connect them by pasting each IMPORTRANGE formula in some cell to get #REF! error
  • then hover over that #REF! error with your cursor and a secret magic button will popup
  • click on it to allow access then you can stack up all import ranges and wrap them in a query:
=QUERY(
 {IMPORTRANGE("ID1-or-URL1", "Sheet1!A1:A"); 
  IMPORTRANGE("ID2-or-URL2", "Sheet1!A1:A");
  IMPORTRANGE("ID3-or-URL3", "Sheet1!A1:A");
  IMPORTRANGE("ID4-or-URL4", "Sheet1!A1:A")}, 
 "where Col1 is not null"), 0)

update:

={       IMPORTRANGE("1sOkV9BT8ptndE0xcN2MUi4tTZ5p-aZxuLY_u29Y6nBI", "Sheet1!A1:N2"); 
  QUERY({IMPORTRANGE("1sOkV9BT8ptndE0xcN2MUi4tTZ5p-aZxuLY_u29Y6nBI", "Sheet1!A3:N" ); 
         IMPORTRANGE("19SFnJab9xVW2SenK-8IuG2cQRT2exJeyqARjTdDE_R8", "Sheet1!A3:N" )}, 
  "where Col1 is not null", 0)}