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

**URL:** <https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918>\
**Category:** Ask for Help\
**Created:** [February 14, 2023, 1:19am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918 "2023-02-14T01:19:05Z")\
**Posts on this page:** 14\
**Page:** 1

<div class="post-metadata">

**Author:** ![Hassan\_Nadeem](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hassan_nadeem/32/72956_2.png) [@Hassan\_Nadeem](https://community.glideapps.com/u/Hassan_Nadeem)\
**Post date:** [February 14, 2023, 1:19am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/1 "2023-02-14T01:19:05Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 1:26am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/2 "2023-02-14T01:26:29Z")

</div>

Use the unique formula to eliminate repeating letters

---

<div class="post-metadata">

**Author:** ![Hassan\_Nadeem](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hassan_nadeem/32/72956_2.png) [@Hassan\_Nadeem](https://community.glideapps.com/u/Hassan_Nadeem)\
**Post date:** [February 14, 2023, 1:30am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/3 "2023-02-14T01:30:30Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 1:31am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/4 "2023-02-14T01:31:41Z")

</div>

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

 ![Screen Shot 2023-02-13 at 8.39.31 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/0/2/02494beddc33570844e9bd8e59f90f6126b3202a.png)

---

<div class="post-metadata">

**Author:** ![Hassan\_Nadeem](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hassan_nadeem/32/72956_2.png) [@Hassan\_Nadeem](https://community.glideapps.com/u/Hassan_Nadeem)\
**Post date:** [February 14, 2023, 1:45am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/5 "2023-02-14T01:45:00Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 1:46am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/6 "2023-02-14T01:46:39Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Hassan\_Nadeem](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hassan_nadeem/32/72956_2.png) [@Hassan\_Nadeem](https://community.glideapps.com/u/Hassan_Nadeem)\
**Post date:** [February 14, 2023, 1:50am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/7 "2023-02-14T01:50:27Z")

</div>

How do I do a vertical array?

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 1:52am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/8 "2023-02-14T01:52:57Z")

</div>

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

 ![Screen Shot 2023-02-13 at 8.56.15 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/a/2/a222e3afe12dc2c7763a6e074f1f21f4d099144f.jpeg)

 ![Screen Shot 2023-02-13 at 9.00.12 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/1/71c22889551f2e6ab67cfe54171f073f8f28ea54.jpeg)

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 3:28am UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/9 "2023-02-14T03:28:11Z")

</div>

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

 ![Screen Shot 2023-02-13 at 10.26.38 PM](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/7/4/7426566839f9e5a6b95cb551f065204fcc46c9fe.jpeg)

No relations, only ONE column!

---

<div class="post-metadata">

**Author:** ![Hassan\_Nadeem](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hassan_nadeem/32/72956_2.png) [@Hassan\_Nadeem](https://community.glideapps.com/u/Hassan_Nadeem)\
**Post date:** [February 14, 2023, 1:52pm UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/10 "2023-02-14T13:52:07Z")

</div>

So cool. Can you please share this javascript here?

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 5:06pm UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/11 "2023-02-14T17:06:49Z")

</div>

sure…

```auto
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)

```

---

<div class="post-metadata">

**Author:** ![Hassan\_Nadeem](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/hassan_nadeem/32/72956_2.png) [@Hassan\_Nadeem](https://community.glideapps.com/u/Hassan_Nadeem)\
**Post date:** [February 14, 2023, 9:39pm UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/12 "2023-02-14T21:39:56Z")

</div>

> [@Uzo](#):
>
> ```auto
> 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!!

---

<div class="post-metadata">

**Author:** ![Uzo](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/uzo/32/22639_2.png) [@Uzo](https://community.glideapps.com/u/Uzo)\
**Post date:** [February 14, 2023, 10:17pm UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/13 "2023-02-14T22:17:35Z")

</div>

Your welcome… is it working good and fast?

---

<div class="post-metadata">

**Author:** ![system](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/system/32/53398_2.png) [@system](https://community.glideapps.com/u/system)\
**Post date:** [February 15, 2023, 10:18pm UTC](https://community.glideapps.com/t/need-help-with-writing-a-complicated-google-sheet-formula-on-glide-tables/57918/14 "2023-02-15T22:18:20Z")

</div>

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.