Google-sheets – Importing data into a Google Forms response sheet

google sheetsgoogle-apps-scriptgoogle-formsimport

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?

Best Answer

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

}
Related Topic