Count Number of Colored Cells with onChange

Question

I have a function which counts number of cells in a column and returns the number in a cell.

function countColoredCells(countRange,colorRef) {
  var activeRg = SpreadsheetApp.getActiveRange();
  var activeSht = SpreadsheetApp.getActiveSheet();
  var activeformula = activeRg.getFormula();
  var countRangeAddress = activeformula.match(/\((.*)\,/).pop().trim();
  var backGrounds = activeSht.getRange(countRangeAddress).getBackgrounds();
  var colorRefAddress = activeformula.match(/\,(.*)\)/).pop().trim();
  var BackGround = activeSht.getRange(colorRefAddress).getBackground();
  var countCells = 0;
  for (var i = 0; i < backGrounds.length; i++)
    for (var k = 0; k < backGrounds[i].length; k++)
      if ( backGrounds[i][k] == BackGround )
        countCells = countCells + 1;
  return countCells;
};

function getFirstCellInRow() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var firstCell = activeCell.offset(0, 1-activeCell.getColumn());
  return firstCell;
}

function getFirstAppt() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var activeCell = sheet.getActiveCell();
  var firstAppt = activeCell.offset(activeCell.getRow()+4, activeCell.getColumn()+4);
  return firstAppt  
}

I then put =countColoredCells(getFirstAppt(),getFirstCellInRow()) in a cell to return a number, which gives me a range error. For the record, =countColoredCells(G26:G,A19) works fine.

Additionally, the function does not fire when I add new colored cells in the column due to the nature of the function. How do I fire countColoredCells on this cell with onChange function? For the record, the location of where this cell is will change everyday, which is why I need to fire the function itself on the cell. I just don't know how to fire a function onChange.

function onChange(e) {
  if (e.range.getColumn() == 7 && e.changeType == 'FORMAT' )
  {
    return
  }
}

I'm not sure how to get it to fire functions on the cells that have that function.

If the linked duplicate doesn't answer your question , [edit] to show why it doesn't. — TheMaster, Jan 20 '21 at 12:07
Really I didn’t know that I’ll have to go check that out thanks for pointing that ou — Cooper, Jan 20 '21 at 15:16
I'm must apologize for knowing that which I did not know and assuming that I did. That's the wonderful thing about making a comimitment and providing an answer knowing that if you're wrong you will get to enjoy the fruits of learning something new because you are surrounded by other volunteers that know more than you. Thank you @TheMaster. — Cooper, Jan 20 '21 at 15:35
It's seems to be limited to the installable version. Oh I guess onChange is only installable. I guess I just don't use these things very much. I always try to walk lightly upon the server usage. — Cooper, Jan 20 '21 at 15:42

score 1 · Answer 1 · answered Jan 20 '21 at 08:23

The onChange simple trigger was deprecated a while ago. Instead, use the installable triggers. Those still have an onChange trigger which should fire when you make format changes.

You could set up the trigger directly using the user interface: How do you create an installable trigger for a Google Apps Script?

Or install them programatically:

function createSpreadsheetOpenTrigger() {
  var ss = SpreadsheetApp.getActive();
  ScriptApp.newTrigger('myOnChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
}

function myOnChange(e) {
  if (e.range.getColumn() == 7 && e.changeType == 'FORMAT' )
  {
    return
  }
}

Count Number of Colored Cells with onChange

1 Answers1