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)
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
