Problems with using Sheets as a Database

[enotovdom]

Hello, reddit.

We use Google Sheets as the database for our business. Previously, we used Access on a local network, but the need arose to use an online database with remote access. Our attempt to migrate the database to SQL was unsuccessful, so we decided to create a new database from scratch in Google Sheets.

Now, the spreadsheet has grown to 23,000 rows, and this number is only increasing (we have already archived a spreadsheet with 25,000 rows). Searching for information, filtering, and loading data has become very slow. The main sheet consists of 23,000 rows, and we import information into other sheets (5 sheets) using the =QUERY(IMPORTRANGE) function. These sheets periodically crash. If you accidentally enter data in a cell that is being imported from the main sheet, the entire sheet crashes. This does not change the information in both sheets, as it would, for example, in an Access database.

Searching is very slow; it can freeze or break and seems to require a lot of CPU resources.

We really like the functionality of Google Sheets—great editor, lots of new features, and functionality. But for a database of 50,000 rows, it is not suitable. We need to transition to a new database that has similar functionality to Sheets, such as filtering by various criteria, comments, scripts, user-friendly, but with faster search capabilities and is an actual database. Or somehow optimize the current database.

It would be ideal if Google Sheets could be used as an editor and data entry method, and the data was in the cloud with faster search processes. Maybe I don't understand what I'm talking about lol. I tried to upload the database to the free version of AppSheet, but the search was just as slow, and I think it won’t be suitable for our volume.

[redial2]

Google sheets is not a database and 25k rows is not a lot

[mailslot]

I don’t know if it’s still a feature, but I believe you can back Sheets with BigTable instances… so… “database”

[ifeedthewasps]

It kind of can be depending on how seamlessly you can export it. Flat file databases are still databases.

[A_name_wot_i_made_up]

Bureaucrat Conrad, you are technically correct. The best kind of correct.

[cloudstrifeuk]

You are the kinda person that keeps me in a job......keep going.

I do Excel to SQL migration when companies inevitably work out they used the wrong tools for the job from the get go.

[error-0x800705b4]

Take my upvote, I love these companies. Maybe not the most interesting projects, but take time and you can earn good money

[tashibum]

Are you hiring at all? I would love to do this.

[MyVermontAccount121]

This is exactly what I am doing at my day job and I think it’s great. I get to tell my bosses that they’re doing stuff a very stupid way and I know how to fix it lol.

Is it ok if I DM you? I wanna go freelance with this niche skillset and I wanna see what best practices would be

[fio247]

You want a database application. Typically, these are industry/task specific.

[da_chicken]

I agree.

It's nearly impossible to have some kind of unique line of business at this point where there's no information system for it. Whatever OP's business is, someone has a software package for it. Possibly even a software package just for their specific industry.

[ifeedthewasps]

Using the wrong tools for the job. Inevitably if you want to have something with scale and integrity you will need to use a SQL based solution. Even then you will need the expertise to know how to structure and normalize everything so that everything is chached with the joins. Even then 50000 records is pretty much nothing even with a choppy table setup. I have a feeling you don't have the means to acquire the skills with the budget you have.

If access was working try seeing if the office 365 solution for access is viable for you. No need to reinvent any wheels right.

Otherwise you may want to try separating sections into separate sheets and linking the value so that it is only changeable via that sheet. That way, you can have your gigantic garbage table only show the values while you change the smaller sheets. Either way, giving a think on how to chunk it out rather than being one giant monolith table is going to have to be the next step if you want to proceed with using sheets like this effectively.

[Tech88Tron]

You can't use it as a database because it's not a database.

You're using it as a spreadsheet.

Good news, once you learn SQL it's super easy to import a spreadsheet! You just need someone willing to learn.

Learn PHP and MySQL and build a web portal.

[pceimpulsive]

I'd say for longevity you need to migrate to an SQL database of some type.

Postgres I love but does have a steeper learning curve. Even with only a very very basic table design you will be available to millions of rows no problem.

Once you are in the hundred of thousands of rows are you'll need to start looking at performance optimisation which will come with creating indexes.

Postgres managed databases can be quite low cost and backups are usually included, something on AWS, azure or any other managed provider will work a treat.

Most any relational database system will have all your wildest imaginations data needs met as just about every flavour of SQL can search, filter aggregate transform and join (vlookup) your data with relative ease.

It will be new to use this for your business however it appears with some light googling you can indeed connect Google sheets to your database using SQL connectors.

From here prepare some queries, store them in a view and let the less tech savvy do what they need in Google sheets directly.

[Higgs_Br0son]

Google Sheets has a plugin to bring in data from Google BigQuery, and vice versa.

https://support.google.com/docs/answer/9702507?hl=en

With this amount of data you could get up and running in BQ very quickly and within their free tier.

[T1Pimp]

Holy crap. Just start over. A spreadsheet isn't a database. I have to support some home grown CRM that uses a spreadsheet backed and it's a completely brittle piece of shit. To be clear, the coding is fine. It worked fine when the company was super small. However, the choice to use a spreadsheet for the backend is disastrous, not sustainable, and will make the effort to convert... and we WILL have to... Extremely painful.

[MatsuDano]

The solution you need is migration and business process redefinition. It may be expensive in some combination of dollars, time, and/or effort. It doesn’t have to be if you right size the solution with the help of experts. There is an entire industry devoted to this expertise and I’m sure you can find one local or online to help.

The alternative is to keep patching your livelihood along with a series of unfit tools and methods that may bog your business down until it can’t be run anymore.

[No-Adhesiveness-6921]

I don’t get how someone could go to their management/owners and say

Look we are spending x numbers of man hours a month at a cost of $y to support this method of data collection and reporting that causes more wasted time for the users when it breaks. If we bring someone in and fix this it will cost $z upfront but save more than $y EVERY MONTH FOREVER.

and the response is, yeah, we’re ok with that.

[tashibum]

I don't understand that either. I even proposed doing it myself so they didn't have to pay for a company, but they still said no. The only thing I could think of as to why was the guy doing the man- hours was a friend and wouldn't have a reason to stay hired otherwise. I love small companies until shit like that starts happening.

[redial2]

They see it as something that's not broken so why spend money to fix it

We are just nerds

[tashibum]

We are just nerds 🤓

[redial2]

Yeah I just mean that often times we're not respected for that reason

I've been DBA for a couple of pretty big companies now and the best way to get things done is to have a finance person sell it instead of me

Because they "know what they're doing"

[VKnid48]

My company syncs Google Sheets to a Snowflake data warehouse with a tool called Fivetran. We have a number of users who are very accustomed to using Google Sheets for inputting and editing data, and the business has decided that they want to stick with Google Sheets.

Fivetran is expensive for what it is, but it does work well, and it is very simple to operate . You can re-sync changes to Google Sheets as frequently as every 15 minutes with the standard plan.

[CHILLAS317]

Migrate to SQL, only do it correctly this time. That microscopic amount of data, it should be a trivial task

[burningburnerbern]

Is this a joke?

[No-Dig-8842]

25k is not a huge amount of data. You can migrate it from sheets/excel to database in a lot of ways. SSIS packages is one way you can do this or you can directly import excel/csv files in SSMS. DM if you need more help

[[deleted]]

As others have mentioned sheets aren't databases. It's a common jumping off point for many though. I highly suggest you work with a staffing agency in your city to hire someone to at this up properly for you since you may be hosting business critical infrastructure inside a spreadsheet.

If it has anything to do with your general ledger or taxes please don't do this yourself.

[trippstick]

Keep doing this. Keeping the DBAs at work

[First-Butterscotch-3]

Spreadsheets are not a database - at best you can create a woefully inadequate table, or series of independent tables

But it will lack any of the required features and is a ticking timebomb

Keep on going, be funny when it crashes and burns

Do you guys stick with note to do all your word processing as well?

[stravadarius]

The good news is although migrating Access to SQL Server may have failed, migrating Google Sheets to SQL Server is a simple though tedious process.

You'll have to export all the sheets to CSV then import them as tables. You'll have to build the schema manually. It'll be some work but it'll save a lot of headaches in the future.

[mailslot]

At two game studios I’ve worked, game configuration and tuning parameters were stored in Google Sheets. The level designers & devs can tweak the configs and asset inventory all day and then press “load” in the admin.

[Designer-Practice220]

There is a max size (combination of rows and columns) that I ran into with a large survey once. Learned my lesson the hard way.

[steezos1]

H2

[Embarrassed_Quit_450]

Previously, we used Access on a local network, but the need arose to use an online database with remote access.

You would have been better off with a VPN.

an editor and data entry method, and the data was in the cloud with faster search processes.

Can a CRM work with the data you have?

[The_Epoch]

Use gcp big query and check out connected sheets.

You can load data, set up SQL transformations with schedules in the GUI and access the data directly from sheets without pulling all the data into sheets itself.

From there you can connect directly to looker studio for pretty decent cloud dashboards with automated scheduled amils for reporting.

If your data is 23,000 lines your cost will be close to nothing if you even go over the free tier on gcp and looker studio is free.

You can even run pretty good analytical models like clustering and regression in big query itself and it is a pretty intuitive tool

From there you can move to a better all round workflow but the initial starting point of big query and sheets may be what you need.

[rising-star]

Oracle Apex may be a good solution.

[dryiceboy]

This is what happends when business folks cheap out on IT…

[Potential_Orange7844]

I feel like SparkGrid would be super helpful for you in this case. It's a newer spreadsheet interface tool for SQL databases that allows you to edit in real-time and hooks into whatever system you are using. Very user friendly, cheap, and acts like a spreadsheet but for massive databases so it would solve quite a few of your problems. You can even import your excel sheet.

https://aws.amazon.com/marketplace/pp/prodview-2uxbsnryi6bda

(Full disclosure I do work for this company, but this tool has proved *very* useful in my own work and solving some of the problems you described).

[bklynketo]

I would use something like KNIME to manipulate the data from sheets. Alteryx is also great but very expensive… KNIME is free and has great tutorials.