Challenging Data Structure

Ask for Help
1

Hello everyone :wave:

I want to pick your thought on a challenging data structure subject!

I work in a real estate agency, our data are stored on a dozens of google sheets, containing dozens of tables each containing thousands of rows.
Each internal team has its own google sheet, but every google sheet is connected to almost any of the other via app script, import range and filters & query inside of google sheets.

Lots of actions are automated on top of these google sheet with app script, autocrat for doc creation, and zaps.

We have two small glide apps plugged on a google sheet, where we import data from all of our google sheets. It means that our glide app is just used for reading and displaying purposes, we can’t edit data. Internally, everyone works on the google sheets, but as you can imagine, it’s slow, it creates errors, and the UX is bad.

My goal is to create one single internal app, under which I would regroup every functionality currently done in our google sheets, and of course, each actions happening afterwards.

But where to begins and what’s the best way?

I have 3 ideas :

  1. Plugging 1 glide app on top of each google sheet. Each team will have their app, it’s the easiest and fastest way, because every automations wouldn’t need to be created again (as it happens on the google sheets). The inconvenient is that or DB would still be slow, unscalable and not duplicable.
  2. Restructure Databases to create one main google sheet, where I would plug our main glide app. I prefer this option, but I need to recreate every actions and automations.
  3. Go full Glide table. I love this options but is it truly scalable ? I’m looking into big query and big glide table, but we don’t find that many experts and there seems to be lots of limitations.

In the long term we want something scalable, and duplicable. I want to keep as much app scripts as possible, or find a way to do it in glide? It’s very convenient to trigger automations and create docs whiteout using zapper, make and PDFmonkey.

Sorry for this long message, and thanks for your feedbacks!

Have a nice day,
Lucas

2

A few questions:

  • How many new rows are being created daily across the board? Need to consider Glide update counts and the cost.
  • What’s the current data volume and how much history needs to be accessible? If you don’t need all the data available in Glide then it can scale forever. Also a good idea to have your data in Google Sheets as a backup.

A blend of Glide Tables and Google sheets is probably the best for scaling, but depends on specific needs.

3 Likes
3

Thanks for your answer!

  1. Hard to tell but not that many rows creation. <100 On the other hand, we edit a lot!
  2. I can clean it down to 30/35k rows, and I need to import every passed data.

As of today not that many brut data, but the data structure needs simplifying, and it will scale quickly.

You’re right for now I have in mind a mix between glide and google sheet. Storing our brut data in google sheets triggering autocrat and appscript, and “smaller” data such as messages entirely stored in glide.

4

What about just enabling the edit+add functions in Glide? And then build triggers in Glide for your App scripts (can do this with javascript column).

So everything stays the same, but Glide becomes the frontend for add+edit as well. So you don’t have to change the data structure, just where the users interact with the data.

5

That’s an idea, but as the data lives in different google sheet I would need to have multiple glide apps, which is not optimal in the long term.

I never tried triggering an appscript from a Glide table, seems interesting!

6

Hola Lucas,

To trigger a GS script from an external source, it has to be deployed as a web app. Google give you 90 minutes daily to run your script (there are other limits of course, but it’s enough for most cases).

You can read/write data from/to your GS via API as well, I have done this before with Glide. This post is a good point to start and join new ideas: https://www.instructables.com/Google-Sheets-for-IOT/

Saludos!

1 Like
7

Hola Gvaleo,

Thanks for sharing, I’ll look into that!!