Checking two fields from a Google Sheet for Authentication

Ask for Help
1

Hi all,

I am attempting to put in an authentication field into my app so that only users who are active members can access it.
I have created a google sheet which will be automatically updated once a week with a list of active members from our database. It has 3 columns, name, Membership number and Year of Birth.
In the user’s spreadsheet of my app, I have the following relevant columns:

  • ID Number (User Specific Number)
  • Year of Birth (User Specific Number)
  • ID Number Relation (Relation looking at Google Sheet - ID Number)
  • Current Member (If-Then-Else Boolean that checks if the relation field is empty or not)

This method marks the boolean true if the ID number entered by the user matches any ID number in the Google Sheet and gives them access to the rest of the app.

What I am attempting to add is a second check for the member’s birth year. The user will be asked to enter both, their ID number and Birth Year. I would like the Current Member Boolean to be true only if the ID and Year entered match the same Row in the google sheet, but if I add a second relation column in the user data, it will pick up multiple relations as many people share the same birth year.

How can I get this boolean to only =TRUE if both the ID and Year of Birth match the same entry in the Google Sheet?

2

Building your own authentication is not very secure and could be bypassed.

Use a Query column instead of a relation and check if the query is empty or not.

But, if you already have a user table with this information and those users sign into the app, can’t you create a relation column in the user table linking the user id in the user table to the user id in the active member table, and then set visibility conditions on tabs and components that check if the relation is not empty?

3

Thanks for that Jeff, I’ll give the query column a try.

The google sheet will be updated regularly as members join and leave. I’m trying to design it so access is automatically checked against an up to date list rather than requiring human intervention to add and remove users from the app itself. The authentication is being added by me so there is some semblance of checking (I realise it is not secure, but better than nothing). If you have any other suggestions for how I might set up this automated access allowing/disabling then I would love to hear it.

2 Likes
4

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