Troubleshooting: Google Drive Spreadsheets - Sort Script

[miko]

I figure if this took me more than 2 minutes to find the answers, I should write it up.

Background: After seeing a link to http://unfuckyourhabitat.tumblr.com/ , I was reminded that I was trying to set up a maintenance schedule for myself at home... I like lists, but the physical list I have just hasn't been working for me - too many different timelines for doing tasks and not enough randomness: I'd just always end up skipping the same things in favour of tasks I enjoy doing more. So, I thought I'd try making myself a spreadsheet to give me a bit more flexibility in timing & also a randomizer that would put something to the top of my list.

I was going to just set it up on one of my computers, but then I thought it'd be useful if I could grab it easily from the tablet as well... so why not try Google Drive's spreadsheet function? Yeah, it'll go down when the internet is down, which sucks, but I still thought I'd try it. Frankly, none of my computers even have the same version of open/libre office on them, so portability of scripts would have been tough if I didn't do it this way.

So, I set up my spreadsheet to do what I wanted it to do... and then realized that the click throughs for sorting were a bit of a pain. I'd have to select the right range, Data > Sort Range, select that it has headers, select the right column, select descending, select a second column. Not the end of the world, but certainly not what I'd like it to be. First search, can I set up a different default sort? Survey says... no. Alright then. What about something like recording a macro in Excel, can I do that since I know exactly what the manual steps are? Google help says... no. Fine, so I need to write a script in their language.

The script: This script should have been the simplest example in the world, but it was bloody hard to find anything in the google help. My frustration perhaps shows in the comments.

// For actually useful help files, start here: 
//    https://developers.google.com/apps-script/reference/spreadsheet/

function sortMaintenanceList() { 
  // Note: to call from a spreadsheet, use the function name "sortMaintenanceList()" 
  // without any reference to the file name.  To clip this to a button, make a
  // shape (Insert -> Drawing ...) - I like to use a textbox with a background
  // colour - and then right click, select the drop down arrow and Assign Script.
  //
  // I don't cotton to these "write everything in one line" ideas. Write readable 
  // code, it makes it more accessible *and* you don't hate yourself later.
  
  // This is how you select your current sheet, so that you can actually run sheet 
  // functions. You'll need to give the app permissions to access your google drive 
  // the first time.
  
  currentSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
  
  // Here I'm selecting the range I want.  In this case, I'm starting at cell(2,2) 
  // in order to not select my row 1 headers or my column 1 numbers. Most times,
  // you'll probably want to start at 2,1 (ie, row 2, column 1) because normal
  // people don't want to skip column 1.
  //
  // The second half is to select to the last row and the last column, so that even
  // if I expand the sheet to add more, it'll still grab it all.  There are also
  // range versions of this sheet function.  Note: for some reason, it wanted the
  // extra brackets.  It still seemed to work, but threw an error without them.
  
  usefulRange = currentSheet.getRange(2,2,(currentSheet.getLastRow()),(currentSheet.getLastColumn()))
  
  // Now sort it.  The sort function from range works differently than the sort
  // from sheet, because apparently consistency is for chumps. Here I'm sorting
  // first by column 7 (note: this is absolute in the sheet, even though I have
  // a smaller range selected), decending, secondarily by column 8, ascending.
  
  usefulRange.sort([{column: 7, ascending: false}, {column: 8, ascending: true}])
}

This is simple enough (and yes, could all be one line of code if I hated future-me), but geez, it was not easy to find quickly via searches. -1 useability, google... the first help pages you sent me to were completely unhelpful. It wasn't even clear where to go to get the class/function definitions, let alone having any useful examples to get me situated. I would think I'd be part of the target audience (familiar with Excel macros and ready to try switching over), but it was cringe-worthy trying to get to this.

If you're curious about my document, I'm totally willing to send it along - I just didn't feel like cleaning it up for public consumption right now.