Need help with writing a complicated google sheet formula on Glide tables

[Hassan_Nadeem]

I am creating a game on Glideapps where users input a word and each letter of that word has a specific point.

For example:
E = 1 point
F = 2 points
G = 3 points
H = 4 points
I = 5 Points

Now for example if a user adds a word “HI”, they will get 9 points (H = 4 points + I = 5 points.)

I can easily achieve that by splitting the text and do a relationship with score sheet and do a roll up to get total points.

The problem is if a word has same letter for example
“HIGH”, the relationship doesn’t know that the letter “H” has been used twice therefore it doesn’t add up its score twice.

The problem is explained better on a loom video attached below.

Here is the formula I used in Google Spreadsheet to get what I want but can’t replicate the formula into glide:
=ARRAY_CONSTRAIN(ARRAYFORMULA(SUM(IFERROR(VLOOKUP(MID(UPPER(C2),ROW(INDIRECT(“1:”&LEN(C2))),1),{“A”,1;“B”,2;“C”,3;“D”,4;“E”,5;“F”,6;“G”,7;“H”,8;“I”,9;“J”,1;“K”,2;“L”,3;“M”,4;“N”,5;“O”,6;“P”,7;“Q”,8;“R”,9;“S”,1;“T”,2;“U”,3;“V”,4;“W”,5;“X”,6;“Y”,7;“Z”,8},2,FALSE),0))), 1, 1)

[Uzo]

Use the unique formula to eliminate repeating letters

[Hassan_Nadeem]

I don’t get what you mean.

The other way I can think of is keep this formula on google sheets but I can’t figure out how to apply it to the entire column so whenever I add a new row on glide it automatically gets the formula

[Uzo]

use the excel formula column or Java column… or I see that you already split your words into letters array… so all you have to do is use Unique Array

[Hassan_Nadeem]

I think you didn’t get what I want. If I do a relationship and roll up, How’d the value be calculated twice?

[Uzo]

you don’t want to calculate twice… that’s what I understood… that’s why I eliminated multiple letters…
ok… then all you have to do is to make a vertical array from split letters and then do relation and rollup

[Hassan_Nadeem]

How do I do a vertical array?

[Uzo]

there are so many examples here…
create a row numbers using row ID lookup, then find the array index… next use a single value column to get the index position of each letter

[Uzo]

@Hassan_Nadeem Or, to make it simple… use the Java column

No relations, only ONE column!

[Hassan_Nadeem]

So cool. Can you please share this javascript here?

[Uzo]

sure…

var x = [['A',1],['B',2],['C',3],['D',4],['E',5],['F',6],['G',7],['H',8],['I',9],['J',1],['K',2],['L',3],['M',4],['N',5],['O',6],['P',7],['Q',8],['R',9],['S',1],['T',2],['U',3],['V',4],['W',5],['X',6],['Y',7],['Z',8]];
var text = p1.toUpperCase();
var res=0;
for(var j=0;j<text.length;++j){
for(var i=0;i<x.length;++i){if(text.slice(j,j+1)==x[i][0]){res=res+x[i][1]}}}
return(res)

[Hassan_Nadeem]

@Uzo

var x = [['A',1],['B',2],['C',3],['D',4],['E',5],['F',6],['G',7],['H',8],['I',9],['J',1],['K',2],['L',3],['M',4],['N',5],['O',6],['P',7],['Q',8],['R',9],['S',1],['T',2],['U',3],['V',4],['W',5],['X',6],['Y',7],['Z',8]];
var text = p1.toUpperCase();
var res=0;
for(var j=0;j<text.length;++j){
for(var i=0;i<x.length;++i){if(text.slice(j,j+1)==x[i][0]){res=res+x[i][1]}}}
return(res)

Thank you so much!!

[Uzo]

Your welcome… is it working good and fast?