CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
Akari
Akari

Reputation: 15

Custom Function to get Cell Value by Cell Color in Google Sheet

I have conditional formatting in my google sheet to fill certain cells with different colors according to their values(dates). I made custom functions GetHex() to get cell color and GetColoredCellValue() to return the cell value with the hex code "#ff0000" but the latter function is not returning anything after I put a cell range in Google Sheet =GetColoredCellValue("A2:HF2")

I've never coded seriously before nor used Excel thoroughly so I'm sorry if my code does not make sense. And thank you in advance!

These are the functions i've written.

function GetHex(input) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getRange(input);
  var result = cell.getBackground();
  return result
}

function GetColoredCellValue(input){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange(input);
  var cell;
  for(var i=0; i<range.length; i++){
    for(var j=0; j<range[0].length; j++){
      cell = range[i][j];
      if(GetHex(cell)== "#ff0000"){
        return cell.getValues();
      }
    }
  }
}

When i run the code in AppScript the Execution Log shows this error

Execution log 2:08:43 PM Notice Execution started 2:08:43 PM Error
Exception: Invalid argument: range GetHex @ Code.gs:3

I wanted to get the cell value of cells with "#ff0000" bg color from each row in a different column. And the template for my spreadsheet would look like this. I attached a screenshot of what my table in spreadsheet would look like as an example.

table example

Upvotes: 1

Views: 787

Answers (1)

Tanaike
Tanaike

Reputation: 201388

Modification points:

  • In your script, range of var range = ss.getRange(input) is a Class Range object. So, range.length returns undefined. I thought that this might be the reason for your current issue.
  • And also, when GetHex(cell) is used in a loop, the process cost becomes high.

When these points are reflected in your script, how about the following modification?

Modified script:

function GetColoredCellValue(input) {
  var ss = SpreadsheetApp.getActiveSheet(); // or SpreadsheetApp.getActiveSpreadsheet()
  var range = ss.getRange(input);
  var backgroundColors = range.getBackgrounds();
  var values = range.getValues();
  var dstValues = values.map((r, i) => (r.map((c, j) => backgroundColors[i][j] == "#ff0000" ? c : null)));
  return dstValues;
}

  • When this script is used as =GetColoredCellValue("A2:HF2"), the cell values of the background color of #ff0000 are returned.

  • When you want to ignore the cells except for #ff0000, please modify as follows.

    • From

        var dstValues = values.map((r, i) => (r.map((c, j) => backgroundColors[i][j] == "#ff0000" ? c : null)));

    • To

        var dstValues = values.flatMap((r, i) => (r.flatMap((c, j) => backgroundColors[i][j] == "#ff0000" ? c : [])));

References:

Upvotes: 0

Related Questions