Calculate column only for CSV export

[Fabian_Weller]

I have a Virtual Column that I only need in the CSV export. It should not be calculated on sync. Only when I download the data via action "app: export this view to a csv file".

Is this possible? I tried different IF() and IFS() with CONTEXT() but with no luck.

[graham_howe]

I'm pretty sure exporting a view to CSV can only export what is shown in the view. So if the VC is not in the view it will not be exported.

[Fabian_Weller]

Hi @graham_howe The CSV will contain all columns of the table or the slice that you use for the view. It will not only contain the columns that you show in the view. If you have a table view, you can control that via "column order". You can exclude a column from the table view. But it will be included in the CSV. You can use a Show_If for the column like CONTEXT("ViewType")="Table. Then it will be included in the CSV, but the values will be blank. So the only way to exclude a column from the csv is to use a slice without that column.

What I want to say is: To include a column in the CSV. you don't need to show that column in the view.

The big question is: Are VCs calculated only on sync, or also when we download a CSV? 

[Fabian_Weller]

@MultiTech I was thinking about your post where you wrap a SELECT() into a CONTEXT() condition.

https://www.googlecloudcommunity.com/gc/Tips-Tricks/How-to-create-an-quot-Inline-Helper-quot-inside-...

Do you have any hint?

[Suvrutt_Gurjar]

Hi @Fabian_Weller ,

May we know any specific reason you wish to calculate the VC only during the CSV export? Obviously one assumption is , it is probably having multi row expressions that are likely to add to sync time if left to calculate during each sync.

[Fabian_Weller]

Thank you @Suvrutt_Gurjar for joining in.
We have at least 2 reasons: 

1. We use "Localize" columns to "translate" the IDs into User readable Labels. This is necessary only for CSV export.
   https://www.googlecloudcommunity.com/gc/AppSheet-Q-A/Export-csv/m-p/414766/highlight/true#M163585
2. We also want to export Child entries. The CSV downloads rows from the parent table. In a VC we want to put in the children with [Related children][Label]
   This is also relevant only for CSV export.

[Suvrutt_Gurjar]

Hi @Fabian_Weller ,

Thank you for all the details. Sorry, if I have not understood the use case but still not clear why you want to calculate the VC only during CSV export. Any specific reason you do not wish to compute them during other syncs?

Unlike multirow VCs,  the related VCs and even columns derived from them with formulas [Related Column Name][Another Column in Child Table] seem to be consuming very less sync time.

In the example below, the two VCs [Related SWC Players] and [AllCoaches' have taken of the order of 0.061 seconds totally between them.  The [AllCaoches] read around 10000 values combined for all parent table records. 

Do you have many such VCs ( Related values and dereferenced list values) that add to the sync time significantly?

[Steve]

I have never heard any suggestion that VCs are recomputed on export. I would be surprised if they are,

[Fabian_Weller]

@Suvrutt_Gurjar @Steve I'm pretty sure that VCs are recomputed on CSV export.
The Performance Profile shows an "ExportView" entry. 
If you click on the binoculars you can see that all VCs are calculated.
Who may have more insight on that?
My goal is to calculate the VCs only on CSV export.
I think @Phil did a lot on CSV, but I'm not sure if he's still in the AppSheet team. 

In Audit History we can see "Operation": "ExportView",
If we could use this in CONTEXT() expression, then we could tell that it should calculate these VCs only if CONTEXT("Operation")="ExportView".

Adding @Koichi_Tsuji because I think you are working a lot with CSV.

[Suvrutt_Gurjar · Accepted solution]

Hi @Fabian_Weller ,

Thank you for more details. I am sure other colleagues will add more. Please explore if below testing and observations help.

1) I created a User Settings column called ExportCSV of Y/N type.

2) I had 2 multirow expressions VCs with SELECT() expressions in a table with 5000 rows ( table called "SWCPlayers5K")

3) For those VCs,  I modified the expression as 

IFS(USERSETTINGS(ExportCSV), SELECT(..............), 

      NOT( USERSETTINGS(ExportCSV)), LIST()

     )

Which essentially means the VCs are calculated with expensive SELECT() statements only when user settings column is  set to Yes or else they just calculate an empty list.

4) I also implemented this user settings column in Export CSV action condition as below

USERSETTINGS(ExportCSV)

which means the action will show only when the ExportCSV usersetting is set to Yes.

5) My results showed that when the usersettings column is set to Yes, which shows the Export CSV action in the view, the VCs also calculate and add significantly to sync time as shown in the screenshot below

6) When the usersettings ExportCSV column was set to No and when the app was synced, the performance profile did not show VCs time for that table, hinting the VC time was negligible as practically it was empty list , LIST()

So you could try to enable VCs to calculate during the Export CSV action only. Please test well. I too will test more for more conclusive results.

[Fabian_Weller]

Thank you @Suvrutt_Gurjar this is exactly what we are doing.
We even just use a simplified IFS()
IFS(USERSETTINGS(ExportCSV)=TRUE, SELECT(..............))
With that nothing will be calculated.
Can you confirm that this is working also for you? And do you think it is more effective?

Another question:
Is 
USERSETTINGS(ExportCSV)
the same as
USERSETTINGS(ExportCSV)=TRUE
?

[Suvrutt_Gurjar]

Hi @Fabian_Weller ,

I tested with the shorter IFS() suggested by you. It works the same way. So you can use a short IFS().

I tested performance with Export CSV actions two times today with shorter IFS() , both the times VCs showed a significant time to calculate with USERSETTINGS(ExportCSV)=TRUE or Yes.

When I tested by syncing the app with the app's sync symbol and with USERSETTINGS(ExportCSV)=FALSE or No, the VCs took extremely small time as the following image shows.

Regrading your question on are USERSETTINGS(ExportCSV)
the same as
USERSETTINGS(ExportCSV)=TRUE,

yes they are same. You can use just the column name for checking TRUE/FALSE status of any Y/N column. This is so because a Y/N column itself evaluates to TRUE or FALSE so using another TRUE like USERSETTINGS(ExportCSV)=TRUE is not required.