Identify specific row based on Date

Question

What this code does is to identify the row of the array based on the input (Date) and return the values associated with the input date.

However, this for loop is not working as it does not respect the if condition and always returns the last row of the array.

function viewData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Overall Cashflow"); //Data entry Sheet
  var datasheet = ss.getSheetByName("Cashflow Tracker Data"); //Data Sheet
  var data = datasheet.getDataRange().getValues();
  var date = formSS.getRange("H5").getDisplayValue();
  
  for (var i = 0; i < data.length; i++){ 
        if (data[i][0] == date) {
           break; 
        }
        var oldinflow = data[i][1];
        var oldoutflow = data[i][2]; 
  }
  

  formSS.getRange("H8").setValue(oldinflow);
  formSS.getRange("H11").setValue(oldoutflow);
}

For a better answer please add some data sample and the corresponding expected result. — Rubén, Aug 08 '20 at 20:09

Marios · Answer 1 · 2020-08-08T13:31:26.300

1

You need to get the valueOf() of the date object instead.

Try this:

function viewData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var formSS = ss.getSheetByName("Overall Cashflow"); //Data entry Sheet
  var datasheet = ss.getSheetByName("Cashflow Tracker Data"); //Data Sheet
  var data = datasheet.getDataRange().getValues();
  var date = formSS.getRange("H5").getValue().valueOf();
  
  for (var i = 0; i < data.length; i++){ 
        if (data[i][0].valueOf() == date) {
           break; 
        }
        var oldinflow = data[i][1];
        var oldoutflow = data[i][2]; 
  }
  

  formSS.getRange("H8").setValue(oldinflow);
  formSS.getRange("H11").setValue(oldoutflow);
}

edited Aug 08 '20 at 13:31

answered Aug 08 '20 at 11:11

Marios

26,333
8
32
52

It didn't work :( – Tan Wei Ming Aug 08 '20 at 12:11
2

`==` will test two object's addresses in memory. When you retrieve one value via `.getValue()` and the "same" value via `.getValues()`, the resulting memory locations will compare false. To use `==` with Date objects, you have to say `range.getValue().valueOf() == bigRange.getValues()[i][j].valueOf()`. – dwmorrin Aug 08 '20 at 13:02

score 0 · Answer 2 · answered Aug 08 '20 at 20:06

getValues() might return Date object for cells holding "dates", by the other hand getDisplayValue() will return a string. This might explain why (data[i][0] == date is no working.

To prevent on quick and dirty solution that might be good enough is instead of using getValues(), to use getDisplayValues().

Identify specific row based on Date

2 Answers2

Linked