Nowadays, User roles in an organization always change depending on the project they are working on. So, there will be a situation where users can’t access certain functions or secure pages based on their changing role.
In this tutorial, we will create an app that will allow certain users to access data based on their roles.
The user should have an Open as App account and the ID should be mentioned in the User list (which is explained in the later steps)
DATA SOURCE PREPARATION
The app creation already starts in your Excel spreadsheet. The cleaner the columns and cells are formatted there, the easier you will turn this file into an app.
- The first step is to create a list of users with their email IDs and their roles as highlighted in the above picture. The role cells can be made a “drop-down list” for easy selection (Hint: Data -> Data Validation).
- The second step is to create a User ID cell and role cell which will be displayed in the app.
- And the final step is to assign rights to the users based on their roles as mentioned. This can be done using IF function.
SELECT DATA SOURCE
In the "Choose your data source" step, Select the file from your PC by clicking on the ‘Choose File’ button.
After uploading the excel file in the platform, you will be automatically redirected to the next step in the app creation process.
- Select (optional) a theme and click "Next".
- In the Design Page, change the input type of User ID cell to ‘output’ and click “Next”.
The next step is to add ‘useremailcell’ definition in the app definitions field.
To add the definition, proceed as follows:
- Navigate to the "Advanced" view on the left menu.
- Select "XML Configuration".
- Add the definition of useremailcell=User!B1; to the "App Definitions" at the top of the page.
Syntax: useremailcell=address, in this case, the address is User!B1 – ‘User’ Sheet in the excel and B1 cell
- Click “Next” and create the app.
Note: If this definition is specified, the user's e-mail address is written to the specified cell as soon as the app is started.
USE CASE – ACCESSING SECURE PAGES WITH ACTION BUTTONS
Based on the logged-in user’s rights, one can jump to certain authorized pages in the app by means of ActionButtons. If a user is not included in the UserList (mentioned in the above steps) they don’t have (because the Excel logic pretends that this user is not authorized) access to see the button and thus not see the contents on this page. For instance,
The user mentioned in the UserList with the rights is shown a Login button, whereas other users with no rights are shown a message. So that certain functions are disabled for the users based on their roles, which is mentioned in the UserList. This can be done by adding a definition “isvisiblecell” in the login action button in the OaA wizard.
Syntax: isvisiblecell = x, the x must be changed by the position of the cell with TRUE or FALSE values in it. In this case, it is User!C7, which returns TRUE when the role is ‘Admin’ or ‘Owner’, and FALSE if it is not.
USE CASE – HIDING ELEMENTS IN THE LIST
Another use case based on the logged-in user is hiding elements in the ‘list’ based on the user using the app (logged in user). For example, consider a list of marks obtained by the user, which will typically look in the OaA app as the below picture
The administrator of the app wants the users to view only their marks instead of the list of all the users. This can be done by just adding a column in the list sheet in the excel.
This column is matching the UserID in the User sheet and the Mark (list) sheet and returning a TRUE or FALSE value. After creating the column in the excel and creating a list page in the OaA app
Click on the list editor, under the “Advanced” tab, “isvisiblecolumn=3” should be specified.
Syntax: isvisiblecolumn=x, the x must be changed by the position of the column. If the list starts in A1 and the column with the TRUE / FALSE values is in column C, the definition isvisiblecolumn=3
And save the changes by clicking on the “Save” button. By doing this only the logged-in user is able to see his or her data on the list.
These cases will be really helpful when the administrator of the app wants to manage user access to only those applications they are required to use and to control what options and functionality within an application are available to different users.