Google Sheets as a database: the point where it starts to break

The setup arrives by accident more often than by decision. A form writes into a sheet. A script reads the sheet and sends a reminder. A second script writes a status back. Six months later, three people depend on that file, one of them opens it on a phone, and somebody has added an array formula in column M that recalculates every time a row lands.

Nothing here argues that this is always wrong. Using a spreadsheet as the store behind a small process is a legitimate engineering choice, and it beats the alternative of a project that never ships. What it needs is an honest account of where the edges are, so the move happens on purpose rather than during an outage. Below are the published limits, the three failure modes that actually bite, and the test for whether the data has outgrown the file.

What it genuinely does well

A spreadsheet gives you four things that a real database makes expensive.

A readable store. Anyone can open it and see the data. No client, no credentials, no query language. For a process that a non technical colleague has to inspect, this is worth more than most of what a database offers.

Editing without an interface. Correcting a typo in a row means clicking the cell. Building that same affordance on top of a database means building a form, a route, validation, and an audit trail.

Analysis in place. Pivot tables, COUNTIFS, and the QUERY function answer questions about the data faster than most reporting layers, and the answer can be checked by anyone who understands the formula.

Free, immediate, and already authorised. No procurement, no server, no deployment. It exists in the account the team already has.

Those four properties explain why the pattern is so common and why arguing against it on principle fails. The useful question is not whether a spreadsheet can be a database. It is which of its properties turn into liabilities as the process grows, and the answer is specific enough to plan around.

The published limits, and which one arrives first

Some of the ceiling is documented and can be checked before committing. The rest is behavioural and has to be reasoned about.

The Sheets API allows 300 read requests and 300 write requests per minute per project, with 60 per minute per user per project, and there is no daily cap as long as the per minute quota holds. A request that takes more than 180 seconds to process returns a timeout, and the recommendation for a request payload is a maximum of 2 MB.

Apps Script, which is how most of these setups are actually wired, publishes its own figures.

Read those numbers against the process in question and notice which one bites first. Almost nothing built on a spreadsheet needs six minutes of compute or twenty thousand outbound fetches. A great many of them need to email more than a hundred people in a day, and on a consumer account that ceiling arrives on the first real mailout. The 60 requests per minute per user figure is the other one that surprises people, because a script that loops through rows one at a time will hit it long before the daily quotas are relevant. Batching the reads and writes rather than touching one cell at a time is the fix, and it is a rewrite rather than a setting.

Spreadsheets also carry a published cell limit rather than a row limit, which means the ceiling depends on how wide the sheet is. The practical wall arrives earlier than the documented one: a file with tens of thousands of rows and several array formulas becomes slow to open and slow to filter, and that is the point where people start making copies. The copies are where the real damage begins, because from then on there is no single version of the truth.

Failure one: two writers and no locking

This is the failure that has no workaround and it deserves to be understood before anything else.

A spreadsheet has no concept of a transaction. If two scripts, or a script and a person, write to the same range at the same time, one of them wins and neither is told. The usual symptom is not an error message. It is a status that reverts, a row that loses a value somebody typed, or a counter that is one short.

The pattern that produces it is ordinary. A script finds the last empty row and writes to it. Two submissions arrive within the same second, both scripts compute the same last row, and one of the two records is silently overwritten. Nothing logs this. The row simply is not there, and the person who submitted it is certain they did.

The partial defences all cost something. Apps Script provides a lock service, which serialises executions and adds waiting, and a script that waits behind a lock is burning against the six minute runtime ceiling. Appending rather than searching for the last row helps, and it still leaves the case of a person editing the same range by hand. A database solves this with a constraint and a transaction, which is most of what a database is for.

The honest summary is that a spreadsheet is safe for one writer and unsafe for several. If the process has one script and one human owner, the risk is small. If it has a form feed, a nightly script, and three people editing, the risk is a data loss that nobody will notice for weeks.

Failure two: no types and no constraints

A database column has a type, so a date column contains dates. A spreadsheet cell holds whatever was typed into it, and the display format is not the same thing as the type.

The consequences arrive slowly and they arrive everywhere. Dates entered by hand end up as text in some rows and as date values in others, so a sort puts them in an order nobody expects. A leading zero disappears from a reference number because the cell was treated as numeric. A phone number becomes scientific notation. A trailing space in an email address makes it a different address from the same address without it, which means the one person who should be one record becomes two.

Uniqueness cannot be enforced. Nothing prevents the same email address appearing twice, which is why the rows that should represent one contact do not. Data validation helps on entry and does not apply to a paste, and it is not applied retrospectively to rows that arrived before the rule existed.

Referential integrity does not exist either. If one tab holds people and another holds their submissions, the link between them is a value copied into a column, maintained by convention. Rename the person on the first tab and the copies on the second tab keep the old name. There is nothing to prevent a submission referring to a person who was deleted.

A well disciplined team can hold this line with validation rules, protected ranges, and a convention that nobody edits the raw tab. The discipline is the cost, and it is paid by whoever is on the team next year rather than by whoever set it up.

Failure three: access is a property of the file

Permissions are the constraint that most often forces the move, and it is the one that a technical assessment tends to skip.

Sharing is per file. A person who can read the spreadsheet can read every row, every tab, and every column, including the internal notes that were never meant for them. There is no row level security and no column level security. The available workarounds are a separate file fed by a formula reference, which duplicates the data and goes stale, or a script serving a filtered view, which means writing and maintaining an application.

Protected ranges are a real feature and a thin one. They prevent accidents among colleagues who are trying to behave. They are not a security boundary, and anyone with edit access can generally lift the protection or copy the file.

The next problem is that the file belongs to somebody. A spreadsheet has an owner, and any script attached to it runs under the authorisation of whoever set it up. Their email quota is the one being consumed. When that person changes role, the triggers keep firing under their name until their account is closed, and then the reminders stop with no error message and no obvious cause. Transferring ownership is possible and it does not move the script authorisations with it.

Finally, there is no history that answers the questions people ask. Version history records that the file changed. It does not tell you who set a particular status, when, or what it was before, without somebody scrolling through revisions by hand. For anything that has to be defensible later, that is not an audit trail.

The test for whether it has outgrown the file

Five questions settle it, and they are about the process rather than the row count.

How many things write to it? One script and one owner is fine. Several writers means the locking problem is live.

Does every row represent work somebody has to do? Reference data sitting in a sheet is harmless. A queue of requests, each needing an owner, a decision, and a reply, is a workflow, and a spreadsheet has no column for any of the three.

Does anybody need to see some of it but not all of it? If yes, file level sharing has already failed and the workaround is a second copy that will go stale.

Will anyone ask what happened to a specific record six months from now? If yes, version history will not answer it.

Is a person writing the replies somewhere else? If the answer to a row is composed in a mail client, the record of it lives in one sent folder and the sheet holds half the story.

Two answers follow from that table, not one. If the sheet holds data and the problem is scale, integrity, or access, the move is to an actual database and the spreadsheet stays as the reporting layer reading from it. If the sheet holds a queue of requests, the move is to a tool built around the submission, where each response carries its own owner and stage and the reply is sent from the same screen. Those tools are typically priced by the number of people with access rather than by the volume collected, which suits a process where submissions grow and the team does not, and the pricing page is the quickest way to see which tier a team of a given size lands on.

What to change first

Answer the five questions above and let the answers pick the direction. If several things write to the file, stop the silent overwrites first by making every writer append rather than search for the last row. If every row is a piece of work waiting on somebody, move the queue out of the spreadsheet and keep the file for counting; running one real week through Halict is the fastest way to tell whether that is the shape of the problem.

Q1. How many rows can a spreadsheet hold before it becomes a problem?

The documented ceiling is a cell limit rather than a row limit, so the number of rows available depends on how many columns the sheet has. The practical limit arrives well before the documented one. Tens of thousands of rows combined with several array formulas makes the file slow to open and slow to filter, and that is usually when people start taking copies.

Q2. Does the Sheets API make a spreadsheet behave like a real database?

It gives programmatic reads and writes, and it does not add transactions, types, or constraints. The quotas are published at 300 read and 300 write requests per minute per project, with 60 per minute per user per project. A script that reads and writes one cell at a time will hit the per user figure quickly, so batching the calls matters more than the daily allowances.

Q3. Can two people safely edit the same sheet at the same time?

For different rows, in practice yes. For the same range, no: there is no locking, and one write overwrites the other without an error. Apps Script offers a lock service to serialise executions, at the cost of waiting time against the six minute runtime limit, and it does not cover a person editing by hand.

Q4. Is it possible to hide some columns from one viewer?

Not securely. Sharing applies to the whole file, protected ranges prevent accidents rather than access, and anyone with edit rights can usually copy the file. The workarounds are a second file populated by formula, which duplicates the data, or a script serving a filtered view, which is an application to build and maintain.

Q5. What is the smallest change that buys another year?

Three things, in order. Make every writer append a row rather than search for the last one, which removes the most damaging failure. Put data validation on the columns that feed any formula, so a paste cannot break the reporting. And move anything that is a queue of work out of the file, keeping the spreadsheet for analysis, which is the job it is genuinely best at.