Need a way to parametize an array sort function, with parameters set in a handler

Question

I'm writing a web app that displays a subset of rows from a spreadsheet worksheet. For the convenience of users, the data is presented in a grid, each row selected from the spreadsheet forms a row in the grid. The number of rows relevant to each user grows over time.

The header of the grid is a set of buttons, which allow the user to control the sort order of the data. So if the user clicks the button in the header of the first column, the array that populates the grid is sorted by the first column and so forth. If you click the same column button twice, the sort order reverses.

I used script properties to communicate the selected sort field and order between the handler responding to the button presses, and the order function called by the sort.

So in doGet():

// Sort Field and Order
ScriptProperties.setProperties({"sortField": "date","sortOrder": "asc"});

And in the button handler:

function sortHandler(event) {
  var id = event.parameter.source;

  if (id === ScriptProperties.getProperty("sortField")) {
  ScriptProperties.setProperty("sortOrder", (ScriptProperties.getProperty("sortOrder") === "asc")?"desc":"asc");
  } else {
    ScriptProperties.setProperties({"sortField": id, "sortOrder": "asc"});
  }
  var app = UiApp.getActiveApplication();
  app.remove(app.getElementById("ScrollPanel"));
  createForm_(app);
  return app;
}

And the order function itself (this is invoked by a sort method on the array: array.sort(order); in the code that defines the grid):

function orderFunction(a, b) {
  var sortParameter = ScriptProperties.getProperties();
  var asc = sortParameter.sortOrder === "asc";

  switch(sortParameter.sortField) {
    case "date":
      var aDate = new Date(a.date);
      var bDate = new Date(b.date);

      return (asc)?(aDate - bDate):(bDate - aDate);

    case "serviceno":

      return (asc)?(a.serviceno-b.serviceno):(b.serviceno-a.serviceno);

    default: // lexical
      var aLex = String(a[sortParameter.sortField]).toLowerCase();
      var bLex = String(b[sortParameter.sortField]).toLowerCase();

      if (aLex < bLex) {
        return (asc)?-1:1;
      } else if (aLex > bLex) {
        return (asc)?1:-1;
      } else {
        return 0;
      }
  }
}

The fly in the ointment with this design is Google. Once the array gets to a certain size, the sort fails with an error that the Properties service is being called too frequently. The message suggests inserting a 1s delay using Utilities.sleep(), but the grid already takes a long time to render already - how is it going to go if the array takes 1s to decide the order of two values?

I tried reimplementing with ScriptDB, but that suffers the same problem, calls to ScriptDB service are made too frequently for Google's liking.

So how else can I implement this, without the orderFunction accessing any App Script services?

Can these variables be made global instead of being initialized each pass? --------------------function orderFunction(a, b) { var sortParameter = ScriptProperties.getProperties(); var asc = sortParameter.sortOrder === "asc"; — ScampMichael, Aug 20 '13 at 15:51
That would be the easiest thing, but Google Apps Script doesn't seem to support global variables of any kind. Or at least I could figure out how to declare them. Just `var sortParameter` outside the scope of any function didn't produce a syntax error, but didn't create a persistent variable either.Bad luck if you learnt your programming in COBOL :-) — mikb, Aug 21 '13 at 04:57
can't do that see http://stackoverflow.com/questions/12549085/using-and-modifying-global-variables-within-handler-functions — DavidF, Aug 21 '13 at 05:11

score 1 · Accepted Answer · answered Aug 20 '13 at 06:01

1

If you are looking for temporary storage, I prefer CacheService to ScriptProperties. Use

CacheService.getPrivateCache().put and CacheService.getPrivateCache().get

answered Aug 20 '13 at 06:01

Srik

7,907
2
20
29

I went down this route, it seemed to disrupt things the least. It does appear to have resolved the issue, at least for the moment. I'm still nervous that we'll hit the problem again down the track. – mikb Aug 23 '13 at 00:31

score 0 · Answer 2 · answered Aug 20 '13 at 06:53

You could also use a hidden widget to pass information between the doGet an handler functions, it will also be much faster than calling the scriptProperties service (have a look at the execution transcript to see how long it takes, you'll be surprised)

On the other hand if you really want to keep using it you can also try to (slightly) reduce the number of calls to the scriptProperties service, for example in your sortHandler(event) function :

function sortHandler(event) {
  var id = event.parameter.source;
  var sortField = ScriptProperties.getProperty("sortField");
  var sortOrder = ScriptProperties.getProperty("sortOrder");
  if (id === sortField) {
  ScriptProperties.setProperty("sortOrder", (sortOrder === "asc")?"desc":"asc");
  } else {
    ScriptProperties.setProperties({"sortField": id, "sortOrder": "asc"});
  }
  var app = UiApp.getActiveApplication();
  app.remove(app.getElementById("ScrollPanel"));
  createForm_(app);
  return app;
}

I've used hidden widgets to communicate between the user inteface code and the handlers successfully, so I know what you mean. And you're right about how slow ScriptProperties is - although, having rewritten the code to use ScriptDB, and found that didn't solve the problem, I can tell you ScriptDB is even slower. I didn't think I could use hidden widgets in the case of the orderFunction() though (or is there a way?), which is where the real problem lies. — mikb, Aug 21 '13 at 04:51
I'm pretty sure you could use the hidden widget throughout,could you eventually show a complete functional code somehowso that I can suggest a working solution without having to rewrite the whole code from scratch? — Serge insas, Aug 21 '13 at 06:41

score 0 · Answer 3 · answered Aug 20 '13 at 23:27

0

...how else can I implement this?

Use HTMLService with jQuery DataTable which does all the ordering that you can imagine and a whole lot more without scripting button press logic and the like. There is an example of the spreadsheet to basic table here http://davethinkingaloud.blogspot.co.nz/2013/03/jsonp-and-google-apps-script.html

answered Aug 20 '13 at 23:27

DavidF

1,335
1
15
26

Can HTMLService do asynchronous? It would be nice to put up some sort of temporary "Please Wait While We Rearticulate Splines" until the array manipulations are finished building the grid. – mikb Aug 21 '13 at 05:00
AJAX? Yes, but the coding gets quite complex for me anyway. With jQuery DataTable you could create the html page with a "please wait" msg. Fill the DataTable onReady and have the created datatable replace the "please wait". Also you can lazy load the table if your table create script supports that but I assumed that the spreadsheet rows were just being filtered. – DavidF Aug 21 '13 at 05:19

Need a way to parametize an array sort function, with parameters set in a handler

3 Answers3