Majelika

Google Spreadsheet as Database

Long time ago, I didnt know databases, but I knew Quattro Pro, and later the amazing Excel. At that time I was sure you can do anything with Excel. Of course, later I discovered Access and afterwards SQL. Bye bye Excel and "table" as a database.

Today Im sure that any serious company needs a real database of some kind. Which is technically the best I dont know and will leave the answer to masters of the database world. But what I found out today is, that spreadsheets are not gone. Well, at least in a very specific situation Google Spreadsheets might be the best solution. And quite a useful one.



Example is real, but the data is a bit changed. This is the spreadsheet sample. Three things are really important:


1. "Dropdown" fields. As you can see in many fields, there are Dropdown fields that are being updated from another Sheets:




How do you create these fields? In any cell you can go to Data => Validate data as described in help.


This means you can actually get data from any sheet, any list you might be having (and updating). It is actually a "basic relationship among tables". Well, I didnt test any cascading, but it is not called "Google database", just "Google Spreadsheets". :-)


2. QUERY


There might not be any relationships, but you can actually "Query" things around and combine results from several tables. Amazingly you can even do these among different documents, as described here in details.






If you look at my example, you can see a Sheet named CemeteriesWithManagers. This Sheet actually combines results from Cemeteries and Managers sheets with 2 simple queries.


In A2 field:


=QUERY(Cemeteries!B2:I500, "SELECT B, C, H")


In D2 field:


=QUERY(People!B$2:E$500, "SELECT B WHERE (D= """&B2&""" AND E = General manager) ")


OK, they might not be the simplest and are explained in details in the link up there. But just 2 queries actually bring a "View" that can be very useful and even put in some iframe as pure html.


There was something in this second query that took me some time to find and can save you some time. It is the criteria:


D= """&B2&"""


This is not easily found. It is quite logical to have a criteria "dynamic", not fixed. So this line up here actually finds the name in the "People" sheet from the value that is in the results sheet in column B (at the same row).


3. Some calculations


There is a SUM field in "Companies" sheet that goes through Invoices (BillsSent or BillsReceived) and calculates values based upon company name.


=SUM(FILTER(BillsSent!H:H;BillsSent!B:B=B2))




Any experienced programmer will know the problems and limitations of this kind of "databases". But the solution is not intended for big systems with hundreds of thousands of possible problems and issues. It is intended for specific, but not rare cases where client is a dynamic organisation that is managed by few people living far appart, has small scope of work, but on the other has to have things organised. Might be a local charity, international association, poets group or something quite extraordinary that has a very unknown future.


Most of all, as everything else in the internet, these possibilities and solutions will most probably be used in very odd cases again and again. :-)