Community Surveys v5.7 & later versions support Google Sheets integration. When a survey is integrated with Google Sheets, the user responses are automatically synced in real-time with the selected Google Sheet. You can export the Google Sheet to an Excel file or create visualizations. So here is the process to integrate your surveys with Google Sheets.
Step 1: Create Google Credentials App
The first step is to create an integration app with Google Cloud Console. If you have not done it already, get yourself an OAuth 2.0 Credentials app created at https://console.developers.google.com/apis/credentials. You need to enter a callback URI when you are creating the OAuth credentials. The callback URI is based on your survey URL. e.g. https://yoursite/surveys-menu?task=sheets.integrate
Here’s a simpler version of the steps to set up your Google app credentials on your website:
- First, sign in to your Google account by going to this page: Google APIs Credentials page
- Next, you’ll need to make a new app. Look for a button that says “Create Credentials” and click on it.
- Fill out some details about your app. You’ll add the name of your application and tell Google where it can send information after someone uses your app. This might look something like
https://yoursitename/surveys-menu?task=sheets.integrate– but it will depend on your survey listing menu alias. In the above URL, “surveys-menu” is the alias name of the “Surveys Listing” menu item. - Finally, get your app’s secret details. After you’ve made the app, click on its name in the list to see a page with various pieces of information. You’ll want to find and keep a copy of the credentials – they’re like a secret code that lets your website talk to Google.
Step 2: Update Google credentials in the Community Surveys options
Now the next step is to copy and add your OAuth Client ID and Client Secret values in the Community Surveys options. Go to Components -> Community Surveys -> Click on the Options button on the toolbar -> Click on the Integrations tab -> Enable the Google Sheets Integration option and add your OAuth details here.
Your site is enabled for Google Sheets integration and you can authorize Google Sheets with your surveys.
Step 3: Connect your survey with a Google Sheet
Go to the Edit Questions page of your survey and click on the Sheets button. This will take you to your Google login page, authenticate with your GMail ID and you are good to go. If you are already logged in, it will automatically enable the integration when you click on this button.
A new sheet is created at your Google Sheets page https://sheets.google.com/. When a user responds to your survey, a new row is added to the sheet.
That’s all, your survey is connected to Google Sheets.