Search code examples
arraysgoogle-sheetsarray-formulasgoogle-sheets-queryimportrange

google sheets array formula split and import range

I am trying to construct quite a complex formula but for now, without success, I will appreciate any help! First some context I am trying to synchronize the sheets that stores answer from google forms with other sheets to make a summary so I have for example 3 sheets synchronized with 3 different forms all have a column named student code (it is not named range just a value in the first row). then I have created by another formula comma-separated list of the spreadsheet relevant for the user (for example I know that student 1 is in exercise a b c so I get links of all of the spreadsheets into the appropriate cell as a comma-separated String) so what I want to achieve

  1. import range from each relevant spreadsheet I am trying to achieve this through the formula 
=Arrayformula(Filter(IMPORTRANGE(SPLIT(AB2, ",", TRUE, TRUE),"'Form responses 1'!A1:Z1000")))

but without success

  1. get the first row from this tables
  2. filter the row that I have a student code (the column named studCode with a value of appropriate student code)
  3. join rows from all of those operations so I will have row 1 (the row with question text) to test 1 then row with students answers then row with questions to test 2 student answers to these tests...

I would really appreciate the help (oh and I rather look for achieving it through google sheets formula not by app script function - because import range is so much more efficient than using sheet service in-app script)

I created test cases in order to help with the resolution of the problem a summary spreadsheet where I want to accumulate data:

https://docs.google.com/spreadsheets/d/1cJn8CX25t98GI9E4aYgsQPNt28w_sX0ynfhwkG3ZKyA/edit?usp=sharing

spreadsheets that mimics data imported from forms:

https://docs.google.com/spreadsheets/d/1BgYN7f6ojk7NhOlj2FuSm0goMt_HjqkebWiOJYQmN0E/edit?usp=sharing

https://docs.google.com/spreadsheets/d/19cDQR-tN5_S_rblc-hbavxVF0xforoMaKUQYjuYBN-E/edit?usp=sharing

https://docs.google.com/spreadsheets/d/1QPMcHIH5PXQwWbAULk7vxJ4g-pSXK1qHKhFvLwdRaAU/edit?usp=sharing

Solution

  • try:

    ={""; ARRAYFORMULA("=QUERY({"&TEXTJOIN(";", 1, "IMPORTRANGE("""&
 TRANSPOSE(SPLIT(B2, ","))&""", ""'Sheet1'!A1:Z1000"")")&
 "}, ""where Col1 is not null"", 0)")}

    0

    then copy generated formula and paste it where you need:

    0

    UPDATE:

    ={""; ARRAYFORMULA("=FILTER(QUERY({"&TEXTJOIN(";", 1, "IMPORTRANGE("""&
 TRANSPOSE(SPLIT(B2, ","))&""", ""'Sheet1'!A1:Z1000"")")&
 "}, ""where Col1 is not null"", 0), REGEXMATCH(TRANSPOSE(QUERY(TRANSPOSE(QUERY({"&
 TEXTJOIN(";", 1, "IMPORTRANGE("""&
 TRANSPOSE(SPLIT(B2, ","))&""", ""'Sheet1'!A1:Z1000"")")&
 "}, ""where Col1 is not null"", 0)),,99^99)), A2))")}

    0