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