App Script cell comparison does not work

Question

I am currently working on a custom timetracker for my worktime.

To clean up the data inserted by another script I need to delete any lines between the arrival and the way home, which is done with App Script.

The mistake seems to be in the comparison of the previous value to the value of the current row, as seen in the code below.

I have already searched the web for other cases, where most often a mistake was made comparing the variables instead of the values. As I am using "getValue()" I am unsure where my mistake is.

I have attached the log file generated while running.

I am thankful for any notes and tips, especially since I am just starting out with App Script.

[18-08-08 05:50:06:542 PDT] B2:B rows: 10
[18-08-08 05:50:06:625 PDT] curr_date: Tue Jul 03 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:731 PDT] previous_date: Tue Jul 03 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:733 PDT] new day found, updating to: Tue Jul 03 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:734 PDT] Deleting 0 middle elements
[18-08-08 05:50:06:736 PDT] curr_date: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:737 PDT] previous_date: Tue Jul 03 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:738 PDT] new day found, updating to: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:739 PDT] Deleting 0 middle elements
[18-08-08 05:50:06:741 PDT] curr_date: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:742 PDT] previous_date: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:744 PDT] new day found, updating to: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:744 PDT] Deleting 0 middle elements
[18-08-08 05:50:06:746 PDT] curr_date: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:747 PDT] previous_date: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:748 PDT] new day found, updating to: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:749 PDT] Deleting 0 middle elements
[18-08-08 05:50:06:751 PDT] curr_date: Wed Aug 08 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:752 PDT] previous_date: Wed Jul 04 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:753 PDT] new day found, updating to: Wed Aug 08 2018 00:00:00 GMT+0200 (CEST)
[18-08-08 05:50:06:754 PDT] Deleting 0 middle elements
[18-08-08 05:50:06:755 PDT] skipping empty row: 8
[18-08-08 05:50:06:757 PDT] skipping empty row: 9
[18-08-08 05:50:06:758 PDT] skipping empty row: 10
[18-08-08 05:50:06:760 PDT] skipping empty row: 11
[18-08-08 05:50:06:761 PDT] All done, stopping

function del_duplicate()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("raw");
  var range = sheet.getRange(col_dates);    // TODO: correct to use only filled cells
  
  var n_rows = range.getNumRows();
  
  Logger.log(col_dates + " rows: " + n_rows);
  
  var previous_date = range.getCell(1, 1);
  var middle_dates = [];
  var curr_date;
  
  for (var i = 2; i <= n_rows; i++) // start at second 
  {
    curr_date = range.getCell(i, 1);
    
    if(curr_date.getValue() === "")
    { 
      Logger.log("skipping empty row: " + curr_date.getRow());
      continue;
    }
    
    Logger.log("curr_date: " + curr_date.getValue());
    Logger.log("previous_date: " + previous_date.getValue());
    
    if (previous_date.getValue() === curr_date.getValue())  // same day as last value    WTF!! Never equal 
    {
      Logger.log("equal day found, pushing: " + curr_date.getValue());
      middle_dates.push(curr_date);   //add to end     
    }
    else // new day in series
    {
      Logger.log("new day found, updating to: " + curr_date.getValue());
      previous_date = curr_date;
      
      // --- delete old ones
      middle_dates.pop(); // don't delete last value
      
      Logger.log("Deleting " + middle_dates.length + " middle elements")
      
      for (var i_array = 0; i_array < middle_dates.length; i++)
      {
        var del_row = middle_dates[i_array].getRow();
        Logger.log("Deleting row " + del_row )
        //sheet.deleteRow(del_row);
      }
      
      // empty list for next date
      middle_dates = [];
            
    }

  }
  
  Logger.log("All done, stopping");
}

Read about comparing dates in Javascript. If you store the result of `getValue()` you'll note that it is a JavaScript `Date`. — tehhowch, Aug 08 '18 at 19:32
Possible duplicate of [Compare two dates with JavaScript](https://stackoverflow.com/questions/492994/compare-two-dates-with-javascript) — tehhowch, Aug 08 '18 at 19:36
@tehhowch That was exactly my problem. I wasn't aware that I could not compare the raw cell value, I have added the updated code as an answer. Thanks! — Robin Modisch, Aug 09 '18 at 11:17
In general you can, though you have to be careful when using strict equality comparison of JavaScript numbers to numbers from the sheet - the spreadsheet numbers are all `floats`, i.e. `1.0` is not strictly equal to `1` — tehhowch, Aug 09 '18 at 11:46

score 0 · Accepted Answer · answered Aug 09 '18 at 11:17

The helpful comment of @tehhowch lead me to investigate further.

Apparently the value of cells formatted as a date is a Date object from JavaScript. I have attached the updated code below as a reference to others having the same problem.

Thank you again for your notice!

col_dates = "B2:B"
col_times = "C2:C"

function register() 
{  
  SpreadsheetApp.getUi().createAddonMenu()
      .addItem('Delete duplicates', 'del_duplicate')
      .addToUi();
  Logger.log("Registered UI");
}

function del_duplicate()
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("raw");
  var range = sheet.getRange(col_dates);    // TODO: correct to use only filled cells
  
  var n_rows = range.getNumRows();
  
  Logger.log(col_dates + " rows: " + n_rows);
  
  var previous_date = range.getCell(1, 1);
  var middle_dates = [];
  var curr_date;
  
  for (var i = 2; i <= n_rows; i++) // start at second 
  {
    curr_date = range.getCell(i, 1);
    
    if(curr_date.getValue() === "")
    { 
      Logger.log("skipping empty row: " + curr_date.getRow());
      continue;
    }
    
    // ------
    var prev_date_value = previous_date.getValue();
    var curr_date_value = curr_date.getValue();
    
    //Logger.log("curr_date: " + curr_date_value);
    //Logger.log("previous_date: " + prev_date_value);
    
    if (prev_date_value.getDate() == curr_date_value.getDate())  // JS Date objects instead of cell value
    {
      Logger.log("equal day found, pushing: " + curr_date_value);
      middle_dates.push(curr_date);   //add to end     
    }
    else // new day in series
    {
      //Logger.log("new day found, updating to: " + curr_date.getValue());
      previous_date = curr_date;
      
      // --- delete old ones
      middle_dates.pop(); // don't delete last value
      
      Logger.log("Deleting " + middle_dates.length + " middle elements")
      
      for (var i_array = 0; i_array < middle_dates.length; i_array++)
      {
        var del_row = middle_dates[i_array].getRow();
        Logger.log("Deleting row " + del_row )
        sheet.deleteRow(del_row);
      }
      
      // empty list for next date
      middle_dates = [];
            
    }

  }
  
  Logger.log("All done, stopping");
}

App Script cell comparison does not work

1 Answers1