2

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.
  • 1
    can you provide some examples? or copy of your sheet? – user0 Feb 25 '19 at 1:36
0

={"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
  • Yup, the first one worked. Thanks. – Nathan Hoffman Feb 26 '19 at 12:02
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. enter image description here

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.