January 9, 2021

How to Integrate Google Sheets with Google Forms [Step-by-Step Tutorial]

Chart with arrows pointing up and down
SECTIONS
  1. Store the results of an existing Google Form in a Google Sheet
  2. Generate a Google Form from a Google Sheet

Did you know that you can integrate Google Forms with Google Sheets?

If you still don’t know, you may wonder, what for?

... therefore, this tutorial will explain how to integrate Google Sheets with Google Forms. You can either store the results of existing Google Forms to a spreadsheet or generate a form from an existing Google Sheet. 

Store the results of an existing Google Form in a Google Sheet

There are good reasons to store the results of an existing Google Forms online form to a spreadsheet. Two of them are as follows:

  1. Redundancy and backup - the form results are stored in a good file format that can be easily read by a user.
  2. Easier to analyze - the form results, once stored in a spreadsheet format, can easily be analyzed using the tools of the spreadsheet software. 

Therefore, you should consider storing a copy of the results to a Google Sheets spreadsheet.

But how? Here are the steps:

Step 1: On your form, click the Responses tab.

Step 2: On the Responses tab, there is a small icon for Google Sheets on the upper-right portion of the list. Click it. 

The Create Spreadsheet button on the Responses tab in the Google Forms online form. 
The Create Spreadsheet button on the Responses tab in the Google Forms online form. 

A spreadsheet containing the responses will be generated. Each response forms a row, while each question in the form gets its own column.

Yes, it's that simple!

Generate a Google Forms online form from a spreadsheet

Now let us consider the reverse: generating a form from an existing spreadsheet. To do so, here are the steps:

Step 1. Click Tools, then Create a form.

Tools option in the main menu, Create form highlighted in the drop-down box. 
Tools option in the main menu, Create form highlighted in the drop-down box. 

Step 2. The blank form will pop up, where you can add questions of various forms. Google Forms offers several types of questions, such as the following:

  • Short answers - used for collecting short written responses, typically no more than just a few words. It can be validated to accept only certain kinds of responses.
  • Paragraphs - allows for longer answers with virtually no length limit. It can also use Response Validation to enforce a minimum or maximum character count. 
  • Multiple choice - offers a number of options, of which you can choose only one. It offers the ability to randomly shuffle the options.
  • Checkboxes -  behaves in a similar way to a Multiple Choice question except it can allow more than one option to be selected.
  • Drop-down - suited for questions where there is a long list of choices.
  • File upload - useful when requiring documents or proofs.
  • Linear scale - best suited for measuring sentiment or feelings about an idea. 
  • Multiple choice grid -  very useful type of question and can be used to measure impressions, opinions, or sentiment.
  • Checkbox grid -  works in a similar way to the Multiple Choice Grid question type, but in this one, you can select more than one choice per row. 
  • Date - using this question type ensures the collected dates will be shown in proper date format in the underlying spreadsheet.
  • Time - can be set to collect either a time or a duration. 

To help you visualize each type of question, you can check a sample form here.

Step 3. To select the question type, click the drop-down box on the right side of the box containing the question. 

The blank Google Form. The drop-down box for selecting the question type is highlighted. 
The blank Google Form. The drop-down box for selecting the question type is highlighted. 

Step 4a. Afterward, type the question on the space marked as Untitled Question. (Note: if the question you are adding is a commonly-asked question, Google Forms automatically selects the best format for it!)

Step 4b. When you select a format where you need to specify the choices, spaces will appear for it, labeled as Option #. At the end of the list, another space labeled Add option will appear. 

The same blank Google form, but with the multiple-choice type selected. 
The same blank Google form, but with the multiple-choice type selected. 

Step 5a. Once you are done, you can add another question by clicking the circle icon with a plus sign on the floating bar on the right side of the box containing the question.

The same blank Google form, but with the additional question icon highlighted. 
The same blank Google form, but with the additional question icon highlighted. 

Step 5b. If you change your mind, you can remove the question by clicking the trash can symbol on the lower part of the box.

The same blank Google form, but with the delete question icon highlighted. 
The same blank Google form, but with the delete question icon highlighted. 

Step 6. Repeat step 5 as needed until you complete the form. When you go back to the connected spreadsheet, columns will appear containing the questions you have added. 

the connected spreadsheet, with the questions forming the top fixed row. 
The connected spreadsheet, with the questions forming the top fixed row. 

You can now deploy your Google Forms online form for gathering survey information!

Suscribe to get more data and analytics tips!

Thank you! Your submission has been received!
Oops! Something went wrong while submitting the form.