Outgrown 100,000 rows in spreadsheet. How to scale up?

[fmahmad88]

I’m trying to import a spreadsheet with more than a million rows. How do I handle this while using the Appsheet interface? Ideas?

[hru]

fmahmad88:

import a spreadsheet with more than a million rows

Waw… are you using google sheet?

[fmahmad88]

Yes, lol. I’ve been using a combination of Excel and Google sheets.

I heard someone talk about MySQL so curious how people would handle massive databases with streamlined front end. Ideas?

[Jonathon · Accepted solution]

For an app this size, you really need to migrate to a real SQL server. I expect Google Sheets / Excel would run very slow.

[Austin]

Even on a database 1 million rows will slightly chug unless your table is still small in overall size. Performing any virtual columns beyond the most basic on this table will eat your performance as well.

[JPAlpano]

This may be an old topic and I agree with huge datasets to be using database servers.
But there are some exceptions.

Hope you can vote for my feature request.

Combining Multiple Tables based on Google Sheet as one list/table (Append)

Use Case :
A table based on a Google Sheet with less than 150,000 rows.

Limitations encountered :
Appsheet can only connect to a Google Sheet with 100,000 rows or less

Available Workaround :
Split the Google Sheet into two worksheets, making sure each worksheet doesn’t have more than 100,000 rows.

• Import the two worksheets as separate tables
• Use each worksheet as data partitions

Problems with the available workarounds
Users will need full access to the list, and switching between tables or partitions by using USERSETTINGS() or table expressions puts some limitations on some use cases where the tables are used as source for adding items to a child table using ref_rows (sub-form).

[YSP]

Interesting to hear that AppSheet limits GoogleSheets to 100,000 rows. How many columns do you have in your database? My understanding was GoogleSheet was limited to 5,000,000 cells (no row limit). Limitations of Google Sheets (Row/Column/File Size) - Spreadsheet Point
I am building an App that will have 250 rows every day. At 100,000 rows i will only get 400 days.

[JPAlpano]

I have 9 columns in my Google Sheet.
I’m definitely not anywhere near the 5M cells limit.
I’ve also deleted any unused columns and rows.

What’s weird is that if I split the sheet into two, I am able import both sheets separately. I just can’t import them together as one sheet. So it can’t possibly be a google limitation.

[YSP]

Agree, it does sound like an Appsheet restriction. I’m wondering if Appsheet does this on purpose to ensure the performance of the app is maintained (especially during sync). I’ve got the same problem where splitting into different worksheets makes it difficult for some users who need access to all sheets at once. By any chance have you tried using different tabs vs different sheets? Same result?

[JPAlpano]

What I dont understand is why appsheet will not allow me to connect to the one sheet with all the rows, while it allows me to connect two separate sheets, which I assume, will both be part of the sync and shouldnt have any difference in performance.

Ive tried both tabs and workbook approach. But what I am hoping for is to at least have the ability to combine the two tables as one splice or onevirtual table.

[JPAlpano]

Guess what I just found out. Even if I only have data on 1,000 rows, as long as my Googlesheet have more than 100K rows, even when blank, APPSHEET throws an error.

[YSP]

Yep I think you’ve proven it’s an Appsheet restriction. Interesting because a few other posts I read pointed towards a database size limit e.g. MB.
What is the performance of your app sync and load times when you have 100,000 rows?

[JPAlpano]

I don’t see any performance issue at all, with 100,000 rows.

[YSP]

Hi, I’ve been playing around with slices across 3 separate worksheets. How did you get it to aggregate all three sheets? i can only select one worksheet in the ‘Source table’ drop down?
Thanks

[JPAlpano]

I haven’t been able to do that. Hence the feature request I submitted.

[YSP]

Just checking, did you have the same issue with excel?

[JPAlpano]

I haven’t tried as my company is using Google Workspace. No MS Office.