Group and Store Column Data

[Amal]

Hi Gliders!

I have a column that rounds up gold coins based on what users spends on, and this data is used to calculate the total gold users have remaining.

Issue is, the data is created in a registration form sheet, which I need to reset once in two days, so I don’t use up all rows, but I want this column data to be saved in another sheet, gold spent needs to be group as per user email and total sum spend must come in single row for that user. I can then use that value to calculate users final total gold, rather than from registration form sheet.

[ThinhDinh]

I had this problem a while back, what I did is use Query to group and sum current week’s data, then every weekend I use a script to hard-code it into a separate file I use solely for historical data storage purpose.

[Amal]

I wish i knew how to do that

Any easier way to do this ?

[ThinhDinh]

Hope others can chime in, I can’t think of another way.

Here are my tutorials for Query.

Tutorial - QUERY: "The most powerful function" in Google Sheets (part 1)

This topic was inspired by our last community meetup, in which we talked about some formulas in Google Sheets and some people want to know more about QUERY, which was branded “The most powerful function in Google Sheets” by famous GSheets teacher Ben Collins. So, what is QUERY, and why is it so powerful. Here’s a writeup on it, which hopefully would help many of you here in this community. Trust me, it would change so much how many of you “clean” your data in the future from raw data, in a very…

Tutorial - QUERY: “The most powerful function” in Google Sheets (part 2)

If you have not read part 1, below is the direct link so you can have a read at what QUERY is, and how it can help your case, whether it be building Glide apps or make your normal day work easier.

6/Dynamically sort your data using ORDER BY

An ORDER BY claused can be used to dynamically sort your data after being queried, and you can change the column that is used to sort with just a letter. By default, the ORDER BY sorts ascendingly, and it can grab the data type in the column automatically.…

[Drearystate]

So…why not have another google sheet that pulls the data from this one at the end of every day and stores it, use this sheet to lookup the information from the other sheet to gather the quantity? You would have the non glide sheet use the IMPORTRANGE function then run a script on a timer at the end of the day that copies that data over so its static and no longer dynamic. That data is summed up so you have a total. Your glide sheet uses same IMPORTRANGE function and pulls total, you just simply use a vlookup that matches names to their gold quantities. now you have infiniti rows and when the sheets are closed and the google servers are handling the load its instantaneous.