G Sheets vs MySQL (and other CMS stuff)

Russinating · 11 Sep 2018 at 13:55

So I think I know the answer, but basically what are the major differences between running a website's database (<2000 visits a month) off G Sheet rather than MySQL? I'm guessing G Sheets is slower when dealing with large volumes?

The reason I ask is it'd be easier for our team to add and amend content via a G Sheet rather than a MySQL db.

I realise I should be using another CMS, probably, that adds that usability above MySQL. And despite being 'ok' in PHP and writing websites in Notepad++, I've never touched Drupal/Joomla and the like and always get a bit lost when I look into them.

Any advice appreciated.

AHarvey · 11 Sep 2018 at 17:42

Isn't there a row limit on G sheets? Can't remember off the top of my head.

I'd also have thought that linking a website to G Sheets as a dbase would take more work than just creating a basic login and database connection for MySQL to allow your team to update.

I've been watching these PHP tutorials on Youtube and they're pretty good, this is the one where you make a login system https://www.youtube.com/watch?v=xb8aad4MRx8

This seems to have some useful info on using G Sheets as a dbase, and was written in March https://codingislove.com/google-sheets-database/

Russinating · 11 Sep 2018 at 19:37

I don't believe there's a row limit, and certainly not one that would be hit by our website. The benefit is we're fully Drive integrated and even a new starter could add a new row to a sheet (as opposed to SQL).

I've already written another site that uses G Sheet API and it wasn't too bad (and now it's all written).

The site would only need read access, and the sheet would basically be a database of projects to display. That blog doesn't mention anything about speed though which is my main concern.

AHarvey · 12 Sep 2018 at 10:48

Seems the limit is 2 million per worksheet. You may not think you'll hit that but it'd be daft to limit yourself.

If you've already written one using G sheets what the speed like on that? does it get similar usage to the new site?

it's an interesting concept i'd not thought about so I've been doing some googling and came across this via Azuqua:

When to use a Google Sheets Database?

Simple relationships between the data. If you require complex relationships, it’s better to use a relational database system, such as PostgreSQL, MySQL or MS SQL.
Read-only records, or if speed doesn’t matter. One of the reasons to use a traditional database is for quickness of transactions. As you are interacting with your Google Sheet database via an API, this is necessarily a longer and more costly operation.

Russinating · 12 Sep 2018 at 13:14

Ta. And whilst I agree on the row limit, I very much doubt we'd ever, ever get to even 500. That would mean we've worked on 500 individual client projects, all of which are being displayed online which would never be the case.

It doesn't get similar usage no, as it's for a targeted campaign, but the speed seems fine. The above helps, so I think it's certainly worth me looking at.

AHarvey · 12 Sep 2018 at 17:26

Ahh I get you, then you may find if it's likely to not go above 500 G sheets may be fine for it.

I'd suggest knocking something up and getting a few people to test the hell out of it. It's supposed to handle simple joins ok so it really depends how you plan the tables out.

Sounds interesting though so good luck with it

Vince · 13 Sep 2018 at 23:49

Russinating said:
So I think I know the answer, but basically what are the major differences between running a website's database (<2000 visits a month) off G Sheet rather than MySQL? I'm guessing G Sheets is slower when dealing with large volumes?

The reason I ask is it'd be easier for our team to add and amend content via a G Sheet rather than a MySQL db.

I realise I should be using another CMS, probably, that adds that usability above MySQL. And despite being 'ok' in PHP and writing websites in Notepad++, I've never touched Drupal/Joomla and the like and always get a bit lost when I look into them.

Any advice appreciated.

If it was me id be tempted to do it in wordpress as its much much easier to get fast results in than Drupal/Joomla. Probably a naughty word around here but with managed wordpress hosting providers available (I use WPEngine) to deal with all the known downsides like the constant updating testing etc it seems like a good deal to me these days. Plus wordpress is much easier to install, navigate and skin/develop on than Drupal/Joomla imo. I remember having worked in wordpress for a few years I inherited a Joomla website and feel your pain as I remember hating having to work on that thing. It got better over time but I never really liked it. Was glad to get rid of it when I stopped doing web work on the side.

I see your talking about adding a row to a sheet but why not add the row through the front end via input boxes where you can do some sensible input validation, much like I guess you would do on a spreadsheet? At least this way you don't have to touch the back end at all.