Range returning more than one value per row on arrayformula

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?

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())

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"


Worked like a charm @Todd57. Thank you so much

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.

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

@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.


1 Like

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

1 Like

@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