Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
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.
How can I overcome this?
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.
The process works like this:
+-------------+
|Form |----+
|-------------| |
|Responses | |
| | v
| | User
+-------------+ submits
^ form
| +
Saved to |
| |
| |
+--------------+ |
| Response |< -+
+--------------+
|
|
Copied to
|
v
+-------------+
|Spreadsheet |
|-------------|
| |
| |
+-------------+
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.
function testOnSubmit() {
var answers = [
["Sue", "39", "Okay I suppose"],
["John", "22", "Great"],
["Jane", "45", "yeah no"],
["Bob", "33", "Super"]
];
var form = getConnectedForm();
var items = form.getItems();
for (var i = 0; i < answers.length; i++) {
var formResponse = form.createResponse();
for (var j = 0; j < items.length; j++) {
var item = items[j];
var itemResponse = item.asTextItem().createResponse(answers[i][j]);
formResponse.withItemResponse(itemResponse);
}
formResponse.submit();
Utilities.sleep(500);
}
}
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.
function ApendResponses() {
var form = FormApp.openByUrl('https://docs.google.com/???????');
var sheet = SpreadsheetApp.openByUrl("https://docs.google.com/???????");
var rows = sheet.getDataRange();
var numRows = rows.getNumRows();
var values = rows.getValues();
for (var x = 0; x < values.length; x++) {
var formResponse = form.createResponse();
var items = form.getItems();
var row = values[x];
var formItem = items[0.0].asListItem();
var response = formItem.createResponse(row[0]);
formResponse.withItemResponse(response);
var formItem = items[1.0].asListItem();
var response = formItem.createResponse(row[1]);
formResponse.withItemResponse(response);
var formItem = items[2.0].asParagraphTextItem();
var response = formItem.createResponse(row[2]);
formResponse.withItemResponse(response);
var formItem = items[3.0].asDateTimeItem();
var response = formItem.createResponse(row[3]);
formResponse.withItemResponse(response);
var formItem = items[4.0].asDateTimeItem();
var response = formItem.createResponse(row[4]);
formResponse.withItemResponse(response);
var formItem = items[5.0].asParagraphTextItem();
var response = formItem.createResponse(row[5]);
formResponse.withItemResponse(response);
var formItem = items[6.0].asParagraphTextItem();
var response = formItem.createResponse(row[6]);
formResponse.withItemResponse(response);
var formItem = items[7.0].asParagraphTextItem();
var response = formItem.createResponse(row[7]);
formResponse.withItemResponse(response);
var formItem = items[8.0].asListItem();
var response = formItem.createResponse(row[8]);
formResponse.withItemResponse(response);
var formItem = items[9.0].asParagraphTextItem();
var response = formItem.createResponse(row[9]);
formResponse.withItemResponse(response);
formResponse.submit();
Utilities.sleep(500);
}
};
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
function FillFormfromSpreadSheet() {
var ss = SpreadsheetApp.openById('1xxCiWOZSfSWHtIeWA9bEJuI19FwI3TBmGwTlkxxxxxx');//Spreasdsheet ID
var sheet = ss.getSheetByName("SheetName");
var form = FormApp.openById('1m1NZ5kB39OqvOvhRKs-BoeU9NWaWAi4Rk40XPxxxxxx');//'Form ID'
var data = sheet.getDataRange().getValues(); // Data to fill
var EmptyString = '';
var items = form.getItems();
for (var row = 1; row < data.length; row++ ) { //jumps first row, containing headers
var response = form.createResponse();
for (var i=0, j=0; i<items.length; i++) {//using 'i' to count both data fields and form items
var resp = data[row][i+1-j];//jumps first data field containing timestamp and offset for compensate pagebreaks, images, etc...
// Need to treat every type of answer as its specific type.
if (resp !== EmptyString ) {//jumps the entire procedure for empty datafields, as may occur for non required questions
switch (items[i].getType()) {//note that data[#][1] corresponds to item[0], as there's no timestamp item!
case FormApp.ItemType.MULTIPLE_CHOICE:
item = items[i].asMultipleChoiceItem();
break;
case FormApp.ItemType.CHECKBOX:
item = items[i].asCheckboxItem();
// In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
// In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
if (typeof resp !== 'string')
resp = resp.join(','); // Convert array to CSV
resp = resp.split(/ *, */); // Convert CSV to array
break;
case FormApp.ItemType.TEXT:
var item = items[i].asTextItem();
break;
case FormApp.ItemType.PARAGRAPH_TEXT:
item = items[i].asParagraphTextItem();
break;
default:
item = null; // Not handling DURATION, GRID, IMAGE, PAGE_BREAK, SCALE, SECTION_HEADER, TIME
j++; // offset for sheet column
break;
}
if (item) {// Add this answer to form
var respItem = item.createResponse(resp);
response.withItemResponse(respItem)
}
else Logger.log("Skipping i="+i+", question="+ques+" type:"+type);//skip any other type of response
}
}
response.submit();
Utilities.sleep(500);
}
}
2021 VERSION - This is a solution to copy a Google Form responses (answers) to another Google Form.
By default, when you copy a Google Form, only the questions will be included. The responses tab will be empty.
The solution is to use Google Script to programmatically read the answers from the original form's answer spreadsheet, and insert them into the new form.
The only caveat is that timestamps will not match the originals. You could edit the script to include them as an additional column if necessary.
See also: GitHub gist.
⠀
Instructions:
EDIT THIS SECTION block.
.⠀
(1) Script editor button:
(2) Source Spreadsheet's id
(3) Destination form's id
(4) Script -- improved & based on @peppeprof's version:
//
// EDIT THIS SECTION
// The source spreadsheet ID (find in url) and sheet Name
const sheetId = '18Z-XvuBOXIYc7LH-Ht162nGPu4JxMb0x2Hxxxxxxxxx';
const sheetName = 'Sheet 1';
// The destination form ID (find in url)
const formId = '1LGuNOBEZFKKYJIjwYvHuhVBfqRyA98kHJE-xxxxxxxx';
// Columns to skip within the spreadsheet. 1 = Timestamp. 2 = Timestamp + Email
const skipColumns = 1;
// Whether to create missing options (eg. if "Other" was activated first, and deactivated later).
// If this option is false and options are missing with "Other" deactivated, the script will throw an error.
const createMissingOptions = false;
// (Optional) How many rows to skip (the header is already included within a value of "0")
const skipRows = 0;
// (Optional) Whether to show the activity log
const showLogs = true;
//
// DO NOT EDIT THIS SECTION (unless you know what you're doing)
function BuildItems(items) {
// These types are not included in the spreadsheet, but are included in the form item definitions. Skip them.
const skipTypes = [ FormApp.ItemType.SECTION_HEADER, FormApp.ItemType.PAGE_BREAK ];
var retItems = [];
for (var i = 0; i < items.length; i++) {
var item = items[i];
var type = item.getType();
if (skipTypes.includes(type) > 0) {
continue;
}
// Need to treat every type of answer as its specific type.
switch (type) {
case FormApp.ItemType.CHECKBOX:
item = item.asCheckboxItem();
break;
case FormApp.ItemType.DATE:
item = item.asDateItem();
break;
case FormApp.ItemType.DATETIME:
item = item.asDateTimeItem();
break;
case FormApp.ItemType.TEXT:
item = item.asTextItem();
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item = item.asMultipleChoiceItem();
break;
case FormApp.ItemType.PARAGRAPH_TEXT:
item = item.asParagraphTextItem();
break;
case FormApp.ItemType.SCALE:
item = item.asScaleItem();
break;
default:
// Not handling DURATION, GRID, IMAGE, TIME
Logger.log("Warning: Question '" + item.getTitle() + "' has unsupported type " + type + ". It will be skipped.");
item = null;
break;
}
retItems.push(item);
}
return retItems;
}
function FillFormfromSpreadSheet() {
var ss = SpreadsheetApp.openById(sheetId);
var sheet = ss.getSheetByName(sheetName);
var form = FormApp.openById(formId);
// All the form's questions
var items = BuildItems(form.getItems());
// Get the data within the spreadsheet
var data = sheet.getDataRange().getValues();
var EmptyString = '';
// Jumps first row, containing headers, plus user-specified amount
for (var row = 1 + skipRows; row < data.length; row++) {
try {
var response = form.createResponse();
// Using 'i' to count both data fields and filtered form items. Jump first data field containing timestamp, email, ..
for (var i = skipColumns; i < items.length; i++) {
var resp = data[row][i];
var item = items[i - skipColumns];
// Jumps the entire procedure for empty datafields, as may occur for optional questions
if (resp === EmptyString || item === null)
continue;
var type = item.getType();
// Handling special cases. Note that data[#][skipColumns] corresponds to item[0], as there's no timestamp item!
switch (type) {
case FormApp.ItemType.CHECKBOX:
// In a form submission event, resp is an array, containing CSV strings. Join into 1 string.
// In spreadsheet, just CSV string. Convert to array of separate choices, ready for createResponse().
if (typeof resp !== 'string') {
resp = resp.join(','); // Convert array to CSV
}
var cResps = resp.split(/\s*(?:,|;)\s*/); // Convert CSV to array
resp = cResps;
if (createMissingOptions === false) {
break;
}
var choices = item.getChoices();
var choicesName = choices.map(c => c.getValue())
var shouldSetChoices = false;
for (var c = 0; c < cResps.length; c++) {
const cResp = cResps[c];
if (choicesName.includes(cResp) === false) {
choices.push(item.createChoice(cResp));
shouldSetChoices = true;
}
}
if (shouldSetChoices) {
item.setChoices(choices);
}
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
if (createMissingOptions === false) {
break;
}
var choices = item.getChoices();
var choicesName = choices.map(c => c.getValue())
var shouldSetChoices = false;
if (choicesName.includes(resp) === false) {
choices.push(item.createChoice(resp));
shouldSetChoices = true;
}
if (shouldSetChoices) {
item.setChoices(choices);
}
break;
}
try {
if (showLogs) {
Logger.log("Q: " + item.getTitle() + " || " + type.toString() + " || " + resp);
}
}
catch (exc) {
Logger.log("Error: " + type + " || " + resp + " || row: " + row + " || form idx: " + itemIdx + " || sheet column: " + i);
throw exc;
}
// Add this answer to form
if (item) {
var respItem = item.createResponse(resp);
response.withItemResponse(respItem)
}
}
response.submit();
Utilities.sleep(500);
}
catch (exc) {
Logger.log("Error: an exception was thrown on row " + row);
throw exc;
}
}
}
skipColumns to 0, all the rest works perfectly
Oct 14, 2022 at 12:28
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.
Hope it can help.
function testOnSubmit() {
var answers = [
['IT',5,5,4,4,'This is a Comment'],
['HR',3,4,5,2,'This is another Comment'],
];
var form = FormApp.openById('1PJ_B.....');
var items = form.getItems();
for (var i = 0; i < answers.length; i++) {
var formResponse = form.createResponse();
var k=0;
for (var j = 0; j < items.length; j++) {
var item;
switch(items[j].getType()) {
case FormApp.ItemType.MULTIPLE_CHOICE: item=items[j].asMultipleChoiceItem(); break;
case FormApp.ItemType.SCALE: item=items[j].asScaleItem(); break;
case FormApp.ItemType.PARAGRAPH_TEXT: item=items[j].asParagraphTextItem(); break;
//case FormApp.ItemType.<OTHER_TYPE>: item=items[j].as<OtherType>Item(); break;
default:
Logger.log("#"+(i+1)+":Do nothing for item "+j+" of type "+items[j].getType() );
continue;
break;
}
Logger.log("#"+(i+1)+": Add answer "+answers[i][k]+" for item "+j+" of type "+items[j].getType());
formResponse.withItemResponse(item.createResponse(answers[i][k++]));
}
formResponse.submit();
Utilities.sleep(500);
}
}
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.
http://c2solutions.com.au/submit-response-google-form-using-google-apps-script-made-easy/
// 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 < 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 < 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];
}
I know this quite an old post but I had the same issue and I had really hard time resolving it. So, here is a solution that can be easily modified to fit your needs.
It is able to populate an existing Google Form with data from a Google Spreadsheet. It goes through each row of your spreadsheet, finds the datapoint that matches the question (which ofc has to be the same as the header in the spreadsheet) and fills out the form automatically.
The full code is available here:
function readSpreadsheet() {
var sheet = SpreadsheetApp.openById("[THE ID OF THE SPREADSHEET]");
var range = sheet.getDataRange();
var numRows = range.getNumRows();
var values = range.getValues();
var form = FormApp.getActiveForm();
var items = form.getItems();
for (var i = 1; i < numRows; i++) {
var value = values[i];
var formResponse = form.createResponse();
var k = 0;
for (var j = 0; j < items.length; j++) {
var item;
switch (items[j].getType()) {
case FormApp.ItemType.CHECKBOX:
item = items[j].asCheckboxItem();
Logger.log("Item number " + j + " is titled: " + item.getTitle() );
formResponse.withItemResponse(item.createResponse(value[k++]));
break;
case FormApp.ItemType.LIST:
item = items[j].asListItem();
Logger.log("Item number " + j + " is titled: " + item.getTitle() );
formResponse.withItemResponse(item.createResponse(value[k++]));
break;
case FormApp.ItemType.MULTIPLE_CHOICE:
item = items[j].asMultipleChoiceItem();
Logger.log("Item number " + j + " is titled: " + item.getTitle() );
formResponse.withItemResponse(item.createResponse(value[k++]));
break;
case FormApp.ItemType.PARAGRAPH_TEXT:
item = items[j].asParagraphTextItem();
Logger.log("Item number " + j + " is titled: " + item.getTitle() );
formResponse.withItemResponse(item.createResponse(value[k++]));
break;
case FormApp.ItemType.TEXT:
item = items[j].asTextItem();
Logger.log("Item number " + j + " is titled: " + item.getTitle() );
formResponse.withItemResponse(item.createResponse(value[k++]));
break;
case FormApp.ItemType.CHECKBOX:
item = items[j].asCheckboxItem();
Logger.log("Item number " + j + " is titled: " + item.getTitle() );
formResponse.withItemResponse(item.createResponse(value[k++]));
break;
default:
Logger.log("#" + (i + 1) + ":Do nothing for item " + j + " of type " + items[j].getType());
continue;
}
if(j==0){
Logger.log("This item is the FORM NUMBER");
};
}
formResponse.submit();
}
}
I think the only information added to the row is the date and time when that form was submitted by the user.
You can probably create a CSV file and set the first column to some fixed date time and import it into your Google Docs.
I cannot comment on Alexis answer without reputation on answer /2021 VERSION - This is a solution to copy a Google Form responses (answers) to another Google Form.
So I am putting it here. I do not know, if its right.
I tried to run script as given in tutorial and faced following error
1:21:44 AM Notice Execution started
1:21:45 AM Error
TypeError: Cannot read property 'length' of undefined
BuildItems @ Code.gs:36
I have tried with const sheetName = 'Form Responses 1'; and also alternatively with const sheetName = 'Sheet 1';
After trawling the Docs, I've concluded that it can't be done. I'd love to be corrected, though.
