Google Sheets Task

Suite
Customer Experience Employee Experience Strategy & Research
Product
Qualtrics
What's on this page

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.

Qtip: This feature is available to any user with access to workflows.
Attention: This task has a limit of 60 calls per minute per user. If you exceed this limit, the task will fail and will be automatically retried.

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.

Screenshot of a google sheet that has columns for stuff like StartDate, Overall Rating, Customer Effort Score, Contact Frequency, and so on.
Screenshot of a google sheet that has columns for stuff like StartDate, Overall Rating, Customer Effort Score, Contact Frequency, and so on.

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.

Using the navigation menu in the top left, go to Admin.
Admin button in the global navigation
Admin button in the global navigation
Go to Extensions.
navigating to the extensions section, and then choosing google sheets
navigating to the extensions section, and then choosing google sheets
Click the Google Sheets extension.
Click Add Account.
clicking add account
clicking add account
Give the account a name. This name is used for organizational purposes and to identify the account.
giving the account a name and clicking "connect account"
giving the account a name and clicking "connect account"
Click Connect Account.
Proceed to log into your Google Drive account.
Screenshot of the google login screen, but with the Qualtrics xm logo on top
Screenshot of the google login screen, but with the Qualtrics xm logo on top

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.

Follow the steps in Creating a Google Sheets Task section until you get to the step where you select an account to use.
Click Add user account.
Image of the "Choose account" window while setting up the task - button for adding the account is in the upper-right
Image of the "Choose account" window while setting up the task - button for adding the account is in the upper-right
Give your account a name. This name is used for organizational purposes and to identify the account.
adding a new account and giving it a name
adding a new account and giving it a name
Click Connect account.
Proceed to log into your Google Drive account.
Screenshot of the google login screen, but with the Qualtrics xm logo on top
Screenshot of the google login screen, but with the Qualtrics xm logo on top

Creating a Google Sheets Task

Navigate to the Workflows tab of a project, or the stand-alone Workflows page.
in the workflows tab, clicking create a workflow and choosing the type of workflow
in the workflows tab, clicking create a workflow and choosing the type of workflow
Qtip: To link this to a particular survey’s responses, we recommend starting in the Workflows tab of a survey.
Click Create a workflow.
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.
Select the Survey response event with at least Newly created responses selected.
choosing the survey event, adding conditions, and choosing the task
choosing the survey event, adding conditions, and choosing the task
Qtip: This will automatically add this survey’s data to your Google Sheets whenever a new response is collected.
If desired, click the plus sign ( + ) then Add a condition to add conditions to your workflow, which determines when the workflow runs.
Qtip: For more information on building conditions in workflows, see Creating Conditions.
Click the plus sign ( + ) and then Add a task.
 Select Google Sheets.
choosing the Google Sheets task
choosing the Google Sheets task
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.

selecting a google sheets task
selecting a google sheets task

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.

Qtip: Any date / time data you pull from your survey will be in Mountain Standard Time (MST-7). This is regardless of your account time zone settings.
Attention: Qualtrics recommends the Google sheet (including all tabs) contain less than 1 million cells of data. You may experience extended wait times and potential errors when adding rows to a sheet that exceeds this limit.
Select Add a row to a Google Sheet.
selecting "add a row to a google sheet" option
selecting "add a row to a google sheet" option
Click Next.
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.
selecting a user account
selecting a user account
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.
Click Next.
Select a Google Sheets file. You can search by name if the file doesn’t show up at the top of the list.
selecting a google sheets file and configuring data
selecting a google sheets file and configuring data
Qtip: You can also select Create a new Google Sheet if you want to create a new Google Sheet to add your data to.
create a new google sheet option
create a new google sheet option
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.
    creating a new sheet in your file
    creating a new sheet in your file
  • 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.
    adding the file to an existing sheet
    adding the file to an existing 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.
Click Add Field to define another column of data in your Google Sheet.
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.
entering data to add to your google sheet
entering data to add to your google sheet
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.
When you’re finished adding all your fields, click Save.
Qtip: When you add rows to a Google sheet with many formulas, data support may be limited due to ongoing formula recalculations. Consider minimizing formulas or using a raw data sheet for your workflow.

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.

Select Look up data in a Google Sheet.
"look up data in a google sheet" option when setting up a google sheets task
"look up data in a google sheet" option when setting up a google sheets task
Click Next.
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.
selecting a user account
selecting a user account
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.
Click Next.
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.
selecting data to use from the google sheet you selected
selecting data to use from the google sheet you selected
Select the sheet (tab) of the file you’d like to search within.
Select the column to perform the search on.
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.
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.
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.
example spreadsheet with email, state, and gift card code
example spreadsheet with email, state, and gift card code

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.
example google sheets task to look up gift card codes
example google sheets task to look up gift card codes

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:
  • 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.
    Image of a google sheet task filled out as described
    Image of a google sheet task filled out as described

FAQs

How can I tell the difference between first party and third party extensions? Where do I go for support for either kind?

How do I add or remove an extension from my Qualtrics account?

The survey response dates in this workflow aren't displaying in my time zone. Is that intended?

Was this helpful?

The feedback you submit here is used only to help improve this page.

That's great! Thank you for your feedback!

Thank you for your feedback!