Nothing earth shaking or melting here, but an answer to an annoyance I have with some uses of Google Forms.
I hate un-necessary scrolling.
When you have the results go to a spreadsheet, one of the bothersome things is that new data gets added to the bottom. At some level, that means that I always have to scroll to the bottom to see what’s new. But also, for a resource I am working to publish results in a data-filterable form (using Awesome Tables), I wanted again to have new stuff first.
That’s not too much to ask for?
My first principle for using Google Forms us leave the data sheet along, where all the stuff comes in. Anything else I do to sort, calculate, is always done on additional sheets that Do Other Things.
But even for a basis data collection, a survey, you may want to just see newest stuff at the top.
So in Choral Explanation mode, I searched on google, and after a few hops ended up on Having new entries show up top in google sheets (it’s from 2014, so most likely relevant).
The “best answer” was a script that sorted the raw data. I decided to skip the “Best Answer” because (a) it was not the simplest (sometimes simples tops best); and (b) I like approaches that do not mess with the raw data.
This is really simple. And so far It Just Works. I create a second sheet, and in the very first cell (A1) enter one formula:
=query('Form Responses 1'!A1:Z, "select * order by A desc", 1)
One formula, in one cell does the whole trick
Column A is the time/date stamp, so selecting the everything in the order of that columns descending means New Stuff First.
That might be all people need.
But for my use, I need a third sheet, to reformat the data in the format Awesome Table requires
There is a second header row that defines how the column is used in Awesome Table (some are hidden). I changed the order of the columns. One of my columns, I convert the URL in text to a hyperlink to with the title for the link text.
But all I need is really to get the formula right in the first row, and fill down. And this works, even as the data changes. No surprise, because I am merely referencing cells in the other sheet.
There is one hitch; I do have to fill down at the bottom of my Awesome Table worksheet to include all the data (more or less the oldest data now). I had trouble with Awesome Table not ignoring blank cells if my formulas put blanks in them.
So I still have some manual fills. So I know how far to fill down, I hacked one more sheet:
This formula is:
=counta('Form Responses 1'!A:A)+1
This tells me for my Awesome Table data sheet that I need to fill down to row 76. It’s one more row than the formula data because I have that extra header row.
I realize what I am doing here likely has no bearing on what anyone else might do. But remember this blog is for me, me, and primarily, me.
The other thing I do for these kinds of collections is add a notification so I get an email when the form changes.
It’s good to know how to do a fancy reverse…
Top / Featured Image: Modification of CC0 Licensed image found on pexels; added red color for “R” and a faded background of a Google docs spreadsheet.
You know I love stuff like this.
Why don’t you wrap your column 3 function in =arrayformula() and set a range for your cell reference? Any time there’s new data, it should fill the next cell.
Because I’m an idiot? Not sure I get it, but will poke around.
I think I get it if you haven’t figured it out. If you want to, throw me a non-production copy and I’ll make a demo.
Arrayformula applies a formula (=hyperlink()) to a range of cells rather than a single. It’ll auto fill as long as you have data in the feeding cells, saving you from having to either fill a ton of empty cells with formulas.
I can also slap a demo together if you’d like. I use it all the time on spreadsheets I need to just work in the background.
Thank you very much for posting this. Simple. Really helpful.
This was exactly what I needed – thanks! I was setting up a Google Form / Spreadsheet to have our custodian receive maintenance requests, but I didn’t want to have him scrolling down forever to find the newest entries. So this fit the bill perfectly!
Thanks a ton! I had a form where only the most recent entry on every ID is useful at all so instead of filtering away the old ones I just use what you did and use formulas that look from up to down!
Wow that was incredibly elegant and useful. Thank you so much!