Restrictions on Calculated Columns

Ask for Help
1

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 :fireworks: :sparkler: 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?

Thanks. </vent>

2

For this case, I would add the email address (Owner) at the time the row is created. ie. as part of the Add Row/Add Form action. This is where “Special Values” come in handy.

As to why computed columns cannot be used as row owners, that’s because computed columns are only applied after data has been downloaded to a users device. And at that point, it would be too late to apply row owners.

Glide are currently beta testing a different architecture model where computations are applied server side instead of client side. If this goes ahead, it could potentially open up the possibility to have computed columns as row owners. (I don’t know if that’s part of the plan - I just know that in theory it could be possible).

4 Likes
3

Ditto on what @Darren_Murphy said. You can just add the email of the signed in user at the time the row is created. That will allow you to use Row Owners. There should be very very few circumstances where you actually need to use formulas in a google sheet. Not saying you won’t, but in most cases you can do the same thing with glide computed columns.

4 Likes
4

@escott - just to put an exclamation mark on the above point from Jeff…
I have a very large complex app that has a Google Spreadsheet data source. The spreadsheet contains just over 60 sheets and well over 1000 columns. In that entire spreadsheet I have a total of 4 columns that use sheet formulas. And 3 of those 4 are not required. They were added before I knew any better, and I just haven’t gotten around to getting rid of them.

3 Likes
5

Thanks (to @Jeff_Hager as well) for the responses. Your input helps me understand the source of the Sheets vs. Glide dichotomy more clearly as a server/client dichotomy, at least in some respects.

I’m new to Glide (but not to no-code), and so come to these concepts with fresh eyes. As feedback (which may or may not be useful for your product team), I find the Special Values thing unsatisfying. It puts behaviors that properly belong in the “controller” into the “view” instead, which feels unclean. Certainly, it creates complications; hence the need to point out in the doc that

If you haven’t enabled editing or adding items and a user changes an editable component, Glide won’t be able to populate your sheet with the date & time or the user’s email.

When it comes to fundamental security tokens like row owners, that kind of “this may not work in certain situations” language makes me nervous. :nerd_face:

I appreciate the prompt and intelligent responses! Thanks again for your help. Now I have some new stuff to play with. :nerd_face:

Scott

2 Likes
6

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