I'm trying to make a sheet that records every 'player's' balance. Transactions come from a form, and I was wondering if I could run a script that
- Reviews each incoming data set
- Adds an amount [what the form said] to the player's balance.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this communityI'm trying to make a sheet that records every 'player's' balance. Transactions come from a form, and I was wondering if I could run a script that
={"NAME", "TOTAL BALANCE";
QUERY({Sheet1!A3:B},
"select Col1, sum(Col2)
where Col1 is not null
group by Col1
label sum(Col2)''", 0)}
={"NAME", "TOTAL BALANCE";
QUERY({'Form Responses 1'!C2:D},
"select Col2, sum(Col1)
where Col2 is not null
group by Col2
label sum(Col1)''", 0)}
={"NAME", "TOTAL BALANCE";
QUERY({'Form Responses'!C2:D},
"select Col2, sum(Col1)
where Col2 is not null
group by Col2
label sum(Col1)''", 0)}
={"NAME"; "TOTAL BALANCE"\
QUERY({'Form Responses 1'!C2:D};
"select Col2, sum(Col1)
where Col2 is not null
group by Col2
label sum(Col1)''"; 0)}
={"NAME"; "TOTAL BALANCE"\
QUERY({'Form Responses'!C2:D};
"select Col2, sum(Col1)
where Col2 is not null
group by Col2
label sum(Col1)''"; 0)}
The previous answer should work, but you can pretty easily achieve this with some much more simple formulas.
In one column, array the unique players using the =unique
formula. Then, in the next column, filter all responses for the first row of players using the =filter
function. Then, you'll want to wrap the =filter
inside of a =sum
to create =sum(filter(...))
. You can then click and drag this filter down to all of your rows.
One note, if you are collecting form data using Google Forms, your formulas will get messed up when there are new form responses. So you'll want to create another sheet where you reference the sheet with the form data to avoid this. If you are no longer collecting new form data, you can ignore this.