ODK XLSX Converter Reference

Excel Worksheets

A workbook is composed of one or more worksheets. XLSX Converter expects the worksheets within a workbook to use the following nomenclature.

Worksheet Reference Table
Worksheet Required?
Description
survey Required
Contains the content and control flow of the
survey. It contains the full list of questions
and determines the order in which they will be
asked. This worksheet can be broken into
different section worksheets for ease of use.
settings Required
Includes such details as the form name and id,
as well as the default language.
properties Optional
Defines the key-value properties that can
specify the detail, list view, and other
properties to use with this table. This sheet
should only be specified in forms whose
form_id matches their table_id.
calculates Optional
Contains the JavaScript formulas that can be
used in other worksheets
choices Optional
Contains the sets of choices for multiple
choice questions. Each row represents a
response. Choices with the same
choice_list_name are considered to be
part of the same choice set. Choice sets can
be used multiple times throughout a survey
(such as a yes/no choice set).
model Optional
Defines the table definition in cases where
multiple forms edit the same data table
queries Optional
Gets data from an external source that can
be used as the choice set for multiple
choice or linked_table questions much like
the choices worksheet.
user_defined_section Optional
Worksheets with custom section names can be
used in conjunction with the survey worksheet
to simplify control flow.
prompt_types Optional
Defines custom prompt types that can be used
within a survey.
column_types Optional
Defines custom column types that are formulas,
functions, or pathnames.
framework_translations Required
ONLY framework.xlsx. Translations for
standard prompts.
common_translations Optional
ONLY framework.xlsx. Application-wide
translations.
table_specific_translations Optional
Only in form_id matching table_id.
Translations specific to a given table_id.

Note

Each worksheet has a set of required and optional columns. For the XLSX workbook to be valid, all entries must have legal values in the required columns. Optional columns can be left blank at any point, and omitted entirely if not used.

Survey

All XLSX Converter form definitions require a survey sheet. The survey worksheet contains the structure and most of the content of the form. It contains the full list of questions and information about how those questions should be presented. Most rows represent a question; the rest of the rows specify control structures such as screen groups. Blank rows are ignored.

Note

In this document, questions and question types will also be referred to as prompts and prompt types.

There are many prompts available for form development. Some ask the user a question and get a response, but other prompts are simply informational and referring to them as questions is not semantically correct.

Required Columns

A list of the required columns for a survey worksheet follows.

Survey Worksheet Required Columns
Column
Description
type
The prompt type that will be used to display information to the user. Prompt
types can also be used to get data from a user.
name
The name of the prompt type. This name will be used throughout the workbook
to reference the prompt.
display.prompt
A string token identifying the translation entry that can define the text,
audio, image and video to display for this prompt.

Alternatively, this column can be omitted and the prompt text can be
specified directly via the display.prompt.text column.

Optional Columns

A list of the optional columns that can be incorporated into a survey worksheet is below.

Survey Worksheet Optional Columns
Column
Description
branch_label
Used to identify which part of the survey to branch to when
used with a goto clause or user_branch prompt.
calculation
When used with the assign prompt type, assigns a value to a
prompt type.
choice_filter
Used to filter the choices of a multiple choice or
linked_table prompt.
clause
Used in conjunction with the condition column to manage the
control flow of the survey. clause and condition control which
questions get asked in what order, if at all. The clause column
contains control flow options such as if, and the condition
column contains a predicate to determine if action will occur.
if statements always require a condition statement. For other
clause statements, a blank condition column is assumed to
be true. Other commands include begin screen, end screen,
and do section.
comments
Never displayed to the user. Used for development purposes to
leave comments about the form for future reference. It is good
style to comment your work.
condition
Used with the clause column to manage the control flow of the
survey. clause and condition control which questions get
asked in what order, if at all. The clause column contains
control flow options such as if, and the condition column
contains a predicate to determine if the following actions will
occur.
constraint
Takes a JavaScript expression. User cannot navigate forward
until the constraint evaluates to true. If left blank, its
default value is true.
default
Used to set the default value.
display.constraint_message
A string token identifying the translation entry with the
text shown to the user if the constraint is violated.

Alternatively, this column can be omitted and this text
can be specified directly via the
display.constraint_message.text column.
display.constraint_message.text
Message displayed to user if the constraint is violated.
Tells the user what needs to change before they can
continue.
display.hint
A string token identifying the translation entry with the text
to display in italics and a smaller font than
display.prompt.text.

Alternatively, this column can be omitted and this text can be
specified directly via the display.hint.text column.
display.hint.text
Used to display text in italics and a smaller font than
display.prompt.text. Can be used to provide extra instructions
to the user.
display.prompt
A string token identifying the translation entry that can define
the text, audio, image and video to display for this prompt.

Alternatively, this column can be omitted and this information
can be specified directly via the display.prompt.* columns.
display.prompt.audio
Allows the user to play an audio recording. Requires a relative
path to where the recording is saved. If saved in the same
folder as the formDef.json, then only the filename of the
recording needs to be specified.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
display.audio column heading).
display.prompt.image
Used to display an image. Requires a relative path to where
the image is saved. If saved in the same folder as the
formDef.json, then only the image file name and the
extension (for example .jpg, .gif) are needed.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
display.image column heading).
display.prompt.text
The text that the user will see for this prompt type.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
display.text column heading).
display.prompt.video
Allows the user play a video. Requires a relative path to where
the video is saved. If saved in the same folder as the
formDef.json, then only the filename of the video needs to be
specified.

Alternatively, this can be specified on the translations sheet
under the display.prompt string token (under the
th:display.video column heading).
display.title
A string token identifying the translation entry that can
define the text to display for this prompt in the contents
screen and as the column name in ODK Tables.

Alternatively, this column can be omitted and this information
can be specified directly via the display.title.text column.
display.title.text
The display value the user sees when the prompt is displayed
in the contents screen.

Alternatively, this can be specified on the translations sheet
under the display.title string token (under the
th:display.text column heading).
hideInContents
Legal value is true. If true, then the prompt on the same row
will not be displayed on the contents screen.
inputAttributes.<attr>
This column can be used in conjunction with the following
prompt types: string, text, integer, decimal. The <attr> can
specify an HTML attribute to be added to the prompt types.
For example, inputAttributes.min with a value of 5 would add
min=”5” into the HTML element for the prompt type.
model.isSessionVariable
Legal value is true. If true, then the data value for the prompt
will be treated as a session variable and won't be saved.
required
Takes a JavaScript expression. If true, the user will not be able
to navigate to the next screen until the question is answered.
If left blank, its default value is false.
templatePath
Must be specified if using a custom handlebars template.
Requires a relative path to where the template is saved. If
saved in the same folder as the formDef.json, then only the
filename of the template needs to be specified.
value_list
Must be used with the choices worksheet. The value_list
column of the survey worksheet connects to the
choice_list_name column on the choices worksheet.

Prompt Types

The following prompt types are available in ODK Survey.

Survey Prompt Types
Prompt Type
Description
acknowledge
Used to display a message to the user and have them click a checkbox
to acknowledge that they have read the message.
assign
Used for internal assignment of a variable.
audio
Used to capture an audio recording.
barcode
Used to capture a barcode.
date
Uses a date picker widget to capture a date.
datetime
Uses a date time picker widget to capture a date and time.
decimal
Used to display a message to the user and have them enter a decimal.
geopoint
Used to capture a GPS location.
image
Used to capture an image.
integer
Used to display a message to the user and have them enter an integer
linked_table
Used to display the instances of table and allows the user to add
another instance, edit an existing instance, or delete an instance.
note
Used to display a message to the user.
select_multiple
Used to ask the user a multiple choice question and allows the user
to click multiple checkboxes.
select_multiple_grid
Used to ask the user a multiple choice question, displays the
choices to the user in a grid, and allows the user to click
multiple grid items.
select_multiple_inline
Used to ask the user a multiple choice question, displays the
choices to the user inline, and allows the user to click multiple
items.
select_one
Used to ask the user a multiple choice question and allows the user
to click one item.
select_one_dropdown
Used to ask the user a multiple choice question and allows the user
to select one item from a dropdown box.
select_one_grid
Used to ask the user a multiple choice question and allows the user
to select one item from a grid.
select_one_inline
Used to ask the user a multiple choice question, displays the choices
to the users inline, and allows the user to click one item.
select_one_integer
Used to ask the user a multiple choice question and allows the user
to click one item. Each item must be set to return an integer value.
select_one_with_other
Used to ask the user a multiple choice question, displays the choices
to the user, and allows the user to click one item. One of the
choices provided is an other option which if clicked provides a text
box for the user to enter a value.
string
Used to ask the user a question and allows them to enter a string.
text
Used to ask the user a question and allows them to enter text.
time
Uses a time picker widget to capture a time.
user_branch
Used to allow the user to pick which section of the form they would
like to enter.
video
Used to capture a video.

Settings

Settings Worksheet Columns
Column
Description
setting_name
The name of the setting within the form
value
The value for the setting
display.title
A string token identifying the translation entry with the text shown to the user
when the (survey) title is displayed.

Alternatively, this column can be omitted and this text can be specified directly
via the display.title.text column.
display.locale
A string token identifying the translation entry with the text shown to the user
when the translation locale is displayed.

Alternatively, this column can be omitted and this text can be specified directly
via the display.locale.text column.

Available setting_name values that can be used:

setting_name values
Value
Required?
Description
table_id
Required
The unique id of the table that the form data gets
stored in.
survey
Required
Specify the title of the form via content of the
display.title.text column. That value will
appear as the title to the user.
form_id
Optional
A unique identifier for the form. Default value is
the unique id that ODK Survey uses to identify the
form.
form_version
Optional
A value used for version control of the form. The
recommended format is yearmonthday (for example:
20131212 to say the 12th of December 2013).
<section_name>
Optional
Used with display.title.text to set how the
section name will appear to the user on the contents
screen.
instance_name
Optional
Used to display the name of saved instances of the form.
This must be the name of a prompt type from the survey
worksheet.
default
Optional
Used with display.prompt.text (no qualifier), or
other fields to set the default translation of a UI
element. Specify label under display.locale.text
<language>
Optional
Used with display.prompt.text.<language>, or
other fiels to set other language options in the form.

A sample settings worksheet might look like this:

Settings Worksheet Example
setting_name value display.title.text display.locale.text display.locale.text.hindi
table_id sample_form      
form_version 20130819      
survey   Sample Form    
default     English English (as Hindi name)
hindi     Hindi Hindi (as Hindi name)

Tip

If the survey has been broken up into multiple worksheets, each worksheet can be assigned its own title by adding a row for it and filling in the display.title.text column.

Tip

In the case of multiple languages, the display.locale.text column determines how the different language options are presented to the user.

Properties

This holds the key-value settings for specifying detail and list views, and other parameters. The columns in this sheet are:

Properties Worksheet Columns
Column
Description
partition
The class of property to set
aspect

key
The name of the property to set
type
Valid options: object, array, rowpath, configpath, string, integer, number, boolean
value
The value of the property to set

For example, the following configuration specifies that the default view for the table is the list view (HTML). It also defines the detail view, list view, and map view HTML files. And, for the map view, it defines the color rule to apply to the pins in the map view and the latitude and longitude columns to use in displaying those pins.

Properties Worksheet Example Table : header-rows: 1
partition aspect key type value
Table default defaultViewType string LIST
Table default detailViewFileName string config/tables/Tea_houses/html/Tea_houses_detail.html
Table default listViewFileName string config/tables/Tea_houses/html/Tea_houses_list.html
Table default mapListViewFileName string config/tables/Tea_houses/html/Tea_houses_list.html
TableMapFragment default keyColorRuleType string None
TableMapFragment default keyMapLatCol string Location_latitude
TableMapFragment default keyMapLongCol string Location_longitude

Calculates

The calculates worksheet is an optional worksheet.

Calculates Worksheet Columns
Column
Description
calculation_name
The name used to reference the calculation in other worksheets.
calculation
The JavaScriptf forumla to be evaluated.

Each row of the calculates page represents a function that can be used elsewhere in the workbook by referencing the individual calculation_name. The calculation column can store any valid JavaScript expression. In general,

Note

Calculations are referenced in the condition column of survey worksheets.

Tip

There are built in functions for ODK Survey that can be used anywhere in the workbook. See the Forumla Functions section for more details.

If a complex calculation is required, you can access the full power of Javascript and the jquery.js (that is: $.some_func(...) ) and underscore.js (that is: _.some_func(...) ) libraries. Internally, the calculate column is wrapped and evaluated as a Javascript function:

function() {
    return (YOUR_CALCULATE_COLUMN_CONTENT_HERE);
}

You can write your own code to perform a join via defining and invoking an anonymous function in your calculate. Here is an example:

(function() {
    var result = "";
    _.each(data('valueListField'), function(element) {
        result = result + ", " + element;
    });
    return result.substring(2);
}) ()

This defines a function and then invokes it. The available functions within a calculates expression are the following:

Available Calculates Functions
Function
Description
Usage
data(fieldName)
Retrieve the value stored under this fieldName
data('myField')
metadata(instanceMetadataFieldName)
Retrieve value stored under this name
metadata('_group_modify')
selected(promptValue, qValue)
Test whether qValue occurs within a select-multiple
selected(data('mySelectMultipleField'),'myChoiceDataValue')
countSelected(promptValue)
Count the number of selections in a select-multiple
countSelected(data('mySelectMultipleField'))
equivalent(promptValue1, promptValue2, ...)
Test if values are equivalent
equivalent(data('promptA'), data('promptB'))
not(conditional)
Negate a condition ( equivalent to !(conditional) )
not(data('fieldA') === data('fieldB'))
now()
Return the current time
 
isFinalized()
Return whether or not the current row is finalized
 
assign(fieldName, value)
Store value in fieldName and return value.
(8 + assign('myField', 5))*10

Additionally, the following functions are also available, but are generally not useful in calculates. They are used within template helper functions (…/system/survey/js/handlebarsHelpers.js).

Template Helper Functions
Function
Description
Usage
getCurrentLocale()
Return the currently-active locale
 
localize(locale, displayProperty)
Localize the given display.xxx text
localize(getCurrentLocale(), display.hint)
width(string)
Determine the rendered width of a string
 
expandFormDirRelativeUrlPath(content)
Return url for a file within the form directory.
 

And, finally, you can also reference the opendatakit object (that is: opendatakit.some_func(...) ) within these functions (system/survey/js/opendatakit.js).

Choices

The choices sheet allows you to specify the set of choices for multiple choice prompts.

Choices Worksheet Columns
Column
Description
choice_list_name
The name used to reference the set of choices. This name must be the same
as the values_list in the survey worksheet.
data_value
The value that gets stored as the user’s response.
display.title
A string token identifying the translation entry with the text shown to
the user for this choice value.

Alternatively, this column can be omitted and this text can be
specified directly via the display.title.text column.
display.title.text
The text that the user sees for this choice.
display.title.image
An image that the user will see associated with a particular choice.

Model

The model sheet is an optional sheet that allows you to specify the data model for the table_id specified in the settings worksheet.

Model Worksheet Columns
Column
Description
name
The name of the data field to be used in table_id
type
The type of data that can be put into this data_field of the table.
isSessionVariable
Whether or not this field is a session variable
(not persisted – defaults to false).

Many more columns can be specified, including a default column or, as shown in the exampleForm, a default[0] column to initialize the first element (index zero) of a select multiple field. Default values cannot be calculates and must be simple literal values (integers, numbers and strings).

Queries

The queries worksheet is an optional sheet that allows you to request data from external sources for use in select prompts. These are some of the things you can do with queries:

  • Connect to website APIs.
  • Get data from external Android Applications via file content providers.
  • Get data from a linked table
  • Open CSV files included in the survey's directory.
  • Pass key-value maps to linked_table forms when creating or opening that form.
Queries Worksheet Columns
Column
Description
query_name
The name used to reference the information returned by
the query.
query_type
Legal value are ajax, csv, and linked_table.
Used to specify the provenance of the query data.
uri
Used by ajax and csv queries. The uri to use
for an ajax query or the name of the CSV file to
use relative to the location of the formDef.json
file.
callback
Used by ajax and csv queries. The function
that will be used to map the query results to the set of
choices for a multiple choice prompt.
linked_table_id
Used by linked_table queries. The table_id
used to identify the table that the data will come
from. This should match the table_id provided
in the settings worksheet.
linked_form_id
Used by linked_table queries. The id of the form
that will be used to get the results for the
linked_table. This value should match the
form_id value in the settings worksheet.
selection
Used by linked_table queries to filter results
when used with selectionArgs. Specifies the
conditions that must be true for the results to be
selected but must have selectionArgs to work.
selectionArgs
Used by linked_table queries to filter results
when used with selection. The arguments to be
used in the selection described above.
orderBy
Used by linked_table queries to specify the
order in which results should be returned.
newRowInitialElementKeyToValueMap
Used by linked_table queries. A Javascript
object containing key value pairs used to assign
initial values when creating a new row in the
linked table. The key is the element name in the
linked form. The value is the initial value to
assign to the element.
openRowInitialElementKeyToValueMap
Used by linked_table queries. A JavaScript
object containing key value pairs used to assign
initial values when opening an existing row in the
linked table. The key is the element name in the
linked form. The value is the initial value to
assign to the element.

The two columns newRowInitialElementKeyToValueMap and openRowInitialElementKeyToValueMap allow you to pass information from your originating form into the linked form. The element keys in these maps correspond to the element keys in the linked form (not the current form). These can refer to any of the form's fields; commonly, the values you would pass into the openRowInitialElementKeyToValueMap would refer to session variables. You would typically pass the instanceID of the originating form (that is: opendatakit.getInstanceID() ) into the linked form when creating it so that you can store that id in a field in that linked table, thereby tying the newly-created row in that table back to the originating form's row.

User Defined Section

A custom named section is essentially a subset of the survey worksheet. Thus, all of the columns that were described in the survey section are applicable in a custom section worksheet. However, the following worksheet names are reserved and cannot be used to name a custom section worksheet:

  • settings
  • properties
  • choices
  • queries
  • calculates
  • column_types
  • prompt_types
  • model
  • framework_translations
  • common_translations
  • table_specific_translations

Custom prompt_types

Custom prompts can be created within the survey. The prompt_types worksheet can be used to specify the custom prompts so that they will be recognized by Survey.

prompt_types Worksheet Columns
Column
Description
prompt_type_name
The name that will be used to reference the prompt_type
type
The type of object that will be used to store the data received by the user
for this prompt type.

column_types

Custom columns can be used within a workbook that are used to store functions, formulas, and path names. The column_types worksheet can be used to specify these custom columns.

prompt_types Worksheet Columns
Column
Description
column_type_name
The name that will be used to reference the column.
type
The type of information that will be stored in the column (for instance, function,
formula, app_path_localized).

framework_translations

The framework_translations sheet is only present in the framework.xlsx file. It defines the translations for all of the standard prompts provided by the ODK-X framework.

framework_translations Worksheet Columns
Column
Description
string_token
The name that will be used string to be translated.
text.<locale>
The value of the translated text string. There can be as many of these
columns as you want translated languages (such as text.default, text.gr,
text.es).
image.<locale>
The value of the image url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as image.default, image.gr, image.es).
audio.<locale>
The value of the audio url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as audio.default, audio.gr, audio.es).
video.<locale>
The value of the videourl fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as video.default, video.gr, video.es).

The locale code should generally be the 2-letter language code, or, if necessary, the language_COUNTRY naming used by Android can be used to identify a specific language variant. For example: en_US, en_UK for US English and UK English, respectively.

common_translations

The common_translations sheet is optional. It should only be present in the framework.xlsx file. It can be used by application designers to define translations used across multiple forms and web pages in an application.

The format for this sheet is the same as that for the framework_translations sheet.

framework_translations Worksheet Columns
Column
Description
string_token
The name that will be used string to be translated.
text.<locale>
The value of the translated text string. There can be as many of these
columns as you want translated languages (such as text.default, text.gr,
text.es).
image.<locale>
The value of the image url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as image.default, image.gr, image.es).
audio.<locale>
The value of the audio url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as audio.default, audio.gr, audio.es).
video.<locale>
The value of the videourl fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as video.default, video.gr, video.es).

The locale code should generally be the 2-letter language code, or, if necessary, the language_COUNTRY naming used by Android can be used to identify a specific language variant. For example: en_US, en_UK for US English and UK English, respectively.

table_specific_translations

The table_specific_translations sheet is optional. It should only be present in the XLSX file whose form_id matches the table_id. It defines translations that are available to all forms and web pages specific to that table id.

framework_translations Worksheet Columns
Column
Description
string_token
The name that will be used string to be translated.
text.<locale>
The value of the translated text string. There can be as many of these
columns as you want translated languages (such as text.default, text.gr,
text.es).
image.<locale>
The value of the image url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as image.default, image.gr, image.es).
audio.<locale>
The value of the audio url fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as audio.default, audio.gr, audio.es).
video.<locale>
The value of the videourl fragment relative to the appName directory
for this locale. There can be as many of these columns as you want
translated languages (such as video.default, video.gr, video.es).

The locale code should generally be the 2-letter language code, or, if necessary, the language_COUNTRY naming used by Android can be used to identify a specific language variant. For example: en_US, en_UK for US English and UK English, respectively.

Built-in Functionality

The jquery and underscore libraries are available when defining calculates expressions.

ODK Survey exposes built-in functionality through formula functions to decrease form development time.

Formula Functions

The following formula functions can be used to simplify calculations or expressions.

Built in formula functions
Name
Description
Example
assign
Assignment operator that will assign the value
to the field and return the value
assign('fieldname',value)
countSelected
Returns the number of items selected from a
select_multiple prompt
countSelected(data(‘options’))
data
Returns the value of a field or session variable.
data(‘options’)
equivalent
Check to see if two values are equivalent
equivalent(data(‘option1’), data(‘option2’))
isFinalized
Returns true if this submission is finalized
isFinalized()
localize
Localizes the text passed in.
localize(data('options'))
metadata
Returns a metadata field of this row
metadata(‘_group_read_only’)
not
Negates the argument passed in.
not(selected(data('examples'), 'label_features'))
now
Returns the current date
now().getDay()
selected
Returns true if the value selected from a select
prompt is equal to the second argument passed
into the function.
selected(data('visited_continents'), 'NorthAmerica')

And, additionally, the opendatakit object is also available for use in calculates expressions.

Warning

The opendatakit object contains many useful functions but these should be considered internal methods subject to change. When upgrading, be sure to confirm that the methods you use have not disappeared!