Creating a Microsoft Excel Task in Qualtrics

Description

Automate your workflows by bringing the data you collect into the software you and your teammates use most. The Microsoft Excel task within Qualtrics allows you to automatically add new survey responses as new rows to a Microsoft Excel workbook. A new row will be created within your selected Excel Workbook each time the Microsoft Excel Task runs.

Note:  This feature is available to any user with access to workflows in Qualtrics.

Environment

All OHIO Qualtrics accounts

Solution

Create a survey in Qualtrics

Review Getting started with Qualtrics for instructions on creating a survey. 

• Note: A survey is a type of project. 

Create a Microsoft Excel Workbook

1. Create a new Excel workbook or determine an existing Excel workbook where you would like to import the survey response data.

2. The Excel workbook doesn’t need to have data in it yet. You’ll be given the option to create a new sheet within the workbook for your data.

   • If you want to import the Qualtrics data to an existing worksheet (tab), it must have column headers set up before you set up your automation in Qualtrics.

     • These column headers should match the field names used in Qualtrics. (See the example below.)

     • Do not use quotes (“) in column names.

     • Ensure that your columns have no duplicate names.

     • Once your headers have been created in Excel, select the cells and select Insert > Tablefrom the menu bar.

       • Select My table has headers and select OK.

   • If you plan to link multiple surveys to your spreadsheet, create and label new tabs to reflect the survey you will link to.

3. Save your Excel workbook to a OneDrive account.

   • Note:Your workflow will not work if your Excel file is saved to your local computer.

   • Note:You are not able to connect Qualtrics to an excel file located in a group OneDrive location.

   • Note: Ensure that your workbook is set up according to your goals before moving forward. Editing your workbook after you connect to the Qualtrics workflow will cause the Task to not work properly.

Create a Microsoft Excel Task

1. In Qualtrics, open your survey and navigate to the Workflowstab within the project.

2. Select Create a workflow.

3. Select Started when an event is received or Started at a specific time (scheduled).

   • Started when an event is received will automatically add new entries to your Excel workbook when new responses are received.

   • Started at a specific time (scheduled)will add new entries to your Excel workbook on a date/time that you choose. This date/time can be recurring.

   • View  Scheduled vs. Event-Based Workflows  for more information about types of workflows.

4. Determine the schedule   for your workflow (if scheduled) or choose the event   that begins the workflow (if event-based).

   • For workflows started when an event is received:

     • Select Survey response.

     • Select Newly created responses.

     • Select Finish.

     • Note: Qualtrics API is only available by request. OIT does not provide support for API and Qualtrics vendor support is limited.

   • For workflows started at a specific time (scheduled):

     • Select the frequency.

     • Complete the additional fields.

     • Select  Save.

5. Select the plus sign ( + ).

6. Select Add a task.

   • You are not required to Add a condition , but can if your needs require it.

   • Add a condition sets criteria for when your workflow will run.

   • For more information on building conditions in workflows, view Creating Conditions .

7. Select Microsoft Excel.

8. Choose the Microsoft account/OneDrive space where your Excel workbook is stored. (Typically, this will be your OHIO ID/email account.)

9. If you have not previously added a Microsoft account to Qualtrics:

   1. Select  Add a user account .

   2. Give your account a name. This name will represent which Microsoft account you have linked to Qualtrics.

   3. Select  Connect account.

   4. Log into your Microsoft account with your OHIO email and password.

   5. Accept the Permissions Requested.

10. If you have already added an account,

    • Select the desired account.

    • Select Next.

11. Select a Microsoft Excel Workbook.

    • You can search by name if the workbook doesn’t show up at the top of the list.

    • Note : New workbooks cannot be created; you must select an existing workbook.

12. Select the worksheet (tab) of the file you’d like to add data to or select Create a new worksheet.

    • If creating a new Excel worksheet, enter a name in the Name your sheet (tab) field.

13. The data imported from the Qualtrics survey to the Excel spreadsheet will be formatted as a table. This table cannot be edited while the survey is active.

    • Under S elect a table within a worksheet, select Create a new tableor select an existing table in the drop-down options.

    • Enter a name for your table in the Name Your Tablefield.

    • Note: The table name cannot be empty or contain special characters.

14. Enter a starting position for the table (the cell where you want your data to begin populating).

    • The standard start position is A1.

15. Navigate to the  Headers section.

    • Headers are the column headings for your table.

    • Generally, headers correspond to your survey questions.

    • You can also create headers for data such as survey date/time, IP address, etc.

16. Select  Add a field to map your survey data to your worksheet headers.

17. If you are using an existing table:

    1. Choose a header from the drop-down menu that appears upon selecting  Add a field.

    2. Repeat this step for each header you'd like to map.

18. If you are creating a new table,

    1. Select  Add a field > New Header.

    2. Enter a name for your header in the Header Value field.

    3. Repeat the steps above for each header you'd like to map.

19. Under  Data, use the piped text symbol {a}to select the desired question response for the column.

    • Piped text refers to data stored in your Qualtrics survey. This includes responses to your survey questions.  

20. To import/map data from survey responses:

    • Select the piped text button ( {a} ).

    • Under Pipe Text from a …select the desired data.

      • Piped text can be used to pull data from the following:

        • Survey questions (most common)

        • Embedded data fields

        • GeoIP location

        • Date and time

        • Panel fields

        • Quotas

        • ResponseID (See Response ID  section on the Understanding Your Dataset page)

        • Response link (Link to the response report . Does not require any kind of login or security setting to view)

        • Status (See Status column on the Understanding Your Dataset page)

    • Note:If you need to update your piped text mapping, you will need to delete all text in the datafield that you are updating, and then re-map the data.

    • View  Tips for Mapping Piped Text to Columns for more information.

21. When you’ve finished adding all your fields, select  Savein the bottom-right corner.

Outcome:You’ve successfully mapped your Qualtrics survey data to an Excel workbook. When a survey response is submitted, response data will automatically populate into your selected Excel document.

Note: Don’t see the changes made in an Excel file reflected to Qualtrics yet? In Qualtrics, select Backin the lower-left portion of the screen, then re-select the account you want to connect to. This will reload the data pulled from Excel, so any new files or columns will appear.

Get help from OIT

• Visit the Qualtrics page to create a ticket

• Visit the  Technology Resources and Training  page for OIT training resources about Qualtrics

Additional resources

• Qualtrics XM Support articles and training material

• *Screenshots and content in this article have been taken from Qualtric's  Microsoft Excel Task  webpage