Results 1 to 1 of 1

Thread: Google Sheets - Script Dates
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Apr 2018
    Location
    UK
    Posts
    234
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Google Sheets - Script Dates

    Hi All


    I am having a problem with making a script. I am making a script that will delete a row a week after something has been approved.

    As of right now i have this which when a dropdown is selected as "YES", another cell gets the current date entered:

    Code:
    function onEdit() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var activeSheet = ss.getActiveSheet();
      if( activeSheet.getName() == "TASKS" ) {
        var activeRange = ss.getActiveRange();
        var activeRow = activeRange.getRow();
        var activeColumn = activeRange.getColumn()
        
        if( activeColumn == 13 && activeRow >= 2 ) { 
          if ( activeRange.getValue() == "YES" ) {
            activeSheet.getRange(activeRow, activeColumn + 1).setValue(new Date());
          }
          else if( activeRange.getValue() != "YES" ) {
          activeSheet.getRange(activeRow, activeColumn + 1).setValue("Not Approved");
          }
        }
      }
    };

    This would then be followed by this which i would run once a day or so:

    Code:
    function isValidDate(d) {
      if ( Object.prototype.toString.call(d) !== "[object Date]" )
        return false;
      return !isNaN(d.getTime());
    }
    
    
    function DeleteEditorialRows() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var taskSheet = ss.getSheetByName("TASKS");
      var lastRow = taskSheet.getLastRow();
      var formattedDate = Utilities.formatDate(new Date(), "GMT", "MM-dd-yyyy");
      
      for (var i = lastRow; i >= 2; i--){
        if (taskSheet.getRange(i, 13).getDisplayValue() == "YES"){
          if (taskSheet.getRange(i, 12).getDisplayValue() == "YES"){
            if((new Date() - taskSheet.getRange(i, 14).getValue()) >= 7 && isValidDate(taskSheet.getRange(i, 14).getValue())){
              taskSheet.deleteRows(i);
            }
          }
        }
      }
    };
    The problem im having is with the section i have highlighted in red.

    When i test the following:

    Code:
    Logger.log(new Date() - taskSheet.getRange(i, 14).getValue());
    The answers i am getting are like 152786.0 when i feel like i should be expecting numbers less than 1 since not even a day has passed.

    Any help is appreciated.
    Last edited by Finalfight40; Sep 16th, 2019 at 08:20 AM.
    VBA is ten percent luck
    Twenty percent skill
    Fifteen percent concentrated power of will
    Five percent pleasure
    Fifty percent pain
    And a hundred percent reason to remember the name

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •