I am testing an Enterprise account with ASDB (I have thought about MySQL, but I don’t know where to start, and it will be even more expensive). I am impressed with the quicker load times and especially being able to quickly load data using security filters, which is a big plus for people working with 4G connections on their phones.
I am running into the 100 column limitation on one of my apps. This is for measuring drainage (sewer) chambers in the field. There are a lot of variables, different measurements needed and If this then measure that options. Currently, my Google Sheet is 212 columns long.
I have done a count and I can safely remove around 70 columns by converting text/information columns into virtual columns and condensing some formulas, but I need to expand this app again to collect even more information, so it will grow up to 200 columns again.
I don’t see any feasible way of making this app smaller without spreading it over many tables and adding extra confusion to the on site workers with how it works - it needs to be as simple as possible and having all the information in one row, means the end user can just keep pressing next on the form and fill out the required details, until they finish the inspection and they press save.
Why is there a 100-column limitation? (I am guessing it is so they can offer a hard limit on the number of rows).
Is there any way to increase this? In the help section, it tells you to talk to sales to increase the row limit, but this will not be a problem for this app. It doesn’t mention the column limit.
I think the 100-column limit is on the small side and is overlooked for apps that collect a lot of field data.
Edit: Just to add I created a Google sheet at 50,000 x 200 cells and ASDB refused to import anything past 100 columns, so it is a hard limit.
AppSheet Database has a lot of flaws, and is really best suited for beginner app creators with simple app needs. I strongly recommend Google Sheets over AppSheet Database for everyone. Why do you want to move away from Sheets? And why did you choose an enterprise plan if you aren’t going to use an SQL database?
Some of the apps that I have made are becoming slow to use, and to load in the field using mobile networks.
It has been a case of, this is an app that I built, can you add this on, and now this and then suddenly what started out as a basic list of job numbers is now a project management tool and contains 100 columns ( edit not rows). Nearly every app references this app, so it loads in all the data every time. Some apps have also been extensively used and now have 60,000 entries in 2 years. Archiving or deleting the data isn’t a great option as we might return to the site in 2+ years time and need the data again.
I know that I need to do data management on most of the apps, so I looked at (and am testing on one account) Enterprise for the database capabilities, to see what options there are and pick the best one (I’ve even looked at other apps).
It seems if I stick with sheets I will have slower loading times (despite security filters) and ASDB has other limitations which I am finding whilst testing.
As for why not using SQL - I know very little about it, other than Googling for the past couple of weeks, and it will need to be hosted in the cloud which could get expensive. (edited) I don’t really know where to start with it. I have roughly have 27 Google Sheets workbooks, some with multiple sheets. It looks like I can have one cloud database containing all these workbooks/sheets. But then I look at scaling costs and I don’t know how much it will be with ram/cpus/data usage.
Thanks. The 60,000 entries seems a lot, but in reality this is people taking photographs on site. I use AppSheet to capture user, time, geo reference and allow for quick naming of the photos on site, which I then use an Excel macro to rename the downloaded data.
I have done some “gemini-ing” on SQL prices. I think where I was going wrong is assuming every workbook/sheet needs a new database, when this is not the case. It looks like it’ll cost £60 - £90 per month on AWS and they also have a free tier for testing. I need to do some more looking into this.
Generally speaking, in ANY relational database, once you get above 50 columns, it’s time to look at splitting up the table into smaller related tables.
Typically, what has happened in tables that get above that size is that there are CONDITIONAL columns of data. Meaning certain columns are only filled in for certain row “types”. This leaves you with MANY rows that have half or more columns that are blank because those columns do not apply to THAT row. This is a very inefficient structure - especially for large numbers of rows - because BLANK columns still require processing overhead for loading of the table and processing of the row.
My guess is that the 100 column limit is meant to force apps into a more efficient structure - which is better all around for everyone, Appsheet and App Creator, for processing and storage efficiency.
There is a second reason tables can get this big - they are in NON-normal form. This basically means the column itself has a certain assumption about the data it contains. In order to add MORE of that type of data you must ADD more columns. A good example might be a questionnaire app where each column represents a certain question - say Question 1, Question 2, Question 3, Etc. To add more questions, additional question columns must be added. This structure is meant to mimic the spreadsheet concept where you can freely add/remove columns as needed. But it is a nightmare within a relational database and with regards to maintaining the app using a relational database.
You want the tables in normal form and that does sometimes mean compromising on the way its displayed to users - at least when compared to previous spreadsheet usage.
You do not really need to know SQL to use a database source. It does help when analyzing data but then you only really need simple query abilities to look at data for de-bugging things.
Most, if not all, of the logic you would be creating to manage your data would be in the AppSheet app.
Note that you cannot store photos in the database - which is no different than what you are doing now using sheets. I only mention it because lots of times those new to databases think they can store their document and images in the database as well. You will still need to use your cloud based storage provider in conjunction with whichever database service you go with
You might consider looking at Google BigQuery as well. It is NOT a relational database but a data warehouse tool. There are minor compromises over a relational database. I have found it a very good option for AppSheet. My usage has been through a client and my understanding is that their cost has been less than $20/month but they do have Workspace accounts which may contribute to a reduced fee.
If you’d like some advice on how you might be able to conform your table(s) to be used in ASDB, or any other db, provide an example of the columns here. I’d be happy to look at it and provide you some options.
Since Ste last replied, I have signed up for a free AWS plan and have set up MySQL on it and connected it to MySQL WorkBench and AppSheet. This is all very new to me, but so far, so good. It is importing a CSV, which in Sheets terms is 100,000 rows and 100 columns. The import is going very slowly but is working. Once it has loaded, I will then have the same data in sheets, ASDB and MySQL so I can do some comparisions.
My original question was about the column limit and you are correct about my drainage inspection form, on a typical entry, there are many boxes that are not filled in because they are not needed, but I don’t think I will find a good way to present this to the end user if I have to split it up into different sections. I also have another table that is 110 columns and though I can trim this down, I think using ASDB long term will cause more issues than it solves.
If MySQL works well and my company agrees that the increase in cost is worthwhile, I think this will be the route I take.
I will reach out to you both again if (when!) I have more questions.
For now, I need to go and do other stuff and will leave the importer running.
Keep in mind that ASDB is a relatively new feature in AppSheet. They have been slowly expanding capabilities over time as the feature matures. Eventually, it will become a viable option for ANY app.
For now, I would steer clear of it as a source for larger deployed apps!
In case anyone is interested, I have set up a AWS account with db.t3.large and some very quick runs with the data show the different speeds that I get
Syncing the data with no security filter, 100,000 rows with 100 columns
Google Sheets = 44 sec
AppSheet Database = 54 sec
AWS Database = 6 seconds
Syncing the data with a security filter of user = “xxx”
Google Sheets = 19 seconds
AppSheet Database = 4 seconds
AWS Database = 2 seconds
Changing 100 records by updating 1 column and changing the colour to orange, with the above security filter
Google Sheets = 7m 50sec
AppSheet Database = 5m 15sec
AWS Database = 1m 49 sec
I know there are lots of different variables here, but what I found interesting was for the full load of 100,000 x 100 Google Sheets was faster than ASDB. I ran this a few times and got the same results.
I will keep testing and then put my case forward to the rest of the company. I am finding MySQL a bit of a pain. I had been trying for the last 28 hours to load the 100mb csv file onto the server using the wizard in SQL Workbench but it would never complete. I ran it from a command line and it uploaded it in 7 seconds - this is why I changed from the free plan to a paid plan for testing as I thought the plan I had couldn’t handle it, but there is something going on in the program instead.
There must be logs in AWS somewhere to help identify the problem. More than likely it is a data syntax issue in the CSV file. Make sure all the data being uploaded conforms the rules of the upload process. One of the most common mistakes is not realizing that data columns have commas in them. These will throw off the whole upload process.
Following the discussion, ghost5 set up a MySQL database on AWS to compare performance. Their tests with 100,000 rows revealed that the AWS database was significantly faster than