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've put together a script for a spreadsheet based on what I have seen here and other sources that should provide a simple username and timestamp function. I have another spreadsheet that uses a very similar process and does not produce the sort of lag that I am experiencing (that spreadsheet is also vastly more complicated). (At the moment, the spreadsheet only consists of 1 sheet, columns A-M, rows 1-172.)

I realize that I need to minimize API calls, but I have seen these sorts of calls reproduced many times without reference to rendering the spreadsheets useless for typical editing.

The lag time is very noticeable, 1-2 whole seconds to change active cell, and 2-6 seconds for the username and timestamp to post in their respective columns. I introduced an execution time check briefly, it was reporting 170-800ms from the top of the function to the bottom even while the experience was much longer.

Why is this script taking so long to execute?

function onEdit(e) {
    var username = Session.getActiveUser().getUsername();
    var datestamp = new Date();  

    var s = e.source.getActiveSheet();
    var sheetName = s.getName();
    var c = s.getActiveRange().getColumn();
    var r = s.getActiveRange().getRow();

  if( sheetName !=  'Image Timeline and Status') { //Name of statistics page

    if (c == 8) {                       
        s.getRange(r,5).setValue(username);
        s.getRange(r,13).setValue(datestamp);
        }
    if(c == 9) {                       
        s.getRange(r,6).setValue(username);
        s.getRange(r,13).setValue(datestamp);
        }
    if(c == 10) {

        s.getRange(r,7).setValue(username);
        s.getRange(r,13).setValue(datestamp);
        }
      }
  }
share|improve this question
2  
I figured it out - it was because I had pasted data from an excel spreadsheet. When I cleared formatting, this eliminated all lag. All scripts working quickly now. –  watersjk Jun 12 '13 at 19:00

1 Answer 1

Code

I've reduced your code to the following:

function onEdit(e) {
  var sName = e.source.getActiveSheet().getName();

  if(sName !=  'Image Timeline and Status') {
    var s = e.source.getActiveSheet();
    var c = s.getActiveRange().getColumnIndex();
    var r = s.getActiveRange().getRowIndex();

    if(c == 8 || c == 9 || c == 10) {
      var username = Session.getActiveUser().getUsername();
      var datestamp = new Date(); 
      var rCol = 5 + (c-8);
      s.getRange(r,rCol).setValue(username);
      s.getRange(r,13).setValue(datestamp);
    }
  }
}

Remarks

The user name (getUsername) should be created, only if the criteria of the columns are met. The same accounts for the date. Therefore these two are placed within the second IF statement.

It's also valid for the column and row indices. These are only created when the sheetname criterion is met.

Given the logic of the IF statements and the additional check, I've come up with only two IF statements.

The changes made, follow logic and will minimize API calls, but the effect on the execution time will be minimal.

share|improve this answer

Your Answer

 
discard

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

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