Open as App can use a fair share of Excel logic, if you give it a chance.
Requirements for a spreadsheet are similar to those of an address list for a form mailing.
- Start your list in cell A1.
Empty lines are a no go. As they count as input your app will show a blank column. You can add decorative headings or logos later on when editing your app. Leave them out in your Spreadsheet.
A list needs to start with the column headings and have no side headings on the left hand side - unless it is a pivot chart.
For a calculation app input cells are either values or results.
- Cells outside the list space.
Don't interrupt the flow of your list by putting fields outside the list space.
- Clear information only.
Color highlights or crossed-out data will not be relevant for the data analysis that leads to your app. However, your highlights already show you what is most important for your app. You will find out, how to "Design" your App in this section: https://support.openasapp.net/hc/en-us/sections/202020989
- Avoid merged cells. One cell - one input. Merged cells cannot be analyzed and should therefore be taken out in advance. This is in particular relevant for labels in address lists or similar.
Don't put multiple information in one cell like here (salutation and last name).
The list above needs to be changed before going into the app creator.
The list below is perfect for Open as App.
- Format the cells in the Excel file. You want the values in a cell to be displayed with a currency symbol? Format your Spreadsheet accordingly. The better the source file, the easier and faster you will convert your Excel file into an app.
- No Pivots. At least not when you use Google Sheets as source data for your app. We are working on it, but for now the wizard is not able to understand and process this feature. You will see the error message "Value cannot be 0" when trying to upload a Google Sheet with a pivot table. In this situation, you need to remove the pivot table from your spreadsheet, and take a "pivot-free" version of the file for the app creation. Excel spreadsheets do not cause this error.
- No Macro. No VBA. For the use of macros we would need a VBA scripting engine in our app. This would mean that Open as App could no longer be available for iOS devices.
- Structured references. Regular A1 or R1C1 references (e.g. "A1:A4") work as usual in Open as App. However, there is an alternate form of referencing cells called "Structured References". These are of the format "TableName[ColumnName]" or "@[ColumnName]". Instead of using table and column names ("=SUMME(DeptSales[Sales Amount])") please user explicit cell references ("=SUMME(C2:C7)").
- External references. If a formula in a cell refers to a cell outside of the file, we cannot translate the formula. For example, if a cell refers to a column on a different sheet within Excel, that other sheet is not part of the Excel that is used as the apps' source file, and therefore cannot be referenced properly. Please update the cell in Excel to only reference cells within the file.
- Array Formulas. Array formulas are not currently supported, and have limited support in Microsoft Excel.
- Specific formulas. There are a handful of formulas specific to Google Sheets that will not be transferred to Excel. This list of formulas might not be complete: