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);
}
}
Maybe this has changed in new Google Sheets because in the second and subsequent Forms it seems possible to select Responses, Choose response destination
:
New sheet in an existing spreadsheet...
, Choose:
find the Response Sheet for the first Form, Select and for this to work.
Best Answer
You can use this code.
Code
I wasn't able to find a clear cut solution, thus ended up with this. It will simply get the
ID
of the last entry