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) }

Google Sheet Query involving counting with differences between columns

Question:

Here's a sample of what the sheet looks like:

A   B    C
1    0    Win
2    0    Win
3    0    Lose
4    4    Lose
5    0    Win
3    2    Lose
2    0    Lose
0    1    Win
1    2    Lose
I want to figure out what the percentage of wins to losses are based on the difference between A and B. So the result should be:
Diff   Percentage
5       1
3       0
2       0.5
1       0.5
0       0
-1      0.5
The query in normal SQL looks like this:
select A-B as Diff, cast(sum(case when C = 'Win' then 1 else 0 end) as float) / cast(count(*) as float) as Percentage from test group by A-B;
I, however, don't know how to do case in sheets queries, and I've read that you can't. I could figure out how to get the differences and the number of times the difference occurred, but I can't seem to figure out how to count the different outcomes in column C. In SQL I would use a case and condition on the possible values.
If someone could help me figure this out, that'd be great. I'd also appreciate it if you could explain how the final query works so I can follow along and figure out where I went wrong. Thanks.

Solution:

Have a look at the following screenshot:

I have the data in columns A,B and C.
Now we need to generate an extra column in which we have "1" corresponding the third column having value "Win".
So I have the formula in cell F1:
=arrayformula({A:C,if(C:C="Win",1,0)})

and as you can see we have generated the column I with the help of above formula. To avoid the "0" where there is no value in first column, we have the following formula in cell L1:
=query(arrayformula({A:C,if(C:C="Win",1,0)}),"select * where Col1 is not null")

Now have a look at the following screenshot:

I have the following formula in cell E1:
=query(arrayformula({A:C,if(C:C="Win",1,0)}),"select Col1-Col2,sum(Col4),count(Col3) where Col1 is not null group by Col1-Col2 order by Col1-Col2 desc label Col1-Col2 '',sum(Col4) '',count(Col3) ''")

and now we can divide the 2nd column (F) generated by 3rd column (G) generated.

So finally we have:

I have the following formula in cell E1:
=query(arrayformula({A:C,if(C:C="Win",1,0)}),"select Col1-Col2,(sum(Col4)/count(Col3)) where Col1 is not null group by Col1-Col2 order by Col1-Col2 desc label Col1-Col2 '',(sum(Col4)/count(Col3)) ''")

Google Sheets Query involving two groups of data

Question:

The easiest way to explain what I'm trying to do is to give an example of the data, so here's a simple example:

A                B          C            D
Name1        4          Name4     3
Name2        3          Name4     2
Name3        5          Name1     1.4
Name4        3          Name3     6
Name5        1          Name6     8
Name2        3          Name7     10
Name1        4          Name5     2
With that above data, I want to produce a pair of columns, the first that has a list of all the unique entries in A and C, the second having the average of the corresponding values in either B or D for the entry in A or C. The result should look something like:
Name1      3.133333
Name2      3
etc.
Columns A and C are guaranteed to be strings, B and D are guaranteed to be positive numbers. It is also guaranteed that A and C will never contain the same value.
Then I also want to do another query similar, except instead of taking the average of all the corresponding values, I want to count the number of times some specific value occurs with respect to the total. As an example:
A                B          C            D           E
Name1        4          Name4     3           Y
Name2        3          Name4     2           Y
Name3        5          Name1     1.4        N
Name4        3          Name3     6           Y
Name5        1          Name6     8           Y
Name2        3          Name7     10         N
Name1        4          Name5     2           Y
In this case, I want to count the percentage of times Y occurs in E over the total number of times each item in A and C occurs. This time, B and D are no longer important. For example, Name1 occurs 3 times, and has an E value of Y twice, so I want 0.66667. So the result should look like:
Name1    0.66667
Name2    0.5
Name3    0.5
Name4    1

If at any point any of this isn't clear, please ask so I can clarify. Thanks.

Solution:

Screenshot of Spreadsheet:

I have the following formula in cell G1:
=query({A:B;C:D},"Select Col1,avg(Col2) where Col1<>'' group by Col1 label Col1 'Name', avg(Col2) 'Average' format avg(Col2) '0.00'")

and the following formula in cell J1:
=query(arrayformula(iferror(query({{A:A,E:E,E:E};{C:C,E:E,E:E}},"select Col1,count(Col3) where Col1<>'' group by Col1 pivot Col2")*1,query({{A:A,E:E,E:E};{C:C,E:E,E:E}},"select Col1,count(Col3) where Col1<>'' group by Col1 pivot Col2"))),"select Col1,(Col3/(Col2+Col3)) label Col1 'Name', (Col3/(Col2+Col3)) 'Percentage' format (Col3/(Col2+Col3)) '0.00'")

Split list and append Data

Question:

I have a sheet called Master which has 3 columns: Name Home_Address, Work_Address. Note that a Name may have one or both associated addresses.

In a second sheet, Called Split. This sheet has 3 columns: Name, Address, Type I want to split each name and address to make 1 row for each Address, be it work or home, and add a last column, Type, with either Work or Home for values.  This will be based on which column the address comes from
How can I make this split list?

Solution:

Screenshot of Sheet "Master":

Screenshot of Sheet "Split":

I have the following formula in cell A2 of Sheet "Split":
={query(Master!A2:B,"select A,B,'Home' where B<>'' label 'Home' ''",0);query(Master!A2:C,"select A,C,'Work' where C<>'' label 'Work' ''",0)}