Combine different sheets with importrange for dynamic data from google forms

Question

I have 4 identical google sheets that get continuously filled with data from 4 different google forms. I want to merge the 4 resulting sheets into one Final Sheet. Moreover I want to be able to distinguish the data in the final sheet by which form (and thus sheet) it comes from for chart purposes, adding this to the next column. How can i manage it using IMPORTRANGE?

I've tried with the formula:

=ARRAYFORMULA(
  QUERY(
   {IMPORTRANGE("sheetkey","worksheetA!A2:D"),
    IF(ISBLANK(IFERROR(IMPORTRANGE("sheetkey","worksheetA!A2:A"))),,"data01");
    IMPORTRANGE("sheetkey","worksheetB!A2:D"),
    IF(ISBLANK(IFERROR(IMPORTRANGE("sheetkey","worksheetB!A2:A"))),,"data02")},
  "select * where Col1 is not null"))

but it doesn't seem to work with dynamic data. Any suggestions?

Answer 1

Welcome!

Perhaps it is worth considering the possibility to execute a QUERY() for each IMPORTRANGE() separately and combine the results of these queries into a single array?

={QUERY( 
  IMPORTRANGE("sheetkey","worksheetA!A2:D"),
    "select 'data01', Col1, Col2, Col3, Col4 where Col1 is not null label 'data01' ''");
  QUERY(
   IMPORTRANGE("sheetkey","worksheetA!A2:A"),
     "select 'data02', Col1, ' ', '  ', '   ' where Col1 is not null label 'data02' '', ' ' '', '  ' '', '   ' ''");
  QUERY(
    IMPORTRANGE("sheetkey","worksheetB!A2:A"),
      "select 'data03', Col1, ' ', '  ', '   ' where Col1 is not null label 'data03' '', ' ' '', '  ' '', '   ' ''")
    }

Answer 2

You shouldn't use importrange() to copy data within a spreadsheet file. Use a cross-sheet reference instead. To merge data from several tabs, use reduce() and vstack(), like this:

=let( 
  stackSheets_,lambda(sn,ra,reduce(tocol(æ,2),sn,lambda(rs,s,let(r,indirect(s&"!"&ra),l,max(index(row(r)*iferror(r<>""))),d,filter(r,row(r)<=l),r_,lambda(v,byrow(d,lambda(_,v))),if(counta(iferror(d)),vstack(rs,hstack(r_(s),d)),rs))))),

  sheetNames, vstack("worksheetA", "worksheetB"), 
  rangeA1, "A2:D", 
  stackSheets_(sheetNames, rangeA1) 
)

See let(), lambda(), reduce(), vstack(), hstack(), filter() and iferror().