Best way of using Google Sheets as a database?

Fixing and making things, what tools to get and what skills to learn, ...
Post Reply
UK-with-kids
Posts: 228
Joined: Tue Oct 09, 2018 4:55 am
Location: Oxbridge, UK

Best way of using Google Sheets as a database?

Post by UK-with-kids »

I have a massive amount of data which is being dumped into a Google Sheet - it's being put there because there's an easy and cheap API to get it from the source system into Google Sheets. The data is the customer orders data of a business - order details such as products sold, date of orders, prices, etc.

I want to run various queries on the data - e.g. for a given customer when they last ordered, order frequency, which products they've never bought, etc. I know that the Google Sheets query function is very powerful and similar to SQL, so that was my starting point. However, as the spreadsheet contains about 2 million populated cells, I'm finding the Google Sheets query can't really cope - I keep exceeding data limits, having to glue portions of the data together, and so on, and then I get random errors and don't know if it's my formulas that are wrong or a data issue. Furthermore, it all runs incredibly slowly and I have to keep refreshing the sheets.

Does anybody have a suggestion of an alternative cheap or free database tool that I can somehow overlay over the Google Sheet to use as a front end or place to query from? Or a totally different way to approach this?

TIA!

Flurry
Posts: 63
Joined: Tue Oct 27, 2020 1:30 am
Location: Vienna, Austria

Re: Best way of using Google Sheets as a database?

Post by Flurry »

I assume you know how to code, right? I personally would write a job that fetches the data from the google sheet, insert into a local relational database (sqlite should be sufficient) and then run the queries there.

In theory you should be able to migrate your spreadsheet data to Google BigQuery but that isn't free. They give you a free quota but I learned from my work projects that you exceed that seemingly large quota very easily and then it becomes expensive. So I would try to write a script for it, I doubt that it takes more than a few hours for an experienced developer.

UK-with-kids
Posts: 228
Joined: Tue Oct 09, 2018 4:55 am
Location: Oxbridge, UK

Re: Best way of using Google Sheets as a database?

Post by UK-with-kids »

Flurry wrote:
Fri Nov 27, 2020 6:06 am
I assume you know how to code, right? I personally would write a job that fetches the data from the google sheet, insert into a local relational database (sqlite should be sufficient) and then run the queries there.

In theory you should be able to migrate your spreadsheet data to Google BigQuery but that isn't free. They give you a free quota but I learned from my work projects that you exceed that seemingly large quota very easily and then it becomes expensive. So I would try to write a script for it, I doubt that it takes more than a few hours for an experienced developer.
Thanks @Flurry.

I am a person who *should* be able to code. I learnt to code in BBC Basic as a child which means it comes very naturally to me now (apart from object oriented programming). However, my career has taken me in other directions so I'm definitely not an experienced developer. I've written some VBA macros to manipulate data in Excel and I've done some online courses in SQL, Javascript and Python. However, I've never managed to take it to the next stage of a practical project. Maybe this should be my first proper practical project BUT perhaps what you're suggesting is too difficult for me at this stage. I don't really know how to go about writing a job to fetch data, as much as I understand the principle.

So I guess I either need to figure this out - which realistically would need step my step instructions - or find an easier tool.

(I think I will also start a different topic about how to get back into coding - although I appreciate there are a lot of similar topics on this forum already).

Flurry
Posts: 63
Joined: Tue Oct 27, 2020 1:30 am
Location: Vienna, Austria

Re: Best way of using Google Sheets as a database?

Post by Flurry »

Oh, if you know how to use sql you should be able to do it. You could directly generate insert statements as this can be expressed with a formula, copy them to a text file and import it into a relational database.
Or maybe you can even directly import CSV.

https://sqlitebrowser.org/ is a nice UI for SQLite.

CDR
Posts: 57
Joined: Sun Sep 06, 2020 9:45 pm
Location: Canada

Re: Best way of using Google Sheets as a database?

Post by CDR »

As Flurry mentioned, you might want to take a look at using an SQLite database. Since you mentioned you have experience with Python, you could take the CSV from google sheets and using python to create a sqllite database with the data. This is the tutorial I used a few months ago to get started with SQLite and Python: https://www.sqlitetutorial.net/sqlite-python/

As someone who has coded on and off for more than a decade, I would recommend jumping straight into doing projects/scripts that you need or could need. I would only recommend this however if your situation is similar to mine. I see programming as a tool and not a means to an end. There is no longer a reason for me to read computer science textbooks and watch courses online. I learn as required to complete the project at hand.

I hadn't programmed in a long time but started with a Secret Santa selector for my job (December of last year!), then wrote a few more scripts for my job related to manipulating excel sheets/CSV, then I jumped into doing a GUI flashcard creation program that I've been using now since April. I developed it enough to start using it (minimum viable product), then added features as things came up in the general course of use. I've made over 5,000 flashcards since April, so I consider it a success!

I also did other scripts for my job, including manipulating word documents, automatic resizing of photos, scripts for sending invoices, etc. There is more I would like to do.

If you think this project is too big, you might consider taking a look at all the computer-based tasks in your job or life right now, and see if there is anything you can automate. If you are generally interested in finding things to automate (but only if you are having a hard time), I would highly recommend reading Reinventing Jobs by John Boudreau and Ravin Jesuthasan. It goes through what they believe is the mindset required to see how to automate jobs. Of course, it can be applied to your own job, and your own life.

If there is one course I could recommend on this subject of practical projects, it would be Building Tools with Python by Scott Simpson. It is on linkedinlearning, I would only recommend it if you already have linkedin learning for free, via a Library or other means. The course is only 45 minutes and helped me better decide what I should be scripting and what I should leave for later.

Hope this helps!

Flurry
Posts: 63
Joined: Tue Oct 27, 2020 1:30 am
Location: Vienna, Austria

Re: Best way of using Google Sheets as a database?

Post by Flurry »

Like I said, you don't even have to code. You can easily use Google Sheets to create insert statements like that:

Image

Then simply copy all these insert statements into a .sql file and import it into your relational database. Then you can run queries on it using SQL.

CDR
Posts: 57
Joined: Sun Sep 06, 2020 9:45 pm
Location: Canada

Re: Best way of using Google Sheets as a database?

Post by CDR »

Ah, I misunderstood your post about where the SQL queries could be created and used. I did not know you could do it right in google sheets!

chicago81
Posts: 307
Joined: Sat Feb 04, 2012 3:24 pm
Location: Chicago, IL

Re: Best way of using Google Sheets as a database?

Post by chicago81 »

Ahhh I never thought of using formulas in a spreadsheet to generate sql that can be used to throw data directly into a DB. Brilliant! Instead I usually would have exported to some format like csv, and written python to throw stuff into a DB. But that's a lot more work!

Post Reply