I have 10 columns in 10 Google sheets and in all sheets are the data labeled like

Month | Total number of winter/summer schools attended | Sr. No. | Title | Date | Venue | Organisation | Number of Female Participants | Total number Participants

Now I want to merge all columns in another sheet. Any formula or any option to marge all the sheet data in one sheet when they write and the new sheet fill automatically in Google Sheets.

Any help is appreciated.


To move data from spreadsheet to another spreadsheet you need to use IMPORTRANGE formula.

=IMPORTRANGE("ID"; "Sheet1!A1:C")

  • where "ID" is:


  • and "Sheet1!A1:C" is sheet name and range to be imported

In order to import multiple sheets from one spreadsheet to another you can use array stacking:

={IMPORTRANGE("ID1"; "Sheet1!A1:C30");
  IMPORTRANGE("ID1"; "Sheet4!A1:C40");
  IMPORTRANGE("ID2"; "Sheet3!B1:D20")}

note: number of columns in all formulas needs to be same

To get rid off empty rows you are able to wrap it into QUERY and do:

 IMPORTRANGE("ID1"; "Sheet1!A1:C");
 IMPORTRANGE("ID1"; "Sheet4!A1:C");
 IMPORTRANGE("ID2"; "Sheet3!B1:D")}; "where Col1 is not null"; 0)

note: each IMORTRANGE formula with unique ID needs to be authorized first separately

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.