Creating a form from a spreadsheet

Source: vignettes/creating_form_from_spreadsheet.Rmd

In addition to being able to specify (pre)registration forms from within R or importing it from the URL of an existing form, you can also specify the form using a spreadsheet. You can use an Excel file (.xlsx) or a Google Sheet. An empty spreadsheet is available here.

Form specification format

The spreadsheet should have the following worksheet names (in whichever order):

  • metadata: metdata about the (pre)registration form
  • instructions: instructions for the (pre)registration form
  • sections: the sections in which the (pre)registration form items are organized
  • items: the items in the (pre)registration form
  • valueTemplates: the value templates specifying valid values

Metadata

The metadata worksheet contains two columns (in whichever order): field and content. Every row holds a different bit of metadata. The field name is specified in the field column and the metadata itself is specified in the content column in the corresponding row. Always specify content for at least the following fields: specify the form’s title as title; specify the form’s author(s) as author, specify the date as date, and specify the version as version.

Regarding versioning, if there is only one version and the creators do not plan to release future version, the recommendation is to set the version to 1.

If the creators have no clear idea about how to version the form (i.e. there may be improvements in the future), the recommendation is to set the first version to 0.0.1, and roughly adopt the system common for software: increment the first number when the form is substantially updated (e.g. such that preregistrations that used the previous version of the form may no longer be valid given the new version, for example becaue sections were added or removed, or value templates changed, etc), the third number for very small changes (e.g. typos, spelling corrections, clarification or extra explanations, bug fixes in regular expressions in value templates, etc), and the second number for changes in between (e.g. changing the order of items or moving an item to another section, or changing value templates to be more permissive (and so, retaining compatibility with (pre)registrations that used the previous version of the form)). In that case, use version 1.0.0 to signal that the form has reached maturity.

Instructions

The instructions worksheet contains two columns (in whichever order): heading and description. Every row holds one specific set of instructions in the description columns that will be included under the heading in the heading column.

These instructions can be used for general instructions about the form, such as the intended use cases or references to background materials.

Sections

The sections can be used to organize the items in the form. The sections worksheet has three columns (in whichever order): section_id, section_label, and section_description.

Every section has a unique section identifier that is specified in section_id. Identifiers can only contain Latin lower- and uppercase letters, Arabic digits, and underscores; and have to start with a letter. These section identifiers are what are specified in the items worksheet to assign an item to a section.

Sections have brief titles that are specified in section_label, and can have longer descriptions (e.g. instructions, background information etc) in section_description.

Items

The items worksheet contain the following columns (again, in whichever order): section_id, item_id, item_label, item_description, item_valueTemplate, item_validValues, item_validation.

The section identifier in section_id specifies to which section an item belongs. The item_id is the unique item identifier that users use when specifying (pre)registration content using [preregr::prereg_specify()]. The item_label contains a brief item name, and the item_description a longer description, for example an instruction.

The item_valueTemplate column is used to specify the value template to use for this item. This determines the valid input formats. The item_validValues column is used to specify the valid values, in case only a few values can be specified for this item; and the item_validation column can be used to override the validation information in the valueTemplates worksheet.

Value templates

Value templates are used to specify valid user input for form fields. For example, often any text input may be allowed. Sometimes, the text input will have to be a number. And other times, it will be necessary to force users to select one value out of multiple.

The valueTemplates worksheet has columns identifier, description, validValues, default, examples, validation, error (in whichever order).

The identifier specifies the unique identifier for the value template as used in the items worksheet to specify which value template applies. The description describes which content is valid for that value template.

The validValues column can be used to specify a finite list of valid values. These values are then used in the examples column, the validation column, and the error column.

The default column is not used in preregr - it is retained in the standard for compatibility with the metabefor package, that also uses value templates.

As yet, the examples column isn’t used yet, either, although that will likely change in future versions (in which case examples will be shown when the item’s description is shown).

The validation and error column specify the validation rules (as R script) and the error to show when validation fails, respectively.

Example form specifications

The spreadsheet specifications for the (pre)registration forms that are included with preregr are publicly available at the following URLs:

In addition, there’s an empty form available here.

Importing a form specified in a spreadsheet

To import a form specification in a spreadsheet, use preregr::form_fromSpreadsheet(). For example:


form_imported_from_a_spreadsheet <-
  preregr::form_fromSpreadsheet(
    paste0(
      "https://docs.google.com/spreadsheets/d/",
      "1bHDzpCu4CwEa5_3_q_9vH2691XPhCS3e4Aj_HLhw_U8"
    )
  );

form_imported_from_a_spreadsheet |>
  preregr::form_show(
    section = "extraction"
  );

Directly initializing from a spreadsheet

It’s also possible to directly initialize a new preregistration from a spreadsheet specification:


form_initialized_from_a_spreadsheet <-
  preregr::prereg_initialize(
    paste0(
      "https://docs.google.com/spreadsheets/d/",
      "1bHDzpCu4CwEa5_3_q_9vH2691XPhCS3e4Aj_HLhw_U8"
    )
  );

form_initialized_from_a_spreadsheet |>
  preregr::prereg_show_item_completion(
    section = "extraction"
  );