Using spreadsheet formula comparing to virtual col...

Islam1 · 10-12-2024 09:17 AM

My first Question:

If I have these options to my app:

1- Spreadsheet formula (Arrayformula)

2- Virtual column

Which option would be better to the performance of my appsheet?

My Second Question:

If the better option is to use virtual column instead of arrayformula:

what should I do, if I need the computed values in my database?

My Third Question:

What is the maximum number of virtual columns which I can use without slowing my app performance, (or how I can use it effectively) ?

My Last Qustion

If I have a VLOOKUP formula in appsheet row, is there a way to update its value on all rows when the app starts?

Steve

Which option would be better to the performance of my appsheet?

Spreadsheet formula.

what should I do, if I need the computed values in my database?

Depends on the database's capabilities.

What is the maximum number of virtual columns which I can use without slowing my app performance, (or how I can use it effectively) ?

Depends on the complexity of the computation done by the virtual columns.

If I have a VLOOKUP formula in appsheet row, is there a way to update its value on all rows when the app starts?

No.

Islam1

Hello Steve,

Thanks for your response.

@Steve wrote:

what should I do, if I need the computed values in my database?
Depends on the database's capabilities.

Could you explain more, please?

Steve

You yourself haven't provided enough information for me to elaborate.

Islam1

Let's say that the virtual column has a vlookup function and we need a copy of its value in the spreadsheet.

scott192

Instead of using a VC, why not put the lookup formula in the Initial Value of the column that is going to hold it? Make sure that you set that column to have the Reset on Edit flag so that whenever that record is updated then the lookup is re-evaluated as well.
The difference? The lookup is only performed when the record is updated and synced, not when ANY changes to any other records are updated and synced.

Islam1

Hello @scott192 ,

Thanks for your response

I will try to explain more what I actually mean

Let's say that we have Column Name [ShippingCode] and [ShippingStatus]

[ShippingStatus] has a lookup to gets the value from shipping data sheet

if the shipping data sheet updates daily, we need the shipping status to update automatically as well

This function we can made with arrayformula in spreadsheet or virtualcolumn in appsheet but the problem when we use virtual column is that we need its value in the database

Trevwiller

https://books.apple.com/us/book/appsheet-standard-nomenclature-protocol/id6480063548

The above can help with most virtual column questions. Not a end all be all, but a great place for direction without scouring the forum for long periods of time on virtual column questions and basic structure. Also there are many different use cases from handling your app data. The more of something the more expensive. less data in each cell more columns you can have. more data in each cell less rows.

using sheet functions can get tricky as there are times that Google will update permissions settings and can cause your query to throw an error crashing the app. (but will be faster, but require more work hours for "Not If But When" fixes.)

A little of both can be a healthy balance but depends on the app and your use case. No one size fits all scenarios. but sometimes how hard do you want future YOU to work?

Aurelien

Hi @Islam1

Depending on your use case, if you are afraid of any impact of virtual columns on your app performance, and given that arrayformula stops calculating beyond a certain amount of rows (I have experienced this in the past), you may want to either use a bot or a script to update values. It works like a charm, provided you are not willing to update a too large amount of records.

To explain a little further, you can use a bot with:

event: add or update

process: task "data change"

Here is an example:

Here is another example:

For reference:

Bots: The Essentials - AppSheet Help

Steps: The Essentials - AppSheet Help