I have some Google Forms and would like to import legacy survey data into the responses spreadsheet, so as to benefit from the data presentation features (having the combined data set) but I've been unable to make it work.

I did import the data but the rows after the last "native" response just get ignored.

It looks like there is some hidden information about where the last response row is in the responses spreadsheet.

7 Answers
7

The spreadsheet is not the actual repository of responses. There is a 'bucket' of responses that is connected to the form itself.

The data presentation features use the responses connected to the form.

You need to use a script to add the extra responses to the Form object. You won't be able to save the original dates with the old responses, as the timestamp is always when you add a response to the Form.

When the response is saved, it is saved to the form and copied to the spreadsheet.

From a different answer (relevant code attached) - you can use something like the test submission technique I used to populate the form with the responses. You would have to get your answers from the spreadsheet, and use the appropriate item type.

Here is the solution that I was able to use to resolve a similar need to import historical data as actual form responses.

the code first opens the form I created, and the spreadsheet with the historical data in the array named "values". The for loop cycles through each line of the spreadsheet, populating each item of the form, and then submitting each complete response.

If all your data is of one type, you could simplify the code by adding a for loop to add each item of the form. For me, the data types for each item of the form were different, so I listed each item separately.

I realize this is a fairly old post, but in attempting to resolve my issue, I found no help. I hope this will help someone.

I used @Jean-Francois answer together with Mogsdad to achieve my task: to merge data coming from many identical forms into just one. I added a skip for empty answers, as my form had many non required questions and jumping sections. To "upload" about 1000 responses with 30 items, I had to run twice the script because of timeout, just by changing initial row at line 8

Here is my suggestion for the script as I used for my project. I have modified the script by Tom to adjust when fields have different types of data (and in my case Section Headers where we should not do anything for). I used a "switch" and a "asTypeItem" to do the trick but if anybody got a better suggestion, I'm all ears!

We can also easily adapt the script to read data from a Spreadsheet as @papasi suggests.

Okay I've made a way to submit a Google Form response programatically as a new post with no hassle. I didn't see a good example of this anywhere so I've posted the code on my site if you want it. There is no need to construct your own response or import them from a spreadsheet.

It'll resubmit your last Google Form response again as a new form response, OR I also made a function that will randomly select any of your previous Google Form responses and resubmit it as a a new post. No hassle!

Simply copy and paste into your Google Form script editor project and run either of the functions from your debugger. Enjoy!

I should add, that this doesn't directly answer the original question in this thread, but I found this post when trying to solve the problem myself. AND used this post a lot when trying to come up with a way to do it myself, so thought it would helpful to others to post my end result here if they find this post too like I did.

// Run this to resubmit your LAST Form response again as a new Form Submission
// You need to go and at least do ONE normal form submission the old school way first :-)
function resubmitLastFormResponseAsNew() {
var [form, loggedTimestamp, thisFormResponses, thisResponse, thisResponseID, thisResponseItems, formEditUrl] = getFormResponsesDetails();
logIt("CALLED resubmitLastFormResponseAsNew");
// Create a form response item
var formResponse = form.createResponse();
// We are going to use just the last response stored in thisResponseItems
// Loop through it and build a form response from the existing form objects
// No fuss required trying to get response types to format them properly
for (var x = 0; x &lt; thisResponseItems.length; x++) {
// Get the last item that was submitted
var response = thisResponseItems[x];
// Respost it back into a the new item
formResponse.withItemResponse(response);
} //END For loop
// Submit the form
formResponse.submit();
}
// Run this to resubmit a random selection from any of your previous form submissions as a new Form Submission
// You need to go and at least do ONE normal form submission the old school way first :-)
// But you are est to go and post a few submission to get a decent selection of randomness
function resubmitRandomFormResponseAsNew() {
var [form, loggedTimestamp, thisFormResponses, thisResponse, thisResponseID, thisResponseItems, formEditUrl] = getFormResponsesDetails();
logIt("CALLED resubmitRandomFormResponseAsNew");
// Pick and random previous submission
var random = randomIntFromInterval(1,thisFormResponses.length);
var thisResponse = thisFormResponses[random];
// Get all the items for this latest response
var thisResponseItems = thisResponse.getItemResponses();
// Create a form response item
var formResponse = form.createResponse();
// We are going to use just the this response stored in thisResponseItems
// Loop through it and build a form response from the existing form objects
// No fuss required trying to get response types to format them properly
for (var x = 0; x &lt; thisResponseItems.length; x++) {
// Get the last item that was submitted
var response = thisResponseItems[x];
// Respost it back into a the new item
formResponse.withItemResponse(response);
} //END For loop
// Submit the form
formResponse.submit();
}
// Generate a random number between 2 numbers
function randomIntFromInterval(min,max)
{
return Math.floor(Math.random()*(max-min+1)+min);
}
// Get the forms response for this submission and its editable URL and return it
function getFormResponsesDetails (formID) {
logIt("CALLED getFormResponsesAndEditableURL WITH " + formID);
var form = FormApp.getActiveForm();
var formID = form.getId();
// Get the form OB
var thisFormOB = FormApp.openById(formID);
// And Array of Objects with all the responses for this form
var thisFormResponses = thisFormOB.getResponses();
// Get an array of all the items (questions and answers and each item details) in the form
//https://developers.google.com/apps-script/reference/forms/item-response#getitem
var thisFormItems = thisFormOB.getItems();
// Get the last response that came through
var thisResponse = thisFormResponses[thisFormResponses.length-1];
// Get the ID for the latest response
var thisResponseID = thisResponse.getId();
// Get all the items for this latest response
var thisResponseItems = thisResponse.getItemResponses();
// get this submitted form's "Timestamp"
var loggedTimestamp = thisResponse.getTimestamp();
// get the url
var formEditUrl = thisResponse.getEditResponseUrl();
return [form, loggedTimestamp, thisFormResponses, thisResponse, thisResponseID, thisResponseItems, formEditUrl];
}