google-sheetsgoogle-apps-script

Function gets wrong value when been called Apps Script


So Im trying to make a function what searches for empty cell. Funny enough it works perfectly in other script but refuses in main one. It seemes like for some reason when I call a function which requires Sheet and Range in order to work, Range is taken by function correctly (example: 'C4:C34'). But the sheet is not. It becomes just "Sheet" in log. But I do give a function "sheet3". Ive tried to give sheet`s name with ' ', to rename "sheet3" to "sheettrd" but nothing seems to help.

Here is the function itself:

function findEmptyCell(sheet, range) {
  var checkRange = transformCords(range);
  Logger.log(sheet);
  Logger.log(range);
  Logger.log(checkRange);
  for (var i = checkRange[1][0]; i < checkRange[1][1]; i++) {
    if ((sheet.getRange(checkRange[0][0] + i).isBlank())) {
      return sheet.getRange(checkRange[0][0] + i).getA1Notation();
    }
  }
}

And where I call it:

var startPos = transformCords(findEmptyCell(sheet3, 'С4:С34'));
for (let i = 0; i < 4; i++) {
  sheet3.getRange(arrDay[i]+startPos[1])
  .setValue(sheet1.getRange('K' + (40 + i)).getValue());
}

Here is the error what appears

And if its important:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet1 = ss.getSheetByName('Журнал');  
var sheet2 = ss.getSheetByName('Бар');  
var sheet3 = ss.getSheetByName('Итоги');

Solution

  • You observantly already tried a plain Latin sheet name (as I guess you're aware of the possibility that the problem is related to Cyrillic characters), which I also suspected initially -- but to no effect. However, the characters actually are the core of the issue, just not where you think it is.

    The problem is almost certainly because of this string 'С4:С34'. The С character used here is an Es Cyrillic character and not a Latin C. Given the identical appearance, this is very hard to spot! They have different Unicode character codes, and the Google APIs account only for the Latin C. I used a simple Unicode detector to highlight the problem.

    Try 'C4:C34' instead. This is the real source of the "Exception: Range not found".

    Note that Logger.log(sheet); appearing as simply Sheet is expected. Sheet here is a reference to the name of the constructor of the object you are logging (analogous to the name of the "class" of the object) and not the name of the sheet that represents it in the Google APIs. It would also show that if you were to log any Sheet object. So in this case, it is a "red herring".