So, I get it: there are restrictions on ways you can use calculated columns. One of these is that calculated columns cannot be used to specify row owners. That’s pretty inconvenient, because in a join, the email address will pretty much always be retrieved from the user table (let’s call that users), whereas the interesting data lives in the other table (let’s call that stuff).
My instinct (which may not be shared by any other human) would be to then create a REF column in the stuff table: otherwise you just have an undifferentiated list of stuff, and you don’t know whose stuff it is! Once you have that, all you need to do is create a LOOKUP column (let’s call it owner) for the email address and you have your row owner.
But, of course, you can’t do that. So instead, I go back to the Google Sheets table and add an owner column with a formula like
=ARRAY_CONSTRAIN( ARRAYFORMULA( VLOOKUP( C2:C,owners!$A$2:$D, 3, false)), COUNTA(A2:A), 1)
That is one ugly-ass formula, but there you are.
One of the very first questions I had when I started using Glide (besides why does my data go missing when I use Glide built-in tables—I’ll get around to asking that one some other time) was about generating unique IDs that could be used as a join field in a REF. Smart and friendly helper @Jeff_Hager assisted me by explaining that I need to set that up as an
ARRAYFORMULA on the spreadsheet side, in an exception to the general rule that it’s preferable to use calculated columns in Glide. Then I ran across the row owner problem, and started wondering how “exceptional” that case really is.
The division of labor here is unintuitive, and the workaround is clumsy and (I am predicting) prone to error. “Use Glide for calculated columns—unless you are actually going to need to use that column in a REF or as a row owner or (insert as-yet undiscovered list of other restrictions here)” may be second-nature at this point to veterans, but it definitely steepens the learning curve for the rest of us.
Am I misunderstanding the problem? If so, how? If not, is there a less awkward solution that already exists?