IntelliSurvey supports the use of spreadsheets in survey programming. Spreadsheets created with external software such as Microsoft Excel can be uploaded to the survey. Spreadsheets must meet some fairly simple formatting requirements to be usable by the survey.
Once a spreadsheet is uploaded, it can be edited directly in IntelliSurvey in r9+. Downloading, editing, and re-uploading the sheet is not always necessary. The sheet editing features are described below.
For more information on IntelliSurvey's spreadsheet management features, see Managing survey spreadsheets (r9). Information on how to reference sheets in SPL can be found in Using spreadsheets in survey programming.
Formatting and creating spreadsheets
New spreadsheets must be created in external software and uploaded to IntelliSurvey. The system accepts three file formats: Microsoft Office XLS or XLSX Excel files, Open Document ODS spreadsheets, and CSV files (comma separated value). Be sure your document is saved in one of these formats before attempting to create a new spreadsheet.
When creating a spreadsheet, it's helpful to think of sheets as similar to SPL setlists. Each row of the sheet is an item in the list, and each defined column is a data point for each row. The following setlist and sheet are equivalent to each other:
SPL | Sheet named 'colors' | ||||||||||||||||||||||||
setlist: colors 1. red {{example: rose}} 2. orange {{example: fox}} 3. yellow {{example: banana}} 4. green {{example: leaf}} 5. blue {{example: sky}} 6. purple {{example: plum}} |
|
The first column must be labeled 'id', and each row must have a unique value in the 'id' column. The column labels must also be unique.
The second column may be labeled 'text' to serve as the option text for each item. If the sheet has a 'text' column in a different position, that column will be moved to the second column after being uploaded to the survey.
Note: The 'text' column is not required. Some sheets serve specific purposes that don't need a 'text' column, such as a MaxDiff design. It is recommended to include a 'text' column if the sheet will be used similarly to a list.
Additional columns can be added with user-defined column headers. These function like option data decorators in setlists. Column headers should not contain spaces, but can have underscores. Any capital letters will be converted to lower case after uploading. Capitalization will be preserved for the rest of the data in the sheet.
IntelliSurvey also allows the use of element decorators as column headers, but programmers should note that these are considered "reserved terms," and will require specific inputs if used as headers in a survey sheet. For a list of these reserved terms/decorators, see Element decorators.
After uploading a spreadsheet, It is a good idea to use the View button to review the spreadsheet's contents, so you can ensure it has been loaded as expected.
Additional formatting information
Autoother options with underscores are supported in sheets. When including autoother options in sheets, the autoother size
, autoother placeholder
, and anchor autoother
tags can be added locally to questions referencing the sheet's autoother options. In order to create the text fields for the autoother options, underscores must be included, e.g., "Other brand ______". If need be, these autoother tags can instead be included on set defaults
for global applications.
If the survey is being translated, "_text" must be added to the end of column headers for those columns to be included in the overlay. In the spreadsheet above, the "example" column would need to be renamed "example_text".
Editing survey spreadsheets
The Sheets applet allows for in-app editing of survey sheets after the sheet has been uploaded. From the Browse Sheets screen, double click on a sheet or click the View and edit sheet button to begin editing a sheet.
Above the sheet is as toolbar with the following actions: Cancel, Save, Upload Edits, and an Actions dropdown menu that contains the History and Delete buttons. The Cancel button will revert any changes and switch back to Viewing mode. The Save button will apply changes and create a new version of the sheet. For information on Upload Edits, History, and Delete, see Managing survey spreadsheets.
Editing cell content
Each cell of the sheet (except for the 'row' column) may be edited by clicking on it. This will activate the cursor to begin editing the text in the cell. Click away from the cell or press the Enter key to apply the changes.
Adding or removing rows and columns
The Row / Column Actions menu has the following actions for adding and removing rows and columns:
- Add Row — Adds a blank row to the bottom of the sheet.
- Add Column — Adds a column to the right side of the sheet. A dialog box will open for you to provide a name for the column.
- Remove Row — Removes the selected row(s) from the sheet. Use the checkboxes on the far left of the sheet to select one or more rows, then select this option to remove them.
- Remove Column — Removes one or more columns from the sheet. A dialog box will open where you can select which columns to remove.
Tip! If you accidentally remove a row or column, click the Revert button to restore any removed rows/columns since the last time the sheet was saved, or click Cancel to abandon the changes.
Changing the order of rows and columns
Rows can be moved by clicking on any cell in that row and dragging it to a new position. Note that the 'id' field will not change to match the new order.
Column order can be changed by clicking on the column header and dragging it to a new position. The order of the 'row', 'id', and 'text' columns cannot be changed.
When dragging the row or column, look for a blue line to appear to indicate the position it will be moved to.
Saving changes
Once the system has detected changes, the Save button will become active. Clicking Save will parse the edits and create a new version of the sheet. After edits are saved, the survey itself must be re-parsed for the changes to take effect; until then, the survey will continue using the previous version of the sheet.
Tip! When editing the content of a specific cell, the edits will not be detected by the system until keyboard focus is changed. If the Save button is disabled, press the Enter key or click away from the cell being edited to apply the changes.
In the orange bar shown above the sheet in Editing mode, the number in parentheses is the current sheet version. After saving, this number will increment, indicating a new version has been created.
For more information about sheet versions, see Managing survey spreadsheets (r9).
Comments
0 comments
Please sign in to leave a comment.