Export data from Google Sheets to Google Form

In this article we explore a method to export data from Google Sheets in Google Forms. It is not the type of posts we usually do but as we faced that issue recently and we didn't find a full solution on Google we decided to share our findings.
We will describe a step by step approach using Google Sheets, Google Script and Google Form. So let's start.

Use cases

Copying data from a Google Sheets document to a Google Form is particularly useful if:

• You want to create a form that add data to an existing set of results
• You want to copy data from one Form to another
• You want to merge data from several forms
• You "forgot" to connect a Google Form to Google Sheets and want to restart from a CSV export of the data of the origin Form

Create a Google Form

The first step is to create a Google Form. To do so go to https://docs.google.com/forms and create a form.

 

Our form contains 2 questions: 

• a short text question: What is your name
• a checkbox question: What do you like to eat

For this tutorial we will create only those 2 questions.

Link the form to a Google sheet

This part is not mandatory but it will ease the definition of the Google Sheet we will use to input the data. Open the tab "Responses" and click on the Google Sheets icon.

Just select "Create a new spreadsheet". Validate.

A new sheet has been created similar to the illustration below:

This sheet will store a copy of all the responses to your newly created form.

Create the data source

Now we have a Google Form connected to a Google Sheet. But we are still missing the source data. Then duplicate the newly created Google Sheet. To do so click on the little triangle beside the name of the sheet to open the sheet menu and click on "Duplicate".

Rename your sheet "Data" by just clicking on the name of the new sheet (which should be something like "Copy of Form Response 1").

Now you have 2 empty sheets in your document:

• Form Responses 1 will store the responses of your Form
• Data will input the data to your Form

You can now fill some data as illustrated below:

 

Important! When entering checkboxes results, you need to use exactly the same spelling than used to define the form. It has to be a comma separated list of words.

Create the Google Script to import data

We will create a simple Google script that will read the data from the "Data" sheet and will submit the responses to Google Form.

First open the script editor (in Google Sheets, menu tools/script editor).

Remove the default myFunction (well it is not mandatory but it will make things clearer).

Then add the following function:

// This function creates form responses for the provided form // Note: the columns have to be in the same order as the questions, the first column being a timestamp column (not used) function fillFormResponses(){ var startRow = 2; // Define the starting row with the data to fill the form with in the data range (to avoid headers) var formToFillURL = 'https://docs.google.com/forms/d/YOUR-FORM-ID/viewform'; // the URL of the form (replace the ID) var dataSheetName = 'Data'; // the sheet containing the data to add to the form var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(dataSheetName); var data = sheet.getDataRange().getValues(); var form = FormApp.openByUrl(formToFillURL); // Get the items of the form var items = form.getItems(); for(var i = startRow; i < data.length; i++) { var formResponse = form.createResponse(); for (var j = 0; j < items.length; j++){ // the first column is not used, considered as a timestamp (useful when you copy the data from 1 form to another). // get the value of the cell var val = data[i][j+1]; if (val != ''){ // get the type of the question var type = items[j].getType(); // we cast to the type of the question var qt; var qr; if (type == FormApp.ItemType.TEXT){ qt = items[j].asTextItem(); qr = qt.createResponse(val); }else if (type == FormApp.ItemType.CHECKBOX){ qt = items[j].asCheckboxItem(); qr = qt.createResponse(val.split(', ')); }else if (type == FormApp.ItemType.PARAGRAPH_TEXT){ qt = items[j].asParagraphTextItem(); qr = qt.createResponse(val); }else if (type == FormApp.ItemType.LIST){ qt = items[j].asListItem(); qr = qt.createResponse(val); } // Add other types to process when needed if (qr) formResponse.withItemResponse( qr ); } } formResponse.submit(); } }

There are a few things to pay attention of:

• replace YOUR-FORM-ID by the ID of the form (you will find it in the URL of the page when you edit your form)
• change the startRow value to fit your needs (here we start on the second line)
• verify that the name of your sheet is Data. If not change the variable accordingly.

Import your data

Just click on the "Run" button of the script editor.

Go to grab a coffee while it runs and tada! You should see all the data populated in the "Form Responses 1" sheet, which means the data has been added to your Google Form.

Limitations

The code which has been provided handles only a few types of questions:

• short text: typically a single line of text
• paragraphs: several lines of text
• checkboxes: multiple selection of responses
• list: single answer from a list of potential answers

Also there is no error handling, meaning it doesn't deal with exceptions. We let that to other people ;-)

References

As usual we used a few references and we want to thank the people who helped us discover the potential of Google scripts:

• Google of course. Our main reference is https://developers.google.com/apps-script/reference/forms/form. We also have a request: please Google, could you add an API to modify responses?
• And a very simple question/answer which simplified our life a lot: https://stackoverflow.com/questions/36831901/how-to-create-a-google-form-response-with-app-script

We hope this tutorial has been helpful for you!