Managing survey spreadsheets with the Spreadsheets applet

Survey questions and variables often require data sources to create options, table rows, and quota buckets. Although lists are the most common type of data sources used in surveys, spreadsheets are also supported and frequently used in the IntelliSurvey platform. Spreadsheets can be stored locally for a specific survey through the Spreadsheets applet. With this tool, survey creators can upload spreadsheets to the survey. Once uploaded, spreadsheets function like lists, but also support more advanced functionality.

For more information about formatting spreadsheets, see below. For information on referencing sheets within survey code, see Using spreadsheets in survey programming. 

Accessing sheets

To access spreadsheets, expand the Structure button group, and select Sheets.

This launches the Survey Spreadsheets Management area, which by default opens the Browse view.

The Browse view presents a list of all spreadsheets presently associated with the survey. These can be sorted by Name, Description, Version, Created date, and Author.

• Name refers to the actual name of the file.
• Description is entered when the file is uploaded or created if the file is a single sheet spreadsheet. In the example above, the sheets "labels" and "primary" are separate pages of a single spreadsheet and thus do not have a description included. 
• Version refers to the version currently considered the "primary" iteration of the spreadsheet. Unless this is manually changed, the system will consider the primary version to be the most recently added version.
• Created displays the date of the primary version of the sheet, defaulting to the most recently uploaded version's created date.
• Author is the user who uploaded or created the sheet.

Adding a new sheet

To add a new sheet to an existing survey, simply click on the New button. You will be taken to a new page to upload the file. 

The Name (ID) is the name of the spreadsheet. The name should be lowercase to differentiate it from any lists possibly used in a survey. The Description is optional but can be used for further clarification of the sheet's contents.

Actions

Perform the following tasks from the Actions menu.

View

Look at the contents of a spreadsheet.

History

View individual versions or compare multiple versions of a spreadsheet in the Version History window. You can also upload new versions, view each version, download existing versions, or designate a version as the "primary" version in this window.

After choosing which versions you would like to compare, a comparison of the two versions will appear, displaying any changes between them. The Color code at the top of the screen lets the viewer know which type(s) of changes occurred between versions. In this example, the 3rd version of the Colors Spreadsheet added a new column, "warm_cool".

Upload

This will overwrite the existing spreadsheet with one selected to upload. The previous versions of the sheets can now be compared, viewed, or made "primary" by selecting Actions > History.

Don't forget to re-parse your survey after uploading a new version of a spreadsheet!

Download

Downloads the selected spreadsheet.

Delete

Deletes the selected spreadsheet.

Download all sheets

Downloads all sheets in the survey. This generates a single workbook with different tabs for each sheet, and even includes a Table of Contents as the initial tab in the spreadsheet.

Batch Upload

Add or update multiple sheets at once using a single Excel file. The uploaded Excel file should contain multiple sheets. The name of each sheet will be used to generate the name for the sheet in IntelliSurvey. In the case of updating an existing sheet, the sheet name must exactly match the name of the sheet to be updated. As with creating a new sheet via the New button, each individual sheet is parsed for correct formatting. After parse, the system presents a grid of all sheets in the uploaded file.

When confirming which pages to include from a spreadsheet batch upload, note in the above examples that there are three to four columns that appear, depending on whether the sheet is being uploaded for the first time ("create") or whether it is an "update" of an existing sheet. In the latter case, any changes between copies of the sheets will be noted in an (extra) 4th column. The columns that are displayed can be categorized as follows.

Column	Description
	Include the sheet in the upload. The default is to include each sheet in a batch upload. Click here to deselect the sheet.
Sheet name	The name of the sheet.
Status	The condition of the sheet. There are two possible values: create, meaning no sheet with this name currently exists and so one will be created, or update, meaning a pre-existing sheet was found and will be updated to this version.
Update details	In the case of an updated sheet, this column displays the number of new, changed, or removed rows.

Click Next to confirm the updates. 

Formatting and creating spreadsheets

Any spreadsheet or text editing software can be used to create a spreadsheet, provided that it is formatted such that the first row defines columns, and the first column defines the row id. The first column must be labeled ‘id’. The second column can be labeled arbitrarily, but if the sheet is to be used as a survey data source, then it is highly recommended to label it as 'text'. The default text for a sheet reference is defined by the 'text' column. Omitting it means that no default text will show when the list is referenced, and text decorators must be used to define the data column to use as text.

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.

Although the system may load your spreadsheet, it may disregard data which is improperly formatted and does not follow these conventions. As such, it is a good idea after creating your spreadsheet to use the View button to look at the spreadsheet's contents, so you may ensure it has been loaded as expected. 

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.

Formatting examples

Given the formatting restrictions mentioned in the previous paragraph, a properly formatted XLS or ODS file looks something like this.

Adding spreadsheets to IntelliSurvey

After creating a properly formatted sheet, it can be uploaded. Begin by clicking the New button.

The system prompts for the following information:

• Name (ID) is the static reference used to call the spreadsheet in survey code. Names should not include any spaces or special characters.
• Description is a short description of the contents of the spreadsheet. The description appears only within the Browse Sheets applet and is used to help search and sort spreadsheets.

When the name and description are completed, click the Browse... button to display the file open dialog box and select the locally-created document. When the file is selected, click the Next button. The system will load and parse the document and create the spreadsheet. If there are any errors, adjust the document, being sure to follow the formatting guidelines above.

The sheet is now ready to be used in a survey.

Referencing survey sheets

For information about how to reference sheets in survey code, please see Spreadsheets.