Using the Google Spreadsheet integration you can push realtime data from your survey into a Google Spreadsheet. It's quick and allows you the flexibility of sharing the document with anyone you want!
When is Google Spreadsheet not the best solution for my data?
A few Alchemer features are not compatible with the integration, due to limitations in the Google API, so if you are using the following question types or features, we do not recommend using this action:
- Piping (page, question, answer, etc.)
- Custom Tables
- Custom Group: Add As Needed (as it is also a form of Piping)
- Max Diff
- Conjoint (Choice Based)
A few quick notes before you get started:
How does it work?
- We pass the Reporting Values, not the answer options, to the spreadsheet.
- This action also works with Hidden Values and Quiz Scores.
- The integration still works even if all of your columns aren't being updated.
- Most Custom Questions should work, as long as they're meant to pass data.
What can it do?
- You can have multiple Google Spreadsheet actions pushing FROM the same survey.
- You can also link the same spreadsheet to multiple actions.
- The action works in Preview, allowing you to to test the integration without collecting a live response. If you delete answers created in the Preview from the spreadsheet, the integration just starts back at the top!
- File Upload questions pass a link to the file into the spreadsheet. You can only pass a single link to a single file. If you need to pass links to multiple files, you will need to have a File Upload question for each file.
What won't it do?
- This action will NOT create a new column in the spreadsheet; it will only update existing columns.
- At this time the Google Spreadsheet action cannot pass any special characters and punctuation e.g. .,*$@# in column headers.
- At this time, the google spreadsheet action cannot pass values to a column name that contains special characters. (e.g. .,*$@#, etc.)
Connecting Your Google and Alchemer Accounts
If you don't have Account Administrator access in Alchemer, you will need to reach out to an account admin to set up the Google Sheets Integration within Alchemer.
The Google Sheets Integration supports one Google Account at a time, account-wide.
First things first, create a spreadsheet in the Google account you're going to link up. Then we're ready to link your Google account to Alchemer!
- Click Integrations > Data Connectors >Google Sheets.
- Click Configure next to the Google Sheets integration found under the Professional Level Integrations and then click the Link a Google Drive Account button. You will be taken to a Google sign-in page and then asked to accept our request for permissions to your Google account. Once it's set up properly, you should see a little green check mark under the Status column if your account is properly linked.
When editing an existing action, users are asked to re-configure the google account and are provided a link back to the integrations page to do so. If one is unable to reconfigure, please contact the administrator on the account for assistance.
If you need to use a different account, return to Integrations > Data Connectors and click Unlink next to the email address. Then follow the steps above to link a new address.
Add and Set Up the Google Spreadsheet Action
Now that you're all synced up, it's time to add the action to your survey. Our suggestion for your workflow is to build out the entire survey first, then add a new spreadsheet in Google and name all the columns for the questions you plan on passing. Then come back to Alchemer to add the Google Spreadsheet Action. Following these steps will save you time and allow you to utilize a feature in step 4!
- Click Add Logic / Action on the page of your survey where you want to add the action.
- Choose Google Spreadsheet from the list of Integrations. Give your action a title and click Save Action and Edit. (If you see an Uh Oh error or a blank page, visit the Troubleshooting steps below for help.)
- Choose your spreadsheet from the Spreadsheet dropdown menu. Then select the Worksheet you want to populate.The dropdown menu will only display your first 100 worksheets. Users are not able to see or select any additional worksheets over that 100 sheet limit.
- Select a Question to Map from the dropdown menu, then click Add Field. Fill in your Google Column Name, which must be EXACTLY the same as the Column Name in your spreadsheet.
Continue adding your questions, and then click Save once you're done. - (optional): You can also pass a fixed Default Value by filling in the Default Value field next to the Google Column Name.
(To pass information from Other or Comment textboxes, you'll need to select them in addition to their radio or checkbox questions and ensure that you have a separate column in your spreadsheet.)
- Run this action: This option will be set to Run when page is displayed to survey taker by default. This is the recommended setting when you want to send your survey data to Google Sheets at the end of the survey (after the respondent clicks Submit). In this case, the Google Sheets action should be placed on the Thank You Page, or another terminal page.
You can also customize the action to Run when the page is submitted. If you change it to Run when page is submitted it must be on a page prior to the Thank You page, or other terminal page.
Please note that a page is submitted when a respondent performs any of the following actions:- the Next, Back, or Submit buttons are clicked
- the Save and Continue buttons are clicked.
How should it look once it's added?
Awesome, you've got it all set up! Here's how it should look and how the data will report in your spreadsheet. Here is what the action looks like in Alchemer:
Here is how the data will look once it's pushed to your spreadsheet (you can see that the order of the Fields to Post is not important, as long as the Google Column names match exactly):
FAQ
Does the Google Sheets Integration use the secure (SSL) protocol to send data?
It does! The integration uses the Google Sheets API which use the secure protocol.
Is there a limit of the number of fields that can be passed?
Yes, at this time you cannot pass more than 100 fields to Google.
Can I pass multi-select question data (checkboxes) into a single column in Google Sheets?
At this time, each answer of a checkbox question (or other multi-select question) must be mapped to its own column in Google Sheets. Mapping all multi-select answers to a single column will result in only one answer being successfully passed (even if multiple answers were selected).
Troubleshooting
There are a few common issues that may arise when using the Google Sheets integration.
My accounts are not linking.
If you have a new Google account without an existing spreadsheet, it will appear that the accounts aren't linked. Add one and you should be good to go!
The Google Integration Action shows up blank when added to the survey.
If you see a blank screen, an Uh Oh message, a blue bar, or any other type of error, this typically means authentication to your Google Account has failed. The can happen for several reasons: you changed your password, you updated to Google's 2-Factor Authentication, etc. To fix this follow these steps:
- Go to Integrations > Data Connectors within Alchemer and delete the Google Sheets Integration.
- Go to My Account within Google and find the option for Connected apps & sites. Click Manage Apps and find Alchemer and click the Remove option. Learn more about Managing Connected Accounts within Google .
- Now re-add your Google Sheets Integration under Integrations > Data Connectors within Alchemer.
Nothing is being passed into my Spreadsheet!
There are a few possible culprits:
- Do the Google Column Names match up EXACTLY with the names of the columns in your spreadsheet? They must be the same! Something so small as an extra space or punctuation can make the Action not pass data. For best results, we recommend having column names without punctuation at all.
- What types of questions are you using? Remember that not all question types are compatible. If you are using Piping, Custom Groups or Custom Tables, this isn't the action for you.
- Check the Run this action setting. By default the Google Spreadsheet action is set to run when the page is displayed to the respondent. This is the required setting of the Google Spreadsheet action if it is on the Thank You page. However, if you are collecting data on the page with the Google Spreadsheet action you'll want to toggle this to Run when page is submitted so that the data from the current page is sent.
- Do your column headers begin with numeric values? After extensive (and frustrating) testing, we've discovered that column headers that have numeric values at the beginning will cause no data to pass.
- Do your column headers contain special characters? The other thing we've found can make this integration to malfunction is the use of special characters and punctuation e.g. .,*$@# in column headers.
- Test data generated on the Test tab will not push to your spreadsheet. Go ahead and submit a response using a link from the Share tab to ensure the action is working.
- Is the cell you want to pass data into selected? We can not pass data into columns where the header cell is selected, or into a cell of a row that is selected. Keep this in mind as you and your colleagues are viewing the spreadsheet when your survey is open and responses are being collected.
- Finally, Google Sheets will throttle our data pushes if it detects a large amount of traffic. This can result in delays of data being pushed to your spreadsheet up to a couple of hours. Learn more about Google Sheets API limits.
Have you tried all of the above troubleshooting steps and are still not seeing anything passed into your spreadsheet? Contact our Support Team for assistance.
My File Upload question shows only half of a link to an uploaded file!
As seen in the image below, there are 5 files that have full links to their uploaded files and two that do not.
If you're seeing incomplete links, that means your File Upload question is not required and that respondent chose not to upload a file. If you require the question, respondents will not be able to proceed without uploading a file.
I deleted my Google Integration in Alchemer and received the following notification, "We were unable to completely Unlink your Google account. How do I fix it?":
While in most cases deleting your Google Integration in Alchemer will successfully unlink the accounts, older integrations may require an extra step.
If you see the above We were unable to complete unlink your Google account message, you will need to manually remove Alchemer as a connected app within your Google Account.
To do so, go to My Account within Google and find the option for Connected apps & sites. Click Manage Apps and find Alchemer and click the Remove option. Learn more about Managing Connected Accounts within Google .
Seeing an alert that your account is linked to Google Drive using an old authentication method? Don't worry!
Google updated their API to v4 and in order to provide continued access, we need to have anyone using this feature re-authenticate their login credentials. It will only take a moment and ensures that your account will continue to work well into the future. They made these updates recently and the previous Google Sheets API will be deprecated September 20th, 2020. Want to know more about Google sheets API? Google has a nice write up here!
Existing actions will continue to work until April 20th but it's best to just go ahead and quickly re-authenticate when you can.
