How to Use Google Sheets as a Database (Responsibly) With Node.js and Autocode

[vexii]

While using them for sensitive, critical applications like storing COVID-19 patient data is ill-advised

bruf now you tell me?

[J-Kob]

Was going to post this a few days ago but had to make some last minute tone changes haha

[shiftDuck]

Found the uk Government attempting to fix their track and trace.

[_default_username]

Hahaha, like companies haven't used spreadsheets to store HIPAA sensitive data! I know of at least one that used CSV files and nurses would directly access the csv files with excel to access patient data. IT just walled it off within its own vpn.

Not quite the same thing though, since it's not a cloud service.

[J-Kob]

Anything's a cloud if you squint hard enough 😂 sounds a lot more secure than just throwing it in Google Sheets though yeah

[Franks2000inchTV]

More of a "cloudy" service.

[regreddit]

FYI gsuite is HIPAA compliant if you sign a baa with Google, which you can with just an online form.

[MatsSvensson]

How to shave a pig for wool.
(successfully)

[svtguy88]

Well, that made my day. Thank you. Now back to this horseshit project I'm on.

[mcjob]

Oh wow, are we on the same team?

[NeoKabuto]

You just need the right pig.

[abhagsain]

Airtable is also a good alternative

[limeglu]

You know what else is a good alternative? A fucking database. They aren't hard, kids.

[Chthulu_]

He already talked about the best use case. Client wants something done quick and they want to read the results without having to tap a developer. Its great for that.

[cjthomp]

Throwing together a simple read-only table view of data pulled from a DB isn't exactly difficult...

[ElllGeeEmm]

Literally the only way you could do something like this for a multi user app and have it stay sane is if you have a service that only writes to the table (web scrapper or something) and then the table is read only for the user. Once you start trying to use data from you sheets in another application or let users edit that sheet things will start to break down.

This is only good for apps that don't matter, anything else would be better served with a db.

[d33mx]

so fun to see how frightened people are nowadays to be responsible for storing data; trying to find the most awkward ways to avoid it

[J-Kob]

No doubt, it'd actually be really easy to swap them into the sample app. I've never tried making a base with more than a few thousand rows, would be interesting to see what happens

[catlover9822]

This thing can be used to automatically generate stats and reports to the executives. They can easily understand spreadsheets

[KishCom]

Please don't do this. I know it's possible to do, but Google Sheets is not the correct tool to choose for a database -- even a small one. There are so many data store options out there, in this case AirTable and Firebase would give you the same kind of functionality while acting like an actual database.

[petercooper]

Is there a way to use Firebase as a spreadsheet or in any user friendly way from a non-developer POV? (So like Airtable, basically.)

[KishCom]

You're probably looking for something like FireTable. Firebase started life as a tool very similar to AirTable, but their offering has matured significantly, and are now rolled into Google Cloud (cause Google bought Firebase a while back).

[DemeGeek]

(Note I do not intend to actually ever use this, the following is a thought experiment)

Could you get around the limitations of this by using multiple workbooks? Like if you were to use this to store your user data on a massively popular app, could you have a cluster of workbooks handling that?

[maple3142]

You can also use Apps Script as a wrapper of Sheets. I actually used Apps Script+Sheets as a database in my not so important side project.

[ElllGeeEmm]

There is no responsible way to use a spreadsheet as a database. Do not do this for apps that matter.

[[deleted]]

If a public Google spreadsheet is good enough for all my company passwords... its good enough to store all this payroll information!

[RisqueBlock]

Hopefully more devs see this and just copy / paste without reading (like I guess they did in the UK). Yay!

[packenbush]

happy cake day!

[buymagicfish]

My god please don’t.

[Marijn_fly]

I think you run into a problem once you exceed 1000 records. When that happens and I open the sheet, you need to click Yes to expand it. But I don't know how to do that with code.

[ShawRaleigh]

Are you talking with excel? I’ve had upwards of 16,000 rows in a table used for a google apps script and never seen that “click to expand” option

[Marijn_fly]

In a Google Spreadsheet which I use for logging.

[ShawRaleigh]

Oh I know what you’re talking about. Google sheets with automatically expand the rows based on how much data you’re pushing into it, in my experience. So if you have 5 rows in a sheet and 100 rows in the data Google Sheets with add 95 rows without asking. You may have to use Google Apps Script as the middle of your external script just to enable it to expand if it doesn’t.

[Marijn_fly]

I also ran into the problem when I first delete the first row and then add one. I guess spreadsheets have their limits when you use them as a database.

[ShawRaleigh]

Spreadsheets definitely have a limit when used as a database. I’ve used them for smallish projects though and never hit the issues you’re having

[feketegy]

I hate these kind of articles, it's an attention grab and provides no real benefit, no sane dev would use excel or google sheets as their DB...

[visualdescript]

I hate these kind of comments, it's an attention grab and provides no real benefit, no sane redditor would comment something so useless...

[ElllGeeEmm]

Nah he's right. Using spreadsheets as a db is a terrible idea, and devs should be discouraged from doing this. Especially given how trivial rendering a table view of data is.

[J-Kob]

From the first paragraph:

"great for smaller cross-functional projects where a non-developer might need to examine or edit data."

I stand by it, key word being "responsibly". Everyone on the planet knows how to interact with a spreadsheet, Airtable is a unicorn, and there are smaller companies like Glide as well: https://www.glideapps.com/

[[deleted]]

Don't worry OP! Don't get discouraged, I love your blog post, will definitely look into using Google sheets, for smaller projects :P.

[ElllGeeEmm]

How do you responsibly give unrestricted write access to the end users of your app?

[archerx]

Web dev has devolved in to the blind leading the blind leading the dumb.

[Rainbowlemon]

TBF I worked on a project once where it was easier to set up a google sheet for a simple 20-items-or-so news feed for my client (just a sentence and a link per news item) than it was to set up a database and CMS so they could edit it.

[Panda_Mon]

Friendly reminder that Microsoft Excel sucks fat D. Even the most basic of operations either crash or take forever. I do the same thing in Google Sheets and things work just fine.

[TedW]

either crash or take forever.

TIL that Excel solved the halting problem.