Worksheets, Tables, Workbooks, and why multiple Workbooks and Worksheets

[Former Community Member]

Hello!

If someone could tell me if I'm on the right path or not. . . would appreciate it very much.

Thank you!

If I understand AppSheet correctly,

Worksheet = “a spreadsheet that contains cells organized in rows and columns.” 

So in other words, worksheet is a spreadsheet organized specifically for AppSheet to read & write.

Table = “a single dataset used by Appsheet.” 

So basically a single page of a worksheet (so if you have two tabs in a spreadsheet that’s organized as a worksheet, that’s 2 tables.)

Workbook = “the file that contains one or more worksheets.”

So basically the folder that Appsheet creates for you when you copy an app template to your Google Drive.

So When they say, 

“You can add multiple standalone spreadsheet files as AppSheet tables.”

It implies, it is possible to get information from a worksheet in a different workbook created for a different app. 

“It’s also possible to create AppSheet tables from different worksheets within the same workbook.”

and the reason you would do this is because,

“The AppSheet backend has to download the entire spreadsheet file.” 

if you have a lot of data, it takes time.

“but if the same file (workbook) is referenced multiple times (via several tables referencing individual worksheets in the workbook),  the backend will download it just once.” 

So no matter the number of referencing points, it’ll download the entire spreadsheet once.

“On the other hand, multiple small files can be fetched and processed efficiently in parallel.” 

So single spreadsheet or multiple worksheet basically depends if you have more data, or more referencing points.

If I’m getting this all wrong, someone please correct me!

If I’m somewhat correct is there a way for AppSheet to tell that my data outweighs the number of referencing points so you’re better off creating multiple worksheets or vice versa?

[Kirk_Masden]

You wrote:

If I’m somewhat correct is there a way for AppSheet to tell that my data outweighs the number of referencing points so you’re better off creating multiple worksheets or vice versa?

This would be nice but I don't think the platform can analyze what you are doing and suggest a more efficient way to organize the data.

I think the following might be relevant to your inquiry:

App Design 101

https://help.appsheet.com/en/articles/1100263-app-design-101

Data: The Essentials

https://help.appsheet.com/en/articles/895267-data-the-essentials

Five Tips for Building Better Spreadsheets for Your AppSheet Mobile Apps

https://blog.appsheet.com/2014/12/03/5-tips-for-building-better-spreadsheets-for-your-appsheet-mobil...

App Design: Multiple tables vs. Single table + multiple slices

https://www.googlecloudcommunity.com/gc/Tips-Tricks/App-Design-Multiple-tables-vs-Single-table-multi...

One suggestion I have is to ask a concrete question about a specific design (data configuration) you are considering.  I think it's often easier to respond to specific inquiries than to respond to questions about general principles.

Good luck!

[Former Community Member]

@Kirk_Masden 

Thank you very much!

Will keep in mind, next time I have a question 🙂

[WillowMobileSys]

Yes, you have the right details but I wouldn't base your decision on performance of loading sheets into AppSheet servers.  This performance is abstracted from users and doesn't really play into the performance considerations you will likely have for them going forward.

Instead I would look at these concerns for deciding how to collate your data sheets for ease of implementation:

1)  Maintenance - It is a pain, at least for me, to have to open multiple sheets across my browser when I am building or analyzing an app.  I place all sheets related to an app together in its own "workbook" (Google's Term is a Sheet and then Tabs inside).  Data sheets that are shared between multiple apps, I will place in their own workbook - e.g. system users.

2) Security - On occasion, not frequently, you may want certain data to be separated and more locked down (i.e. less shared access).  This is an occasion where you might decide to place that sheet somewhere more hidden from the rest.

3)  AppSheet Features - AppSheet provides data scaling features to divvy data between multiple "workbooks" and even multiple sheets.  An example might be for multi-tenant apps but could be for other reasons for a single tenant as well.

4)  Archiving  - For longevity of an AppSheet app on sheets, I strongly recommend having an archival/purge plan.  You don't need to implement it right away.  Many apps never really get off the ground so don't waste time on something that is not yet needed.  When it is needed, archiving data not used on a daily basis will keep your app running crisply.  While you can simply use Security Filters, this does require loading of that history data for AppSheet servers to decide what to filter out.  Fine for smaller sets of data (thousands of rows).  If the dataset is large (5000+ rows) , my suggestion is to move this data into its own sheet(s) entirely and then create an app for viewing only of this data.  

I'm sure there are other considerations.

I hope this helps!

[Former Community Member]

@WillowMobileSys 

Thank you very much!

[Phil]

Let me try first clarify the terminology. I will described this in terms of Microsoft Excel but Google Sheets is similar.

A worksheet is a single sheet composed of cells organized in rows and columns. Typically the first row of the worksheet contains a header row with a unique name in each column. Subsequent rows contain data values in each column that reflect the column header. For example, in an Employee worksheet, the first column might contain a header value of EmployeeId and subsequent data rows would each contain a unique employee id number. The second column might contain a header value of EmployeeName and subsequent rows would each contain an employee's name. And so forth.

A workbook is a collection of one or more worksheets that reside in an single .xlsx file. Typically all of the worksheets in a workbook are related in some way. For example, if the workbook is used to track orders, the workbook might contain one worksheet listing customers, another listing orders, another listing orderdetails, and another listing products.

A typical Appsheet app often obtains its data from one or more worksheets contained in a single workbook. For example, if you were creating a order capture app that used the order workbook just described, it would like have a Customer table based on the customer worksheet, an Orders table based on the orders worksheet, an OrderDetails table based on the orderdetails worksheet, and a Products table based on the products worksheet. 

What I just described is typical, but you can organize your workbooks and worksheet in any way you like. You could place every worksheet in its own workbook. You could put two worksheets in one workbook and the other two worksheets in another workbook. The choice is yours.

The way you organize the worksheet and workbooks can have an impact on performance if you are using Excel and your workbooks are not stored on Microsoft OneDrive or Office 365. Microsoft Excel when hosted on Microsoft OneDrive or Office 365 supports an API  that allows Appsheet to read and write individual cells from individual worksheets. This makes it relatively efficient for Appsheet to read and write data to your Excel worksheets.

If you Microsoft Excel workbook in not hosted on Microsoft OneDrive or Office 365, then no such API is available. Instead, Appsheet must read and write the entire Excel workbook (i.e. .xlsx file) when reading and writing cell values in a worksheet. This can be expensive depending on how much data is present in your Excel Workbooks. 

The Appsheet documentation provides tips for how best to organize you Excel workbooks and worksheets in this case. For example, it suggests keeping worksheets that are seldom if ever updated in one workbook and worksheets that are updated frequently in another workbook. It suggests moving worksheets that are not being used by your Appsheet application into a separate workbook that Appsheet can ignore entirely.  

Google Sheets are always stored on Google Drive. Google Sheets supports an API that allows Appsheet to read and write individual cells from individual worksheets. This makes it relatively efficient for Appsheet to read and write data to your Google worksheets. One exception to this is if your Google sheet contains Hyperlinks. The Google Sheets API is currently incapable of returning both the hyperlink text and hyperlink URL. When a hyperlink is present, we resort to reading and writing the entire Google Sheet which is more expensive.