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

JPAlpano
Participant V

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).

Proposed Solution
An ability where we can import the two worksheets separately, then an AppSheet feature where we can combine the two tables into one list which can be used as a source for a form.
It can be a new option in splice or whatever, as long as I can use the two tables together.
You can still impose a limitation to the total combined rows, maybe 300K rows (meaning upto three tables with 100K rows each)?

I don’t recommend that this feature will allow combining tables that reaches a million rows, or not even half a million rows. I agree that really huge datasets should be in a database server such as an SQL or a Google BigQuery, but the list is just 150,000 rows and is not expected to grow. Subscribing to a database server just because of the 50,000 rows over the limit will be overkill and is not a practical cost.

Status Open
2 11 1,961
11 Comments
Koichi_Tsuji
Participant V

I never heard this limitation. Where have you heard this from ?

My understanding Spreadsheet has limit to max 5 mil cells, not row limits.

JPAlpano
Participant V

I saw this in many posts in this community. Just do a search for the keyword “100,000” and you’ll see them quickly. I didn’t pay much attention until I experienced it myself. I have a customer list which is 150K rows.

If I add that sheet as source to table, Appsheet will cause errors.
If I split that into two sheets with less than 100K each, then I can add the two sheets as separate tables: one table with 100K rows, and another table with 50K rows. No Issues.

But if appsheet can import the two sheets totalling to 150k rows anyways, why can’t I just import it as one table? But seems that it’s not possible, so I’m asking if there’s a way that I can just combine the two separate tables into one.

Koichi_Tsuji
Participant V

Jsut read the table (add table to your app) with the same schema, but just few rows only.
Once you managed to read this table, then swap the table soruce to your original one with rows more than 100k.

JPAlpano
Participant V

I tried this already. It will work when I fetch the fewer rows. Once synced, I change the worksheet and point it to the one with more than 100K rows, then the app would throws errors. I switch it back to the fewer row sheet, and it goes back working properly again.

Koichi_Tsuji
Participant V

We tested with our apps, and it works fine.
Not sure why you see error, maybe better to ask support@appsheet.com then.

JPAlpano
Participant V

How many columns and how many rows do you have in the Sheet?
I have 9 columns and less than 150k rows.

And even if I only have data in 1,000 rows, as long as the total rows, even when blank, exceeds 100,000, AppSheet throws the error.

Status changed to: Open
Pratyusha
Community Manager
Community Manager
 
KCa23
Community Visitor

I get the same issue for this limitation (100.000 rows) and only 4 columns !!!

Is there some new relfexion for this !!!

JPAlpano
Participant V

Any plans on this @Pratyusha ?

dbaum
Participant V

FWIW, I just did some quick and dirty testing to see for myself. I used a simple 2-column table.

  • Add table from sheet with 1 populated row and 101K blank rows: Worked fine
    • Sync app after populating all rows: Worked fine
  • Add table from sheet with 99999 populated rows and 0 blank rows: Worked fine
    • Sync app after adding and populating additional rows in sheet for total of 101K rows: Worked fine
  • Add table from sheet with 101K populated rows: I received an error that the table schema is missing and AppSheet didn't generate any column structure
JPAlpano
Participant V

Thanks Dbaum.  I will try what you did in the second bullet.

 

Still, I would love this feature as it coukd still prove beneficial to have data organized in separate sheets in the backend but allowing the users to access them as of it's a single table without having to make selections first in able to switch between partitions.