Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have some Google Docs Forms and I would like to import legacy survery 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?

share|improve this question
add comment

4 Answers

up vote 1 down vote accepted

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

}
share|improve this answer
    
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
add comment

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

  }

};
share|improve this answer
add comment

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

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

share|improve this answer
add comment

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.

share|improve this answer
    
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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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