I was thinking about my expenses Form/Sheet process Spending Tracker that I’m very pleased with. A method to capture my spending. My concern is that the sheet will bloat over time with older records.
I was surprised that one of my big sheets in Excel could not be opened in Google Sheets because it was too big. Spending Tracker being online I thought it may slow down if the sheet size got too large and the Form to Sheet transfer required the sheet to be opened (I’m not sure this is the case).
The obvious method for managing this is just to save a copy of the existing sheet to another name, then increase the records by a cut/paste record when I didn’t want to keep the records in the original sheet. This is just transferring the bloat to another sheet, that would be slow to open if you had too many records.
I asked MrGoogle if there was a Database that linked with Google Drive data, as the Google Docs suite seems to have most other tools that you’d normally need. DB’s tend to be faster than spreadsheets as, at their simplest level, they are just a structured table. ObviBase was thrown up as a result. It is pretty simple and you can import/export via a CSV file. No linking and basic querying, but a simple way to store data. It is free for personal use, you can share it, read only, with others via a link (see below), but since it is just a table and you cannot connect to it with apps its pretty limited. Reasonably easy to use though if you want just basic filtering.
I decided to clear out my first months data from my Spending Tracker sheet and save it into the database. I’ll keep that data there.
I was concerned with the existing data being overwritten if I imported later data , but in fact it just appended the information, which is what you want the import process to do.
Filtering was the next issue, and that took me a time to get working. I tried using the Timestamp which has date and time (using wildcard like 2/*) and I couldn’t filter it to a specific month, I tried If() with and/or and slice() and map() in the formula syntax list but couldn’t get any combination of these to work to create an upper/lower bound filter. So ended up with 3 columns, the first was T1= if([ID] < 40, 1, 0) so you can choose a lower ID number to filter out, and next column was T2=if([ID] > 60, 2, 0) so all the records above number 60 will have a different number, then the 3rd column was just T1+T2 and then filter that column where number =0 and that got the in-between rows, and from there you can export to CSV file and it will only export those specific rows.
Note, I got slice([Timestamp], 0, 1) to work to get the month, I had to make the column TEXT, not Number. So I only need one column to filter by a specific month of data. Less of an ordeal than the 3 column solution above.
This method allows me to do a specific export on only part of the data so that I would not overload a spreadsheet in the future with the data I wanted to review. A bit time consuming but there were minimal examples of the syntax and minimal examples where these were demonstrated in videos. At the end I have a way to export data between upper/lower bounds to analyse.
The link https://www.obvibase.com/p/bUIrqp1wQgNNCVmL below takes you to a public ObviBase table, that is filterable, but you cannot export it, you can copy the filtered table but have to use “Paste Special” in a spreadsheet with some remapping to get the data into any semblance of order as there are no export tools when sharing a link. So its really only a personal tool unless you want to pay for the monthly subscription.
Google Drive Apps
On YouTube looking up ObviBase I came across the video below that talks about additional apps for Google Drive, similar to adding apps to Docs , Sheets or Forms. I didn’t realise you could do that:
There seem to be a number of apps in a number of categories such as education/Business/ productivity & Utilities, some paid with free versions.
I had a look in the Productivity & Utilities categories and there seemed to be either Notepad editors and lots of PDF file management tools. Nothing leapt out at me apart from a Business app, Smartsheets, that does a Gant chart ( I mentioned that the Clickup programme had this facility too) and in a review one person said that for the free version you can get up to 10 Gant charts free.
I may explore that later, not a toy I play with too much, I tend to just use a spreadsheet with coloured cells to represent timeline. Pretty static but does the job quickly.
The Obvibase tool seems a bit over the top for a simple expense sheet but I thought I could gather a lot of data and analyse it later. It is being used in this exercise as a storage base for the information I’m accruing.
I want to ensure:
that the form filling process remained fast by ensuring a small sheet size.
to be able to manage other processes such as time sheets and say, equipment sharing, via a Form/Sheet process.
that future processes may end up with a lot more content so planning structure early extends the process viability.