Search code examples
arraysgoogle-sheetsimportgoogle-sheets-formulaspreadsheet

how to combine many google sheets in one sheet by using query

I created two online sheets and tried to collect them in another one sheet by query but there a missing data although it's exists in the original sheets (data sources), so why that query hide some data and leave the others.

=QUERY({importrange("1xytv1Cl2ANXZP4paiNn4Zejvgl2BBKOLgbdEqeYL_KM","After Sales!A1:AI10");importrange("1gmimVYQQeN_v7sl6yyyvPdN6FkIO7NYADvTf6tGj2-k","Sales!A1:AI10")},"Select * Where Col1 is not null",1))

enter image description here

enter image description here

Solution

  • try:

    =INDEX(QUERY(TO_TEXT({
 IMPORTRANGE("1xytv1Cl2ANXZP4paiNn4Zejvgl2BBKOLgbdEqeYL_KM", "After Sales!A1:AI10");
 IMPORTRANGE("1gmimVYQQeN_v7sl6yyyvPdN6FkIO7NYADvTf6tGj2-k", "Sales!A1:AI10")}), 
 "where Col1 is not null", 1))