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:

One formula, in one cell does the whole trick
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

awesome-table

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:

number-rows

This formula is:

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.

The post "Shifting Those Google Form Data Spreadsheets into Reverse (Order)" was originally dropped like a smoking hot potato at CogDogBlog (http://cogdogblog.com/2016/12/google-form-data-reverse-order/) on December 8, 2016.

5 Comments

  • Tom

    You know I love stuff like this.

  • Brian

    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.

    • Alan Levine aka CogDog cogdogblog.com

      Because I’m an idiot? Not sure I get it, but will poke around.

      • Tom

        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.

      • Brian

        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.

Leave a Comment

All fields are required. Your email address will not be published.

Creative Commons License
Shifting Those Google Form Data Spreadsheets into Reverse (Order) by CogDogBlog is licensed under a Creative Commons Attribution-Share Alike 4.0 License.