Google-sheets – Single Response Spreadsheet (with multiple sheets) from multiple forms

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

}

Filter view

One tool that can be used for this purpose is a filter view. Create a view in which the responses are sorted by column A in reverse order (Z to A). Copy the URL of this view and share it with the users who are supposed to view the response sheet.

A drawback of filter view is that if a new response arrives while a user is looking at the response sheet, the new response is added at the bottom. One would need to reload the page for the sorting to include the new responses.

Another sheet

Create another sheet with the command in cell A1 such as

=sort('Form Responses 1'!A:H, 1, False)

This means sorting by the 1st column, descending order.

Then share that sheet with the viewers. It will update as soon as new data is submitted, without a need for page reload.