Saturday, October 3, 2015

Google Spreadsheet Query and Vlookup Formula


Hi, I am new to using google sheets, but am finding them very helpful. I have 1 problem I haven't figured out yet though.
I have a sheet with the following info.

Name      Den      Amount
Bob         Tiger         55.00
Tom         Tiger       75.00
Grant       Bear         100.00
Zack        Bear          80.00

What I would like to do is get the row that has the MAX value for each Den.
Tom   Tiger     75.00
Grant  Bear     100.00



Have a look at the following screenshot:

I have the following formula in cell G1:
=query(A:C,"select B,max(C) where A<>'' group by B label max(C) 'Amount'")

And then, I have put the following formula in cell F1:

Sunday, September 20, 2015

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


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

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


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

Saturday, September 5, 2015

Google Sheet Query involving counting with differences between columns


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.


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:

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

Thursday, September 3, 2015

Google Sheets Query involving two groups of data


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
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.


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

Tuesday, September 1, 2015

Split list and append Data


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?


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

Thursday, July 23, 2015

Fetch data in MS Excel from Google Sheet


( by Chris Gob Shite )

I have a google form which is capturing data that is saved in a Google Sheet

I want to merge that data into an official form in MS Word

MS Word will only merge with an Excel sheet and not a Google Sheet
Currently just copy/pasting everytime there is a new entry
Just wondering
Any Sheets gurus know how to Create a dynamic link from an Excel Sheet to a data in a Google Sheet?


Following are steps that will allow you to get the data from Google Sheets to MS Excel:

In your Google Sheets, click "File" > "Publish to the web...".
And then select the sheet "Form Responses 1" (from which you want to fetch the data), and then click "Publish".

When you click Publish, you will get the URL of your published Google Sheet, Copy it... We will use this link in MS Excel.

Open Excel 2007 (I am not sure whether this works in earlier versions). Click the "DATA" Tab on the top. There is a "Get External Data" section, click "from web"...

Paste that URL Link in the address bar of the dialog box that opened when you clicked get data from the web.
If you receive any error message like "So you want to continue running scripts on this page".. then Click "Yes".

Click the check box (in yellow color) that appears in the upper left of your website (in the Excel Dialog Box)... and click Import.

It may ask "Where do you want to put the data?", Input whatever option suits you.

And note: In MS Excel, if you need to refresh the data in every minute or every 30 minutes then you can set the time duration:

Click "Properties" in Data tab, and then Click on the check box "Refresh every" and set the time. Also click "Refresh data when opening the file", if you need it.

You are done...!!
Enjoy auto Importing of data from Google Sheets to Microsoft Excel.

When you close MS (after saving it) and open it again, you might notice "Security Warning"

Click on "Options", and then you can "Enable this content" and click "OK"..
But if you don't want to do it every time you open your Excel then you can click on "Open the Trust Centre".

Click on "External Content" and then choose "Enable all Data Connections" and "Enable automatic update for all Workbook Links".

Finally you are done.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: 


Friday, April 25, 2014

Google Spreadsheet Solution With Advanced Query Formula



Who can help me to solve this problem: 

I would like to sum ​​the number of packages in a column having a specific code for each month. 
How can I do?



Screenshot of Sheet1:

Screenshot of Sheet2:

I have the following formula in Cell A1 of Sheet2:

=query(arrayformula(iferror(if({1,1,1,0},Sheet1!A:C,if(Sheet1!B:B="","","Q.TA' "&text(Sheet1!B:B,"MM MMM"))))),"select Col1,sum(Col3) where Col1 is not null group by Col1 pivot Col4")

I hope the above solution will help you, and if you need more help then please do comment below on this blog itself, I will try to help you out.

I also take up private and confidential projects:
If this blog post was helpful to you, and if you think you want to help me too and make my this blog survive then please donate here: