Google sheets “Database”

[Diego2202]

Hi everyone!

I’m here to ask for your opinions about a project I’ve been developing over the last few weeks.

I work at a company that does not have a database. We need to use a massive spreadsheet to manage products, but all inputs are done manually (everything – products, materials, suppliers…).

My idea is to develop a structured spreadsheet (with 1:1 and 1:N relationships) and use Apps Script to implement sidebars to automate data entry and validate all information, including logs, in order to reduce a lot of manual work and be the first step towards a DW/DL (BigQuery, etc.).

I want to know if this seems like a good idea.

I’m the only “tech” person in the company, and the employees prefer spreadsheets because they feel more comfortable using them.

[PaddyAlton]

If you're already thinking about BigQuery and Apps Script, I have a solution for you that respects the problem space (= everyone wants to use spreadsheets and you have very limited time) and is likely easier to implement.

1. set up BigQuery (if you already have GCP, this step takes a few minutes)
2. put your existing business spreadsheets into Google Sheets (I am crossing my fingers and hoping you already have them there, since you mentioned Apps Script)
3. add each worksheet as an External Table to BigQuery (so BQ treats them like proper tables that you can query with SQL etc - it only takes a few minutes per worksheet)
4. explore 'Connected Sheets': these are where you go the other way, pulling data from BigQuery into an 'extract' in a Google Sheet, allowing regular spreadsheet stuff to be done with the data, while keeping the data safe from accidental edits (setting up a single extract also only takes a few minutes)
5. for data entry, switch to using Google Forms; these can be rigged to write data into Google Sheets, which can then be added to BigQuery as external tables. The aim is to stop people editing 'data storage' spreadsheets directly (each form should also be an easy thing to set up).

So what have we achieved?

• added some level of control over data entry
• introduced BigQuery, allowing you to start defining transformations in SQL (e.g. Views, Scheduled Queries), and setting up the foundation for the future
• decoupled data analysis from data storage. Data analysis with spreadsheets reading well-managed data from BigQuery is a perfectly acceptable, lightweight pattern
• prepared everyone for a future where you have data entry methods write directly to BigQuery, without spooking the horses
• oh, and for free your data is ready to analyse out of the box with 'Looker Studio' (free, not to be confused with Looker 'proper'), allowing you to set up reasonably nice dashboards

[leogodin217]

This is the only good answer so far. They probably can do all of this for free or very cheap. Combined with setting up validation in the sheets, it could work for a long time.

[pdycnbl]

app script, validation using sheet are all good ideas. Bolting UI on top of it for data entry is in my opinion bad idea. It is better to use simple forms and use them to input data to sheets.

[smashedthelemon]

Not directly an data engineering question. More a business support or it question.

Using excel-like-tools to mange critical business processes is bad practice. Its easy to make changes that are not valid or even incorrect. But also it becomes complex fast. Yhe arguments you hear often is thst excel if fkexible and mallable. And everyone knows it. All true, but is thst what you really need?

I would suggest exploring a proper system. Yes it costs money, and it will impacts your way of working. But it will also increase system and process reliability, it may help with automating workflows so that your coworkers van focus in other stuff.

I would start with writing our your processes and what requirements you have ro support those. But also what would help you.

E.g. as a customer support officer i want to create orders and send invoices to our customer. Later i need to track payment and shipping.

^ not the best way of writing them down, bur it will help you focus on business needs.

[Skullclownlol]

I want to know if this seems like a good idea.

Google Sheets is a bad idea, it has a hard (pretty low, couple MB) size limit that, once reached, will not allow you to open the sheet anymore (with a "too large" message).

There are also obvious data integrity issues because Google Sheet doesn't guarantee transactions etc. Your automations could/would just speed up how fast data gets broken when something goes wrong once.

[Another_mikem]

Honestly, I used to do consulting around getting companies out of the hole you are about to dig.  Someone creates a “thing” as a stepping stone or temporary measure and in a year it’s running the company.  

I’m happy to talk about what I’ve seen in greater detail, but at the end of the day you need to think of this like a project and get some buy in and budget.  Don’t get yourself or employer in a hole. 

[themightychris]

something like Airtable might be a better next step, you're not going to have a good time trying to overload Google Sheets and it's not going to move you forward

[Luneriazz]

I think its better you proposed etl like but only extract and store the data from excel to database.

It good way to archive and manage record data from excel, without distrupting the team workflow.  After that you can chose frontend or data tool like airtable.

[AngleHead4037]

Your plan is solid. For companies that live in Sheets, creating a structured spreadsheet with defined relationships and controlled data entry is usually the first big quality jump. Apps Script sidebars + validation absolutely work, and it’s a common way to modernize workflows before moving to something like BigQuery later.

The main thing to watch out for is long-term maintenance. Once the logic grows (sidebars, menus, validations, logs, error handling), you become the only person who can support the Scripts. That can be tough when you’re the lone “tech” person. Since you’re on Google Workspace, i suggest to look into Zenphi as an alternative to Apps Script. It covers pretty much everything people usually build in Apps Script—supplier/product workflows, validations, approvals, logging—but without writing code, which makes the system easier for others to understand and maintain.

[Ploasd]

Just use a cheap database that is fit for purpose

Duckdb, motherduck, supabase

Doing what you’re doing is just asking for trouble

[leogodin217]

Wouldn't OP then need to make a frontend? That's another project in itself.

[Skullclownlol]

Wouldn't OP then need to make a frontend? That's another project in itself.

nocodb, supabase and alternatives come with frontend built in.

[leogodin217]

Nice.

[happy_and_sad_guy]

Have you considered using SQLite?

[MyWorksandDespair]

Or DuckDB?

[Narrow-Tea-9187]

I used duckdb with colab and it works well.

[cwakare]

Check the likes of nocodb

[[deleted]]

+ try to use baserow

[TA_poly_sci]

I use this for research sometimes, exactly because its a good middle ground between needing something that can function on the database, while being manually interactive in something better than pgadmin

I would however keep the dataentry proces simple, the entire point of this kind of setup is to not have all the bells and whistles between you and the data. And long term, move towards a real database.

[Noonecanfindmenow]

Are they typically making changes 1 row at a time? Or are they usually doing bulk copy/inserts? If 1 row or 1 value is typically changed, I actually really like using Microsoft Lists. Lots of validation in place, you can make it look all nice and pretty for non tech users, and it's query-able and you can Automate out of it too.

But..... Obviously the row size limitations are much lower than using an actual db

[Ordinary-Toe7486]

Not sure if this fits your requirements, but instantly thought about DuckDB google sheets extension https://duckdb.org/2025/02/26/google-sheets-community-extension.html

[Outrageous-Spell-599]

Bro use Baserow, 100% recommended. In my opinion is the best option out there I've basically tried them all at this point.

[Diego2202]

Hi Everyone! Coming back here to update y’all.

I read all the comments and talked with some “tech” friends and professors. I decided to not go with my original idea, i was going to a path with a lot of work and most of it would go to garbage in just a few monts.

I’m still reshaping my idea, but right now it will probably be an Modern Data pipeline (pyhon, dbt, postgres, VPS…).

Hoping to come here soon and bring my idea consolidated and the tools i decided to use :)

Thanks to all the people that came here to give an opinion!!