Google Sheets – Fix ‘Range Not Found’ Error in User-Defined Function

google sheetsgoogle-apps-script

I'm writing a UDF and get an error. From the documentation, it seems straightforward. My goal is to check the background color of a cell. The code is shown below and the calling format is "isColor(cell)". I get an error (Range not found (line 5, file "Code") on the line var cell = ...

I tried to pass the cell address as a string by making the calling format isColor(cell("address",A2))

function isColor(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  Logger.log(input);
  var cell = sheet.getRange(input);
  var color = cell.getBackground();
  Logger.log(cell);
  color = cell.getBackground(); 
  Logger.log(color);
  var result = false;
  if (color == '#ffffff') {
      result = false;}
      else {
      result = true;
      }
  return result;
}

Best Answer

Short answer

Google Sheets pass custom function arguments as values, not as objects, in this case not as a cell/range.

Workaround

Use SpreadsheetApp.getActiveRange().getFormula() to get the cell formula and extract the reference from it.

Example

Note: Custom functions recalculates when their arguments changes their values. Changing the background of the referenced cell will not make that Google Sheets recalculate them.

function isColor(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var activeRange = SpreadsheetApp.getActiveRange();
  var activeSheet = activeRange.getSheet();
  var formula = activeRange.getFormula();
  try {
    var rangeA1Notation = formula.match(/=\w+\((.*)\)/i)[1].split('!');
  } catch(e) {
    throw new Error('The reference isn\'t valid');
  }
  //
  Logger.log(rangeA1Notation);
  var cell = sheet.getRange(rangeA1Notation);
  var color = cell.getBackground();
  Logger.log(cell);
  color = cell.getBackground(); 
  Logger.log(color);
  var result = false;
  if (color == '#ffffff') {
      result = false;}
      else {
      result = true;
      }
  return result;
}

Explanation

In the Google Sheets / Google Apps Script argot, user-defined functions are called custom functions.

From the custom functions official documentation:

Data types

Google Sheets stores data in different formats depending on the nature of the data. When these values are used in custom functions, Apps Script treats them as the appropriate data type in JavaScript. These are the most common areas of confusion:

  • Times and dates in Sheets become Date objects in Apps Script. If the spreadsheet and the script use different time zones (a rare problem), the custom function will need to compensate.
  • Duration values in Sheets also become Date objects, but working with them can be complicated.
  • Percentage values in Sheets become decimal numbers in Apps Script. For example, a cell with a value of 10% becomes 0.1 in Apps Script.

IMHO the documentation should include a note regarding cell/range references as the Spreadsheet service includes Range as class.

Related Topic