Is there any function to solve this?

[juanpa]

I have a table similar to this one:

The first column has some text, in the other columns you can find the translation of that text to the respective language.

I'm looking for a function that when I open the app and select the language (ie: Spanish) in USERSETTINGS the app translates everything I have in the "text" column to the language I selected.

I know this is possible and easier to do if I use the LOOKUP function and build the table like this:

However, if the table has that structure I will have more than 1000 columns which will generate issues.

Is it possible to solve this using functions, virtual columns, etc or do you have any suggestions?

Thanks in advance for your time 🙂

[Swoopy · Accepted solution]

Please consider changing the table structure as shown above. You will have only 3 columns.

Note that the * indicates key columns of this table.

[Kirk_Masden]

Check out the explanation from @MultiTech in response to the following post of mine:

https://www.googlecloudcommunity.com/gc/Tips-Tricks/How-to-instantly-apply-quot-global-quot-format-r...

I now use index() instead of lookup() in situations like this.

[juanpa]

Thanks @Swoopy and @Kirk_Masden both ideas were very useful for me 🙂