Google Docs: How to automatically sort form responses so new entries are always at top

December 5, 2014 - Tech

I have a Google Form that is set up to receive responses indefinitely, but the problem is retrieving any new response can be annoying because one has scroll all the way down to the bottom of the Google response spreadsheet to get to the most recent submission.

It’s not too much of a hassle when you have a few responses, but as more and more form submissions come in, it becomes more difficult to access the latest entry.

Unfortunately, there’s no setting in Google Docs to easily auto sort by time stamp. I know you could always sort the form by “timestamp” but I would have to sort the form every time I opened the document — and I might as well just mouse myself to the bottom of the sheet.

Is there was a better, automated way to display the latest form submissions (by timestamp) at the top of the sheet. How can I have new form submissions placed into the response spreadsheet so the most recent submissions appear at the top as opposed to at the bottom?

The good news is there’s an easy solution that requires very little work.

Automatically Show New Form Responses at Top of Google Spreadsheet

All it takes to sort form responses automatically by most recent (e.g. having new submissions at the top) is a formula.

First you’ll need to create a new tab in the form response sheet. I named this new tab “SortedByNew”. 

The next step is to use a sort formula that will not only pull the original response information into the new tab but will sort the data by time stamp (however, this formula will let you sort by any column). Specifically, plug in the following formula* in cell A1 of your “SortByNew” tab:

=sort(Responses!A:M,1,0)

Auto sort form responses in google docs

Here’s the formula explained:

  • =sort is a function that tells Google Sheets that it’ll be a formula to sort data in a specific way
  • Responses refers to the name of the original tab where the responses are recorded
  • A:M defines the column range of the information you’d like to pull from Responses tab.
  • 1 tells Google Sheets which column to sort, in this example, column 1 is time stamp
  • 0 means to sort that column in ascending (by most recent date)

The great news about this forumla is that it will automatically add new responses at the top of the form. It’s pulling the data from the original Responses tab, so you’ll still need that tab. If two tabs are bothering you, then you can choose to hide the Responses tab

* Formula on Cloud Computing: Google Docs Way

› tags: formula / Google / Google Docs / Google Form / how to / SORT / sort function /

Comments

  1. Sue says:

    I have a hall pass form that automatically feeds the student requests to leave into a spreadsheet. When they come back to the room the teacher logs the time they return. I used this sorting method to allow the responses to appear in a spreadsheet according to the timestamp. When the teachers go to add the return time however, the times do not move down with the entry.

  2. Rich Tanksley says:

    Wow, you just changed my life. Thanks so much! Does this make me a geek?

  3. Diana says:

    I am trying this, but it’s not working. I’m thinking I’m confused with the Responses part. I inserted a new column at the beginning and entered this in A1 exactly
    =sort(Responses!B:K,1,0)

    I changed it to A:K because I only have responses in that area.

  4. Sophie says:

    I keep getting a message saying “unresolved sheet name” when I try to use this formula. Please help.

  5. Ben says:

    Thanks so much for posting this. I had a script for a Google form that was doing this, but it stopped working. This got me running again.

  6. Deb says:

    Thank you!
    Simple and concise solution.
    Took a while find though

  7. Jean says:

    Hi
    With a little script you can “display the latest form submissions (by timestamp) at the top of the sheet”.

    This script places the new data (from the form) at the top of the sheet.
    ====
    function maFonction(e) {

    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getActiveSheet();

    // tri inversé – reversed sort
    s = SpreadsheetApp.getActiveSheet();
    s.sort(1, false);
    ====
    The trigger is “OnFormSubmit”

    With this script you don’t need a second sheet to sort your data from the form.

    Jean from Québec City

  8. Doug says:

    First you’ll need to create a new tab in the form response sheet. I named this new tab “SortedByNew”.

    sorry basic question – what does create new tab mean, how do I do this?

  9. Michael Eardley says:

    Is there a simple way to first sort by Column A and then by Column B automatically?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.