What are the key principles when breaking a l...

(David Ackling-Jones) #1

What are the key principles when breaking a large app, with many tables, into a set of smaller apps, with a launcher?

Currently there are 50+ tables in a single Google Sheet backend, with lots of references between tables.

Should I create a set of separate Google Sheets documenta to house different portions of the data?

Should I use partitions, and if so, when?

Lots of questions.

Keen for help, too.


(Simon Robinson) #2

So partitions are used mainly when you coming up against Sheets’ 2million cell limit.

You can have your data spread across many files with a partition to get around this.

Many Apps are fine.

Bu check the licensing your using.

You might get a bill for each person on each app.

I recommend that people only use a sperate App when it has data completely unrelated to others.

You can control things nicely in just one App using security filters, slices and hiding columns.

To me, 50+ tables sound a lot.

The biggest App I designed only has about 20ish tables.

That was because each table the App used also had 2 associated tables in Sheets purely to process the data into a specific order (something that Appsheet couldn’t do in a workflow attachment)

I’d spend a while looking at your tables to see if you can either merge some together.

Or use more complicated references so that they link to multiple other tables rather than just one.

Happy to chat if you want further advice.

(David Ackling-Jones) #3

Thanks for this piece of the puzzle Simon. Partitions aren’t needed yet. Our biggest table is not likely to hit the two million cell mark for a while.

Our problem is that with so many tables, references, calculated columns, workflows, etc. the sync time is about 40 seconds, and this makes use and development of the app a chore.

We run all aspects of our business through the app, and have grown rapidly and intend to continue growing for a while to come.

Praveen recommended I get some help from a Partner, on the appsheet directory, and I have reached out to a couple. No response yet.

Praveen noted that the Appsheet business, to manage itself, uses about 20 separate Appsheet apps linked by a launcher.

So my next question is, this. Given that one may want to undergo a project to break a large app with a single backend into multiple apps, linked to a set of non read only tables in the same backend spreadsheet; and, given that this might be the only way to break a large app, with a normalised backend into smaller pieces; what is a good general strategy to use to maintain the integrity of that data, while doing incremental changes with this goal?

(Leah Hoogstra) #4

This is a really helpful thread! Thank you. Lots of overlap with challenges I’m currently facing. I am also wondering to what extent moving to SQL would mitigate the need for splitting a large app into multiple smaller apps.

(Aleksi Alkio) #5

The “real” limit with gSheet is somewhere 400…500.000 cells. Then the app will still work quite well if it’s well constructed. That’s what I have seen with large gSheets.

It’s quite normal that the app series will work even quicker than an app

with one bigger gSheet… even when you need to open different apps. If you create one home view for example with a Deck view, you can use an app column with the app and view name. Then you don’t need to create any additional menu views for all other apps. One home page and you can use it for all apps.

(Simon Robinson) #6

Hi @David_Ackling-Jones .

If it helps, I’m an Appsheet partner and the apps we develop tend towards running entire businesses too.

So if you want me to take a look I’d be happy to give you some advice.

(David Ackling-Jones) #7

Thanks @Aleksi_Alkio

and @Simon_Robinson . Here’s my project, as far as I can lay it out right now. I want a separate app for each Responsibility covered by my current app, i.e an app for: -staff management -contact management -client intake and setup -client allocation and relationship/pipeline management -client progress notes -accounts recievable -accounts payable and payroll -incident and hazard management -task and project management -policy and compliance management -performance dashboard

Each app would need read only access, for example, to our Person table, which contains every client, staff, and contact relating to the business.

Question 1: Would it be wise then, for only the Contact Management app to have Update, Delete and Create access to that table?

The whole goal here is to minimise sync time.


Question 2: Would it be wise to start using different Google sheet files, instead of 50 plus seperate tabs on a single Google Sheet file?

Question 3: What are the general principles to minimise sync time? E.g. running smaller apps is one. Using Read only tables when possible is another one. What about number of different files vs having a number of different tabs in the same file?

Question 4: If I have multiple different apps, referencing the same backend table, is it important that only one of those apps is able to modify that table and the rest need to be read only? Might this be essential to avoid data corruption across multiple users?

Question 5: My thought on how to get this project

happening is to keep my current app in operation, but build these small apps on the side, and when they are tested, deprecate the part of the current app with that Responsibility. Can you see any pitfalls in this approach, based on work you have done in a similar context?



(Simon Robinson) #8

If you’re gonna redo it from scratch, and it has to be fast.

You might want to consider SQL instead

(Aleksi Alkio) #9

I believe the best way in your case (if SQL is not posible) would be as few tabs as possible because when you read the gSheet, we need to read the whole gSheet, not just those tabs/tables you are using with your app. You probably need few generic tables that all apps or most of them are using and those tables you can add into one gSheet.

(David Ackling-Jones) #10

@Aleksi_Alkio @Simon_Robinson

Guys going to SQL has been a goal from the start, but I like the agility of gsheet.

If I do go to SQL, will it still be necessary to have multiple apps to minimise sync time? I know SQL is the best for managing data in the long run.

If I stay in GSheet, what is my best way to minimise data sync errors with multiple users across multiple apps? Is only allowing one app to have Update access to any one table a good and necessary principle?

(David Ackling-Jones) #11

@Aleksi_Alkio @Simon_Robinson +Steve Coile

Thanks for the great advice so far. Just want your feedback on what seems to be my best way forward.

  1. Create a new Launcher App, and add my current big-app to it as the first option 2. Build a series of new small-apps to replace parts of the big-app, and gradually add those to the launcher, while deactivating those same parts of the big-app. 3. Cut worksheets out of the gsheet

for the big-app, and create a series of smaller gsheet files, that relate to each small-app 4. For those sheets that relate to more than one app, I will try and make them read only, except in the one app that can update them.

Think this way will allow us to test and ensure our live data is not corrupted during the transition.

Open to more advice.



(David Ackling-Jones) #12

@Simon_Robinson Which Appsheet Partner are you with? I could use some help. Does your team have experience with Azure SQL, or MySQL? If not, can you recommend a developer with SQL and Apsheet dev abilties?

The goal is to create something robust, and fast syncing to allow us to scale, and fix the long sync time problem we are already having.

Rather than breaking our Google sheet up into lots of smaller ones it might be more efficient to just shift it all across into one SQL database.

I have lots of experience in Microsoft Access and Appsheet, I just have limited time now to dive in and get the deep knowledge of MySQL or Azure I would need to run this project.

In the meantime, I will keep using sheets.

Cheers, David.

@Aleksi_Alkio +Steve Coile +Jarrod Rapson