Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I'm trying to setup a form and spreadsheet database to track inventory on a daily basis at a remote location. The idea is that the person at the remote location can update our stock via a simple online form (using jotform integrated w/ google spreadsheets) and then take the raw data on sheet1 and make a nice looking/organized front page with all the pertinent info plus some basic calculations (re: does this mornings opening stock match yesterdays closing level, etc).

The best way I can figure to do this is to setup some basic call functions on the "clean" front page, but in order to make sure I'm using the latest data I need to ensure that I'm always pointing to the most recent time. The simplest way that I can think of to do this is have the spreadsheet autosort every time a new entry is made so that the last entry is always at the top.

That way I can just write something like "='sheet1'!C2" in order to post the last entry in column 2 which should be the latest entry. And as new entries are made the front page should be updated with the latest numbers. (and also, I can know what I need to drive out in the morning before opening)

I've tried using both an onEdit and onOpen approach to the problem, but the onEdit only works when I go in and make manual changes to column D, which is annoying. when I use onOpen, it doesn't sort at all. I've read something about the possibility of sorting on change rather then on edit, will this work for me?

Here are the two sample scripts I've been working with that haven't produced what I wanted it to.

    // LinkBack to this script:
 // http://webapps.stackexchange.com/questions/7211/how-can-i-make-some-data-on-a-google-spreadsheet-auto-sorting/43036#43036

 /**
 * Automatically sorts the 1st column (not the header row) Ascending.
 */
function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 4;
  var tableRange = "A2:AQ149"; // What to sort.

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: false } );
  }
}

====================

function onOpen(event) { 
  var sheet = SpreadsheetApp.getActiveSpreadsheet(); 
  var columnToSortBy = 4; 
  var tableRange = "A2:AQ149"; 
  var range = sheet.getRange(tableRange); 
  range.sort( { 
    column : columnToSortBy, ascending: false } ); }
share|improve this question
    
Hang in there user40937, an answer is eminent (I hope). –  Jacob Jan Tuinstra May 29 '13 at 18:00
    
Can you share a doc/form with us, showing the expected outcome? –  Jacob Jan Tuinstra May 29 '13 at 18:01
    
Hi Jacob, I wish I could share a doc/form with you showing the expected outcome, but I can't really find an example of this anywhere. <_> Which is frustrating because it seems like it should be so simple to implement. create a form where the newest information populates at the top of the list rather than the bottom. how hard could this be? –  user40937 Jun 4 '13 at 16:32
    
I found something called onChange which I tried to swap out with the onEdit code, but it didn't work. But it seems like it's a step in the right direction. –  user40937 Jun 4 '13 at 16:35

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.