CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Brian - RGY Studio
Brian - RGY Studio

Reputation: 505

Simple Google script running strangely slow

I have a Sheets database setup I'm building, and some of the code is running extremely slowly (sometimes, not 100% of the time). This one in particular befuddles me:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var rc = s.getActiveCell().getColumn();
  var rr = s.getActiveCell().getRow()
  var job = s.getRange(2,2).getDisplayValue();
  if (rc == 2 && rr == 2 && s.getName()=='Entry' && job != "") {
    s.getRange("C1").setValue("LOADING...");
    SpreadsheetApp.flush();
    var helperSheet = ss.getSheetByName("Helper");    
    var testVals = helperSheet.getRange("E1:E").getValues();
    var firstRow = testVals.filter(String).length+1;
    var workData = helperSheet.getRange("E3:J"+firstRow).getDisplayValues();
    s.getRange("C5:H").clearContent(); //tech to end time (work chart)
    s.getRange(5,3,firstRow-2,6).setValues(workData); //tech to end time (work chart)
    SpreadsheetApp.flush();
    s.getRange("C1").clearContent();    
  }
}

After I cleaned it up a bit, I got it to complete in 2-3 seconds normally, but sometimes (and 100% of the time on my client's side), it times out at 30 seconds. Is there anything you can see that I've done wrong here that is slowing this down so much? I know it has a few too many function calls, but I've minimized those as much as I can. My only thought at this point is that the flush() calls are an issue. I use those so that "LOADING" appears as early as possible, and only clears once everything else is complete. Without them, it only appears for a half a second.

Any help would be wonderful as it may help guide me with the other scripts that are also running to slowly.

Also, after reading through various other answers here for other scripts that run/ran slowly, I've come across the idea of using installable triggers. Is there a reason that would make my script(s) run more quickly?

UPDATE: I realized I could move the LOADING text add/delete calls to outside of the if test and thus remove the flush() calls. This way, editing another cell adds and removes the text simultaneously, so the user doesn't see it. And on my computer, it's running very quickly now. However, my client hasn't tested it yet. So, I think there's two questions now: 1) Does flush() really slow down a script that much? 2) Is there anything else silly going on in my code to make it slow...sometimes?

The new code:

function onEdit(e){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var s = SpreadsheetApp.getActiveSheet();
  var rc = s.getActiveCell().getColumn();
  var rr = s.getActiveCell().getRow()
  var job = s.getRange(2,2).getDisplayValue();
  s.getRange("C1").setValue("LOADING...");
  if (rc == 2 && rr == 2 && s.getName()=='Entry' && job != "") {
    var helperSheet = ss.getSheetByName("Helper");    
    var testVals = helperSheet.getRange("E1:E").getValues();
    var firstRow = testVals.filter(String).length+1;
    var workData = helperSheet.getRange("E3:J"+firstRow).getDisplayValues();
    s.getRange("C5:H").clearContent(); //tech to end time (work chart)
    s.getRange(5,3,firstRow-2,6).setValues(workData); //tech to end time (work chart)
  }
    s.getRange("C1").clearContent();    

}

Upvotes: 1

Views: 290

Answers (1)

Cooper
Cooper

Reputation: 64062

Try this:

function onEdit(e) {
  const sh = e.range.getSheet();
  const job = sh.getRange(2, 2).getDisplayValue();
  if (e.range.columnStart == 2 && e.range.rowStart == 2 && sh.getName() == 'Entry' && job != "") {
    sh.getRange("C1").setValue("LOADING...");
    let hsh = e.source.getSheetByName("Helper");
    let workData = hsh.getRange("E3:J" + hsh.getLastRow()).getDisplayValues();
    sh.getRange(5, 3, sh.getLastRow() - 4, 6).clearContent();
    sh.getRange(5, 3, workData.length, 6).setValues(workData);
  }
  sh.getRange("C1").clearContent();
}

Upvotes: 1

Related Questions