1

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;
}
  • Related:webapps.stackexchange.com/questions/10629/… – Rubén Sep 8 '16 at 14:30
  • =isColor(cell("address",A2)) works fine, right? – Rubén Sep 8 '16 at 14:37
  • It doesn't work. It always returns true and the log commands provide no information other than "undefined" – John Faig Sep 8 '16 at 15:04
  • If you are using the debug tool, you should use an auxiliary function to call the main function in order to pass the required arguments, i.e. function test() { isColor('A2'); } – Rubén Sep 8 '16 at 15:30
2

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.

  • Thanks! I didn't see any documentation about how difficult it is to pass an argument to a UDF. How would I handle the formula referencing a cell on a different sheet? – John Faig Sep 8 '16 at 15:41
  • @JohnFaig: Does this answer was helpful for you? It was "updated" by the "community user". – Rubén Nov 7 '16 at 18:03
1

I found some different partially workaround, it's not perfect but I think I could be helpful: instead of sending as a parameter I sent it as a string, for example, when choosing the range it will be =example(a1:a3) I used: example("a1:a3") when I first selected the range and then changed it manually.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.