How to get a value if the present time is within a time window?

Question

I have been trying to copy the value in A9 into the cell in column D, when the current time (now or time.new) corresponds to the time specified in the same row in column C.

I managed to get a script that pulls the value D1 once in an hour into the last cell in column D, which would be perfectly fine if the "time-driven" trigger would not pick a random time in the hour. It needs to be exactly 2 minutes past every full hour (+/- 30 seconds). So I thought to make the script run every minute and paste the value in a cell if the time in colum C is within 1h2m30s and 1h1m30s before the present time... But with my script there seems to be an error.

Please find the test document here, where the function is currently being executed once every hour.

The Apps Script that runs at a random time every hour is as follows (currently running):

function myFunction() {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet= ss.getSheetByName('FACTS & PREDICTIONS');
  var Direction=SpreadsheetApp.Direction;
  let aLast =sheet.getRange("D"+(sheet.getLastRow()+1)).getNextDataCell(Direction.UP).getRow();
  let val= sheet.getRange('A9').getDisplayValue();
  sheet.getRange(`D${aLast+1}`).setValue(val);
}

The new script, that I have been trying to make is as follows:

function myFunction() {
  var ss= SpreadsheetApp.getActiveSpreadsheet();
  var sheet= ss.getSheetByName('Admin Panel');
  var Direction=SpreadsheetApp.Direction;
   if(e.source.getActiveSheet().getName()=="FACTS & PREDICTIONS") {
    if(col == 4 && cell.offset(0,-1).getValue() > new Date()-TIME(1,2,30) && col == 4 && cell.offset(0,-1).getValue() < new Date()-TIME(1,1,30)){
      let val= sheet.getRange('A9').getDisplayValue();
     e.source.getActiveSheet().getRange(row,4).setValue(val);
    }
  }
}

The above script is actually patched together because I have no clue of Apps Scrips. I have no idea what is wrong with the code or what I need to change.

Edit: Just fount this, where you can make a Trigger locked to a specific minute, BUT it has +/- 15 minutes deviation. Useless...

Welccome to [so]. The expressions comparing Date objects are wrong because directly comparing them always will be false. — Rubén, May 04 '22 at 14:31
Getting a trigger to occur at exactly 2 minutes after the hour is difficult with google apps script timebased triggers. Depending upon what your goal is, it may be easier for you to create your own triggers in a sidebar utilizing clientside functions like setInterval or setTimeout. It does require that you keep the dialog open and remove the screen hiding feature. — Cooper, May 04 '22 at 14:43

Rubén · Answer 1 · 2022-05-04T15:27:08.377

Using a time-driven trigger to run every minute might be the right approch but the expressions comparing Date objects are wrong because comparing them always will be false.

As you are new to Google Apps Script, you might find worthy to spend some time learning the basics and common quirks about Google Apps Script and JavaScript, i.e. Compare two dates with JavaScript

Also you might have to rethink if Google Sheets / Apps Script is the right tool for this task specially when requiring to work with precise times / small time tolerances and requiring high reliability. This because the Google Apps Script triggers are not 100% reliable, i.e. the trigger might fail to run all and every minute, time-driven triggers have a daily total time execution quota.

How to get a value if the present time is within a time window?

1 Answers1