5

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?

5

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);
  }

}
  • Ok, can you elaborate on the script/solution? – villares Nov 18 '13 at 19:52
  • @villares I have elaborated in the answer – Tom Horwood Nov 26 '13 at 23:58
  • yet another reason google sucks. – pathfinder Apr 28 '15 at 14:47
5

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);

  }

};
4

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; i<items.length; i++) {//using 'i' to count both data fields and form items
      var resp = data[row][i+1];//jumps first data field containing timestamp
      // 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
            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);
  }
}
2

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);
    }

  }
1

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.

  • Sorry if I was not clear, Armit, I did import the data but rows after the last native response just gets ignored. – villares Jun 22 '12 at 18:46
1

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 &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];
}
-2

After trawling the Docs, I've concluded that it can't be done. I'd love to be corrected, though.

https://developers.google.com/apps-script/reference/forms/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.