SWITCH to assign a value to another column and output a text value for the current column.

[Boleslav]

I use switch to define the Initial Value. The problem is that I have 2 columns that can be defined with one formula and I don't want to call the same formula for the Initial Value of the other column. I would like to use switch to assign a value to the other column and the current column where I used the formula. I tried this but instead of assigning a value it just compares the value to the one I want to assign.

I call this formula in the Initial Value of the [Amount] column.

SWITCH([Type],

"Replacement", ([_THISROW].[Status]="Unpaid", [_THISROW].[Amount]="1.00"),

"Exchange", ([_THISROW].[Status]="Waived", [_THISROW].[Amount]="1.00"),

([_THISROW].[Status]="No Fee", [_THISROW].[Amount]="0.00"))

Please help me to understand the syntax for performing several actions.

[TeeSee1]

You cannot do what you want to do. Simply write a switch statement in both columns 

By the way why is it a problem to write a switch statement in both columns?

[Boleslav]

My application is very heavily loaded with a huge number of complex formulas. There are 40 columns + 11 virtual columns at each step requires checking 4-5 parameters. There are a lot of options for combining columns, but most of them obey the rules and not to change several parameters at the same time, it is necessary to change different columns based on a change in one parameter while taking into account other parameters. The point is that the user can not make a mistake and he did not need to learn all the rules. If I find a solution to how to perform such a formula variant it will reduce the load on the application and I'm not just talking about this column but I have 10-15 other places where I can use less calculations if I find a way to perform multiple actions when a condition occurs. I don't think it's efficient to run the same formula 2 times. I am afraid that the application will slow down because of the amount of calculations. I have never done such a complex application.

When I use SWITCH and fulfill the condition I can execute some functions to use and in the response for example CONCATENATE("123", [test]), it seems to me that there should be an option where I can execute several unrelated functions and the problem is only in the syntax. It's just normal in programming languages that after the SWITCH condition is met, you can call another function or several functions.

[Boleslav]

When I use SWITCH and fulfill the condition I can execute some functions to use and in the response for example CONCATENATE("123", [test]), it seems to me that there should be a way where I can execute several functions unrelated functions and the problem is only in syntax. It's just normal in programming languages that after the SWITCH condition is met, you can call another function or several functions.

[TeeSee1 · Accepted solution]

As it is written clearly in the document of the function switch, the only thing you can specify as it's return object is a value.

Appsheet does not provide a full blown programming language like Java.

If you absolutely need to reduce the amount of computation within app sheet then you might want to consider using Google apps script to calculate your results and update the row through app sheet API.

[Boleslav]

How can I call Apps Script from the Initial Value? As far as I understand I can only call Apps Script with a bot. There seems to be an add trigger, but I don't really understand at what point it is triggered after I clicked save? I would like to see some values before I hit save as in rare cases they will need to be changed. But it would definitely work for some of the columns thanks for the idea.

[TeeSee1]

Yes, you need to use a bot. And it is triggered when you hit save so those columns you want to check before saving, use Initial values and the rest can depend on Apps Script updates. Try and see what happens. It is always good to try and see for yourself.