CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Tom
Tom

Reputation: 233

Bringing the cell's location rather than it's value

I'm trying to create a custom formula for Google Sheets using Apps Script. The function will return the Hexadecimal background color of the cell (e.g. =getHEX(B1) will return the color of cell B1).

My problem is that I can bring the value of the cell, not the location. If I use quotes in the function it works (=getHEX("B1")) but I wonder how can I avoid using the quotes and still get the value of the cell.

I tried to use the A1Notation function and it supposedly bring the location (B1) but when added to the function I still get the same error (Range not found). Below is the function as a reference:

function getHEX(cellReference) {   
   const getCell = SpreadsheetApp.getActiveSheet().getRange(cellReference.toString()).getBackground();
   return getCell;
}

Upvotes: 0

Views: 1932

Answers (2)

Salix
Salix

Reputation: 1384

Following Marios' Answer, I'd like to add a solution 3 which does simplify the function like OP asked.

I basically had the same issue where I wanted to just pass the cell as a range instead of a value and went with:

Google Apps Script:
function cell_getBackground(cell_address) {
  var bg = SpreadsheetApp.getActive().getRange(cell_address).getBackground();
  return bg;
}
Named Function:

On the spreadsheet page: Data > Named Functions > Add new function >

Function name:
getHEX

Argument placeholders:
cellRef

Formula definition:
=cell_getBackground(cell("address", cellRef))

Google Sheets:
=getHEX(B1)

Upvotes: 1

Marios
Marios

Reputation: 27350

Issue:

  • Unfortunately, there is no straightforward way to do that since =getHEX(B1) would pass directly the value, and not the actual range that is used in google apps script to get the background.

Simple workaround:

  • You can use ADDRESS, ROW and COLUMN to get the reference as a string in the google sheets side and pass it as an argument in your custom function so it can be used by the getRange(a1Notation) method.

Solutions:

Solution 1:

Google Apps Script:

function getHEX(cellReference) {
   const getCell = SpreadsheetApp.getActiveSheet().getRange(cellReference).getBackground();
   return getCell;
}

Google Sheets:

=getHEX(ADDRESS(row(E4),column(E4),4))

to get the background color of cell E4. You can also drag down the formula in this way.

enter image description here

Solution 2:

Use the coordinates directly but structure the function to accept two arguments (x and y).

Google Apps Script:

function getHEX(x,y) {
   const getCell = SpreadsheetApp.getActiveSheet().getRange(x,y).getBackground();
   return getCell;
}

Google Sheets:

=getHEX(row(E4),column(E4))

enter image description here

Upvotes: 2

Related Questions