How complex can an app be built on top of appsheet

sandeepraon · 01-13-2023 11:04 AM

Hi all community friends,

for the past 2 months i have been trying to ideate / conceive and build small apps with appsheet and experiment with features that one can utilize to build apps, i see some gaps and some limitations on with feature set and tools available in appsheet.

Now its time for me to upgrade to real time production scenarios such as below: special note: both of these apps need to be as close to real time as possible meaning that as rows get updated the app need to reflect what event just happened and take action based on that. (one reason for why we plan to chose google sheets as backend datasource)

1)Example 1: Event Exit / entry app - where information is collected on who entered and exited and do some immediate action like specifying on what one can do, based on the time of entry / exit , and data collection and response to the event need to be as close and immediate based on the time of event. this scenario can exponentially increase based on event where 100's -200's of entry/exit is recorded. and multiply this with days of the event , it will easily become large with minimum of 500 rows in single day.

2)Example 2: Retail contact less pickup (customer stays in parking lot and notifies the store of pickup goods), (post covid has encouraged such contact less pickup scenarios in walmart or target stores) where customers will park in the parking lot and notify for retail store checkout on their products ordered that needs to be pickedup.

for both of these above scenarios, i m planning for having google sheets to start with as a datasource. but im also worried that every day there might be bunch of rows added 1 row per order , 200+- 500+ rows getting updated every day during peak periods. Im worried that as days progress the data source might become large with 1000's of rows in google sheets.(example for a 30 days scenario it might be multiplicative of 6000-10000 rows and so on ).

Anyone experienced issues when dealing with large sheets i.e large enough for appsheets to start having performance issues and eventually getting into a state of either loading slowly or customer fatigue setting in using the appsheet app's performance.

any suggestions on better alternatives to google sheets being the backend and appsheet behaving well with other datasources than google sheets. what backend would you rate to use for such kind of intense - high load scenarios where the size of tables would increase rapidly.

Simply put my question boils down to ,

1) how large and complex can the apps be built with google sheets as backend and appsheets on front end .

2)is there a load testing platform for appsheets and google sheets *backend) or other datasources being used or

3)Suggestions to help me relieve of my pain in assuming a scenario that my app will not fail in the production scenario's out there that can hit me big time.

Any information from any who can share your experiences with such apps in the community will be highly appreciated.

Special Thank you for those who spared time to read this in detail till the end and respond.

SkrOYC

Two comments.

The platform can build very complex apps as long as you understand and accept the feature limitations that there are for obvious reasons. One of them is that it's just not a real-time platform. Every interaction with the data is on a queue in each local copy of the clients of the app and are applied in the backend on a "last change wins" kind of way.

If you are worried about how GSheets can respond to a lot of interaction, check GSheets limitations, not AppSheet limitations. You are not pushed to use GSheets as a backend.

sandeepraon

@SkrOYC Thanks for the response, my question is in regards to performance and how production apps are tested against performance in Pre production environments and then rolled out.

my questions are around how companies who build apps on appsheet are rolling out apps to customers, since im new to appsheets and app development in general, as of now we dont have any formal structure around appsheets environment like in typical build cycles - developement, UAT, preprod and production, typcially the ones you see in enterprise setting. since its a no or low code environments all i have is the appsheets built-in monitor (https://support.google.com/appsheet/answer/10104997?hl=en&ref_topic=10105391 )

right now all we have is just production app and thats about it. we want to understand

1)how things are done in a more formal app roll out phases (multi developer led teams) and how the app versions are maintained etc etc,

2)do any load testing platforms exist in appsheet where we can understand how the app behaves with varied load datasets>? or load structures?

SkrOYC

So, you can have a Stable version of an app and an up-to-date one.
Maintain a stable version of an app - AppSheet Help

But, that is just for certain pricing plans.

If you want to have Dev - Testing - Production, you need to manage 3 copies and upgrade between them when making changes.

Upgrade an app - AppSheet Help

They explained some of this in an Office Hours session:

Swoopy

I've an app tied to one main table having 30,000+ rows, takes 10-15 sec in sync.

Another considerably complex app tied to 4 related tables with one bigest table having 20,000+ rows, takes 15-25 sec in sync.

*Note that I avoid placing expensive expressions on AppSheet's editors.

You need/must have plan to compact your GS when a table reaches 40,000+ rows. I roughly assess AppSheet would be very sluggish when you have 60,000+ rows and would not run with 100,000 rows.

SkrOYC

@Swoopy wrote:

would not run with 100,000 rows

Just curious what you exactly mean by this

Swoopy

Try making & testing an app linked to 2 related tables(and more), each contains 50,000-100,000 related rows(and more). See how it goes.

SkrOYC

@sandeepraon Some comments by the guy that founded AppSheet about performance:

@pravse wrote:

I guess we should publish some general rules of thumb (adding @Derek FYI).

I’d say, assuming a PRO plan, and painting in very broad strokes here (there are many differences between SQL and google sheets and excel and …):

If you have N tables, assume N/3 seconds of baseline latency

Take your largest table in terms of cells (rows * columns) and add 1 second for every 5000 cells

In #2, If you have security filters, and they are simple (ANDs, equality or IN conditions) and you use a SQL database, then you count the rows that pass the security filters only … otherwise you count all rows

Avoid virtual columns with a SELECT and anything but an equality or IN filter condition

So that’s a good rule of thumb. Syncs should be under 10 seconds. Maybe 15 max. Otherwise, you most likely have (a) too many tables — break up your app into sub apps with a launcher, (b) too much data — use security filters, © inefficient SELECTS in the security filters, or (d) inefficient SELECTS in the virtual columns.

@pravse wrote:

To be clear, SELECT() and all these variants of SELECT() are useful and that’s why they exist. However, some SELECT() functions are efficient and some are not. This depends on the filter condition being used. Equality, IN(), and AND() of these are efficient filter conditions. Inequality and OR() functions lead to inefficient filter functions.
MAXROW() and MINROW() are also inefficient

sandeepraon

Thanks @SkrOYC this is very informative and appreciate you finding and sharing the information.

Kirk_Masden

That's here, by the way:

https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Improving-performance-by-getting-rid-of-unneces...

MultiTech

Provided that everything is built to best practices, you can build an extremely complex app.

I've got an app that I've been maintaining for nearly half a decade now, that handles the day-to-day operations for a telehealth medicine company that helps connect cardiac specialists with the primary care team of patients that are in critical cardiac situations.

Patient Enrollments, lab results, weights, meds, recommendations, treatments, etc. etc.
We have 5 main data point tables that we regularly maintain at ~90k records each
- and this is on top of 30-ish other tables to support the system

And it's not like we can filter this data out... the doctors need to see everything involved with a patient to provide the best possible care.

Provided you build things appropriately, you can have extremely complex apps - which won't take more than 10 - 20 seconds to load - covering every and all aspects of whatever's going on... all in ONE app.

It's in building and maintaining systems like these that I've gained all my wisdom and insights, - many of which are common knowledge now in the community.

daniel_sanchez

oh

Markus_Malessa

Question if you don't mind? What kind of data do the 30ish support tables hold (how many columns), and about how many VCs would you say each table carries? I would say that my most intensive app has 24 tables (12 regular, 12 many-to-many key column tables only) and each regular table probably has between 10-20 VCs. Most of my sync times average about 40-50 seconds. The largest regular table is about 60k records with one of the many-to-many key column tables probably storing about 100k records. All other tables have 2000-5000 records.

MultiTech

Hey @Markus_Malessa

I actually just opened that app for the first time in a few weeks - and was pleasantly surprised to see it load much faster than it used to.

So fast in fact that it had me worrying that maybe it didn't load all the data
Especially since I'm the dev, and basically turned off ALL the security filters - so I see everything, but it also means it takes forever for the app to load.

Not today! All data loaded in less than 8 seconds. ⚡👀

-------------------------------------------------------------------------------------

This app is in dire need of a rebuild, with the foundation having been built back in 2018! Here's a breakdown:

Data Summary	53 Tables 1687 Columns 39 Slices
UX Summary	164 Views 49 Format Rules
Behavior Summary	241 Actions

This thing is F-A-T.... REALLY needs a rebuid.
(this little investigation was really eye opening)

The tables in the app include things like:
*(record count)

Patients (3,500)
Patient Enrollments (3,600)
BPs, weights, meds, etc. (each ~83k)
Cardiac Plans (6k)
Notes (8k)

And then a bunch of smaller support tables (none more than 100 records)

Users
Facilities
Hospitals
Statuses
Reports
Analysis Components
Welcome Screen
Menu
etc.

------------------------------------------------------------------------------------------------

In regards to columns, they are kept minimal - that's the only way something like this works in AppSheet.

Largest table (Enrollments) has 54 physical columns & 7 virtual

It's the virtual columns that MUST be kept down for something this size to work, especially with all the interconnectedness of the ref_rows() - you can't have ANY extra baggage in the app.

Markus_Malessa

I appreciate the breakdown. Seems like VCs is something I need to work on the most. I'm just dreading having to built the columns into MySQL. I also have noticed that when I do add columns into my DB and then regenerate the table in Appsheet, certain columns get stripped of their settings. I believe this is because the column in the DB is stored as a number though and in Appsheet I have it as an enum of text.

Markus_Malessa

Something else I have thought about is that if global variables would be available that could be set based on a currently selected row of a table, then that would allow me to get rid of approximately 75% of my VCs. However in Appsheet that is not possible (yet?). So basically I am stuck with VCs being calculated on 2500+ rows when I really only need those calculations on the currently selected row, and then of course recalculate if I select a different row. Coming from Google App Maker I used variables like this very often.

MultiTech

@Markus_Malessa wrote:

I'm just dreading having to built the columns into MySQL. I also have noticed that when I do add columns into my DB and then regenerate the table in Appsheet, certain columns get stripped of their settings. I believe this is because the column in the DB is stored as a number though and in Appsheet I have it as an enum of text.

There's an easier way, one built by AppSheet specifically for situations like this!

With this, AppSheet will go through all the necessary steps to setup your SQL columns as they should be set based on your column settings.
The process is actually pretty straightforward:
- Create your MySQL database (It's not necessary to create tables)
- Connect this database to your AppSheet account as a new source
- Open your app and press that button highlighted above
- Select the database you want to connect to
- A process begins that creates the table, the columns, sets the settings, etc. all for you automatically

@Markus_Malessa wrote:

Something else I have thought about is that if global variables would be available that could be set based on a currently selected row of a table, then that would allow me to get rid of approximately 75% of my VCs.

If you've got a Users table, and the Current User System installed, that's the space to hold your global variables right there!

Create some "extra" columns in the User table to hold these variables
Make some way for the user to set these
- Enhanced Dashboard
- Discreet Actions
- etc.

Check out my enhanced dashboard video - I talk about how to setup the variable spaces, how to make the editable by the user in a dashboard, and how to dynamically update the data displayed in the dashboard based on the selections of the user.

sandeepraon

If possible can you share what type is the datasource (gsheets, ms sql, excel ?)
any recommendations which one would you prefer/recommend for a better performance & low latency in responsiveness. ( my app would be around scan, identify and authenticate/authorization functionality, as the scanners and the viewer will be in a close proximity scenario aka we only have maximum of 15-20 seconds of time for both parties to respond as the need to be syncd and viewed , so the latency in writing and loading the data would be as minimal as possible.) any order of preference from the below datasources, we currently are preferring for GSHEETS.

Native	Spreadsheets	Databases	Other data or provider services
AppSheet databases	Google Sheets Microsoft Excel (Office 365 and SharePoint)	AWS DynamoDB Google BigQuery MariaDB MySQL On-premises database Oracle PostgreSQL SQL Server	Airtable Apigee Box Data Studio Dropbox Google Calendar Google Drive Google Forms OData Salesforce Smartsheet

MultiTech

Everything is in Google Sheets, except for the large tables - which are hosted in a GCP MySQL server

I'm not sure which database provider is faster.... a lot actually depends on that, and you have some control over things (like regionality, etc.) that can help decrease latency.