Very strange Google Apps Script behavior

Question

I am seeing some very strange behavior with Google Apps Script. All I am trying to do is parse some rows in my sheet and if for any row, if the date is > 1/1/1999 then add a dropdown which pre populates some Error Value. The strange behavior here is that this always populates the dropdown one row above the row which satisfies the date check condition. So for example if row #5 satisfies the condition, the dropdown is populated in row #4. Not sure why this is happening. I also tried to do this

var range = sheet.getRange(i+1, 14);

but that doesn't seem to show anything on the screen. I am unable to understand this strange behavior. Any help will be appreciated.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[1];

var dataRange = sheet.getDataRange();
var values = dataRange.getDisplayValues();

for (var i = rowNum; i < values.length; i++) {

if (new Date(values[i][9]).getTime() > new Date('1/1/1999').getTime()){
  SpreadsheetApp.getUi().alert(values[i][2]);

  var range = sheet.getRange(i, 14);
  var rule = SpreadsheetApp.newDataValidation().requireValueInList(['Error Value'], true).build();
  range.setDataValidation(rule);
}

Anees Hameed Anees Hameed · Accepted Answer · 2017-03-06T19:26:28

Use

var range = sheet.getRange(Number(i)+1, 14);

When you are using i+1 and when i=20, i+1 will become 201 and you will not seeing any visible difference on the screen. row 201 will be far down your screen.

In JavaScript, numbers and strings will occasionally behave in ways you might not expect. It is always good to take precaution before doing operation on variables.

Also read https://autotelicum.github.io/Smooth-CoffeeScript/literate/js-intro.html#operations-on-numbers-strings

Very strange Google Apps Script behavior

2 Answers