Creating an App with User-Specific Data Entry

In this tutorial we create an app in which all persons enter data in a list, but each user can only view and edit his or her data records.

For this it is necessary that:

• we can see which user has opened the app and pass this information on to the Excel file. For this we set the app definition UserEmailCell.
• the new row can be uniquely assigned to a user when the data records are added. For this we add an additional parameter in the detail view of the list (DefaultValueCell).
• the list is correctly filtered by user so that everyone can only see their own records. For this purpose we use the definition IsVisibleColumn which we set in our list element.

UserEmailCell

When a user opens the app, we receive the e-mail address with which he/she registered with Open as App as information. For our use case it is therefore necessary that the user has an Open as App account and is logged in.

The email address of the person who opened the app can be passed to the Excel file we use as the data source using the app definition useremailcell=address.

To add the definition, proceed as follows:

• Open the app in the web portal.
• Navigate to the "Advanced" view in the left menu.
• Select "XML Configuration".
• Add the definition of useremailcell=Config!B1; to the "App Definitions" at the top of the page.
• In our example, the user's email address is inserted into cell B1 on the "Config" worksheet, so the definition of useremailcell=Config!B1;
• Save your changes.

If this definition is specified, the user's e-mail address is written to the specified cell as soon as the app is started.

Based on this information we can vary data in the Excel file, or pass commands to the app. For example, hide elements dynamically in the app (see: https://support.openasapp.net/hc/en-us/articles/360000448324). These can be ActionButtons by means of which the user can jump to certain pages in the app. If a user is not shown a certain button (because the Excel logic pretends that this user is not authorized) he cannot open this page and thus not see the contents on this page. The article contains sample files and explanations on how to dynamically show/hide elements in the app.

In the use case in this tutorial, we want to hide the individual rows in the list and not the list element itself. To do this, we must first ensure that each line that the user creates can be clearly assigned to him. The "DefaultValueCell" is used here.

DefaultValueCell

In the "UserEmailCell" section, we have managed to have the e-mail address of the user who opens the app entered in a specific cell of Excel. Now the value from this cell is to be entered automatically in each row that this user adds. To do this, we define an additional parameter for a column in the detail view.

Note: Since we want to collect data, this function must first be activated under "Manage data" > "Write back data". Further information can be found in the article: "Editing lists via the app". In addition, we must set the column into which the DefaultValueCell is set to "ReadOnly". For information on how to exclude individual columns from editing, see the same article in the section "Excluding individual columns from editing".

To add the additional parameter, proceed as follows:

• Open the app in the web portal.
• Open the editor for the list. (Preview " Pencil icon on the list element)
• Switch to the "Advanced" tab.
• Enter the address of the cell whose value is to be written to the detail view column as parameter for the desired column.
  In our example, the value from cell Config!B1 should be entered in the first column of our list. We therefore search for the definitions for the first column of the detail view columns and enter the address of the cell as parameter at the 4th position. For example: detailcolumns=3:text::,4:currency::,1:email:readonly:Config!B1;
• Confirm the changes by clicking on the check mark in the lower area of the editor.

IsVisibleColumn

In the "UserEmailCell" section, we have managed to have the e-mail address of the user who opens the app entered in a specific cell of Excel. Now the value from this cell is to be entered automatically in each row that this user adds. To do this, we define an additional parameter for a column in the detail view.

Note: Since we want to collect data, this function must first be activated under "Manage data" > "Allow client changes". Further information can be found in the article: "Editing lists via the app". In addition, we must set the column into which the DefaultValueCell is set to "ReadOnly". For information on how to exclude individual columns from editing, see the same article in the section "Excluding individual columns from editing".

To add the additional parameter, proceed as follows:

• Open the app in the web portal.
• Open the editor for the list. (Preview > Pencil icon on the list element)
• Switch to the "Advanced" tab.
• Enter the address of the cell whose value is to be written to the detail view column as parameter for the desired column.
  In our example, the value from cell Config!B1 should be entered in the first column of our list. We therefore search for the definitions for the first column of the detail view columns and enter the address of the cell as parameter at the 4th position. For example: detailcolumns=3:text::,4:currency::,1:email:readonly:Config!B1;
• Confirm the changes by clicking on the check mark in the lower area of the editor.

In the "Advanced" tab of the list editor, the "Visibility (column)" is then specified. The definition is 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 B, the definition isvisiblecolumn=1 must be specified in the XML.

Please make sure that the values TRUE or FALSE are Boolean variables in your Excel and not string values. Therefore, do not use quotation marks in formulas. A good indication whether you have programmed Excel correctly: Boolean variables are automatically centered in Excel.

Correct: =IF(A1=1;TRUE;FALSE)
Wrong: =IF(A1=1;"TRUE";"FALSE")

(Since the formulas must be continued when adding new lines, this feature cannot currently be used with Google Sheets, as the formulas are not continued when adding new lines.