Advice on archiving old data and balancing sync performance against bloat when using google sheets

so i have been building an app that i expect to generate roughly 30,000 rows per year of data between 15 tables. about 18,000 rows will be in a single table. we have used slices where possible and have several tables which exist simply to house virtual columns that produce real time reports for the managers. they use enum lists to allow the selection of report filters, such as clients, date ranges, etc. i am using security filters so that standard users will not need to load these report tables. everyone will need to load the data tables in order to either enter or report on the data.

i am concerned about bloat and the 2 million cell limit from google and plan on archiving and emptying the data tables every 6 months. will emptying the tables cause the report menus and enum lists to go crazy? should i look into moving as much of the reporting features from virtual columns to spreadsheet formulas?

1 1 502
1 REPLY 1

MultiTech
Participant V

Hey Seth.

Sounds like youโ€™ve got quite the awesome app

I too have an app thatโ€™s been in operation for a couple of years now, and weโ€™ve got a similar issue with huge tables (except we have 7 of these huge tables ).

Ultimately this is a dancing act youโ€™ll have to play with reports v. data bloat.

  • On one hand you need all the data there to make the reports;
  • on the other you donโ€™t want all that older data sitting around making an app fat.

To answer this directly: yes. If these virtual columns are creating a list of options from all the data in the table, if you remove some of this data then the options will change. If Iโ€™ve got data from 1/1/2018, but I remove everything from 2018 - now I wonโ€™t have 2018 options in my lists for reporting.

But you donโ€™t want to allow people to use values that donโ€™t match the data in the database, then the report would be all funky.

So itโ€™s a balance of: how long do we need to keep the data before any insights we might glean outweigh the hassle of all that data.

You could make a reports table that saves all the analyses done, then perform a higher analysis on that data set - this would allow you to thin the herd while still maintaining a long-term analysis chain.

Top Labels in this Space