1

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?

1
  • This question has been bumped by Community User 4 times.
    – Rubén
    Oct 17 at 5:47

1 Answer 1

0

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' '', ' ' '', '  ' '', '   ' ''")
    }

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