Google Spreadsheet: add rows to specific columns?

Question

I have a spreadsheet that is linked to a form. Forms submissions are appended to the bottom of the spreadsheet by default. I wanted it so the most recent submission would appear at the top, so I created another spreadsheet and used the formula =SORT('Form Responses'!A1:F, 1, 0) to copy the responses in descending order. This works great.

Now what I want to to is have another column on the sorted spreadsheet to add comments to the form responses. The problem is that any comments I make do not move down when another form response is submitted. I would like to add another row to the comments column whenever another form response is submitted. How would I do this?

**EDIT: I implemented a solution shown below. I ended up getting rid of the second spreadsheet and =SORT function and just using a script that is linked to the spreadsheet's onFormSubmit event

Answer 1

Problem is that you are using a dynamic formula combined with a static column. If you want it to work the way you are talking then try one of the below options.

1. You can add the comments to the Form Submission tab and then your sort will work on your second tab.

2. Rather than the =sort function you could use App Script and use the onFormSubmit trigger to copy the response to the top of the sheet.

Answer 2

I ended up getting rid of the second spreadsheet and =SORT function and just using a script that is linked to the spreadsheet's onFormSubmit event. I came up with a solution using the script editor.

function moveLastRowToTop() {

  var spreadSheet = SpreadsheetApp.getActive();
  var activeSheet = spreadSheet.getActiveSheet();
  var lastRow = activeSheet.getLastRow(); 
  var lastCol = activeSheet.getLastColumn();
  //get range of row to copy
  var range = activeSheet.getRange(lastRow+1,1,1,lastCol);
  //insert a new row at the top of the sheet
  activeSheet.insertRowsAfter(1, 1);
  //copy last row contents to newly created row
  range.copyTo(activeSheet.getRange(2, 1, 1, lastCol), {contentsOnly:false});
  //delete last row
  activeSheet.deleteRow(lastRow+1);
}