Create Dynamic Form Questions with Google Sheets

Is your team interested in creating dynamic form questions that allow you provide unique answer values for a target group?

Example 1: Your organization hosts events that provide meals for attendees. Each event has a different meal choice, and some events may have a charge associated with the meal choices.

Example 2: Your organization asks for areas of interest for newsletters to send out to your donors. However, you have different forms for different sections of your organization and you need to provide different areas of interest depending on the form or the target group accessing a form.

Adding individual, unique form questions to each form takes a lot of time and manual effort on your part. Instead with our Google Sheets integration, you can create questions that can be applied to multiple forms but have remotely defined answer values that you pass through with a URL parameter. This can be used in combination with remotely defined Donation/Registration Levels.

Note: For form questions, Google Sheets can only be used to remotely define MCSR (multiple choice/single response) and MCMR (multiple choice/multiple response) question type answers.

Implementing Google Sheets for Form Questions

Form Question Default Answers

With your question configuration menu, we strongly recommend that you set default answers within the configuration directly. This will be useful in cases when the data from Google cannot be fetched.

Example of default values configured on the form (typical form question):

Enabling Google Sheets for Remotely Defined Question Answers

Google Sheets can be used for Global and Local Questions, along with Question Templates. If a question is going to be applied to multiple forms, we advise making the question a Global Question.

Note: The instructions provided below will show you how to enable remotely defined answers for a Global Question; however, the process is the same within the configuration menu for a Local Question or Question Template.

To enable google sheets on your form to produce remotely defined question answers, you'll need to follow the below steps:

1. Go to the Library tab and click on Global Questions

2. Click on the "+ADD NEW" button

   

3. Select either Single Response or Multiple Response from the Question Type dropdown

   

4. Configure your question with the default answer choices

   1. Name is the stored value for the question/the column your would add to reports

   2. Label is how the question is presented for the donor/registrant

   3. After the rest of the options are set, provide your default options within the Choices menu

      

5. Now, to implement remotely defined question answers, select "Yes" for Remotely Defined

   

6. Then select "Yes" for Use Google Sheet

   

7. You'll need to provide a Remote URL, which will need to be generated via the Google Sheet

   

8. Within the Google Sheet you've created for your remotely defined levels, click the "File" button

   

9. Click the "Share" option

   

10. Then click the "Publish to web" option

    

11. Select the first dropdown, which defaults to the Entire Document

    

12. Then select the name of the tab that contains the levels you want to use for your form

    

13. Then click on the second dropdown defaulted to Web page

    

14. Select the Comma-separated valued (.csv) format

    

15. Finally, to generate the necessary link click the green Publish button and click "OK" for the Google Drive window that pops up asking if you want to publish that section of the document

    

16. That should generate the Remotely Defined URL for your form, but also make sure that under "Publishing content & settings" you are not restricting access to the document

    

17. Back in the platform with Remotely Enabled and Use Google Sheet set to "Yes," paste the link provided via the Google Sheet into the Remote URL box and click "save"

    

18. Now if you go in to edit the question, you should see a caption that states "Prompts are provided by a remote URL. These are defaults only."

    

Using Your Remotely Defined Levels

Now that the set up for your remotely defined question answers is complete, you can now use your answers from your Google Sheet.

To use the Google Sheet answers, you will need to use the URL parameter of urlData=code. The code here is the code defined in your Google Sheet (column B). You can target multiple code groups by adding each code to the parameter separated by a comma: urlData=code1,code2.

By default, when you access the form without urlData= or the code defined, the default answers from the question configuration should populate:

Now for the example above, I want to target the specific "mp1" code group from my example Google Sheet. I will append the URL of where my form is embedded with urlData=mp1. So the full URL for this example would be:
​

https://ashleighenterprisetest.weebly.com/gsheets-registration-test.html?urlData=mp1

With the URL parameter present with the code defined, we can see the question answers update on the form:

The code group with a List Price provided for answers:

MCMR Example:

Default:

URL Parameter defined:

That is how you enable and use Google Sheets for remotely defined MCSR and MCMR question answers.

If you have any questions about this integration, please reach out to our team via the blue chat bubble or email us at [e-mail].