# Help with a google sheet query for a billing app **URL:** \ **Category:** Ask for Help\ **Created:** [May 14, 2020, 12:14am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099 "2020-05-14T00:14:38Z")\ **Posts on this page:** 20\ **Page:** 1
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 14, 2020, 12:14am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/1 "2020-05-14T00:14:38Z")
I have a billing app that has 3 unique data - bill title, due date, and payer. Every time a biller uses the app to bill a payer, these three pieces of information are recorded. The original data, looks a bit like this: ![Screen Shot 2020-05-13 at 3.47.09 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/60f8d8895491141330995a1f862a6254b0754273.png) I’m looking for a query that takes this data and only extracts information pertinent to the latest date for each bill along with all the associated Payers affected. My results would ideally look like this: ![Screen Shot 2020-05-13 at 3.49.13 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/6996ec0dd5d9b4ee644a47c365c43f9565732e6a.png) Keep in mind each bill has monthly billing dates and that payers can be added or removed each billing cycle. The closest I have come is this query: ARRAYFORMULA(VLOOKUP(QUERY({ROW(A2:A),SORT(A2:C)}, “SELECT MAX(Col1) WHERE Col2 IS NOT NULL GROUP BY Col3 LABEL MAX(Col1)‘’”,0),{ROW(A2:A), SORT(A2:C)},{2,3,4},0)) Unfortunately, this query does not enable me to assign more than one bill to a Payer and I have been unable to come up with a query that does. Here are what the results look like for this query: ![Screen Shot 2020-05-13 at 3.50.27 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/c/c03c23f2c596b63b8e40a1c7d7d92dc8ae6fb19f.png) I would really appreciate any ideas folks in this forum might have. Any help or thoughts would be appreciated! ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 14, 2020, 12:49am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/2 "2020-05-14T00:49:59Z")
Hi, here’s my 2 cents on your problem. You’re having trouble with it because you want to return multiple matches from both the payer & bill title columns, but there’s no unique column to do so. My idea is to concatenate them into one helper column, and do a simple query using that. ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/628594ba337e94b73f2f90d644fa16a2667f8525.png) The formula: `={"Helper";ARRAYFORMULA(IF(A2:A<>"",CONCAT(B2:B,C2:C),""))}` Your desired result: ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/e/e1a3ca15d8b464ee3e69f04fdeb0698f16f30fa2.png) The query to achieve it: `=QUERY(A2:D,"SELECT MAX(A), B, C GROUP BY B, C, D ORDER BY C LABEL MAX(A)''",-1)` Tell me if I can help more. ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 14, 2020, 8:30pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/3 "2020-05-14T20:30:50Z")
Thank you so much for your suggestion, ThinhDinh. It seems to work. I did have a followup question. I actually have, in my original data set, columns of data all the way to column I that I would like to have included in the query results. Can you please tell me how to adjust your suggested query to include these addition columns in the result? I simply want the query results to include the relevant data from columns E thru I. I tried changing the query as follows but keep getting the attached error: =QUERY(A2:I,“SELECT MAX(A), B, C, D, E, F, G, H, I GROUP BY B, C, D ORDER BY C, D, E, F, G, H, I LABEL MAX(A)’’”,-1) ![Screen Shot 2020-05-14 at 1.28.56 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/6/6139eca5fd15f60ee65fa1bb690d46b228a01612.png) Really appreciate your interest and offer for help. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 14, 2020, 11:45pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/4 "2020-05-14T23:45:55Z")
Hi Fassil, If you want more columns to be in the query results, just put them all in the GROUP BY clause. It looks like this: `=QUERY(A2:I,“SELECT MAX(A), B, C, D, E, F, G, H, I GROUP BY B, C, D, E, F, G, H, I ORDER BY C, D, E, F, G, H, I LABEL MAX(A)’’”,-1)` Tell me if it works! ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 15, 2020, 12:31am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/5 "2020-05-15T00:31:48Z")
I know I’m a little late here and if you have a solution that works, then that’s fine, but I think this maybe can be achieved within Glide. If the intention is for the user to sign in and simply see’s a list of latest bills, then here’s a simple solution all within Glide. In Billing sheet, create template (helper) column that joins the payer and bill title (much like @ThinhDinh mentioned) Use that template to create a relation column back to the same sheet and same template column. This will give you a relation of all matching payer/title combinations. Next create a Rollup column that gets the latest date for the payer/title combination. Next create an if/then column that compares the rollup column to the date column. If it matches, return ‘true’, else return ‘false’. Now you can filter the list by signed in user and when the if/then column is ‘true’. This will show only the latest bills for each payer/bill title. ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 15, 2020, 12:36am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/6 "2020-05-15T00:36:54Z")
Hi ThinhDinh. Unfortunately, I get a parse Error message with that. I am not familiar with Group By queries…do you have to list every column you want included in the results after GROUP BY Aand ORDER BY for this kind of query to work? Also, there is “B” missing after ORDER BY in your query - is that on purpose on your part? For the record, I still get an error whether I put the “B” in there or not. I really feel bad about taking up your time on this but no changes I tried with seem to work. Let me know if you see anything that is obvious. And thank you! ![Screen Shot 2020-05-14 at 5.30.52 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/1/18ccdc7337a23761116c5ab4e396deeabf3b0eb6.png) ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 15, 2020, 12:55am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/7 "2020-05-15T00:55:13Z")
My mistake. If you have columns from A to I, then setup a helper column in J. The query to achieve your desired result would be: ``` =QUERY(A2:J,"SELECT MAX(A), B, C, D, E, F, G, H, I GROUP BY B, C, D, E, F, G, H, I, J ORDER BY C, D, E, F, G, H, I LABEL MAX(A)''",-1) ``` If you have time, try Jeff’s method as well. Here’s a test sheet: [https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F\_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1244323104](https://docs.google.com/spreadsheets/d/1sEQHpSKyHx9w2F_Xx3lm56SU1p-8eI81OYU5TYvQFvw/edit#gid=1244323104) ---
**Author:** ![Wiz.Wazeer](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/wiz.wazeer/32/72423_2.png) [@Wiz.Wazeer](https://community.glideapps.com/u/Wiz.Wazeer)\ **Post date:** [May 15, 2020, 2:11am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/8 "2020-05-15T02:11:12Z")
I tried your method today. Works fine. Learned something new. ---
**Author:** ![ThinhDinh](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/thinhdinh/32/49_2.png) [@ThinhDinh](https://community.glideapps.com/u/ThinhDinh)\ **Post date:** [May 15, 2020, 2:13am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/9 "2020-05-15T02:13:37Z")
Nice to hear. Hit me up if any problem occurs. ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 17, 2020, 5:00pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/10 "2020-05-17T17:00:43Z")
Thank you very much for stepping in and for the suggestion, Jeff. I wanted to be on the same page with you with respect to the fact that my primary customer would be the Biller and not the Billers customer before implementing. I am not intending for any of the Billers customers to use my app - just the Billers. As such, I have a couple of questions: 1. When you say “If the intention is for the user to sign in and simply see’s a list of latest bills” I assume the “user” you are referring to is the Biller. Is that correct? 2. Is everything you are suggesting above happening on the same single sheet (Billing Sheet)? 3. What do you mean by “Use that template to create a relation column back to the same sheet and same template column.” What is the “template” in relation to the Billing Sheet? 4. What is the best way to create the Rollup Column with the latest dates (eg. query? IF statement?) 5. When referring to the ‘date’ column - are you referring to the last billing date (as entered by the biller)? 6. Is there anyway for me to get the user email independent of the user submitting a form? In other words, is there a place I could automatically go to within my sheet where the email of any user that is currently using the app can be found in real time? Thanks in advance for your insight! ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 18, 2020, 11:53pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/11 "2020-05-18T23:53:57Z")
Just a note, non of the following involves an additional changes within the google sheet. This is all done within the glide data editor. 1. I was referring to the Payer, so everything I said before was assuming it was the payer signing in and seeing their own data. 2. Yes, everything would be in the same sheet as far as the additional columns, but in the Glide data editor. Not the actual spreadsheet. 3. Just like @ThinhDinh mentioned with a helper column that joins to column values together into one, you would create a template column in the Glide data editor to join the payer and bill title together in one column. Then create a relation column. In the relation you would set the template column as the initial value and then select the same sheet and the same template column in that sheet. So essentially you are creating a relation for that payer and bill, but it will relate to all similar payer/bill combinations in the same sheet. [https://docs.glideapps.com/all/reference/data-editor/computed-columns/template-column](https://docs.glideapps.com/all/reference/data-editor/computed-columns/template-column) [https://docs.glideapps.com/all/reference/data-editor/computed-columns/relation-column](https://docs.glideapps.com/all/reference/data-editor/computed-columns/relation-column) 4. The rollup column would be created in the data editor and use the relation you created to find the latest bill date of the related payer/bill combination. [https://docs.glideapps.com/all/reference/data-editor/computed-columns/rollup](https://docs.glideapps.com/all/reference/data-editor/computed-columns/rollup) ![image](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/1/12ec3883aa8d4e01b55a76b72e5e08744046f3fe.png) 5. Yes, I’m referring to the date entered by the biller. 6. Sure, you could create a choice component that uses in the form that is built from the list of users as long as you have a sheet that contains those users. [https://docs.glideapps.com/all/reference/components/pickers/choice](https://docs.glideapps.com/all/reference/components/pickers/choice) ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 19, 2020, 12:36am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/12 "2020-05-19T00:36:26Z")
Thank you so much for the detailed response, Jeff. If my intention was for this just to be for the Biller - with no Payers having access - what would change? ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 19, 2020, 1:11am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/13 "2020-05-19T01:11:12Z")
Nothing really. I don’t know how you intend to display everything, but I’m picturing a simple filter on a list that only looks for ‘true’ in the if/then column. This will display the latest bill for each payer. ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 19, 2020, 10:51pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/14 "2020-05-19T22:51:19Z")
Thank you so much for the detailed explanation, Jeff. I am trying to implement but have a question. What do you mean by: “Then create a relation column. In the relation you would set the template column as the initial value and then select the same sheet and the same template column in that sheet.” Does this mean create a relation column of the Template column with itself? I don’t understand how “select the same sheet and the same template column in that sheet” can mean anything else. Assuming my interpretation is correct, it does not seem to work. ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 19, 2020, 11:07pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/15 "2020-05-19T23:07:18Z")
Yes, the relation would like the template column in that row to the same template column in the same sheet. Yes, it’s relating to itself, but will also pick up any other rows that have the same template value. What does not work? What is the relation showing? ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 19, 2020, 11:17pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/16 "2020-05-19T23:17:20Z")
Here are what the columns look like. Does not look like the Rollup is picking up the latest dates. ![Screen Shot 2020-05-19 at 4.16.39 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/b/bacb58af7c2e921d9cc926967aa79ff349a1bbcb.png) ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 19, 2020, 11:51pm UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/17 "2020-05-19T23:51:25Z")
> [@Jeff\_Hager](#): > > you Not sure if it helps but when I try to do the Rollup, the “Done” button is grayed out. ![Screen Shot 2020-05-19 at 4.50.25 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/1/1f1ded9c010d6e5aee689b0e7fd74454810e9445.png) ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 20, 2020, 12:19am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/18 "2020-05-20T00:19:30Z")
This appears to currently be a bug. I’m guessing you renamed your relation column after you created it. Delete it and recreate it with the proper name, then create the rollup column again. > [@Rollups for count](https://community.glideapps.com/t/rollups-for-count/8146/20): > > I think you definitely identified a bug. Possibly with the new ability to rename columns in combination with using a rollup against a renamed relation. Did you happen to rename the relation column after you created it? I was able to reproduce the problem. First I created an identical relation and then created a rollup column against that relation. Everything worked fine and I was able to get a count of the true reserved checkboxes. It wasn’t until I renamed the relation column that I could… ---
**Author:** ![Fassil\_Befekadu](https://avatars.discourse-cdn.com/v4/letter/f/df705f/32.png) [@Fassil\_Befekadu](https://community.glideapps.com/u/Fassil_Befekadu)\ **Post date:** [May 20, 2020, 12:50am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/19 "2020-05-20T00:50:35Z")
Glad you were able to verify. Thank you for putting the time you put in duplicating and for all your help. I am trying to implement your recommendations but am getting stuck on the IF/Then part. What do you mean by: “Next create an if/then column that compares the rollup column to the date column. If it matches, return ‘true’, else return ‘false’. Now you can filter the list by signed in user and when the if/then column is ‘true’. This will show only the latest bills for each payer/bill title.” Is the ‘date column’ you are referring to - todays date? And when you say ‘if it matches’ - do you mean ‘if the rollup value equals today’s date’? What were the exact conditions you used for the attached IF/Then box? ![Screen Shot 2020-05-19 at 5.48.10 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/2X/5/5109d637fa3d75b444f5731aa28ef18e118a8b80.png) ---
**Author:** ![Jeff\_Hager](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/jeff_hager/32/43_2.png) [@Jeff\_Hager](https://community.glideapps.com/u/Jeff_Hager)\ **Post date:** [May 20, 2020, 1:22am UTC](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099/20 "2020-05-20T01:22:28Z")
Ugh, I keep forgetting that you can’t compare two column values to each other, so I’m going to further complicate this with additional steps. Now that your have your first relation and rollup combo, now we need to create another template to that will join the original date/payer/title columns together. Then create yet another template that will join the rollup/payer/title together. Create a relation column that links the rollup/payer/title template to the date/payer/title template. Then you can create an if/then column checking if the relation is empty or not. Or you can skip the if/then column and add a filter on your list that will check if the relation is empty or not. [Next page](https://community.glideapps.com/t/help-with-a-google-sheet-query-for-a-billing-app/9099.md?page=2)