Filter to use Per User email and "contains" operator

I’m working on solution for this thread (Help designing spredsheet/Glide - Curriculum with many user) and building it into into this app ( I think I came up with a really simple solution to use one list of tasks, but display it and filter Finished and Not Finished tasks for multiple users. My idea is to use a Filter with the Contains operator. The only thing I’m missing is the option to obtain the user email for the filter. I don’t think the Per User Data would work in this case because I’m building a column cell with a concatenated list of all emails that have completed the task. That’s why I want to use the new filter option with the “contains” operator.

I’d also like to request that switches and checkboxes can be marked as required.

1 Like

This is really clever!

I was working on the opposite idea, which is to concatenate all the finished modules into one cell (one per user). But I think your idea may be better. To be clear…

  1. You are still making each user, submit a Glide Form for each module completed?

What I’m doing now is based upon your previous idea…

a) the Form sends the module # and the user’s email to a Sheet.

b) This is how the Form submission looks on my Sheet

c) Then I concatenate all the occurrences of an email and the Module # into one cell

  1. So, in this new idea, are you still sending the user’s email and task/module # to a Sheet, then using index/matching and concatenate to add that user’s email to the Task Complete of the specific task #?

  2. Then in Glide, you are filtering out the Tasks who contain the user’s email? So, when a user looks at all of the Tasks in Glide they will only see those that do not contain their email, correct?

  3. Curious, in your spreadsheet how are you displaying who’s completed what Tasks (if you are doing this at all)? Meaning, how you see visualize the completed task for user - and/or - visually see which users have completed a given task? Since all the emails are contained in a single cell, it seems it would be diffiuclt to visualize.

@Jeff_Hager thank you SO much for your work on this!

@Jeff_Hager Yes having the logged in users email as some kind of global variable that you could refer to would be very handy to have.


@Jeff_Hager I just added this feature request based on these ideas.


To answer your questions:

  1. Yes, I’m still making the user submit a form for each module. I added extra data on the form to show which module/task they are on along with a checkbox that they have to check to confirm that they completed it. Feels a little more formal than an empty form page.
  2. Yes, I still have a form response sheet with email and task, along with date and a true/false value if they clicked the box. I basically copied the generic task list that @TSam had come up with. I am not using index/match. In the tasks/modules sheet I added a column that uses a query along with concatenate to build the list of emails for each task. Only emails, where they selected the checkbox will show.
  3. I’m still stuck on the filtering of completed tasks. That’s why I created the feature request above. I think I’d have it solved if that option was available. But, yes, the plan is to filter out tasks that are not completed.
  4. Just visit and click on the Tasks button. I want to keep the app public, but in theory, I would be using Public with Email or Whitelist to force the user to log in. I’ve attempted to fake this out with a list that first shows 2 emails that are available. The app can be copied as a template and there is a link to my sheet within the app. This should answer most of your questions.

I tried using the index/matching method that @tsam came up with, but I didn’t see an advantage to using each email as a heading. I was able to dynamically load the headers from another sheet of emails using transpose and unique, but it’s a bit vulnerable if the email sheet gets resorted, or emails are removed. Also headers changing that much would be a problem in glide and cause the data to become corrupted. the true/false values would not match up with the original email header. The only other alternative that I can think of would be to join a list of emails and all the tasks into one sheet, but like you said earlier, you have 60 users and 120 tasks, so that makes for a very large sheet. Even then it remains vulnerable if the users or tasks change. I’m hoping that glide will come up with a global email value like @George_B suggested.

thanks very much!

I am using query and textjoin (similar to yours to add all the emails of a complete task into one cell) …

=textjoin( " , ", true , query ( find all emails that have completed a given task )

All we need now is the Glide Filter function “contatins”.

1 Like

FYI, I could have sworn that I had allowed the app to be copied as a template. I just noticed that wasn’t the case, but now I’ve allowed the app to be copied.

Good new @mptpro. I think this might be doable now. I can’t fully duplicate it in my concepts app since I have it set to public, but take a look at it again. Make a copy and set to public with email. Then you can set the filter for the completed and not completed lists to signed in and not signed in user. I think that will give you want you want…at least with with way I have it set up.

Thanks Jeff! It’s sooooo close, however, my cell that indicates completion has many email addresses in it (all the people who have completed the task). So, the “is” filter doesn’t work. If it was “CONTAINS signed-in user” it would most likely work.

Thanks again for following-up with this!

No, you can’t use that method like we tried before where we built a delimited list of completed emails. That column can go away. If you look at my example, I added a relation from the Task Details to the Task Response sheet. I then created a Lookup column to pull in all of the emails that have completed that task. The lookup column is what I would use with the signed-in user filter on the 2 list relations.