Importing table data
We have made some changes to Calcapp since this video was first published. As a result, some screens may look slightly different. We will update the video in the future.
Most apps created with Calcapp don’t just consist of calculations, they also need data to work. That data may be parts of your product catalog or your price list. More often than not, all this data is maintained in spreadsheets and needs to make its way to Calcapp Creator, our app designer.
Calcapp requires that all data is made part of formulas. In this tutorial, we use an app we have developed to convert data maintained in a Google Sheets spreadsheet to formulas using the CHOOSE function. We recommend that you continue maintaining your tabular data in spreadsheets and convert your columns to formulas every time you make an update.
The XLOOKUP function is also supported, which is far more powerful than CHOOSE. We provide an app that creates XLOOKUP formulas too.
The video above includes closed captioning explaining the various steps. A more detailed discussion appears below. Run the conversion app here.
Inspecting the spreadsheet (00:02)
In this video, we create an app from a simple product catalog stored in a Google Sheets spreadsheet (click the link to open it if you want to follow along). Importing data from Microsoft Excel, Apple Numbers, LibreOffice or any other popular spreadsheet is very similar.
There are three columns: Product, Price and Color.
Creating the form screen (00:08)
Start Calcapp Creator and create a new app. We’ll create three form fields, each one holding a value for a different column. The color field is a text field and the price field is a currency field. The product field should be a drop-down field, enabling users to select the product from a menu.
Copying the product names from the spreadsheet (00:51)
Switch back to the spreadsheet and select all product names and copy them to the clipboard by pressing Control+C (PC) or ⌘+C (Mac).
Pasting the product names into the app (00:59)
Back in Calcapp Creator, press Edit as text to edit the drop-down values of the product field as text. That enables you to easily paste the product names from the spreadsheet by pressing Control+V (PC) or ⌘+V (Mac).
Select the first product name in the drop-down field in the app designer to make that particular product pre-selected when the app starts.
Copying the prices from the spreadsheet (01:11)
Select all prices in the spreadsheet and copy them to the clipboard by pressing Control+C (PC) or ⌘+C (Mac).
Converting the prices to a formula using an app (01:23)
To convert the price data to a CHOOSE formula, use our app. Paste the data into the Column data field (press Control+V on a PC or ⌘+V on a Mac).
To ensure that the generated CHOOSE formulas use the right drop-down field, enter its name under Field name. In this case, type “Product”.
You will find that two CHOOSE formulas are generated for you, one suitable for use with number fields and one for use with text fields. The CHOOSE formula for text fields use all values you have pasted as-is, enclosed in quotation marks.
The formula for number fields tries to interpret your values as numbers. If a value can’t be interpreted as a number, the formula BLANK()BLANK() is used instead of the value (this function simply returns a blank value).
As prices are numbers, you need to copy the formula generated for number fields to the clipboard.
Pasting the prices into the app (01:45)
Switch back to Calcapp Creator, make sure that the Price field is selected and paste the formula you copied into the formula field.
Copying the colors from the spreadsheet (01:58)
Select all colors in the spreadsheet and copy them to the clipboard.
Converting the colors to a formula using the app (02:07)
Go back to the conversion app and remove the old data. (Press the Column data field and press Control+A on a PC or ⌘+A on a Mac followed by pressing the Backspace key.)
Paste the new data. Colors are text strings, meaning that you need to copy the CHOOSE formula for text fields.
Pasting the colors into the app (02:18)
Switch back to Calcapp Creator, make sure that the Color field is selected and paste the formula you copied into the formula field.
Previewing the app (02:25)
That’s all there is to it! Press the play button to bring up a preview of the app. Try selecting a few different products. You’ll notice that the price and color shown in the app match the spreadsheet.