{"id":5290,"date":"2019-03-07T14:49:43","date_gmt":"2019-03-07T14:49:43","guid":{"rendered":"https:\/\/max-drake.cc\/?p=5290"},"modified":"2019-03-07T14:49:46","modified_gmt":"2019-03-07T14:49:46","slug":"free-db-obvibase-google-drive-add-ons","status":"publish","type":"post","link":"https:\/\/max-drake.cc\/?p=5290","title":{"rendered":"free DB ObviBase & Google Drive add-ons"},"content":{"rendered":"\n

Obvibase database with Google Sheets<\/h3>\n\n\n\n

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. <\/p>\n\n\n\n

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).<\/p>\n\n\n\n

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. <\/p>\n\n\n\n

I asked MrGoogle<\/em> 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<\/a><\/strong> 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.<\/p>\n\n\n\n

\"\"
Obvibase share via embed on web page, link for sharing for read-only or upgrade to pro plan (i think $5\/month for pro-plan to allow others to edit<\/figcaption><\/figure>\n\n\n\n

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. <\/p>\n\n\n\n

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. <\/p>\n\n\n\n

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\/*<\/em>) and I couldn’t filter it to a specific month, I tried If() <\/strong>with and\/or <\/strong>and slice()<\/strong> and map()<\/strong> in the formula syntax list<\/a><\/strong> 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<\/strong>= if([ID] < 40, 1, 0)<\/strong> so you can choose a lower ID number to filter out, and next column was T2=<\/strong> if([ID] > 60, 2, 0)<\/strong> so all the records above number 60 will have a different number, then the 3rd column was just T1+T2 <\/strong>and then filter that column where number =0 <\/strong>and that got the in-between rows, and from there you can export to CSV file and it will only export those specific rows. <\/p>\n\n\n\n

Note, I got <\/em>slice([Timestamp], 0, 1)<\/em><\/strong> 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.<\/em><\/p>\n\n\n\n

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. <\/p>\n\n\n\n

The link https:\/\/www.obvibase.com\/p\/bUIrqp1wQgNNCVmL<\/a><\/strong> 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.<\/p>\n\n\n\n

Google Drive Apps<\/h2>\n\n\n\n

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:<\/p>\n\n\n\n

\n