Calculations thru google sheet


I have a doubt about using queries in google sheet to process information.

I did the queries as suggested by the forum, and they are working. But i forgot about the users, they are the ones that add information and the result should be different to each.

If i do it with google sheet, the results will be the same. I already tried adding a user code. But i'm not understanding how to achieve what i need.


Show your relevant blocks. Show your queries. Show what you expect.

I see you use a google form to load data to the sheet. Is this fed from the app?


yes the user add countries and thru google form i load data in google sheets. I did this procedure, with your help to compare with the main list ("Folha 2" tab) and do the calculations i need.

Your suggestion, or the forum's suggestion, was because it is faster than creating blocks with countless calculations. And it really is, is fast, is great !!!

But the list must be different for each user. The way I did with the blocks I send attached and the formulas in google sheet when I open the app with another user I always have the same list regardless.

As i mentioned before taking into account the added countries (by the user) and the main list (that is global) that is in tab "Folha 2" in Google Sheets, when they click in a "Result" Button, the app must show a list of the names that have less that 5 countries taking into account the countries the user added. He has his own list.

By now is showing all the countries added regardless of the user.

Even if i add a user code, if the user is at the same time than other using the app, what happens ?

I already tryed to know better about queires, used different formulas, but maybe i'm not thinking the right way.


It has become very confusing....(for me).

What is the query here:


Sorry, it's
Select N where Q matches 'ok'

It's normal for a game app, for example to do calculations to each player. That's what i'm trying to do.

If i do this calculations with the blocks, the app takes a long time, frozen and sometimes stops. At least with my calculations, that's what happens, the alternative was for the google sheet to do it.

Why not try:

SELECT N where N matches 'user' AND Q matches 'ok'

where 'user' is the name of the app user (e.g. Nathan or Sam)

The user, is code number 1,2,3, etc. is not related to the names in the list.

You appear to be querying the sheet called Folha2? This section?

If I sent the query:

SELECT N,O,P,Q,R where N matches 'Nathan' AND Q matches 'ok'

I should get back:

KEY1	count KEY1	KEY2	KEY3	KEY4
Nathan	        2 	  6     ok  	4

Is this not what you want ?

Is not that. For user 1 is not Nathan. For user 1 the result shoud be Nathan and Sam.

The result that you are seeing in the google sheet (N:R) is for user 1, because i was testing, it has an if condition. I'm trying to understand how can i see the result for user 2 or 3.

I want to compare the countries added by user in the app.
for example user 1 added Angola, Dinamarca, China e Ucrania with the list that is in column A:E

My problem is if i am user 2, now the way the formula is created i'll get the results of user 1 and 2. Not only user 2.

I am struggling with this today, perhaps a good nights sleep and I will be able to get my head around it!

Ok Tim, my english probably is not helping. Thank you, hope you can help !

Hello Tim,

I already have the solution. Someone help me to adjust the query taking into account the user.

Thank you.

Yes, I saw on your sheet that someone had beaten me to it :smiley:

Please show your solution so that others may benefit.

To compare the countries added by user 1, 2 and so on with the main list that was Folha 2 tab


=ArrayFormula({"User"\A1:E1;QUERY(SE.ERRO({PROCV(B2:B;G2:H;2;FALSO)\A2:E});"Select * Where Col1 Is Not Null Order By Col1 Asc")})


to help those using google sheets in a UK/USA/English locale:

=ArrayFormula({"User",A1:E1;QUERY(IFERROR({VLOOKUP(B2:B,G2:H,2,FALSE),A2:E}),"Select * Where Col1 Is Not Null Order By Col1 Asc")})

or it can be written this way:

={"User",A1:E1;ARRAYFORMULA(QUERY(IFERROR({VLOOKUP(B2:B,G2:H,2,FALSE),A2:E}),"Select * Where Col1 Is Not Null Order By Col1 Asc"))}

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.