An AppSheet app defines a table as having one table source. The table source is usually a spreadsheet file with an optional worksheet name, but it could also be a database table or other table source.
When using the Data Partition feature, you:
- Partition (i.e. divide) the rows of the table into many table sources;
- Register these different table sources in the app's table definition via the app editor; and
- Provide a partition expression that lets the system determine which table source to use for which user.
For example, if you have a table of timesheet entries for employees and there are 10,000 employees, you can partition this data into 10 separate sheets or partitions, each holding 1000 employees. The partition expression will identify a specific partition for each user based on the USEREMAIL() or USERSETTINGS() values.
Data Partitions are a powerful mechanism to scale your app while keeping your data in the format of familiar spreadsheet systems like Google Sheets, Excel, or Smartsheet. However, because you have divided your data set into several physical sheets, you may create extra management overhead for data maintenance and reporting.
It is important that all the partitions have identical column structure! AppSheet cannot guarantee this behavior, so this becomes an additional responsibility for the app creator.
Data partitioning: Multiple Worksheets
If you are using Google Sheets and have enabled the Bulk Read option (via the Behavior > Offline Settings pane), the system can efficiently read a single worksheet from the overall workbook/spreadsheet. Therefore, when considering data partitioning for scale, the easiest initial step is to partition the data into several worksheets in the same workbook/spreadsheet. This is an option in the Table definition in the app editor:
Data partitioning: multiple spreadsheets
Whatever the data source, you can partition your table into several spreadsheets. This is an option in the table definition in the app editor:
Data Partitioning: Maximal Scale
The different data partitioning mechanisms can be combined. The data can be partitioned across several spreadsheet workbooks and each can be partitioned across several worksheets. It is important that each of the workbooks has identically named worksheets. A security filter may further reduce the data sent to the mobile device. This combination will maximize the scale of the app.
As long as each user of the app needs only a reasonably small subset of the overall table, the underlying data can scale almost infinitely in this fashion. Users on the Smartsheet platform can easily scale past the 5000 row limit imposed by Smartsheet. Users on the Google Sheets platform can easily scale past the 2 million cell limit imposed by Google.
Data partitioning: Other Scenarios
While data partitioning is primarily a feature to improve performance at scale, it can also be a convenient mechanism to handle deployment scale in a relatively common scenario. It is sometimes the case that data sets needs to be maintained in separate physical containers (database tables or spreadsheet files) for reasons other than performance. For example, if an app is used by five different clients, it might be important to keep each client's data in a separate sheet. However, if you want to build a single app that is used by all five clients, data partitions provide a convenient solution. You can build, maintain and distribute a single app, yet each client sees just their own data and the data sets for each client are all kept physically distinct.
Learn more about partitions in this demo: