I set up this custom function that returns the background color of a certain cell:

/* Returns the Hexadecimal value of a cell's background color.
 *
 * @param {number} row The cell's row number.
 * @param {number} column The cell's column number.
 * @return The Hexadecimal value of the cell's background color.
 * @customfunction
 */
function BGHEX(row, column) {
  var background = SpreadsheetApp.getActive().getDataRange().getCell(row, column).getBackground();
  return background;
}

At first it worked, but when I applied the script to a button, it started throwing the following mistake:

Error
Exception: The parameters (null,null) don't match the method signature for SpreadsheetApp.Range.getCell.

Any one has an idea of why it stopped working all of a sudden and how to fix it?

CodePudding user response：

You might be able to do it like this

function BGHEX() {
  let r = SpreadsheetApp.getUi().prompt("Get Row and Column","Enter row , column",SpreadsheetApp.getUi().ButtonSet.OK);
  let t  = r.getResponseText().split(',');
  var background = SpreadsheetApp.getActiveSheet().getRange(t[0], t[1]).getBackground();
  Logger.log(background);
  return background;
}

or this way by selecting the cell

function BGHEX() {
  var background = SpreadsheetApp.getCurrentCell().getBackground();
  Logger.log(background);
  return background;
}

CodePudding user response：

The function has two parameters, row and column, both are required as they are used as the getCell parameters.

To use this in a button, you have to someway provide the required parameters:

1. provide default values,
2. use a user interface, i.e. a prompt or a dialog to ask the user for the parameters

Or set the cell to be used with getBackground() without requiring the row and column parameters, i.e. by using getActiveCell.