3

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 } ); }
  • 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
0

Simple triggers like onOpen and onEdit have restrictions.

In contrary of onOpen and onEdit that are reserved words onChange it's not, but its commonly used to name an installable trigger to be called by the spreadsheet change event. In the same way it's possible to set functions to be called ty the the spreadsheet open and change events which not have the restrictions of simple triggers.

We could create simple triggers manually or programmatically.

For further details please read Triggers and Events Guide.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.