Google Spreadsheet Column of "from" labels along each IMPORTRANGEd row of data

Question:

Hi all, I've asked it before but got no reply. I will try a more visual approach this time.

Here is what I have in a spreadsheet:

Sheet description    |  URL to IMPORTRANGE

Classroom 1          |  https://docs.google.com/spreadsheets/d/1yjSEFek(...)

Classroom 2          |  https://docs.google.com/spreadsheets/d/yadayada(...)

So I am going to import ranges from the URLs(spreadsheets) described in the second column. This imported data from all of them will be placed somewhere in the sheet, one below the other.

But here is the final result I need (and I still haven't found a way to do it):

Imported from    |  Student   | Math grade   | Biology grade | ...

Classroom 1      | John       |     8        |      6        | ...

Classroom 1      | Paul       |     5        |      8        | ...

Classroom 1      | George     |     6        |      7        | ...

Classroom 2      | Allan      |     9        |      4        | ...

Classroom 2      | Graham     |     7        |      7        | ...

Classroom 2      | Tony       |     4        |      8        | ...

Imported data placed in the columns labeled "Student", "Math grade" etc. That's OK. But I need to know where each line came from, using the labels from the "Sheet description" column, in another column alongside the imported ones.

I tried a lot of ways of doing this along every IMPORTRANGE in an ARRAYFORMULA, but nothing works. I spent four days studying how to write custom formulas, just to find that I can't use SpreadsheetApp.openByURL() in custom formulas anymore.

I don't know what the **** else can I do.

Thanks for any help.

Fabricio Rocha

Brasilia, Brasil

Solution:

Have a look at the following screenshots:

Following is the screenshot of "Sheet1" of Spreadsheet "Classroom 1":

Following is the screenshot of "Sheet1" of Spreadsheet "Classroom 2":

Following is the screenshot of "Sheet1" of Spreadsheet "Master - All Classrooms":

Following is the screenshot of "Sheet2" of Spreadsheet "Master - All Classrooms":

I have the following formula in cell "A1" of "Sheet2" of Spreadsheet "Master - All Classrooms":

={ query(importrange(Sheet1!B2,"Sheet1!A:C"),"select '"&Sheet1!A2&"',Col1,Col2,Col3 where Col1<>'' label '"&Sheet1!A2&"' 'Imported From'",1) ; query(importrange(Sheet1!B3,"Sheet1!A2:C"),"select '"&Sheet1!A3&"',Col1,Col2,Col3 where Col1<>'' label '"&Sheet1!A3&"' ''",0) }