Auto sort of Forms responses so that the most recent one is on top

Question

We use Google Forms to collect guest surveys for our resort. New responses get added to the bottom of the response sheet. While we can manually sort the responses using the time-stamp feature, not all of the viewers to the response sheet can edit, so it means they need to scroll to the bottom of the pages. This is not so bad on a desktop but tedious on a mobile device.

Any suggestions as to how we can auto sort the responses so the most recent response appears at the top of the response sheet and not at the bottom?

Answer 1

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.

Answer 2

Add this script on current project's trigger (for every minute):

function Sort() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('A1').activate();
  spreadsheet.getActiveSheet().getFilter().sort(1, false);
};

this will keep sorting your sheet every minute.

• and yes, don't forget to put filter on your sheet to make script running

Answer 3

You can make a second sheet and sort the data from the sheet where the responses come in. Full instruction in this blogpost: http://yogi--anand-consulting.blogspot.nl/2011/07/yogiset-up-spreadsheet-so-that-new.html