Result displaying & response time

[Xav]

Hello everyone !

My app is designed to gather data from users and then to give them back a dashboard about their working hours and when they can go back to work.

So, one big sheet is populated by all users every day and many times a day (around 20 columns).
Then, from the main data sheet, I extract one sheet for each user with a FILTER formula.
In each of these personal sheets, I have a lot of formulas in cells : 38 more columns beside the extracted data.
Then a sheet called DASHBOARD gather the most recent information of each user which is shown on the App.

It works but when a new entry is made, the time to refresh the dashboard is very long and I would like it to be immediate (of course )

So I did optimize all my sheets by :

• deleting unused rows / columns
• filtering the main data on the smallest period of time needed for further calculations
• giving conditions to my spreadsheet formulas for calculation to be done only when needed

What else could I do ?

• putting the 38 formulas inside appsheet ? it would be pretty long to do but would it be faster ?
• putting the 38 formulas inside code in google script ? which would be also a lot of work.
• using Appsheet slices instead of FILTER() from spreadsheet ?
• I am on a PRO plan but did not change anything for now.
• using firebase ? (I just heard of it)

I am also thinking about leaving appsheet for Android Studio / SQL database or so …

Any idea / recommendations ?

Thanks a lot

Xavier

[MultiTech]

Hey Xav.

Sounds like you’ve got everything working from the sheet, am I right? If so, then yes and updates made will have a slow sync time because the app waits for all the spreadsheet calculations to be completed before grabbing the newest info.

A re-design might be in order, if it’s the case that everything is being done on the sheet; AppSheet is very powerful at splitting records into individual lists - slices and security formulas will do amazing things with data.

If you migrate all your formulas over to appsheet, only some of them will be working at certain times - this will give your app a huge performance boost. For instance, the filtering of data entry records into a list of ONLY the current users records can be done in-app with a slice; then you can use that list and extract all sorts of data out of it and work with that smaller list (easier on the system, less data to crunch).

It will take time, yes, but migrating things over to appsheet is the way to go.

In fact, I advocate the removal of ALL conditional formatting, formulas, any plugins you might have working on the sheet… anything that performs calculations should be removed. Treat the google sheet as if it were only a table, nothing else; it’s only a place to store data. If you want to work with the data, IMPORTDATA() into another sheet in another workbook; this way the calculations are being done on a workbook that’s NOT connected to the app.

[Xav]

Hi

Thank you for the quick reply.

I knew about slices but I never took the plunge because of the long migration.
I also thought 20 slices (~around 20 users) compared to my filtered sheets would slow my app down.
-> So you confirm it is the opposite ?

The reason I used filtered sheets is that the 38 calculations are done for each row (when needed) AND also depend on previous rows (which must be related to the same user). This is why I did not put these formulas in the main data base.
-> So when the Slice is created for a user, where do I write all the formulas I had on my filtered spreadsheet ?

When I create a new slice (copy from another one), can the “row filter condition” use the Slice Name ?[user] = [Slice Name] instead of [user]=‘XAV’
(the equivalent of sheetName() I use in spreadsheet for my FILTER)

Best regards

Xavier

[MultiTech]

You can go this route and create a slice for each individual user - or it sounds like you might benefit from the creation of a ralational database for your app.

If you create an Employees table, then create a reference column in your data entry table, you can connect the two together in AppSheet using the reference system. This will automatically create sub-lists for each employee of their related data entry records.

Check out references:
https://help.appsheet.com/en/?q=references

[Xav]

Ok so if I understand you, no need for slices.
I can just use reference bonds between my main database and the dashboard of my employees ? Is that right ?
But again where do I make my calculations that take for inputs :

• the element of the last entry related to a specific user
  AND
• previous entries related to the same user ?

[MultiTech]

Creating reference bonds between your tables will allow for easy navigation of these related records for admins, much more efficient formula calculations, and more - but it won’t customize the interface for the user of the app.

To individualize the app for your specific user, I’d recommend implementing a “Current_User” system; this way you can create a context-awareness in your app about who’s using it. You’ll need:

• A user table
• A Current_User slice (with a formula something like: [User_Email] = USEREMAIL()

It’s from this Current_User slice that you can then base your dashboard off of. Instead of having to create a slice for each user, instead you create one (or possible one for each of your separate data entry tables - if you have more than one) slice/view combo and each of your users uses that - which is individualized for them.

Not sure if there’s any good sample apps showing this… perhaps @Steve would know of any?

[Xav]

When you talk about user table, is it an empty sheet in my google spreadsheet in order to perform my calculations ? I am still confused where I could perform my operations.

A slice can be based on the user connected which is great.
But still I need a main dashboard with the analysis of all employees, for the manager to have a global vue. So my analysis cannot depend only on the connected employee.

Thank you