Grapevine Surveys and Google Sheets
If you're looking to slice and dice your zero party data for further analysis without the need to log in to the Grapevine Surveys app, then we have a workflow that will help you to gain the insight you need, your way.
Please Note:
This article is aimed at merchants/developers who are familiar with Shopify Flow and Google Sheets.
Overview
This article will walk you through setting up a Shopify Flow workflow to populate a Google Sheet with your survey responses. The workflow will be triggered using the Grapevine Surveys Question Answered trigger (read more about Grapevine Surveys Shopify Flow triggers here).
All Shopify Plans have access to Shopify Flow and Grapevine Surveys Shopify Flow triggers.
There are many ways to send your data into Google Sheets, but for the purpose of this article, we will use a survey with a single question... "How did you hear about us?". We'll push each survey response into a new row within the same Google Sheet.
What you do with the data from there is down to your expertise with a spreadsheet and the insights you're looking to extract. Let's get into it.
Quick links
- Requirements
- Configuring the Google Sheet
- Creating the Shopify Flow Workflow
- Turning on the Workflow
- Testing the Workflow
- Download and amend a template .flow file
- Would you like us to set this up for you?
- Need any help?
Requirements
- You will need the Grapevine Surveys app installed in your store
- You will need to have published at least one survey within Grapevine Surveys
- You will need the unique survey code of your published survey
- You will need the Shopify Flow app installed
- You will need to have installed/enabled the Google Sheets extension within Shopify Flow
- You will need the URL of your Google Sheet
Configuring the Google Sheet
We'll keep things simple to show you how this feature works. Within your Google Sheet, add these column headers:
- Date
- Respondent Email
- Respondent Name
- Surface
- Answer
- Customer ID
- Order ID
- Order Name
- Order Total
You can name the columns whatever you prefer, and include/exclude columns as you choose, it's not important, but your Google Sheet should look something similar to this:
Leave the Sheet Name as Sheet1. If you do decide to rename it, then please remember this when configuring the Add Row Google Sheets action within your Shopify Flow workflow.
You will also need the URL of your Google Sheet.
Creating the Shopify Flow Workflow
Go to the Shopify Flow app within Shopify Admin and create a new workflow.
Begin by selecting a trigger. As long as Grapevine Surveys is installed in your store, you will see it in the right-hand pane of the Workflow editor:
Click on the Grapevine Surveys app and then select the Question answered trigger:
This trigger will then be added to the main Workflow editor pane:
Click the + icon on the trigger:
Select the Condition option:
Wait for the page to refresh and then select Add a variable from the Condition settings in right-hand pane of the Workflow editor:
The right-hand pane of the Workflow editor will refresh and you will be presented with all of the variables that are available within the Question answered trigger.
Select the surveycode variable:
The right-hand pane of the Workflow editor will refresh and a Surveycode text input field will be displayed:
Enter your unique survey code into the Surveycode text input field:
This will ensure that only answers to a specific survey will be pushed into your Google Sheet. This is important as you may have more than one survey live at any one time.
Click anywhere on the the main workflow pane to hide the right-hand pane of the Workflow editor.
For the next step, you're going to nee the URL of your Google Sheet.
Please Note:
You may run into issues if the Shopify admin account you are using to create the Workflow does not also have edit permissions to the Google Sheet you are trying to send the survey responses into. The user that authenticated the Google Sheets action originally needs to have edit access to the Google Sheet being populated.
Click on the Then option of the Check If condition and select Action:
The available actions will appear in the right-hand pane of the Workflow editor:
Click on the Clear all link to clear the "Installed apps" filter:
Type "Sheets" into the search bar and as you type, you will see the option for the "Add row to spreadsheet - Google Sheets for Shopify Flow" action. Select that option:
The Workflow editor will refresh and you will see the Add row to spreadsheet Action in the main Workflow editor. If you have not previously used the Add row to spreadsheet Action, then an error message will appear in the right-hand pane of the Workflow editor:
You will now need to install the Google Sheets for Shopify Flow app.
Installing the Google Sheets for Shopify Flow app
Select the Install button underneath the error message in the right-hand pane of the Workflow editor:
A new tab will open in your browser asking you to sign in with your Google account:
Follow the prompts to authorize Shopify Flow to access your Google Account.
You should arrive at a confirmation screen stating that Google Sheets for Flow has been connected:
Close that browser tab and return to the tab with your Shopify Workflow.
Refresh the page and you will see that the error in the right-hand pane is no longer showing:
It's now time to configure the action.
Configuring the add row to spreadsheet action
You will now need the URL of your Google Sheet.
Paste it into the Spreadsheet URL setting field:
You now need to add the name of the Tab/Sheet into the Tab name setting field. In our example spreadsheet, the tab name is Sheet1, but please check your spreadsheet for the correct name:
Now we get to the interesting bit, defining the survey response data that you'd like to add to your spreadsheet.
Configuring the Row contents
Based on the columns you have created in your Google Spreadsheet, you will now need to add in the corresponding variable that has been sent in the Question answered trigger.
You can see all of the available variables by selecting the Add a variable link at the bottom right of the Row contents setting field:
Clicking on the Add a variable link will refresh the right-hand pane and will show each variable that can be selected. If you select one of those variables, e.g. answer
...the right-hand pane will refresh again and the variable will be added to the Row contents setting field:
Please Note:
The Row contents setting field can parse liquid, therefore, if you need to make any adjustments to the variable values sent across in the trigger, it is possible to manipulate them using liquid.
The variables that you add into the Row contents setting field must be in the same order as the columns that you have specified in your Google Sheet. Based on our Google sheet example, these are the variables that we will include in our action:
- Date: {{ "now" | date: "%Y-%m-%d %H:%M" }}
- Respondent Email: {{customeremail}}
- Respondent Name: {{customername}}
- Surface: {{surface}}
- Answer: {{answer | replace: ",", ";"}}
- Customer ID: {{customerid}}
- Order ID: {{orderid}}
- Order Name: {{ordername}}
- Order Total: {{ordertotal}}
As you can see, the Date variable above is not a variable contained within the trigger, but it is using a liquid variable to output the date and time in YYYY-MM-DD HH:MM format
As each column of data needs to be separated with a comma, we will use the replace liquid filter to replace any commas found in the survey answer with a semicolon.
Here is the full comma separated survey response data that we will enter into the Row contents setting field:
{{ "now" | date: "%Y-%m-%d %H:%M" }},{{customeremail}},{{customername}},{{surface}},{{answer | replace: ",", ";"}},{{customerid}},{{orderid}},{{ordername}},{{ordertotal}}
And this is how the code looks within the Row contents setting field within the Workflow editor:
Once you have added your own data into the Row contents setting field, give your workflow a name that explains what the workflow does:
That's it! You have configured your Workflow. Only one thing left to do now!
Turning on the Workflow
Select the Turn on workflow button from the top right of the Workflow editor:
If you're ready, select the Turn on button from the dialogue confirmation window:
That's it, your workflow is now live!
The Workflow can be turned off at any point from within the Shopify Flow app by clicking on the Workflow and then selecting Turn off workflow from the top right hand side of the page:
Last thing to do now is to test it out!
Testing the Workflow
To test the workflow, you will need to submit a response to your survey. If you have defined order ID and customer ID columns in your Google Sheet, then you will either need to place a test order on your store to access the survey widget on the thank you page / order status page, or, you can visit the order status page of a previous order.
Regardless of how you choose to access your survey, submit a response.
Within Shopify Flow, visit the workflow you have just created and in the Recent runs section at the bottom of the workflow, it should show that the workflow has been run:
Now check in your Google Sheet and all being well, you should have your first survey response:
If you are having any issues getting this to work, then please do get in touch with our support team who may need access to your store and your Google Sheet.
Download and amend a template .flow file
If you'd like a starting point for your Shopify Flow Workflow, the example we have put together in this article is available for download as a .flow file at the bottom of this page.
You will need to import this file into Shopify Flow and make changes to the surveycode and the Google Sheet URL. You may also need to make changes to the Row contents depending on the fields you have configured in your Google Sheet.
Would you like us to set this up for you?
If you're not too keen on setting this up yourself, then we'd be more than happy to do it for you.
We will need to request collaborator access to your store and access to your Google Sheet (we will let you know the email address that requires access).
We will also need to know the survey response data that you would like to include into your Google Sheet. There are many different ways to structure the data into a Google Sheet, so please provide as much information as you can to help us set this up for you.