0

I'm trying to fetch data from multiple google sheets into another google sheet. The sheets contain data of the customers on a monthly basis. I want to create a master sheet with the complete customer data. I tried the ImportRange formula but it does not take the other sheet's data and the previous data gets deleted.

Suppose there are 3 Google Sheets, namely, "Jan", "Feb", "March" I have a Google sheet named "Master customer Data", I want to add the complete customer details in this sheet ( if in the future the data gets updated (in "Jan", "Feb", and "March" Sheets) then this sheet ("Master customer Data") needs to be updated automatically).

P.S: The data contains: DOB, Names, contact numbers and User IDs (mix of numbers and alphabets)

Thank you

Raman Singh
  • 73
  • 5
  • Based on your criteria `importrange` should do the job. bit unclear on the part where you mentioned `I tried the ImportRange formula but it does not take the other sheet's data and the previous data gets deleted` Can you maybe shed some light on this point here?! – rockinfreakshow Mar 07 '23 at 08:04
  • If I use the Import Range formula (For January Data) in the "Master customer Data" sheet (A1:A10), it shows the data but I want the display February data from A11:A20 and the n March Data from A21:A30). Import Range formula only displays data from the last formula, here January and February data will not be displayed. – Raman Singh Mar 07 '23 at 08:08
  • 1
    then restrict the Jan data to strictly 10 rows by using `=array_constrain(jan_data,10,1)` – rockinfreakshow Mar 07 '23 at 08:14
  • Could you please share the complete formula or is this enough? – Raman Singh Mar 07 '23 at 08:28
  • Will it work for a large dataset as well ? – Raman Singh Mar 07 '23 at 08:29
  • 2
    wrap your existing Jan `importrange` formula within `array_constrain` to restrict the output to 10 cells (A1:A10) so that when you use Feb Importrange formula in A11, the formula in A1 doesnt throw an error. If you need further clarity please do share some sample sheets with demo data for us to work on. – rockinfreakshow Mar 07 '23 at 08:32
  • 1
    Does this answer your question? [Combining multiple spreadsheets in one using IMPORTRANGE](https://stackoverflow.com/questions/31657470/combining-multiple-spreadsheets-in-one-using-importrange) – Daniel Mar 07 '23 at 16:41

1 Answers1

2

Try this, replace the sheet_link with your data sheet links:

=Query({IMPORTRANGE("sheet_link", "Jan!A:D");IMPORTRANGE("sheet_link", "Feb!A:D");IMPORTRANGE("sheet_link", "March!A:D")},"select Col1, Col2, Col3, Col4 where Col1 is not null")

It will return all rows from 3 sheets whose 1st column is not blank. You may edit "Col1" as required or add multiple conditions. You may also add more sheets.

Do wait for a few seconds if the formula shows an error as it takes time to load.

Ganesh Chandak
  • 61
  • 2
  • If I want to show Column 27 to be from all the 3 sheets then should I write, "("select Col27, where Col27 is not null")"? I want to show the 27th column only from all the 3 sheets. – Raman Singh Mar 12 '23 at 06:32
  • 1
    Replace importrange A:D with AA:AA(with the column name) and then "select Col1 where Col1 is not null". – Ganesh Chandak Mar 12 '23 at 07:04