Working with spreadsheet form definitions 2: The survey sheet

This is part two of our series on spreadsheet form definitions. Check out the first part and table of contents here.

You can check out the form definition spreadsheet demonstrated below here.

The survey sheet is the main sheet of the form definition, showing the main form design, including all fields and their properties

The top row is the header row, and there is a column for each field property. Every other row on that sheet will be a field.


If you look at the properties, they will look very familiar, since they are the same field properties you see in the online form designer, only sideways:


Recognize the type, label, and name field properties?

In this article, we will walk you through the main attributes of the survey sheet:

  1. Default fields
  2. Adding a new field
  3. Adding groups
  4. Organizing your fields
  5. Adding other properties

1. Default fields

A new form definition spreadsheet template comes with 13 fields. The top nine fields are metadata fields, meaning they collect helpful information in the background.


Metadata fields

To learn more about each of these fields, check out our documentation. Our recommendation is to keep these fields, and start adding new fields below them.

The fields on rows 12-14 (names "intronote", "q1", "q2", and "q3") are just there to demonstrate some basic field types. You can delete these before starting your main form. You can either delete the whole rows, or simply select cells A12:C15 and delete their contents with the Backspace or Delete key.


Example field that can be deleted

2. Adding a new field

To add a new field, go to a blank row, and enter the field properties you would like to use in the corresponding columns.

Let's say we wanted to add a new text field with the name "resp_name" and the label "What is your name?". 

First, go to a blank row.


We want this to be a text-type field, so in the type column, enter "text".


One cool feature of the form definition template is that the row colors will auto-update based on the field type. The row here turned pink-orange so it is easy to tell at a glance that it is a text field.

In the name column, enter the name of the field, in this case, "resp_name":


In the label column, enter the field label:


And that's it! If you save your form, upload it to your server, and go to fill it out, the form will have one visible field: a text field with the name "resp_name" and the label "What is your name?".

Try adding other fields to the form! You can add any field type following these steps, even hidden fields, like calculate fields. In part 3 of this guide, we will discuss how to assign choice lists to select_one and select_multiple fields.


3. Adding groups

To start a group, add a row with the type "begin group". To end a group, add a row with the type "end group". All rows between those two rows will be part of that group. Make sure the "begin group" row has a name and label. While the "end group" row does not need a name, we recommend giving it the same name as the "begin group" row, so it is easier to keep track of when each group begins and ends.


To start a repeat group, add a row with the type "begin repeat", and to end a repeat group, add a row with the type "end repeat". To set a repeat count, enter it into the repeat_count column of the "begin repeat" row.


Just like with fields, groups can have relevance expressions and appearances you can set in the respective column of the "begin" row.

4. Organizing your fields

Just like in the online form designer, when the form is being filled out, the fields will appear in the same order as they appear in the form definition, with the fields at the top appearing first, and the fields at the bottom appearing last. So, when an enumerator fills out this form, first the field "consent" (row 12)  will appear, then "resp_name" (14), then "age" (15), and so on. If you would like to add a field between two fields, right-click the column number you would like to add a field above, and click Insert or Insert 1 row above. A new blank row will be created, and you can add a new field there.

As you scroll down, you may notice that the header row is "frozen"; that is, it stays at the top as you scroll down, so you can easily see which columns are for which properties. It may also help to freeze the left-two columns as well, so you can see the field names as you scroll to the right to add relevance expressions, calculation expressions, and more:
  • To freeze panes in Google Sheets, go to View > Freeze, and select the number of columns you would like to freeze.
  • To freeze panes in Microsoft Excel, select the cell just below and just to the right of the rows and columns you would like to freeze, then go to the View tab, and click Unfreeze Panes (to undo the currently frozen top row), then Freeze Panes.

It is perfectly fine to have blank rows in your form. These blank rows will be ignored by SurveyCTO, so they can be a great way to divide groups of fields without using actual groups. For example, take a look at this part of the form:


In this example, there is a blank row before the part of the form related to the repeat group. That way, it is easier to navigate the form design later.

5. Adding other properties

You can add additional field properties to a field under the appropriate column. For example:

  • If you wanted to add a calculation expression for a calculate field, you would add it to the 'calculation' column of that field's row. For example, this calculate field has a calculation of simply index():
  • If you would like to add skip-logic in your form, use the relevance property. Below, the group "gave_consent" will only appear when the choice "Yes" (value "1") is selected for the field "consent", as determined by the expression in the relevance column:
  • If you would like to restrict the data that can be entered into a field, use the constraint property. Below, the field “age” will only allow integers between 0 and 125 (inclusive).
  • If you would like to ensure a field is required, give it a required value of "yes" or "1".
For some field properties, you will need to create expressions using SurveyCTO functions, which you can learn more about in our documentation on using expressions.

To learn about all of the available field properties, check out the "help-survey" sheet of the form definition spreadsheet template.

Continue to part 3 to learn about the choices and settings sheets, or go to part 1 to learn how to get started with spreadsheet form definitions.

Do you have thoughts on this support article? We'd love to hear them! Feel free to fill out this feedback form.


Article is closed for comments.