0

I have an onEdit function running fine into googlesheets, it is basically a countdown timer and displays the countdown into a cell. However after around 30 seconds of running, the display no longer updates and just freezes at whatever time it was at, while the countdown still runs down in the background. To have the display "wake up", I have to manually update any cell in the spreadsheet.

I have tried having a RANDBETWEEN formula to recalculate every second the timer ticks down, however this formula update does not "wake up" the countdown display. From my limited understanding only a manual user update will. Is there a way to have the code automatically update itself such that I do not require a manual user update? If so how.

My countdown timer code:

function onEdit(){
  const d = new Date();
  currTime = d.getTime();
  
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName('Sheet3');

   if (sh.getRange('A1').getValue() == ""){
    sh.getRange('A1').setValue(currTime)
   }

    else {
    sh.getRange('A4').setValue(Math.round((sh.getRange("A1").getValue() - currTime)/-1000))
    }
  
  SpreadsheetApp.flush();


//if (e.range.getA1Notation() == "G11"){

    var ss = SpreadsheetApp.getActive();
    var sh = ss.getSheetByName('Sheet3');
    var m = sh.getRange('F2').getValue();
    var s = sh.getRange('G2').getValue();


  const countdown = (m*60 + s) - sh.getRange('A4').getValue()
  const cdm = Math.floor(countdown /60);
  const cds = countdown % 60;
  sh.getRange('D4').setValue(cdm + ":" + cds);
  SpreadsheetApp.flush();


  if (sh.getRange('F7').getValue() == "r"){
      sh.getRange('A1').setValue("")
      sh.getRange('F7').setValue("")
      SpreadsheetApp.flush();
      onEdit();
  }
  else if (countdown <= 0){

  }
   else if (sh.getRange('F7').getValue() != "s"){
      SpreadsheetApp.flush();
      onEdit()

  }

 //}

}

My Referenced spreadsheet: The referenced spreadsheet

1

1 Answer 1

0

The onEdit(e) function is a simple trigger. Its runtime is limited to 30 seconds. See Quotas for Google Services.

It would be better to implement your countdown timer through some other means, perhaps through a sidebar that runs a JavaScript function with setInterval().

2
  • The runtime for a custom function is 30 seconds; but isn't the runtime for a script 6 minutes?
    – Tedinoz
    Feb 11, 2022 at 0:48
  • Simple triggers are restricted to 30 seconds. It is a miracle that the code the OP quotes runs even that long, because it is recursive and a busy beaver. The stack will get very large. Feb 11, 2022 at 6:48

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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