1

Can my form's responses populate in chronological order upon submission somehow automatically? I just did a test response with my co-worker and her 'order' was placed at the bottom, which I now have to go back in and sort chronologically in the spreadsheet. I am hitting a wall!

  • I don't think this is possible without the help of a script periodically running to sort your data. What I would personally do is to hide the form response sheet and run a =query over it and sort it by ASC (ascending) on your time serries data. – Datanovice Oct 11 '18 at 22:16
1

Elyse. I'll try to explain clearly what to do.

First, let's assume the following for my example:

  • Your Google Form data is coming into tab one of a spreadsheet, and that tab is named "Sheet1."

  • Column A of Sheet1 contains the date each form is submitted.

  • Your data runs from Columns A:K.

You can't directly change the order that Google Forms sends over the information. But you can create another tab that will:

Create another tab and call it "Chronological" (or whatever you like).

Given the example assumptions above, in the new "Chronological" tab, cell A1, place this formula:

=QUERY(Sheet1!A:K,"Select * Where Not A = '' Order by A Desc")

Explanation:

A QUERY() is a way to bring a range of information easily to another place and to sort or filter it as you like.

The example QUERY() above will start by bringing in all data from range Sheet1!A:K (adjust to include all columns in your actual Sheet1 data set).

The "Select" clause tells the QUERY() what information in that range to bring over and how.

We don't want to bring over any blank rows; that's the part that says "Where Not A = '' " (meaning "Don't bring over any rows where Column A in that range has nothing in it").

And we want to sort so that the most recent dates are up top. That's the "Order by A Desc" bit (meaning "Put the data in order according to whatever is in Column A, but in DESCending order instead of ASCending").

The "Chronological" tab will now autopopulate with your orders from Sheet1, with the most recent dates up top. From there, just use the "Chronological" tab as if it were the original. You can pretty much ignore Sheet1 thereafter. Google Forms will continue to send data to Sheet1 and your new "Chronological" tab will continually update so that the most recent is on top.

Hope that meets your need.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.