Google Sheets Task

About the Google Sheets Task

Automate your workflows by bringing the data you collect into the software you and your collaborators use most. The Google Sheets task allows you to automatically add new survey responses as new rows to a Google Sheet and look up data in an existing Google Sheet.

Data Edits

For this extension, survey retakes are always treated as a unique new response and added as a new row of data, rather than rewriting old data.

Any edits made to previously collected data in Qualtrics will not be reflected in Google Sheets.

Creating a Google Sheet

If you plan on using an existing Google Sheet in your task, make sure you’ve already created a Google Sheet where you plan to import the data, and make sure you know the file’s name. If the file you’re planning to use was created by someone else, make sure they’ve given you editing access.

The Google Sheet doesn’t need to have data in it yet, but if you plan to add to an existing sheet (tab), it must have column headers set up before you set up your automation in Qualtrics. See the example below.

Make sure you don’t have duplicate column names in your sheet. Do not use quotes ( “ ) in column names.

Do not edit a sheet after connecting to the Qualtrics extensions, or it may not work correctly.

Connecting a Google Drive Account

Before you can use the Google Sheets task, you must connect a Google Drive account for Qualtrics to import data to. Accounts may be added by Brand Administrators for other users in the brand to use, or by individual users for their personal use.

Adding an Account as a Brand Administrator

If you are a Brand Administrator, follow the steps in this section to add a Google Drive account for some or all users in your license to use.

1. Using the navigation menu in the top left, go to Admin.
   
2. Go to Extensions.
   
3. Click the Google Sheets extension.
4. Click Add Account.
   
5. Give the account a name. This name is used for organizational purposes and to identify the account.
   
6. Click Connect Account.
7. Proceed to log into your Google Drive account.
   

For more on managing which users have access to this account, see Determining Who Can Use an Extension Account.

Adding an account as an Individual User

Follow the steps in this section to add a Google Drive account for your personal use. Only you will be able to use the account that you connect.

1. Follow the steps in Creating a Google Sheets Task section until you get to the step where you select an account to use.
2. Click Add user account.
   
3. Give your account a name. This name is used for organizational purposes and to identify the account.
   
4. Click Connect account.
5. Proceed to log into your Google Drive account.
   

Creating a Google Sheets Task

1. Navigate to the Workflows tab of a project, or the stand-alone Workflows page.
   
   Qtip: To link this to a particular survey’s responses, we recommend starting in the Workflows tab of a survey.
2. Click Create a workflow.
3. Select Started when an event is received.
   Attention: You should not use a scheduled workflow with this task. You need a survey response event to establish a data source, that you can then pull from to save in your Google Sheets file.
4. Select the Survey response event with at least Newly created responses selected.
   
   Qtip: This will automatically add this survey’s data to your Google Sheets whenever a new response is collected.
5. If desired, click the plus sign ( + ) then Conditions to add conditions to your workflow, which determines when the workflow runs.
   Qtip: For more information on building conditions in workflows, see Creating Conditions.
6. Click the plus sign ( + ) and then Task.
7.  Select Google Sheets.
   
8. Decide whether you want to Add a row to a Google Sheet or Look up data in a Google Sheet and follow the steps in the corresponding section to finish setting up your task.
   • Adding a Row to a Google Sheet
   • Looking up Data in a Google Sheet

   

Adding a Row to a Google Sheet

Once you’ve created a new Google Sheets task, follow these steps to configure your task to add rows to a Google Sheet. This task is useful for adding survey data directly to a Google Sheet. New rows are added to the bottom of the sheet below existing rows.

1. Select Add a row to a Google Sheet.
   
2. Click Next.
3. Choose the Google account to add data to. Any accounts added by Brand Administrators, as well as accounts you’ve added yourself, will be listed here.
   
4. If you want to add new account credentials, click Add a user account and follow the steps in the connecting a Google Drive account section.
5. Click Next.
6. Select a Google Sheets file. You can search by name if the file doesn’t show up at the top of the list.
   
   Qtip: You can also select Create a new Google Sheet if you want to create a new Google Sheet to add your data to.
7. Select the sheet (tab) of the file you’d like to add data to. You can choose one of the following:
   • Create a new sheet in your file. If you select this option, you will need to name the new sheet.
     
   • Add the file to an existing sheet. Make sure the sheet already has headers inside it, and ensure there are no empty cells in the header row of the sheet.
     
     Qtip: Don’t see the changes made in a Google file reflected to Qualtrics yet? No problem. In Qualtrics, click Back in the lower-left of the window, then re-select the account you want to connect to. This will reload the data pulled from Google, so any new files or columns will appear.
8. Click Add Field to define another column of data in your Google Sheet.
9. Under Header, put the header of the column / name of the field. You will type the name of this column if you’re making a new sheet, and select from a list of existing columns if you’re using an existing sheet.
   
10. Under Data, enter the value for that field. We recommend using the piped text button ( {a} ) to dynamically link the respondents’ answers to the columns. See Tips for Mapping Piped Text to Columns.
11. When you’re finished adding all your fields, click Save.

Looking up Data in a Google Sheet

Once you’ve created a new Google Sheets task, follow these steps to configure your task to look up data in a Google Sheet. This task is useful for looking up data in Google Sheets that can be used in subsequent tasks.

1. Select Look up data in a Google Sheet.
   
2. Click Next.
3. Choose the Google account to add data to. Any accounts added by Brand Administrators, as well as accounts you’ve added yourself, will be listed here.
   
4. If you want to add new account credentials, click Add a user account and follow the steps in the connecting a Google Drive account section.
5. Click Next.
6. Select a Google Sheets file. You will be able to search it by name if it doesn’t show up at the top of the list.
   
7. Select the sheet (tab) of the file you’d like to search within.
8. Select the column to perform the search on.
9. Type in the value you want to search for. You can use the piped text button ( {a} ) to pipe a value from a survey field. See Tips for Mapping Piped Text to Columns.
10. Select a lookup column. After finding the row with the search key value, the value from this column in that row will be pulled.
    Qtip: If there are multiple rows in the column with the same key value, the first row with the key value will be selected.
11. Click Save.

Example: Customers that complete a feedback survey will be awarded a $5 gift card for their participation. Gift card codes are stored within a Google Sheet.

A Look up data in a Google Sheet task can be used to search for a respondent’s email within the Google Sheet every time a survey response is submitted.

The returned gift card code value can then be used in subsequent tasks, such as sending an email with the code directly to the respondent.

Compatibility of Formulas

Formulas are used in Google Sheets to transform data and make calculations. You can see a list of Google Sheets formulas in their documentation.

The Qualtrics Google Sheets extension is compatible with almost all formulas, except those listed below. If the Google Sheet you’re connecting to has one of these formulas, the formula will not execute; instead, an apostrophe ( ‘ ) will be added beforehand, and the formula used be be displayed. (E.g., ‘IMAGE)

Incompatible Formulas

This is the full list of formulas we do not support.

• IMPORTXML
• IMPORTFEED
• IMPORTHTML
• IMPORTRANGE
• IMAGE

Tips for Mapping Piped Text to Columns

For more information on piped text, see the linked support page.

• Piped text can be used to pull data from the following:
  • Survey questions
  • Embedded data fields
  • GeoIP location
  • Date and time
  • Panel fields
  • Quotas
  • Response ID (See “Response ID” column 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)
• When you’re adding the piped text for a question, the “Selected Choices” option will dynamically paste the answer the respondent selected into the field.
• When you’re adding the piped text for a question, the “Selected Choices – Recode” option will dynamically paste the numeric value of the answer the respondent selected. Make sure your recode values are set before you collect data, though – because you’re ingesting data into Google as it’s submitted to Qualtrics, any changes you make to recode values later will not be reflected in Google files.
• “Panel fields” refers to contact list information. This will only be relevant if you distributed your survey with a method that required a contact list. (Email, SMS, authenticator, personal links, etc.)
• You can type values in the fields instead of using piped text, but we don’t always recommend doing this, since that means every respondent will have the same value for that field. It could be useful if you are using conditions in your workflows to dynamically assign different values to different groups of respondents, but even then, it is often better to use logic and embedded data to assign values, and pipe that embedded data’s value into the Google Sheet; that way data is saved in the survey response and the Google Sheet, not just the Google Sheet.
  Example: In the image below, the field “Name” is an example of a field where everyone will get the exact same value, because information was typed into the field, instead of using piped text. “Treatment Group” will vary based on the embedded data that respondents have been assigned, because it is piped text for a given field.
  

FAQs