---
title: "Creating a form from a spreadsheet"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{Creating a form from a spreadsheet}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---

```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```

In addition to being able to specify [(pre)registration forms from within R](https://preregr.opens.science/articles/creating_prereg_form.html) or [importing it from the URL of an existing form](https://preregr.opens.science/articles/importing_form_from_url.html), 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](https://docs.google.com/spreadsheets/d/14Qbak7JbBhTqmJaMgJ4tU9ZROaBbUfq37_UzkoHnM60/edit?usp=sharing).

# 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`](https://r-packages.gitlab.io/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:

- The OSF Prereg form: https://docs.google.com/spreadsheets/d/1kmghGrkutrt3K9aEFFrWu-BfuJayFRSsV1WKQ-4QSM0 (form shown in the vignette at https://preregr.opens.science/articles/form_OSFprereg_v1.html);
- The Inclusive Systematic Review Registration Form: https://docs.google.com/spreadsheets/d/1bHDzpCu4CwEa5_3_q_9vH2691XPhCS3e4Aj_HLhw_U8 (form shown in the vignette at https://preregr.opens.science/articles/form_inclSysRev_v0_92.html);
- The Psychological Research Preregistration-Quantitative (aka PRP-QUANT) Template: https://docs.google.com/spreadsheets/d/1bVXx_RrXp0Yf2k6pE84HYm2DD1GG-alysEMsaKmqkoQ (form shown in the vignette at https://preregr.opens.science/articles/form_prpQuant_v1.html);
- The Preregistration Template for Quantitative Ethnographic Studies: https://docs.google.com/spreadsheets/d/1l3N0bWnKlRvXuHO-JFg3XHdWzgJWLNli90bnUUa5op4 (form shown in the vignette at https://preregr.opens.science/articles/form_preregQE_v0_93.html);

In addition, there's an empty form [available here](https://docs.google.com/spreadsheets/d/14Qbak7JbBhTqmJaMgJ4tU9ZROaBbUfq37_UzkoHnM60/edit?usp=sharing).

# Importing a form specified in a spreadsheet

To import a form specification in a spreadsheet, use [preregr::form_fromSpreadsheet()](https://preregr.opens.science/reference/form_fromSpreadsheet.html). For example:

```{r, eval=FALSE}

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:

```{r, eval=FALSE}

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"
  );

```