Range returning more than one value per row on arrayformula

Ask for Help
1

Not sure if the title is right but I am having the following problem.

I am building a rock, paper, scissors game in it there are essentially challenges that have 2 states.
Once created a challenge goes into the state of “waiting for player 2”
Once player 2 plays, theres a state of “winner is this” or “tie”

This is my formula. The problem I am having is in concatenate.
=Arrayformula(IF(LEN($A2:$A) = 0, “”,if(M2:M = “Finished”,if(D2:D=J2:J, “It’s a tie”, if(or(and(D2:D=“Rock”, J2:J=“Scissors”), and(D2:D=“Scissors”, J2:J=“Paper”), and(D3:D=“Paper”, J2:J=“Rock”)), “Player 1 won”, “Player 2 won”)),CONCATENATE(“Waiting for “,H2:H,” to play”)))
)

CONCATENATE(“Waiting for “,H2:H,” to play”) is returning the name all of player 2 on the list rather than the one for that row only.
I am not great with spreadsheets but all the others seem to be working in a “per row” basis.

Does anyone know why it isn’t working?

2

It’s hard see if what I’m thinking would work without the spreadsheet but…
You have to get rid of the H2:H and replace it with something like this. I did not test this!

INDIRECT("H" & row())
3

Instead of using Concatenate, which doesn’t work with Arrayformula, you should make this part of the formula: CONCATENATE(“Waiting for “,H2:H,” to play” into this "Waiting for " & H2:H & " to play"

2 Likes
4

Worked like a charm @Todd57. Thank you so much

5

You’re very welcome. I ran into the same thing the other day when I was trying to create a column of concatenated values. Thankfully, the new Template column in Glide works a lot better for my needs. I found that I couldn’t use Arrayformula in sheets where new rows needed to be added from within Glide. If I added a new row, it would end up way down at the bottom past the first 1000 rows, and the Arrayformula wouldn’t perform its task on the new data.

6

I solved that by deleting all rows and making sure to add =if(len($A2:A = 0), “”, Place your formula here) @Todd57
It seems to do the trick.

Still need to learn what the template column in glide does. Anywhere I can find documentation for this?

1 Like
7

@Todd57 Just a note when using array formulas, you have to delete all empty rows from your sheet. Array formulas should still function whenever Glide adds a new row to the sheet.

@Sandro_Brito

1 Like
8

This simplest of solutions is always the best. Nice one @Todd57!

1 Like
9

@Jeff_Hager & @Sandro_Brito, thank you both. I never even thought about doing that, and Glide is still relatively new to find that level of questioning via Google search. I’ll keep this in mind when I come across another instance where ArrayFormula is needed.

For now, the Template Column is serving my needs just fine. It was a great addition to Glide. However, if the Choice component gave us the ability to store a different value than what is displayed in the pick list, I may not have even needed the Template Column. I’m using the Template Column to generate a unique identifier for each row in my sheets (kind of like a composite key), which I use to populate the choice component in other forms.

1 Like