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.
-
1can you provide some examples? or copy of your sheet? – user0 Feb 25 '19 at 1:36
={"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)}
-
I got the following error: In ARRAY_LITERAL, an Array Literal was missing values for one or more rows. Sheet: imgur.com/jTC46Ss – Nathan Hoffman Feb 25 '19 at 12:30
-
your range is C2:D that's 2 columns. eg Col1 and Col2. but in your formula, you have Col3 and Col4 which do not exist because you selected C2:D (2 columns only) - this results in error – user0 Feb 25 '19 at 12:39
-
Okay. First of all, I fixed that. Second, I noticed it was selecting Sheet1 and the responses go in Form Responses 1. I fixed that, but now I get the following: 'Formula parse error'. Shows up as #ERROR! on sheet. Image: imgur.com/a/WH2cqrv – Nathan Hoffman Feb 25 '19 at 22:03
-
@NathanHoffman answer updated. one of those last 4 formulas should work for you – user0 Feb 26 '19 at 2:42
-
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.
