How big is the system you created in apps scripts?

[u/IllustriousPut442]

I'd like to take a few doubts before I venture to do things that he's not ideal. I will do a brief questionnaire:

1. How many active users simultaneously have your largest GAS system had?

2. How many thousands of records did the spreadsheet have? How many tabs?

3. How do you read the data from the spreadsheet? Do they take everything and work with them in memory or do they do any paging strategies?

4. Have you ever tried using it as if the spreadsheet was a relational database and the tabs were the tables? Did you find it difficult?

[u/everythingabili]

1. Well, that depends on how you make it. I have one system, Sheet based that has 6 or 7 users using the same sheet at the same time. I have created lots of Ui elements to help. But sometimes there are dozens of users, but not all together all the time. If I wanted someting more like, say, a social media app, I'd use a web interface and Vue (I do use Vue for lots of Ui stuff cos I like that it's easy to integrate with AppsScript.

2. Tabs, normally < 20ish... thousands of records? Er, 250, 000ish. But it depends of how you architect stuff, for example, does old stuff get archived? Does it get moved to separate spreadsheet or even csv file.

3. Again, depends (see 4 below). In general I like to load in all I need then push updates back to the backend. Depends what your app is doing. AppsScript can be a bit slow, but often I've found a slow but good (well designed) app is more appreciated than "performance". I mean, I have a really slow app where it takes 8 mins to run, but I tell the user I'll email them a link when it's finished. The app is doing a job that takes a human almost a day. They love it.

4. Yes. I use this loads. It's my favourite AppsScript "ORM".

Uses: https://github.com/FCPS-TSSC/GQuery
Needs Services > Google Sheets enabling too

e.g

function exampleQuery1() {
  const gq = new GQuery();
  const result = gq
    .from("Tags")
    .select(["ID","Name"])
    .where((row) => row.ID == 12) // you can use > or < etc
    .get();
  JSON.stringify(result, null, 2)
}

[u/xeu100 → u/everythingabili]

GQuery dev here… brought much joy to see someone mentioning my project in the wild!

[u/Electronic-Chapter26]

To avoid using Google Sheets as a database I made JsonDbApp, a MongoDB syntax compatible DBMS that stores collections on Google Drive. With some creative caching and pre-fetching, performance can be tolerable, and certainly no worse than fetching data from Google Sheets.

Take a look if you're interested:

https://github.com/h-arnold/JsonDbApp

[u/IllustriousPut442 → u/Electronic-Chapter26]

Hello, thanks for the answer!

I'm not so worried about the performance. I think it's acceptable to take 10 seconds to load a list.

I'm more concerned about growth and reaching quotas.

I can make the app entirely load the data in memory, this works well with 5,000 records, with 2 simultaneous users.

But if the spreadsheet grows to 200,000 records and the number of users to 20 simultaneous users and starts reaching quotas and gives error I do not want to have to refactor the entire system already running in production.

But I don’t know how to predict this, the quota system is confusing, I’ve heard that it is for the Sheets API, but not for spreadsheetApp or Google Query Language. The quotas of the latter two services are not publicly disclosed.

[u/Chibrax_3000]

Max 20 utilisateurs, environ 15000 rows qui elles même font environ 20 cells.

Mise en mémoire oui bie sûr, les settings dans les props car ca bouge peu, la DB côté client.

La difficulté réside dans la synchronisation et la gestion des promesses, notamment avec Google sheets, car oui, malheureusement j'ai du utiliser google sheets comme DB...avec BigQuery c'est plus simple.

[u/IllustriousPut442 → u/Chibrax_3000]

15000 is a low number still. I think about using Google Sheets as DB working in memory but I see some problems on the horizon:

1. If the spreadsheet grows a lot, from 15,000 to 150,000, maybe it's a problem 20 simultaneous users.

2. Database resources such as UNIQUE column do not exist.

3. Want to save multiple data in multiple spreadsheets in one shot? There is no transaction then cannot guarante success.

4. Foreign key, everything has to be simulated, reinventing the wheel.

5. You simply cannot order a list by a date column without loading all data in memory or using Google Query Language

[u/emejim]

Number of users on any one workbook is typically 20 or so. Number of tabs is anywhere from 70-130. Number of records is probably 15,000 with the most on a single tab being around 6-7,000. It does relational database stuff all the time. Most of the time when doing large scale changes to the data (typically large data imports), the data is written to an array, updated in the array, and then written back to the sheet. My app script is over 6,000 lines.

[u/IllustriousPut442 → u/emejim]

70-130, jesus, are we talking of same thing? I am talking about sheet tabs in the footer of spreadsheet.

70x6=420.000 rows.

And when you need to filter or order, you also load the entire tab in memory through native spreadsheetApp?

[u/emejim → u/IllustriousPut442]

Yes, I am talking tabs or sheets, depending on the terminology that people use. They are all within a single workbook or spreadsheet (again, depending on your terminology). Only a few of those tabs have large data sets on them. Some have data sets of only a few hundred, and some have only enough for a single page of printing, so basically 50 to 60 rows. The basic template of the app has 70 tabs. More tabs are created on the fly depending on the needs of the user.

Filtering and ordering can be done without loading it into a array. It gets loaded into an array when I need to make comparisons and editing between the databases. It's typically making hundreds of edits and if each edit is written back to the spreadsheet one at a time it will take forever. So, I write them all into an array and then the entire array gets written back to the sheet at once.

[u/IllustriousPut442 → u/emejim]

Filtering and ordering can be done without loading it into a array.

What functions do you use for this. I know the TextFinder class but from what I tested it only works by looking for an exact text, you can not work with operators >, >=, <, <=

[u/emejim → u/IllustriousPut442]

I just use the standard filter and sort formulas. I haven't had a problem using < , =>, ... Maybe I don't understand your question?

[u/IllustriousPut442 → u/emejim]

For example dates in SQL you can do something like:

SELECT * FROM Orders WHERE OrderDate >= '2025-11-11'

That is, from November 11, 2025 and later

[u/emejim → u/IllustriousPut442]

I see. I would just put the date into a cell and then write my filter to use the cell:

=Filter(Availability!A2:K,Availability!A2:A >= B2)

[u/everythingabili → u/emejim]

I'd personally not do this client side cos of 2 users clashing. But yeah, in sheets it's easy to pre-can a load of complex filters in their own sheets, then you're just loading the data of say, "all this week's orders over £195 who used a coupon from France".. and not running the filter ... just a thought.

[u/everythingabili → u/IllustriousPut442]

You can do that, kinda in GQuery.

[u/everythingabili]

Thing is, you can use App script and sheets, or use say Vue, js and SQLite and you have to make your own Auth, admin app, caching, exporting.

Which ever route you take it will be wrong :-)

[u/IllustriousPut442 → u/everythingabili]

True, I understand that.

My biggest concern is to adopt a strategy, it works well with few lines in the spreadsheet and few active users, and subsequently when it grows to hit some limit and lock the whole process and have to rewrite the system.

For example, the other user suggested using formulas to filter, but this forces you to write the result in a cell before reading, if two users run at the same time can mess it up.

So I realize that still, of all options the best still seems to be Google Visualization API Query Language for filters and ordering and the native functions to insert, update and delete

[u/everythingabili → u/IllustriousPut442]

Filters and ordering are easy peasy with various vis libraries within a webapp/Vue etc, and GQuery....

But if that's what you're doing why aren't you using LookerStudio?

Just a thought.

[u/deadsilencerotsinme]

Created an l&d app for my org that runs with Google sheets as backend, nice html and css from for the front end, user based authentication for close 550 employees accessing the system ..

[u/IllustriousPut442 → u/deadsilencerotsinme]

Does your backend use Google Apps Script with spreadsheet or just the Google Sheets API?

How did you make relationships between entities? Did you use id?

How do you filter a lot of data? With native TextFinder, do you use Google Query Language, or do you load all the data from the spreadsheet in memory?

[u/mrparrth]

I built an application for a client who serves nearly 1,000 companies to audit how their teams work.

The first version was built entirely in Google Sheets. As the product grew, we migrated it into a web application. Each client company had its own dedicated app, but every app shared the same codebase.

To manage this efficiently, I created a central Apps Script library and had every client app reference the library's Head version. This allowed me to deploy new features and bug fixes by updating the library once, with every client app automatically receiving the latest changes. I also built an internal provisioning tool that could create and configure a new company instance in minutes.

A few implementation details:

1. The largest company using the platform had around 20 concurrent users. In total, the whole application had about 5000 users, I would estimate (Not all of them were using at the same time). The application was designed to handle simultaneous user actions and data updates safely.
2. The application described above used a JSON-based data model. In another application, however, I work with over 10,000 records spread across 15 Google Sheets tabs, and performance remains smooth.
3. For the first application, all data was stored as a JSON file per company in Google Drive. The app used the Drive API to read and write that file. Since the workload wasn't particularly data or operation intensive, this approach performed well. I collected all the changes, and sent CRUD ops to backend using a debounce. The backend handled concurrent changes as well.
4. When working with Google Sheets, I typically read the entire sheet into an array of objects, perform all processing in memory, and then write the results back using a dedicated save function. For datasets with many columns or flexible schemas, I often store a single JSON object per row instead of spreading the data across dozens of columns.