Is there a way to delete rows based on a set condition? Now that there are row limitations for free apps, I’m thinking of a way to build an app that basically resets itself every month so that it (hopefully) never hits the 500 row limit.
Here’s the concept: users enter workout details into the app (i.e. on Monday, I biked to work). Each workout has a points value attached. Users are then ranked and top users for the month are recognized in some way for promoting health and wellness (FYI I work at a health centre).
The goal would be to avoid the 500 row limit by clearing the data every month and having users start a fresh “competition” for wellness points each month (without me having to worry about clearing the sheet on the first day of each month).
Any idea if this is possible?
Note: I’m not trying to come up with ways to use Glide for free and avoid paying for the great service. We are a non-profit and this is something that would be meant for employee wellness/team building, which isn’t something we receive funding for (so we do what we can for free whenever we can!).
Alternatively, could Glide look at the latest 500 rows only? I believe the current setup looks at the first 500 rows and anything after that is ignored until you upgrade to Pro. If Glide looked at the 500 newest rows, this would allow users to maintain all their data in the Sheets and then only display the latest 500 in Glide. PRO users would then be able to display all their rows in Glide.
I’ll look into the script option, but I think what I mentioned above is a good compromise for the new Pro structure that should be considered.
What @Kyle_Heney proposed would work if you only had one sheet I assume. I haven’t run into the 500 limit yet but I thought it was the sum of all rows on all sheets. That may make it a show stopper for @kyleheney 's needs if he has one sheet with over 500 rows and other sheets with less. Which rows would be shown from the other sheets if 500 rows are being shown on the one with more?
Sorry, I should first mention that @kyleheney and @Kyle_Heney are the same (work PC is signed in differently than my phone, sorry for confusion).
I hadn’t thought about the per sheet side of things. I’m curious if any other “free alternatives” have been brainstormed to get around the limit in creative ways. Like I said, I don’t want to intentionally get around the need to pay, but rather create an app that respects the limits but allows me to preserve my data in the Sheet, but not necessarily in the app. Maybe a script to move rows to a new spreadsheet all together (like an “archive” spreadsheet) that would allow the “active” spreadsheet file to be used as the app content with a 500 row limit that auto-archives rows after 500.
If this is something the Glide folks think would be respectful to their new Pro structure, a tutorial would be very much appreciated that walks through the necessary script steps, etc.
For a couple of my apps I use Google App Scripts to move rows of data from an active sheet to an archive sheet. I set a trigger to run every night and it loops until there are no more rows to move.
If you google a bit you should be able to find code. You would use GetSheetByName, getRange, .insertRows(), .clearContent() and .CopyTo.
Thanks for the info. This sounds pretty much exactly what I want to do, I think the only spot I might get hung up is figuring out how to do it based on a date. I want to archive my data on the 5th day of each month, for example. I’ll try to figure it out, but may ask for some assistance again!
You could keep your data for long time without delete nothing. The Google sheet’s maximun number of cells is 5.000.000!
Just show/use the 500 newest records (or 450 if you are using other records for other purposes) using Query() function and you won’t need a script.
The query (of course) must use as parameter the day of week and LIMIT clause to avoid having more than 450 or 500 rows.
If your sheet has 2 tabs for example (Data_Raw and Data_Filter), the first one will have all data that your users have written since the famous Y2K but in the 2nd one (Data_Filter), create a Query() like this:
=Query(A1:D,“Select * Order By A Limit 450”,1)
Where A could be your timestamp column to have a set of data based on date. Here you will have your Top 450 always.
The tab Data_Filter will be the tab where your Glide App will load your data excluvisely.
There are many combinations to have a more powerfull query but I just try to give you an idea.
It’s very easy to pick the 500 newest rows within the sheet using FILTER and SORT—this would let you control which 500 rows Glide allows you to use for free. The obvious downside is that they are read-only if you structure your sheet this way.
Could I have a hidden sheet with all my data (more than 500 rows) that is then referenced to pull totals from that data… without counting towards the limit?
My case: a workout app where people add different types of activities and track their activities on a monthly basis. The main stats are shown on a monthly basis (so “this month” users can see all their activity details,etc). I still want to be able to reference the older data on a high level (so users can see the total # of activities from 2 months ago — stored in the hidden sheet — and compare that to current activities — stored in the active/current sheet).
Yes you are correct. @kyleheney. You can use query in google sheets to create a new sheet with limited data up to 500.
Because Glide Adds to the bottom of the sheet you will have to invert the count to reflect the last 500.
Instructions:
You can create a new sheet and use the QUERY function to get the last 500 rows:
If column A is unique and sortable, you simply sort your data in reverse order and take the top 500 rows, and then reverse sort it again: =SORT(QUERY(Sheet1!A:Z,"order by A desc limit 500"),1,1)
If that is not the case, then you need to calculate the offset by finding the last row and subtracting 500 from it, and then query the data to return 500 rows starting from that offset: =QUERY(Sheet1!A:Z,"limit 500 offset "&(COUNT(Sheet1!A:A)-500))
However like @david said, this will be a Read Only Data and you will not be able to add to the sheet.