CODEWITHSUNDEEP
javascriptgoogle-apps-script
sojim2
sojim2

Reputation: 1307

Google App Script: Set cell blank if time is older than one hour

In this spreadsheet: https://docs.google.com/spreadsheets/d/1pExqHJQubnSPDKczkF9HMA2QN1cxTYmzyewQdugDRYs/edit#gid=1512058574

I have normal & epoch time:

enter image description here

I'm looking to clear NormalTime & EpochTime cells if they're older than one hour.

So I've written this script that deletes cells older than one hour:

function removeOldProcessTime() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Time");
  var processTime = sheet.getRange("B2:B").getValues();
  var now = new Date();
  var ONE_HOUR = 60 * 60 * 1000; /* ms */
  var oneHourAgo = now - ONE_HOUR;

      for (var i = 0; i < 300; i++) 
    {
      if(processTime[i] < oneHourAgo)
      {
        sheet.getRange(i + 2, 1).setValue('');
        sheet.getRange(i + 2, 2).setValue('');
      }
    }
}

However, when I run, only row 8 is deleted:

enter image description here

What am I doing wrong?

Requirement: I'm looking to delete cells that are older than one hour.

Data:

NormalTime  EpochTime
8/24 11:27:34 AM    1566671254626
8/24 11:50:43 AM    1566672643507
8/24 3:27:34 PM 1566685654609
8/24 5:27:34 PM 1566692854531
8/24 6:27:34 PM 1566696454446
8/24 8:27:34 PM 1566703654460
8/24 9:27:34 PM 1566707254819
8/24 10:27:34 PM    1566710854416

Upvotes: 0

Views: 127

Answers (1)

sojim2
sojim2

Reputation: 1307

Looks like I needed to format column B to number format of 0. GAS automatically converts the cell into date and so in the script, it sees the EpochTime as normal formatted date and it had trouble converting it back to Epoch time

Upvotes: 2

Related Questions