How to turn an Excel form into an app and collect data

With Open as App, you can mobilize existing Excel spreadsheets or Google Sheets within minutes. We enable automated app creation from Excel or business systems with a cloud-based tool that does not require any programming knowledge from the user.

This way you can build forms and analyze the user submissions in the admin panel in the Open as App web portal. Creating mobile surveys based on Excel has never been easier.

When clicking on the upload button in the app, users can submit the values from the app to a log. The admin of the app sees all transmitted values in the protocol of the app. Submitted data can also be processed into any other system using webhooks.

In this article

• Create a new survey app based on your spreadsheet
• Capture images in the app and save them in the log
• View and download all user submissions
• Evaluate submitted data live in your own app
• Activate the survey functionality in existing apps
• Connect your app to external services and processes by using webhooks

Create a survey app based on your spreadsheet

The data source for your app can be an Excel file with some questions, predefined response possibilities, or freely definable text fields.
In our example, the survey app should contain five questions, three of which can be answered via a selection list (function in Excel: "data validation"), one where users can give a numeric value, and one where the user can add a comment to the last question.

The Excel file for our support survey app looks as follows:

In the first step of the app creation, we select this file as a data source.

1. Start the app creator.
2. Select the Excel file you want to use as your data source for your survey in the "Choose your data source" step and click "next".
3. Select "Survey" as the app template.
   This will automatically set all the settings required for a survey.
   
4. Select the cells you want to operate in your survey app.
   Note that we automatically add captions to the cells, so it is not necessary to add the cells containing the value itself and the cell containing the description of it.
   
   Just look at the preview when selecting the cells from your data source. You can change the automatically set captions in the element's editor. To do so move the mouse over the element and click the pencil icon. (Insight: For our caption proposal, the wizard looks to the left of the selected cell until it finds a text. Otherwise, it looks up until it finds a text. Otherwise, it takes "something".)
   
   After you select the relevant cells (in our example the cells B11 - B15), go to the next step in the design process.
   
5. Click "Next" to go to the "Create" step.
   Select the name for your app and add a description (optional).
6. Click "Create app" to publish your app.

Everything is ready now and your app is waiting to be shared and used with other people. Submitted data can be viewed in the protocol as described in the section View and download all user submissions.

New here? Your app can be opened and used with the "Open as App" app. "Open as App" is free for iOS, Android, and Windows. Download "Open as App" in your store and log in with the data entered during registration. All apps you create are placed in the "My Apps" views. How to download and use Open as App is described in the article "Open as App | Download and Login".

In the article "The 1x1 of App Creation with Open as App" you will find detailed information about the individual steps.

Capture images in the app and save them in the log

You can capture images in the app or select images from your device and upload them to the server when submitting the survey. The images are stored in the Microsoft Azure West Europe Cloud of Open as App. The link to the image is stored in the data log of the survey app. Please note that the image files are not inserted into the source file so that they do not appear in the PDF printout etc.

To capture an image, you need a corresponding input element in your app. Add this element from the selection list next to the preview:

Then define the address - i.e. a cell from the data source with which this element communicates. Please note that the created image will not be inserted into the Excel cell.

This gives you a photo upload element in your app. This element is not yet displayed in the preview and is not supported by the Web Client. Always check the changes on your smartphone. The photo upload element will look like this:

When uploading the data from the app, the image file is saved online and the link to this file is saved in the log. If you create an app based on this protocol (as described in the section "Evaluate submitted data live in your own app, you can see the images there directly. Make sure that the column type of the column in which the links to the images are located is defined as "image".

View and download all user submissions

You can see the data users have uploaded in your app in the change protocol.

To view the change protocol, proceed as follows:

1. Open the app in the web portal.
2. Go to "Manage Data".
3. Select "Show change protocol".

If there is no "Show change protocol" visible, please make sure the "Allow client changes" functionality is activated.

Download change protocol

The change protocol can also be downloaded as a complete excel file and handled further. You can import this file into one of your systems, for example.

1. Open the app in the web portal.
2. Go to "Manage Data".
3. Select "Show change protocol".
4. Below the table, with all the data sets you will find the "download" button to download the whole change protocol as a .xlsx file.

Create an app based on data from the survey

In the previous section, you learned how to download the server-side log as a .xlsx file. Each time you click the download button, you will be redirected to a URL where the file is generated on-demand.

You can use this URL as a data source for creating your evaluation app! Every time you check the evaluation app for updates, a new copy of the log is loaded into your app. List Analysis Charts (if activated in your app) will be updated as well.

To create an app based on this data, proceed as follows:

1. Open the app in the web portal.
2. Go to "Manage Data".
3. Select "Show change protocol".
4. Click the "Create an app based on this data" button to start the app creation process with the server-side log.

Alternative way:

1. Get the link to the log .xlsx file:

1. Open the app in the web portal.
2. Go to "Manage Data".
3. Select "Show change protocol".
4. Right-click the "Download" button under the table with all the records.
5. Click on "Copy address of the link" to copy the link to the clipboard.

2. Create an app based on a URL:

1. Start the app creator.
2. In the "Select Data Source" step, click Shared URL. (The button is located in the list of possible data sources on the right side.)
3. Paste the link into the pop-up window. (Right-click and insert the URL in the pop-up window.)
4. You will automatically be redirected to the next step "Confirm data" after a few seconds. Click "Next" to continue with the app creation process as usual. (If necessary, you must select the primary worksheet here.)
5. Select "List" as the app template.
6. Define the visible columns for list view and detail view.
7. In the "Design" step, you can also edit your app and, for example, activate the Dynamic Dashboard via the list editor. You will find detailed information on this function in the article "Auto-generated dashboard in lists".
8. Click "Next" to go to the "Create" step.
9. Select a name for your app and add a description (optional).
10. Click "Create app" to complete the app creation process and publish your app. (The app is first released in the prototype mode, so only you as the creator can see it.)

Activate the sending calculations functionality in existing apps

To activate the sending calculations functionality for existing apps, proceed as follows:

1. Open the app in the web portal.
2. Go to "Manage Data".
3. Select "Allow client changes".
4. Activate "Allow sending calculations" by switching the toggle.
5. Save changes.
6. Update your app.

Connect your app to external services and processes by using webhooks

Instead of uploading those information to the Open as App log, you can connect external web services and let the app send the submitted data to this source. From there you can trigger any process you like and automate your workflows by connecting various tools, steps and processes.

1. Open your app in the web portal.
2. Go to "Advanced".
3. Select "Connect Web Service".
4. Put the server / webhook url in the input field.
5. Save changes.
6. Update your app.

To get the Server URL / Webhook and process the collected data using Open as App, you can use tools and Platforms like integromat.com or Power Automate. Of course, you can also write your own web service within your infrastructure.

It will definitely take a few hours to get into this if you're just staring… Also, learning how the flows work etc. will take some time... BUT we think this can get really powerful in terms of process and workflow automation.

Connecting Open as App with Integromat

To get a webhook address, use the “Webhook”-Element in Integromat. Copy the address from there and paste it in the webhook input field of your app as described above. That’s it.

Connecting Open as App with Microsoft Power Automate

Connecting your Open as App with Microsoft Power Automate is slightly more difficult compared to Integromat, but we are looking for possibilities how this can be improved. For now, please follow the steps below to process data collected using your app within the Power Automate environment.

1. Start from blank with an automated flow
   
   
2. Close the popup to get to the design area
   
   
3. Chose the "When an HTTP request is received" trigger from the list of built-in triggers
   
   
4. Click the "Use sample payload to generate schema" link
   
   
5. Paste an example request from your webhook.
   1. You can get an example request from the OAA portal (Advanced -> Connect Web Service)
      ⚠ note that you have to replace exemplary fields in the JSON with the actual fields from your app
      
6. Have fun power automating your Open as App app!