I’m guessing this isn’t possible ‘on purpose’ because it makes things less secure, but…
I have a Manager Sheet and an Employee Sheet.
Employee submits a form (row owner is Employee)
Manager views the submissions in a copied sheet (using an arrayformula; row owner is Manager)
Manager edits the submission on their sheet and checks off the “Approved” checkbox
Action — increment Approved by 1 (to activate the switch); set column in Employee sheet to increment that approved column too.
2nd part of the action doesn’t fire though (likely because Manager cannot access Employee rows). This results in the employee not being able to tell if their form is approved.
Should I be doing this differently? Should I use an Array to put the manager and employee in the same Row Owner column? Any other ideas?
1 Like
Is this a private app by any chance? How many managers are you dealing with?
No it’s not private. Just public pro, so I’m using row owners (not roles).
I’m currently only working with one manager, but the final product will need to support 10+ managers and not all of them would “belong” to each employee. So I didn’t think I could use the combo array of Employee, Manager since that combo isn’t going to be the same for every employee row.
If row owners supported computed columns, I think I could create a way to make it work dynamically, but that’s currently not possible.
If it was Private, I was thinking roles, but instead I’m thinking an array column for the employee email and the manager email. You would have to assign the employee to a manager anyway, correct? Probably in the user profile sheet or employee sheet?
I’m thinking an array column in the user profile or employee sheet, and then passing those same values in the form to another array column in the form response sheet.
I believe row owners supports array columns, but they can only be set up in Google sheets, so it will not work with Glide tables.
Hmm I’ll give this a try tomorrow. Thanks for the advice!
1 Like
@kyleheney very interested to hear how this goes. I can imagine that this would be a fairly common scenario, so will be good to know if there is robust way to deal with it.
I have another out-of-the-box idea to try tomorrow that should work and may be a cool way to deal with this. Will definitely post any findings!
2 Likes
Okay I’ve come up with a way to make this work.
-
Create a 3rd sheet called “Unsecure” (I did this as a new Google Sheet, but I think you could accomplish this with a Glide Table using an Action to add a row, if you want go that route)
-
Create an arrayformula in this new sheet to bring in the RowID from the Employee sheet (I did something similar for the Manager sheet, but in that sheet, I pulled in all columns of data and protected it with Row Owners on the Manager Email column). For this new sheet, I only want the RowID column.
-
Create an “Approved” column in the new 3rd sheet.
-
Create a relation between the Manager sheet and the Unsecure sheet (this is how the Unsecure sheet can be set from the Action).
-
Create a relation between the Employee sheet and the Unsecure sheet (this will update the Employee sheet with the Approval status).
-
In the Approval Action, Increment the Approval column by 1; set the approval column in the Unsecure sheet to be the Approval column.
-
Perform some lookups to get the info you want to display to the users.
This works and I think it’s still a secure method because the 3rd “Unsecure” sheet is only pulling in the RowID of the Employee sheet (no other identifying information). This 3rd sheet has no Row Owners associated with it, so it’s accessible by all users.
I’d appreciate @david’s input on this method to see if it would be an approved secure approach to handling the situation.
3 Likes
Very nice!
I can imagine there could be lots of use cases for this approach (I can already think or 2 or 3 where I would use it).
Fingers crossed that @david doesn’t rain on the parade 
1 Like