Google Form linked to Google Spreadsheet - Order of responses

Question

I have a Google Form that is connected to a Google Spreadsheet. I noticed that if I decide to manually write something on this Google Spreadsheet, the order of entry is not preserved during future responses.

For example: Assume there are 3 rows each with a Google Form response and I manually write something in the 4th row. The next time there is a Google Form response, the spreadsheet will show the new response in the 4th row, and move my manual input to the 5th row.

I was wondering if there was anyway to get the new responses to show up below any manual inputs so that the order is preserved. (Have 3 rows with Google form data, the 4th row with the manual input, and 5th row with the Google Form response.)

there is definitely a way to do this but on a separate sheet, so you will have a sheet with your form entries, another sheet with your manual entries and 3rd sheet with your desired output. also possible to only first two sheets if you use script — player0

Nikko J. Nikko J. · Accepted Answer · 2021-06-21T21:59:18

You can create an onFormSubmit Trigger that will check if there is a manual input in the spreadsheet by comparing the last row and the response row. If the response row is not equal to the last row, it means a manual input has been made and we move the response to the last row.

Go to Tools -> Script editor and paste the code below:

function moveToLastRow(e) {
  var sheet = e.range.getSheet();
  var lastRow = sheet.getLastRow();
  var row = e.range.getRow();
  var data = e.values;
  if(lastRow != row){
    sheet.deleteRow(row);
    sheet.appendRow(data);
  }
}

Create an onFormSubmit Trigger:

Open your Apps Script project.
At the left, click Triggers alarm.
At the bottom right, click Add Trigger.
Select and configure the type of trigger you want to create.
Click Save.

For this case, copy these configurations:

Output:

Google Form linked to Google Spreadsheet - Order of responses

1 Answers

References